SQL Backup procedures...

[gcetti]

Can anyone recommend backup procedures for a SQL Server/MSDE product?
We have upgraded our program from Access to SQL & before we could just
tell our customers what .mdb files to include on their backup script.

Now with SQL, the BACKUP/RESTORE SQL commands work as backups, but
would involve a separate process for our customers to execute, when
what they know right know is to copy the .mdb files.

Is there a way they can backup the database files associated with our
program in SQL with whatever backup procedures they do? Would it be
reliable for them to just include the .mdf & .ldf files that go with
our database on their backup script - if they needed to do a restore
could they just copy that file back and the SQL/MSDE engines would
recognize it?

Our program is for SQL Server or MSDE - and in the MSDE case the user
would not know password to be able to manually run the Backup/restore
commands.

Thanks for any help,
Greg

[Erland Sommarskog]

(gc****@nauticom.net) writes:

Can anyone recommend backup procedures for a SQL Server/MSDE product?
We have upgraded our program from Access to SQL & before we could just
tell our customers what .mdb files to include on their backup script.

Now with SQL, the BACKUP/RESTORE SQL commands work as backups, but
would involve a separate process for our customers to execute, when
what they know right know is to copy the .mdb files.

Is there a way they can backup the database files associated with our
program in SQL with whatever backup procedures they do? Would it be
reliable for them to just include the .mdf & .ldf files that go with
our database on their backup script - if they needed to do a restore
could they just copy that file back and the SQL/MSDE engines would
recognize it?
Normally, you cannot backup an SQL Server database with Windows
backup. This is because the backup would not be transactionally
consistent, if there is activity in the database while the backup
is running.

However, if you set the database offline prior to taking the backup,
or simply stop SQL Server, backing up the MDF and LDF files works
fine.

I don't know about Access, but reasonly the same applies there - you
cannot just copy thy MDB file, if there are activity going on.

Note that if you do with Windows backup, you must set the database in
simple recovery, so that the transaction log is regularly truncated.
Else the disk will explode after some time.

If you want up-to-the-point recovery, then there is no alternative
to SQL Server Backup. (Save for 3rd party softwares like LiteSpeed,
but that's targeted for bigger databases.)
Our program is for SQL Server or MSDE - and in the MSDE case the user
would not know password to be able to manually run the Backup/restore
commands.

They can always log in with Windows Authentication. And, unless you
drop BUILTIN\Administrators, the users can log with Admin privs.
I mention this, in case you believe that you have your intellectual
property by a password.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp