Backup MSDE database from .bat file

Hi,
I have seen hundreds of suggestions on how to backup a MSDE database on
the net, most of them have different approaches.
Does it exist an 'approved' way of doing this?
I need to run a .bat file which backs up my MSDE database (i.e. stops
the server, performs copying and the starts the MSDE engine again).
Thanks for hints on where to find the 'bulletproof' way of doing this.
I also need to know how to restore such a backup ;-)
Thanks if there is a kind soul somewhere
best regards
Tor
What you are doing is called a "cold" backup. Which doesn=B4t check the
integrity of the backup afterwards. If you are not concerned about
second or minutes of performance in your backup time frame you can do a
"hot" backup which does a normal backup on SQL Server (Sqls server
sercice running) and additionally can do a verify afterwards the
backup.
Look for the BACKUP statement in the BOL for more information.
Depending on how time critical your backup and your data is, I would
also consider using a transactional / differential backup on top of the
Full backup. This allows you (with the right recovery mode) to do a
restore point-in-time. Look for Restore and backup considerations in
the BOL for further information.
HTH, Jens Suessmeyer.
|||Jens,
Thanks for your quick reply. As this is a small database I assume that
it wouldn't take too long to do a 'hot' backup. However this is a MSDE
install made from a third party application and I don't know what 'BOL'
is.
If you or someone else can point me in a direction where I can find
approved scripts for backup and restore in my sceanrio I would
appreciate that. I don't want to find out when it is too late that the
performed scripts were inadequate ;-|
Thanks again
Tor
|||hi,
bushtor wrote:
> Jens,
> Thanks for your quick reply. As this is a small database I assume
> that it wouldn't take too long to do a 'hot' backup. However this is
> a MSDE install made from a third party application and I don't know
> what 'BOL' is.
> If you or someone else can point me in a direction where I can find
> approved scripts for backup and restore in my sceanrio I would
> appreciate that. I don't want to find out when it is too late that
> the performed scripts were inadequate ;-|
http://support.microsoft.com/default...-US;q325003#12
have a look at the entire article about oSql.exe "uses" so that you can pass
a .sql file to oSql.exe itself from a bat file to perform the required
task..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Andrea,
Thanks for the
http://support.microsoft.com/default...-US;q325003#12
link.
I played a bit with the osql commands but I couldn't get it to work due
to lack of access rights.
This MSDE backup should be run by a 'normal' user with no admin
priviledges. I hoped to put the necessary commands in a .bat file.
However the logged in user did not have the appropriate rights when I
tried to execute the osql backup command. Can I somehow explicit input
the db's 'sa' password and execute:
osql -E -q "EXEC sp_grantdbaccess 'sa', 'sapassword'"
osql -E -q "BACKUP DATABASE mydb TO DISK = 'C:\mydb.bak'"
-- logout command ?
... or how do I do this to have an unpliviledged user to perform a
backup?
regards
Tor
|||hi,
bushtor wrote:
> tried to execute the osql backup command. Can I somehow explicit
> input the db's 'sa' password and execute:
> osql -E -q "EXEC sp_grantdbaccess 'sa', 'sapassword'"
> osql -E -q "BACKUP DATABASE mydb TO DISK = 'C:\mydb.bak'"
> -- logout command ?
define a SQL Server Login for backup operations and map it to a database
user (and make it) member of the db_backupoperator fixed role..
(http://msdn.microsoft.com/library/de...urity_6ndx.asp)
this will "limit" permissions and privileges for that login so that you can
create a bat file including username and password in clear..
osql -S(Local)\InstanceName -Uthe_backup_user -Pthe_wpd -Q "BACKUP DATABASE
mydb TO DISK = 'C:\mydb.bak'" >c:\BckLog.txt
oSql is case sensitive for the supplyed parameters..
http://msdn.microsoft.com/library/de..._osql_1wxl.asp
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> skrev i melding
news:44rt36F3liojU1@.individual.net...

> define a SQL Server Login for backup operations and map it to a database
> user (and make it) member of the db_backupoperator fixed role..
>
(http://msdn.microsoft.com/library/de...-us/adminsql/a
d_security_6ndx.asp)
> this will "limit" permissions and privileges for that login so that you
can
> create a bat file including username and password in clear..
> osql -S(Local)\InstanceName -Uthe_backup_user -Pthe_wpd -Q "BACKUP
DATABASE
> mydb TO DISK = 'C:\mydb.bak'" >c:\BckLog.txt
Hi,
Thanks for the example. But can you elaborate '(Local)\InstanceName' ? Is
this a literal parameter or should I replace it with something?
Will 'the _backup_user' be logged in with MSDE until the computer restarts
or is this a persistent db user from the first time this command is run?
And we can run this command once a day without problems?
What will the restore command look like for a backup like this?
Thanks again for comments on this issue
best regards
Tor
|||Just an additional question to my post just above this one:
Is 'the_backup_user' in the command above the windows user name (who has no
admin rights)?
Isn't it necessary to specify the MSDE SA username and its password
somewhere with these commands?
Thanks for clarification on this in addition to my issues in the post above
regards
Tor
|||hi,
bushtor wrote:
> "Andrea Montanari" <andrea.sqlDMO@.virgilio.it> skrev i melding
> news:44rt36F3liojU1@.individual.net...
> (http://msdn.microsoft.com/library/de...-us/adminsql/a
> d_security_6ndx.asp)
> Hi,
> Thanks for the example. But can you elaborate '(Local)\InstanceName'
> ? Is this a literal parameter or should I replace it with something?
it's not a literal.. SQL Server 2000 grants you to install one or more
instances of the engine per machine.. you can have up to 16 instances per
machine where only 1 can be the default instance and all the other will be
named instances
http://msdn.microsoft.com/library/de...setup_2xmb.asp
default instances are "known" to users as "(Local)" for local connections
and "ComputerName" for remote connections...
named instances are "known" to users as "(Local)\InstanceName" for local
connections and "ComputerName\InstanceName" for remote connections...

> Will 'the _backup_user' be logged in with MSDE until the computer
> restarts or is this a persistent db user from the first time this
> command is run?

> Is 'the_backup_user' in the command above the windows user name (who
> has no admin rights)?
>
the "the_backup_user" is intended as a SQL Server Login, so that you do not
have to reboot, disconnect or switch to that Windows account.. there will
be no corresponding Windows account but just a SQL Server login you provide
for this quite-admin task..

> And we can run this command once a day without problems?
yes... or even schedule it via AT/SCHTASKS...

> What will the restore command look like for a backup like this?
osql -S(Local)\InstanceName -Uthe_Restore_user -Pthe_wpd -Q "RESTORE
DATABASE [MyDbl] FROM DISK = N'c:\MydB,bak' WITH FILE = 1" >c:\RestLog.txt
to restore a database the logged user has to be member of the sysadmin or
dbcreatore server role or be member of the db_owner of the database to be
restored..
no one must be connected to the database while restoring it..
http://msdn.microsoft.com/library/de...ra-rz_25rm.asp
usually the restore operation is not performed on a "routine" base but only
on demand when required, so you usually do not set up an apposit script for
it but perform it if and when required to.. and in this case you can use
"sa" SQL Server login as well without the security hole to store it's
credential (the pwd) in a bat file (which is not a good idea indeed :D)

> Isn't it necessary to specify the MSDE SA username and its password
> somewhere with these commands?
as long as you use another (the_backup_user) login/user with enogth
permissions you are not required to use "sa"... "sa" is a special "system"
SQL Server login member of the sysadim server role and can perform whatever
tast on a SQL Server instance... usually you should provide a policy of
least privileges and thus only grant the permissions as required, no more,
better less :D (you can later adjust)... and I will always stress this
politics :D
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote
news:453od6F4oru9U1@.individual.net...

> it's not a literal.. SQL Server 2000 grants you to install one or more
> instances of the engine per machine.. you can have up to 16 instances per
> machine where only 1 can be the default instance and all the other will be
> named instances
Will the name(s) of the(se) instance(s) be listed in the small MSDE SQL
'manager' applet opened from the systray icon?
I haven't had access to the concerned computer for a week but I will try
this tomorrow. Thanks a lot for all your hints.
Tor