3

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!

3
  • Good question I also would like to know this answer. +1
    – Campo
    May 12, 2010 at 19:41
  • Tempdb will be created when the mssqlserver service starts.
    – jl.
    May 12, 2010 at 21:37
  • You don't need to restore TEMPDB. As it's name implies, it's temporary and as jl stated, it will be created automatically. A new TEMPDB (or multiple TEMPDB's, depending on your configuration) is created whenever SQL starts.
    – joeqwerty
    May 13, 2010 at 2:21

2 Answers 2

1

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...

2
  • Also - just wanted to add that I am normally an advocate of replacing Batch files with Powershell - in this circumstance I am not though, backup/restore through Powershell is painfully verbose in comparison.
    – MattB
    May 13, 2010 at 15:07
  • Instead of killing the SQL Server task issue a shutdown command from within sqlcmd. This way SQL shuts down gracefully.
    – mrdenny
    Feb 12, 2011 at 3:54
1

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!)

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .