View previous topic :: View next topic |
Author |
Message |
jhadur Just Arrived
Joined: 04 Jan 2007 Posts: 0
|
Posted: 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?
|
|
Back to top |
|
|
Groovicus Trusted SF Member
Joined: 19 May 2004 Posts: 9 Location: Centerville, South Dakota
|
Posted: Tue May 08, 2007 6:16 pm Post subject: |
|
|
Dump the old master, and restore it to the new system.
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.
|
|
Back to top |
|
|
moondoggie Lurker
Joined: 27 May 2005 Posts: 19
|
Posted: 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...
|
|
Back to top |
|
|
Groovicus Trusted SF Member
Joined: 19 May 2004 Posts: 9 Location: Centerville, South Dakota
|
Posted: 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.
|
|
Back to top |
|
|
moondoggie Lurker
Joined: 27 May 2005 Posts: 19
|
Posted: 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
|
|
Back to top |
|
|
Groovicus Trusted SF Member
Joined: 19 May 2004 Posts: 9 Location: Centerville, South Dakota
|
Posted: 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.
|
|
Back to top |
|
|
moondoggie Lurker
Joined: 27 May 2005 Posts: 19
|
Posted: 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 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...
|
|
Back to top |
|
|
stimpy99 Just Arrived
Joined: 11 Sep 2005 Posts: 0
|
|
Back to top |
|
|
jhadur Just Arrived
Joined: 04 Jan 2007 Posts: 0
|
Posted: 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.
|
|
Back to top |
|
|
Pogle37 Just Arrived
Joined: 29 Apr 2008 Posts: 0
|
Posted: 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
|
|
Back to top |
|
|
|