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)
 restore master db to different location

Author  Topic 

itsenter
Starting Member

18 Posts

Posted - 2007-06-27 : 16:10:58
Hi,
I need to restore master ot different location to another server.

I restarted server in single user mode. Then run the following -

restore database master from disk = 'd:\sqlbackup\master_20070627010016_1.bak'
with
move 'master' to 'D:\Microsoft SQL Server\MSSQL\Binn\Data\master.mdf',
move 'mastlog' to 'D:\Microsoft SQL Server\MSSQL\Binn\Data\mastlog.ldf'

receive an error -

Server: Msg 3179, Level 16, State 1, Line 1
The system database cannot be moved by RESTORE.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Does anybody know how to solve this ?

Any help is appreciated.

Thanks,
irene.




tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-06-27 : 16:34:47
http://support.microsoft.com/default.aspx?scid=kb;en-us;224071&Product=sql2k

Tara Kizer
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

itsenter
Starting Member

18 Posts

Posted - 2007-06-27 : 17:20:57
hi Tara,

In the article you reference, the instruction to move master is to change the location of the master data file in the server properties, then copy file and restart server, but I do not have a copy of the master data file. i need to restore master from the backup file (created by running backup database ... ).

is this possible in general ?

Thanks
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-06-27 : 17:41:39
Restore the database to the same location as it was during the backup. Then if you need to move files around, use the procedure in the link I provided.

Using the master database from a different server is not wise unless the paths to the data files is exactly the same on both servers.

Tara Kizer
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

itsenter
Starting Member

18 Posts

Posted - 2007-06-27 : 17:54:15
Do I understand correctly, that SQL Server does not allow to move master to different location from backup ?

Thanks,
irene.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-06-27 : 18:03:39
I don't know. I only know how to move the files once the files are in place.

Tara Kizer
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-06-27 : 23:16:37
You can try move existing master db files to new location then restore from backup.
Go to Top of Page

itsenter
Starting Member

18 Posts

Posted - 2007-06-28 : 09:05:21
Unfortunetly I can not do this since I am moving from production server which is runnnig 7x24.
Go to Top of Page

jaybee
Yak Posting Veteran

72 Posts

Posted - 2007-06-28 : 09:09:57
I'm sort of curious to know WHY you'd want to move Master to another server...????
Go to Top of Page

itsenter
Starting Member

18 Posts

Posted - 2007-06-28 : 10:03:57
I am testing the recovery of production server. It appeared that the test box has different disks layout then production box.
Go to Top of Page

itsenter
Starting Member

18 Posts

Posted - 2007-06-28 : 13:46:35
Apparently SQL Server does not allow restore master to different location, all docs avoid this topic ...
Go to Top of Page

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-06-28 : 23:30:03
How can you restore master db then? Does that bring down sql server? The change I mentioned is on targer server.
Go to Top of Page

itsenter
Starting Member

18 Posts

Posted - 2007-06-29 : 09:52:44
I made backup of the master and all other dbs on the source.
Now I want to recreate the soure server on different box. it hqappened that the disks layout on the source is different then on the target. it is easy to recreate user dbs using restore with move command. Unfortunetly this does not work for master, or perhaps works, but I do not know how.

Thanks,
irene.
Go to Top of Page

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-06-30 : 21:26:29
As said, you need to change master db location by modifying sql startup parameters on target server.
Go to Top of Page

itsenter
Starting Member

18 Posts

Posted - 2007-07-02 : 09:28:05
So let's say I have on the source master data file on c:\data\master.mdf, and log on c:\data\master.ldf

On the target I do not have drive c:, and need since that I need to locate master on drive d:

My startup parameters on the target are
-dd:\data\master.mdf
-ld:\data\master.ldf

How should I change these parameters to restore master db from the c: drive of the source server ?

Thanks,
irene.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2007-07-02 : 09:34:57
Restore the backup to the place that SQL Server is expecting it, and then MOVE it to where you would like it.

So in your example you will need to "fake" a C: on the Target Machine.

Kristen
Go to Top of Page

itsenter
Starting Member

18 Posts

Posted - 2007-07-02 : 09:52:42
There is no drive c: on the target server,so sql server will not start if I'll change startup parameters from d: to c:, and since the source is production and can not be stopped, I have only backup files, not the copy of the database files.

Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2007-07-02 : 10:37:10
You'll have to "fake" the C: then in order to restore the master database.

The issue as I perceive it is that the master database has all sorts of location-specific stuff stored in it.

If you restore the master database and restart SQL Service its going to immediately use that data to try to reference things. ONCE it has restarted THEN you can issue commands to MOVE it elsewhere, but its a Catch-22 until that time.

So Fake the C: in the interval

Kristen
Go to Top of Page

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2007-07-02 : 15:14:55
You can create a virtual drive (using SUBST still, believe it or not and assign it to drive letter C:. Then do the restore of the master database and MOVE it, as Kristen mentioned.
Go to Top of Page

itsenter
Starting Member

18 Posts

Posted - 2007-07-03 : 10:23:14
Still have a problem -

All files on the source server locate on drive r: So we created an alias R: and then I was able to restore master.

The command dir R: from the command line works fine.

Unfortunetly SQL Server does not start, since when bringing up model it does not understand alias.

Here is an error from the error log -

Starting up database 'model'.
udopen: Operating system error 3(The system cannot find the path specified.) during the creation/opening of physical device R:\Program Files\Microsoft SQL Server\MSSQL\data\model.mdf.
FCB::Open failed: Could not open device R:\Program Files\Microsoft SQL Server\MSSQL\data\model.mdf for virtual device number (VDN) 1.
Device activation error. The physical file name 'R:\Program Files\Microsoft SQL Server\MSSQL\data\model.mdf' may be incorrect.
2007-07-03 08:31:41.37 spid5 Database 'model' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.


Does anybody know how to fix this ?

Thanks,
irene.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2007-07-03 : 10:24:56
Have you got the MODEL database, as well as the MASTER database, at that location?

Kristen
Go to Top of Page
    Next Page

- Advertisement -