Master database question? - 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)

Master database question?

Author

Topic

thecoffeeguy
Yak Posting Veteran

98 Posts

Posted - 2007-01-31 : 12:27:49

I was doing some testing here at work lately and felt I needed to get a better understanding of the Master database that comes with SQL 2000. I wanted to ask some questions here as well as backing up and restoring methods.

First question is, what is the main purpose of the master database? I know the model DB is a template for newly created databases. What does Master do?

Second, is it recommended that you backup master regularly? What should the recovery mode be in? Simple? Full?

Lastly, assuming that you back up your master DB, what is the proper way to restore it since it is a systems database?

I am coming up with some backup and recovery methods for our database here. I am drafting up what we need to backup and how to restore.

Thanks for the help.

TCG

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-01-31 : 13:15:15

Master is the core of SQL Server. It is the database that stores information about the other databases. It stores logins and all sorts of other system stuff.

Yes you should backup master daily! You can't change the recovery model of master, so it is FULL and should be FULL.

To restore the master database, you must set SQL Server to startup in single user mode. SQL Server Books Online goes into pretty good detail on how to restore it. Please check there rather than me duplicating the information.

Tara Kizer

thecoffeeguy
Yak Posting Veteran

98 Posts

Posted - 2007-01-31 : 13:56:44

quote:
Originally posted by tkizer

Master is the core of SQL Server. It is the database that stores information about the other databases. It stores logins and all sorts of other system stuff.

Yes you should backup master daily! You can't change the recovery model of master, so it is FULL and should be FULL.

To restore the master database, you must set SQL Server to startup in single user mode. SQL Server Books Online goes into pretty good detail on how to restore it. Please check there rather than me duplicating the information.

Tara Kizer

Thanks Tara for your explanation. I was pretty sure it was important, just wasn't sure.

I was trying to do a test restore of the master DB and came across this error (SQL is booted in singel server mode)

Here is the script that I am using:


RESTORE DATABASE master FROM
DISK = 'C:\tempbackups\master\master_db_200701250300.BAK'
WITH MOVE 'master.mdf'
TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\master.mdf',
MOVE 'mastlog.ldf'
TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\mastlog.ldf',
REPLACE

here is the error:


Server: Msg 3234, Level 16, State 2, Line 1
Logical file 'master.mdf' is not part of database 'master'. Use RESTORE FILELISTONLY to list the logical file names.

Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

I am a little confused.
I took the master backup from another test box and moved it over to this box. Started SQL in single user mode and tried the script above, but no luck.

ANy suggestions?

Thanks.

TCG

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-01-31 : 14:00:48

You aren't using the correct logical file names in the WITH MOVE syntax. Run RESTORE FILELISTONLY as the error suggests to get the correct names.

You should not restore a master database to another server unless all paths are identical on both servers.

Tara Kizer

thecoffeeguy
Yak Posting Veteran

98 Posts

Posted - 2007-01-31 : 14:28:47

quote:
Originally posted by tkizer

You aren't using the correct logical file names in the WITH MOVE syntax. Run RESTORE FILELISTONLY as the error suggests to get the correct names.

You should not restore a master database to another server unless all paths are identical on both servers.

Tara Kizer

So, hack the query to look like this?

RESTORE DATABASE master FROM
DISK = 'C:\tempbackups\master\master_db_200701250300.BAK'
RESTORE FILELISTONLY
REPLACE

On your second part, Ya, I purposely restored a DB from one server to another where both have different paths, because I wanted to see what happens.

Thanks

thecoffeeguy
Yak Posting Veteran

98 Posts

Posted - 2007-01-31 : 14:32:40

Wait wait wait...BoL says differently

Looks like:

RESTORE FILELISTONLY
FROM DISK = 'C:\tempbackups\master\master_db_200701250300.BAK'

that should do it?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-01-31 : 14:35:40

That'll give you the logical names to then use in your RESTORE DATABASE command.

Tara Kizer

thecoffeeguy
Yak Posting Veteran

98 Posts

Posted - 2007-01-31 : 14:40:14

quote:
Originally posted by tkizer

That'll give you the logical names to then use in your RESTORE DATABASE command.

Tara Kizer

All part of one command?

Got it...Here is what I did:

RESTORE FILELISTONLY
FROM DISK = 'C:\tempbackups\master\master_db_200701250300.BAK'

RESTORE DATABASE master
FROM DISK = 'C:\tempbackups\master\master_db_200701250300.BAK'

Seems to have worked.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-01-31 : 14:44:37

You are missing the point. They are separate commands.

In your initial restore command, you weren't passing the correct values to the WITH MOVE command, hence the error. To fix the error, you run RESTORE FILELISTONLY to find out what the correct values are. You then use those values in the RESTORE DATABASE command. Notice how RESTORE FILELISTONLY produced output on your screen. Use that output!

Your restore might have worked, but it didn't move the files to the new paths as you wanted to.

Tara Kizer

thecoffeeguy
Yak Posting Veteran

98 Posts

Posted - 2007-01-31 : 14:48:06

quote:
Originally posted by tkizer

You are missing the point. They are separate commands.

In your initial restore command, you weren't passing the correct values to the WITH MOVE command, hence the error. To fix the error, you run RESTORE FILELISTONLY to find out what the correct values are. You then use those values in the RESTORE DATABASE command. Notice how RESTORE FILELISTONLY produced output on your screen. Use that output!

Yep, got it...completely understood. Really appreciate your help.

quote:

Your restore might have worked, but it didn't move the files to the new paths as you wanted to.

I forgot to put that info in the script and now SQL won't start.
Good thing this is a test box.

Thanks for your help!

TCG

Kristen
Test

22859 Posts

Posted - 2007-01-31 : 15:01:43

In case it helps there is an example of RESTORE syntax here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=RESTORE%20syntax%20/%20example

Kristen

thecoffeeguy
Yak Posting Veteran

98 Posts

Posted - 2007-01-31 : 15:02:49

quote:
Originally posted by Kristen

In case it helps there is an example of RESTORE syntax here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=RESTORE%20syntax%20/%20example

Kristen

Thanks Kristen. Should have looked their first.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources