Home / server / Questions / 141254
Accepted
ciscokid
Asked: 2010-05-13 11:37:20 +0800 CST

Command line scripts to restore the 4 system databases of MS SQL Server 2008

  • 772

can someone give me some advice on how to restore the 4 system databases (master, msdb, model, tempdb) of a sql server 2008 please?

I've already done some testing myself (on restoring the master database) with the following commad line script as a result:

::set variables
set dbname=master
set dbdirectory=C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA
title Restoring %dbname% database
net stop mssqlserver
cd C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn
sqlservr -m
sqlcmd -Slocalhost -E -Q "restore database master from disk='c:\master.bak' WITH REPLACE" 
net start mssqlserver
pause

After the execution of the sqlservr -m command (used to start the server instance in single-user mode, which is only necessary when restoring the MASTER database), the script stops. So in order to execute the last 2 commands I need to separate the script into 2 smaller scripts, and run them one after the other.

Does anyone has an idea on how I can merge them into one single script that runs completely without any interruption?

I also want to restore the other 3 system databases using command line scripts like this one. Can someone please advice me how I need to go on? I've already noticed that restoring the temdb is not so easy, but there has to be a way...

Looking forward to your advice!

scripting sql database restore

2 Answers

  1. Best Answer

    As mentioned in the comments - you don't backup/restore TEMPDB.

    To get the script to continue after the sqlservr -m command, you need to "background" the command. To do this, use the start command in front of it. Something like:

    start sqlservr -m

    should work. After that though, you will need to wait for SQL Server to actually start up before issuing any commands. On Windows 2003 you can get sleep.exe from the Windows 2003 Resource Kit to do this. On Windows 2008 you can use the timeout.exe command. You will need to time a few startups to get an idea for exactly how long to wait before attempting the restores (and add a little time to it, just in case...)

    As far as restoring msdb and model though, it should be 2 more simple sqlcmd restores just like the one you have for master. You also need to make sure you stop the SQL Server instance before restarting the service.

    Final script would look something like:

    ::set variables
set dbdirectory=C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA
title Restoring system databases
net stop mssqlserver
cd C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn
start sqlservr -m
timeout /t 60
sqlcmd -Slocalhost -E -Q "restore database master from disk='c:\master.bak' WITH REPLACE" 
sqlcmd -Slocalhost -E -Q "restore database msdb from disk='c:\msdb.bak' WITH REPLACE" 
sqlcmd -Slocalhost -E -Q "restore database model from disk='c:\model.bak' WITH REPLACE" 
taskkill /im sqlservr.exe
net start mssqlserver
pause

    Be sure to check out this technet article as well.

    note: I didn't test this, I'm assuming your sqlcmd statements are correct here...

    • 1

  2. Rather than "taskkill /im sqlservr.exe" to stop the SQL Server engine, a cleaner way is the following:

    sqlcmd -Slocalhost -E -Q "shutdown"

    The "shutdown" is a T-SQL command that can be issued to the SQL engine via any kind of connection, so it works even when "net stop mssqlserver" won't, such as in single user mode. It can also be done from an admin connection. (I haven't tried, but it might even work in a stored procedure. Great fun on April 1!)

    • 1