DB Backup Methods

I am trying to better understand the process of backing up a SQL (MSDE)
database. There are several 3rd party tools on the market that include a
backup function, but are dependent on first establishing a connection to the
server. My specific questions are:
1) is it possible to merely copy (using a DOS or windows copy command) the
DB file for backup purposes? If so, is there any trick to restoring the
files?
2) what is the advantage (or requirement) of performing the backup through a
connection to the server? What is the process for creating such a backup --
does it depend on T-SQL statements?
My goal is to create a backup/restore routine within my application so that
the process is very easy for my end-users to manage and also something that
could be set up on an automated schedule.
Appreciate any help! --RS
hi RS,
Rob S wrote:
> I am trying to better understand the process of backing up a SQL
> (MSDE) database. There are several 3rd party tools on the market
> that include a backup function, but are dependent on first
> establishing a connection to the server. My specific questions are:
> 1) is it possible to merely copy (using a DOS or windows copy
> command) the DB file for backup purposes? If so, is there any trick
> to restoring the files?
actually it is... MSDE defaults all it's created databases with the
autoclose option, so that when not in use, they will be freely available for
copy tasks, but you have to consider connection pooling befor proceeding as
that can prevend direct file access... you could then stop the service, copy
the required dbs and restart it, but I do not consider this a best practice
approach.. stopping the service will provide a clean "close" of each db, and
you could even replace the data and log files of a database for a sort of
dirty restore operation, but, again, this is a dirty trick..
BTW... personally I always change that db setting to off, in order to speed
up I/O operation... and this will be a problem in this kind of management...

> 2) what is the advantage (or requirement) of performing the backup
> through a connection to the server? What is the process for creating
> such a backup -- does it depend on T-SQL statements?
yes, it relies on Transact-SQL statements, both for backup and restore
task.. and this is the preferred solution as you do not depend on particular
db settings (autoclose) and a proper backup of the db is taken in "native"
and supported mode... the server can remain up and running for other users
(only during backup, as restore requires esclusive access) supporting "hot"
backups... depending on the recovery model of the dbs, different backup
solutions are available, as full backup and differential, granting high(er)
speed recovery for faults events
for the relative statement syntax and synopsis please have a look at
http://msdn.microsoft.com/library/de...ba-bz_35ww.asp
depending on your needs you can use the SQL Server Agent to schedule backups
in order to plan the correct backup strategy based on your actual need and
critical information stored in the database, as long as depending frequency
of data changes and the like in order to plan for disaster recovery...
http://msdn.microsoft.com/library/de...kprst_63eh.asp
http://msdn.microsoft.com/library/de...kprst_7drn.asp
http://msdn.microsoft.com/library/de...kprst_7kvb.asp

> My goal is to create a backup/restore routine within my application
> so that the process is very easy for my end-users to manage and also
> something that could be set up on an automated schedule.
automated schedule should be better provided via standard SQL Server Agent
managed jobs, where you usually provide a job step of T-SQL subsystem with
the desired backup statement (and this is the way to plan for disaster
recovery strategy as you can not count on your users for manual execution of
"suggested" manual clean up tasks), where monotonic and one shot backup(s)
can be easily directly implemented in your application executing both
dynamic T-SQL statements (say from an ADO/Ado.Net connection) or via
predefined stored procedures provided by your db metaschema...
the same is true for restore operation, tha can be implemented within your
application without problems...
but restore operations are usually not that often perfomed as backup... or I
hope so :D
just a caveat, remember that sysadmin server role membership, or db_owner or
db_backupoperator database role membership is required in order to perform
backup operations, where sysadmin or dbcreator server roles membership is
required for restores, so you have to consider this kind of requirements as
well in your authorization plans...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Here's a .bat file that I run (part of a LONG run) that backs up all
teh DBs that I have on my local machine (MSDE, so no visual tool to
use)
osql is a command line utility that I think comes with SQL Server, all
installs.
You can check out the parameters of osql.exe -- -E is for logging in
via a trusted connection, I think, -S is for the server name, -Q is the
statement that you want to run.
As for the statement, you can see that it is pretty straightforward --
I haven't really investigated all the options that you can do with
incremental backups or anything, but this should get you started -- to
do this from your progra, I'm assuming VB6, then use the Shell command.
; back it up to a temp file
osql -E -Spbk01imd34\PM -Q"BACKUP DATABASE [EMDB] TO DISK =
'C:\Documents and Settings\Matt\My Documents\DataBackup\EMDB.bak' WITH
INIT "
; copy that temp file to the network that gets backed up.
xcopy "C:\Documents and Settings\Matt\My Documents\DataBackup"
"H:\DataBackup" /S /E /F /H /R /Y
Matt
|||Andrea & Matt,
Great input! I now have some clear direction to begin some testing. My
sincere thanks. --Rob
"Rob S" wrote:

> I am trying to better understand the process of backing up a SQL (MSDE)
> database. There are several 3rd party tools on the market that include a
> backup function, but are dependent on first establishing a connection to the
> server. My specific questions are:
> 1) is it possible to merely copy (using a DOS or windows copy command) the
> DB file for backup purposes? If so, is there any trick to restoring the
> files?
> 2) what is the advantage (or requirement) of performing the backup through a
> connection to the server? What is the process for creating such a backup --
> does it depend on T-SQL statements?
> My goal is to create a backup/restore routine within my application so that
> the process is very easy for my end-users to manage and also something that
> could be set up on an automated schedule.
> Appreciate any help! --RS