Restoring the master db on another server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Restoring the master db on another server

Author

Topic

N-IGMA
Starting Member

7 Posts

Posted - 2004-11-19 : 11:48:58

Hi All,

I am running some tests to restore a master db from another server, basically attempting to mirror an installation. After creating a complete backup of the master db on the production server, I ran the following code through the Query Analyzer to a fresh install after starting the server in single user mode...


set quoted_identifier off
GO
restore database master from disk = 
  "E:\master.BAK" 
  with recovery, 
  replace,
  move "master" to "P:\MSSQL7\Data\master.mdf",
  move "mastlog" to "P:\MSSQL7\Data\mastlog.ldf"
GO

This is the message that was returned


The master database has been successfully restored. Shutting down SQL Server. SQL Server is terminating this process.

Now the server will not start at all! Can anyone help?

TIA

- Phil.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-19 : 11:52:04

It is not a good idea to install the master database onto a server other than where it originated. The only way this will work is if the path information is identical on the originating server. Since you are using the move option in the restore command, I'm guessing you've got different paths.

Tara

N-IGMA
Starting Member

7 Posts

Posted - 2004-11-19 : 12:10:39

So, as long as the paths are the same there should not be a problem?

These tests are being performed as I need to reinstall our production SQL server and I do not want to have to manually re-create logins, operators, jobs, maintenance plans, etc...

- Phil.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-19 : 12:13:19

Yes, that's correct and I've done it numerous times. Take a look at this:

http://support.microsoft.com/default.aspx?scid=kb;en-us;224071&Product=sql2k

Tara

N-IGMA
Starting Member

7 Posts

Posted - 2004-11-19 : 12:18:28

So in summary, the master, model & msdb dbs should be detached from the new installation and the backups made from the production server should be attached?

- Phil.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-19 : 12:39:27

The easiest way to move an entire SQL Server is to have the second SQL Server built the same way. Then stop the MSSQLSERVER service on the originating server and new server, copy all MDF and LDF files over to the new server, start up the MSSQLSERVER service on the new server. You should now be up and running. But the installation of SQL Server must have been the same for the data files at least in order for this to work. So you don't even need to do restores. The copying of files while the service is stopped is in essence the same thing as detaching and attaching.

Tara

N-IGMA
Starting Member

7 Posts

Posted - 2004-11-19 : 16:38:42

Thanks Tara, looks like I was over complicating things somewhat

- Phil.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources