Restoring system databases - SQL Server Forums

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 system databases

Author

Topic

SQL360
Starting Member

20 Posts

Posted - 2008-09-18 : 15:20:35

I restored all the user dbs successfully to new sql instance.I restored these from .bak files. Now I am trying to restore the master, model and msdb dbs from .bak files as

RESTORE DATABASE Master
FROM DISK = 'D:\BACKUP\master_db_200809150245.BAK'
WITH NORECOVERY,
MOVE 'master' TO 'D:\MSSQL2000\MSSQL\Data\master.mdf',
MOVE 'mastLog' TO 'D:\MSSQL2000\MSSQL\LOG\mastlog.ldf' well but getting error message. Server: Msg 3179, Level 16, State 1, Line 1
The system database cannot be moved by RESTORE.

I found steps below, which states to
Moving the master database
1. Change the path for the master data files and the master log files in SQL Server Enterprise Manager.

Note You may also change the location of the error log here.
2. Right-click the SQL Server in Enterprise Manager and then click Properties.
3. Click Startup Parameters to see the following entries:
-dD:\MSSQL7\data\master.mdf
-eD:\MSSQL7\log\ErrorLog
-lD:\MSSQL7\data\mastlog.ldf-d is the fully qualified path for the master database data file.

-e is the fully qualified path for the error log file.

-l is the fully qualified path for the master database log file.

4. Change these values as follows: a. Remove the current entries for the Master.mdf and Mastlog.ldf files.
b. Add new entries specifying the new location:
-dE:\SQLDATA\master.mdf
-lE:\SQLDATA\mastlog.ldf

5. Stop SQL Server.
6. Copy the Master.mdf and Mastlog.ldf files to the new location (E:\Sqldata).
7. Restart SQL Server.

It has those setting already on the server upto step 4, but does that mean that in order for me to be able to copy .mdf and .ldf files from the source server I need to stop the source server? Is it the only solution to restore the systeme databases? I have .bak files for all these system databases and kind of confused so what is the use of these .bak system databases files? Please suggest and help.
Thank you.

SQL360

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-09-18 : 16:34:46

You can restore the system databases, but the process is not the same as for user databases.

See here for info on restoring master-
http://technet.microsoft.com/en-us/library/ms190679(SQL.90).aspx

--
Gail Shaw
SQL Server MVP

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-09-19 : 08:19:53

Instead , I would transfer logins and password(Master),Script all jobs ,move all DTS packages to other server if you have any.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources