SQL Server Exercise: Lost Primary Data File - Full Recovery Model
Description: (back to top)
We will end up destroying the primary data file and using database backups and transaction log backups to do a full restore. In SQL Server 2000, you can perform a transaction log backup even if the primary data file is destroyed. This could represent a real world scenario where the disk containing your primary data file is destroyed - but the disk containing your transaction logs is still intact.
Requirements: (back to top)
- As with all of the exercises on this site, do not perform this exercise on a production server. Perform this exercise in a closed development environment
- The database needs to be using the Full Recovery Model - for the exercise on changing the recovery model, click here
- You will need to have a backup device already created - for the exercise on creating a backup device, click here
Setup: (back to top)
none.
GUI Steps: (back to top)
1) Start the steps to perform a full database backup by going into Enterprise Manager, right clicking on the Northwind database, then choosing "All Tasks" and "Backup Database".
2) The Backup screen comes up. Choose the database name (Northwind). Name this backup Northwind Full. Make sure that the "Database - Complete" radio button is selected. Also make sure that you choose a destination (if no destination is chosen click the "Add" button). Then choose the Append to media radio button. Then click the "OK" button to perform the backup.
3) In Query Analyzer, insert a row into the shippers table and then select from the table to make sure that our insert was accepted
4) Perform a transaction log backup, by doing exactly what we did in step 1 to bring up the backup database screen. (In Enterprise Manager, right click on the Northwind Database, then choose "All Tasks" and "Backup Database").
5) Now perform the Transaction Log backup. Choose Northwind as the database, and name the backup "Northwind TRANSLOG". Select "Transaction Log" for the backup type. Then choose your backup device and append to the media. Click on the "Options" tab to verify the backup.
6) Insert one more row. We're going to back this transaction up in a transaction log backup AFTER we simulate the loss of a data file. Go into Query Analyzer and perform the following statement.
7) We are going to simulate the loss of the primary data file by actually deleting it from the database. In Enterprise Manager, right click on the Northwind database and choose "Properties" (from the first pop-up menu which is pictured in step 1. Under the "Data Files" tab, you will see which files contain the data so we can delete one of them after the database is stopped. The Primary data files will have the .mdf extension - secondary data files are .ndf by default. There is only one data file in the Northwind database, and it has the .mdf extension, so we know that this is the file we are going to be deleting.
8) Stop SQL Server by right clicking on the SQL Server icon in the systray. Choose "MSSQLServer-Stop". Then click 'YES' when you are asked if you are sure that you want to do this.
.
9) Delete the Northwind primary data file (the .mdf file). Be sure to only delete this primary data file and not any other data files or transaction logs. We deleted the file located at C:\Program Files\Microsoft SQL Server\MSSQL\Data\Northwnd.mdf. The data file is now completely gone. The backup that we took earlier in this exercise must me restored in order to get our data back.
10) Start SQL Server by doing what was shown in step 8 only select 'MSSQLServer-Start'
11) In a typical restore and recovery situation, we would try to determine exactly what the issue is. Since we know that we're missing a primary data file, we're going to back up the transaction log. We're able to do this in SQL Server 2000 as long as the transaction log is still intact, even if you are missing a data file. In Enterprise Manager, right click on the Northwind Database, choose "All Tasks", "Backup Database"
12) You are now brought to the Backup Database screen. Name the backup NWIND TRANS2. Choose Transaction log backup, and click the "OK" button.
13) Now we can begin the restore process. Right click on the Northwind Database, , choose "All Tasks" and "Restore Database".
14) The "Restore Database" screen pops up. Choose "Restore as database" Northwind. Choose the "Database" radio button. In the "Parameters" section, choose Northwind for the "Show backups of database" item. Three backups are shown as indicated in the image below. By default, SQL Server wants to restore our full backup, followed by the transaction log backups. We're going to just hit the "OK" button to do our full restore.
15) In query analyzer, check our status. Note that the database has been fully restored. Remember that the transaction log containing the insert to the SHIPPERID = 20 wasn't backed up until after the database was already marked as suspect (after the primary data file had been deleted). We were still able to recover the transaction by backing up the transaction log on restart and doing a restore and recover.
T-SQL Steps: (back to top)
1) In Query Analyzer, backup the database using the BACKUP DATABASE command.
2) Still in Query Analyzer, insert a row into the shippers table
3) Do a transaction log backup in Query Analyzer.
4) Insert another row into the shippers table
5) While connected to Northwind in Query Analyzer, determine which file is the primary data file that we will delete in the next step by using exec sp_helpfile. In our example below, the top file is the data file. We know it is a data file, not only because of its name, but also because transaction logs can NEVER be part of a filegroup. In addition, the files by default end in .mdf or .ndf and log files end in .ldf (this can be altered by the DBA however, so be careful). You can just expand the filename column to see the full path.
6) In Query Analyzer, shut down the database.
7) Delete the primary data file in the OS
8) You can restart the server by right clicking on the SQL Server icon in the systray and clicking "MSSQL Server - Start".
9) Normally at this point we would go through the steps to determine what has happened to Northwind. In our case we know we have to restore the data file that we just deleted. First, we need to take a transaction log backup to preserve the last transaction that we entered. Note that we must use the NO_TRUNCATE clause - this is used to back up the transaction log in events like this one where the database is damaged.
10) We have all of our data preserved, we can now work on restoring the primary data file and recovering the database. First we need to find the files we need to recover from our backup device. In Query Analyzer, run the RESTORE HEADERONLY command. We can see our backup Names and their associated positions in the file.
11) While connected to the master database in query analyzer, perform a full restore by issuing the following statements.
12) Query the database to see that the restore worked successfully.
