- Rebuilding and Recovering the <tt>master</tt> Database
- Potential Problems in Rebuilding and Restoring the <tt>master</tt> Database
- Recovering Other Databases after <tt>master </tt> Has Been Rebuilt
- Notes on Logins and Users after a <tt>master</tt> Rebuild
- Program to Generate Logins from the User Database
- Remapping Orphaned Users
- Recovering msdb
- Summary
This chapter is from the book
This chapter is from the book
This chapter is from the book
Recovering Other Databases after master Has Been Rebuilt
This section deals with the scenario of losing a master database and not having a back up of it.
Once master has been rebuilt, you have a freshly installed server. SQL Server at this point does not know anything about your previous databases that were on this machine. If you had a backup of master you would restore it now. If you did not, you have two choices: Either restore the databases from tape or disk or use SQL EM or the sp_attach_db stored procedure to re-associate the database files at the OS level back to the database.
TIP
If your other data files appear to be okay (meaning the disk drives were not damaged), I strongly suggest that you first try to use the sp_attach_db procedure because it is a lot faster to attach pre-existing files than to restore a whole database. The reason is that a restore will have to lay all the bits back on the disk, while the sp_attach_db stored procedure just puts a few entries back in the system tables so SQL Server will recognize the database. The worst thing that can happen is that sp_attach_db doesn't work. Then, you drop the database and restore from tape. The worst thing is that you lose five minutes; the best is you save several hours of restore. If you proactively create the sp_attach_db scripts, it will make recovery even faster. Also, attaching the existing databases allows you to back up the tail of the log.
Using the RESTORE Command Method
Add the backup devices.
Restore the database(s).
Synchronize the users if necessary (use sp_fixlogins later in the chapter).
use master go sp_addumpdevice 'disk', 'full_backup', 'c:\tmp\sql_backup\full_backup.dat' go sp_addumpdevice 'disk', 'log_backup', 'c:\tmp\sql_backup\log_backup.dat' go backup database mcbath to full_backup with init go backup log mcbath to log_backup with init go –– boom... lose your master database... –– use the rebuildm.exe tool to rebuild your master database –– then load your database back from tape use master go sp_addumpdevice 'disk', 'full_backup', 'c:\tmp\sql_backup\full_backup.dat' go sp_addumpdevice 'disk', 'log_backup', 'c:\tmp\sql_backup\log_backup.dat' go restore database mcbath from full_backup go restore log mcbath from log_backup go
Using the sp_attach_db Method
You need to know the sources for the database files. These can be located from a variety of sources:
Old database logs
sp_helpdb
Using a dir command to look for files in the data file directory
Here is an example of attaching data files using sp_attach_db:
sp_helpdb mcbath
go
/*
name db_size owner
compatibility_level
mcbath 61.00 MB sa
name fileid filename filegroup size
mcbath_Data0 1 c:\tmp\sql_data\data0.mdf PRIMARY 1024 KB
mcbath_Log 2 c:\tmp\sql_log\log0.ldf NULL 5120 KB
mcbath_data1 3 c:\tmp\sql_data\data1.ndf data 10240 KB
mcbath_index0 4 c:\tmp\sql_data\index0.ndf index 10240 KB
mcbath_data2 5 c:\tmp\sql_data\data2.ndf data 5120 KB
test_filegroup_file0 6 c:\tmp\sql_data\test_filegroup0.ndf test_filegroup 10240 KB
test_filegroup_file1 7 c:\tmp\sql_data\test_filegroup1.ndf test_filegroup 10240 KB
test_filegroup_file2 8 c:\tmp\sql_data\test_filegroup2.ndf test_filegroup 10240 KB
*/
n we could have just added the files via sp_attach_db which is *MUCH* faster
than a restore
n because it's just adding the entries into system tables as opposed to
loading data into
n the database. note, we are assuming the data files are ok. run dbcc checkdb
just to be sure
–– once the database is up and running.
use master
go
select name from sysdatabases
go
sp_attach_db @dbname = 'mcbath',
@filename1 = 'c:\tmp\sql_data\data0.mdf',
@filename2 = 'c:\tmp\sql_data\data1.ndf',
@filename3 = 'c:\tmp\sql_data\data2.ndf',
@filename4 = 'c:\tmp\sql_data\index0.ndf',
@filename5 = 'c:\tmp\sql_data\test_filegroup0.ndf',
@filename6 = 'c:\tmp\sql_data\test_filegroup1.ndf',
@filename7 = 'c:\tmp\sql_data\test_filegroup2.ndf',
@filename8 = 'c:\tmp\sql_log\log0.ldf'
go
select name from sysdatabases
go
/*--Before
name
master
tempdb
model
msdb
pubs
Northwind
(6 row(s) affected)
--After
name
master
tempdb
model
msdb
pubs
Northwind
mcbath
(7 row(s) affected)
*/