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'withmove '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 1The system database cannot be moved by RESTORE.Server: Msg 3013, Level 16, State 1, Line 1RESTORE 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=sql2kTara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
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 |
|
|
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 Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
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. |
|
|
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 Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
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. |
|
|
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. |
|
|
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...???? |
|
|
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. |
|
|
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 ... |
|
|
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. |
|
|
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. |
|
|
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. |
|
|
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.ldfOn 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.ldfHow should I change these parameters to restore master db from the c: drive of the source server ?Thanks,irene. |
|
|
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 |
|
|
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. |
|
|
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 intervalKristen |
|
|
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. |
|
|
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. |
|
|
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 |
|
|
Previous Page&nsp;
Next Page
|