Restoring MASTER database in SQL Express 2005 SP4

I need to restore all system databases from one SQl Express 2005 SP4 server to another with the same build, plus 1 user database.  I know I need to restore the master database first, in single-user mode, but when I start the named instance of SQL Express in single-user mode(sqlserver.exe -m -s 'named instance'), I get the command window that indicates that the server is in single-user mode.  However, when I try to restore the master database, I get the following error:

TITLE: Microsoft SQL Server Management Studio Express
------------------------------

Restore failed for Server 'SERVER\INSTANCE'.  (Microsoft.SqlServer.Express.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: To restore the master database, the server must be running in single-user mode. For information on starting in single-user mode, see "How to: Start an Instance of SQL Server (sqlservr.exe)" in Books Online. (Microsoft.SqlServer.Express.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&LinkId=20476

------------------------------


In the drop-down window to select the database to restore to, master is not listed, only model, msdb, and the user database.  When I check Options from Properties of the master database, it still shows up as MULTI_USER and it won't let me choose SINGLE_USER.  I get this error message when I do that:

TITLE: Microsoft SQL Server Management Studio Express
------------------------------

Alter failed for Database 'master'.  (Microsoft.SqlServer.Express.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+Database&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)

------------------------------

Option 'SINGLE_USER' cannot be set in database 'master'. (Microsoft SQL Server, Error: 5058)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.5000&EvtSrc=MSSQLServer&EvtID=5058&LinkId=20476

------------------------------


The Microsoft links either go nowhere, or provide no help.
LinkEngrAsked:
Who is Participating?
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
You should look here.  It has all the steps to do this.  I have done this myself and it works great.

http://social.msdn.microsoft.com/Forums/en-US/sqlexpress/thread/3d07451e-5765-47f6-b955-b528456523f6/
0
 
LinkEngrAuthor Commented:
Great job.  I searched all morning on the Internet for something like this.  You earned every point!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.