Restore Master Database in SQL 2000

Networking/Security Forums -> Databases

Author: jhadur PostPosted: Tue May 08, 2007 3:20 pm    Post subject: Restore Master Database in SQL 2000
    ----
I am going to be reinstalling our SQL Server. I will need to restore the master database when it has been installed. How do I do that?

Author: GroovicusLocation: Centerville, South Dakota PostPosted: Tue May 08, 2007 6:16 pm    Post subject:
    ----
Dump the old master, and restore it to the new system. Confused

Sql 2000 has tons of online documentation; are you not finding what you need, or am I not understanding your question. I don't use SQL 2000, but in MYSQL it is pretty simple.

Author: moondoggie PostPosted: Tue May 08, 2007 8:28 pm    Post subject:
    ----
if you have the sql2000 client tools installed, there is an ebooks client as part of that suite of tools. if you can't find it in there, it probably can't be done...

Author: GroovicusLocation: Centerville, South Dakota PostPosted: Wed May 09, 2007 12:28 am    Post subject:
    ----
Quote:
if you can't find it in there, it probably can't be done...


Apparently I am misunderstanding the problem. I just presumed that all relational databases had the ability to restore from backups.

Author: moondoggie PostPosted: Wed May 09, 2007 1:48 am    Post subject:
    ----
if it's MS SQL, the "master" database has a separate way of getting backed up than the other db's in your system

Author: GroovicusLocation: Centerville, South Dakota PostPosted: Wed May 09, 2007 2:54 am    Post subject:
    ----
Just to explore the depths of my ignorance as far as SQL 2000 is concerned; if you do a database backup, it doesn't restore the master? That seems... well, weird.

Author: moondoggie PostPosted: Wed May 09, 2007 6:14 pm    Post subject:
    ----
it is weird, and the only thing i can figure out is that MS just wants to be difficult Rolling Eyes you can't backup the transaction logs on the master db, and restoring will break a lot of different things if you aren't extremely careful. i wish we could go to a higher level of SQL but our main software requires SQL 2000 so we're stuck...

Author: stimpy99 PostPosted: Wed May 09, 2007 8:59 pm    Post subject:
    ----
Good article on Technet all about backup and restore of dbs including the master:

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx

Author: jhadur PostPosted: Wed May 16, 2007 3:40 pm    Post subject:
    ----
I will review that document Stimpy but so far all the MS documents on how to restore the master have done nothing but totally kill the SQL installation. I had to reinstall SQL 3 times after trying to restore the master. We finally just gave up and recreated all of our login info from scratch. SIGH! You would think that the database that holds some of the most important information in SQL would be one of the easiest to restore but I guess not. Mad

Author: Pogle37 PostPosted: Thu May 01, 2008 5:56 pm    Post subject:
    ----
Try this
1. Installed SQL Server 2000 Set the Data Drive, selected Mixed mode and supplied sa password.
2. Installed SQL Server SP3
3. Go into Control panel, Services
Started MSSQLServer service in single mode (parameter /m)
8. Started Enterprise Manager and navigated to local, Management, Backup
Right click on backup and select New Backup Device
Enter the device name Diskdump (backup device to recover from)
Enter the physical file name D:\Program Files\Microsoft SQL Server\MSSQL\Data\Backup\DISKDUMP.BAK
9. Recover Master
Navigate to Databases, Master, Right Click, select all tasks
Click on Restore Database
Click on Device radio button and select device Diskdump
Click View Contents, select Master database and click OK
SQL Server should then restore the backup of the master database
10. Navigate to Databases, user database, Right Click, select all tasks
Click on Restore Database
Click on Device radio button and select device Diskdump
Click View Contents, select User database and click OK
SQL Server should then restore the backup of the User database
11. Restore Model Database
Navigate to Databases, Model
Right Click and select Properties
Select Options tab, click on Restrict access and click on single user
Right Click and select all tasks
Click on Restore Database
Click on Device radio button and select device Diskdump
Click View Contents, select Model database and click OK
SQL Server should then restore the backup of the Model database
12. Repeat step 10 for Msdb Database

I use this method in my DR tests with success



Networking/Security Forums -> Databases


output generated using printer-friendly topic mod, All times are GMT + 2 Hours

Page 1 of 1

Powered by phpBB 2.0.x © 2001 phpBB Group