SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 restore master db to different location
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

itsenter
Starting Member

18 Posts

Posted - 06/27/2007 :  16:10:58  Show Profile  Reply with Quote
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

USA
35951 Posts

Posted - 06/27/2007 :  16:34:47  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 - 06/27/2007 :  17:20:57  Show Profile  Reply with Quote
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

USA
35951 Posts

Posted - 06/27/2007 :  17:41:39  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 - 06/27/2007 :  17:54:15  Show Profile  Reply with Quote
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

USA
35951 Posts

Posted - 06/27/2007 :  18:03:39  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 06/27/2007 :  23:16:37  Show Profile  Reply with Quote
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 - 06/28/2007 :  09:05:21  Show Profile  Reply with Quote
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 - 06/28/2007 :  09:09:57  Show Profile  Reply with Quote
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 - 06/28/2007 :  10:03:57  Show Profile  Reply with Quote
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 - 06/28/2007 :  13:46:35  Show Profile  Reply with Quote
Apparently SQL Server does not allow restore master to different location, all docs avoid this topic ...
Go to Top of Page

rmiao
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 06/28/2007 :  23:30:03  Show Profile  Reply with Quote
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 - 06/29/2007 :  09:52:44  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 06/30/2007 :  21:26:29  Show Profile  Reply with Quote
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 - 07/02/2007 :  09:28:05  Show Profile  Reply with Quote
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

United Kingdom
22403 Posts

Posted - 07/02/2007 :  09:34:57  Show Profile  Reply with Quote
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 - 07/02/2007 :  09:52:42  Show Profile  Reply with Quote
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

United Kingdom
22403 Posts

Posted - 07/02/2007 :  10:37:10  Show Profile  Reply with Quote
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

USA
391 Posts

Posted - 07/02/2007 :  15:14:55  Show Profile  Reply with Quote
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 - 07/03/2007 :  10:23:14  Show Profile  Reply with Quote
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.

Edited by - itsenter on 07/03/2007 10:25:36
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 07/03/2007 :  10:24:56  Show Profile  Reply with Quote
Have you got the MODEL database, as well as the MASTER database, at that location?

Kristen
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000