Home > Articles > Data > SQL Server

  • Print
  • + Share This
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

  1. Add the backup devices.

  2. Restore the database(s).

  3. 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)
*/
  • + Share This
  • 🔖 Save To Your Account