SQL Server Exercise: Lost Database - Simple Recovery Model
Description: (back to top)
In this exercise, we are going to be performing a full restore. We will end up destroying the primary data file in a production database, then using our database backup to perform a full restore. The destruction of the data file will simulate a loss of the disk in which the data file was residing on.
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 should be using the Simple 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) In Query Analyzer, insert values into the shippers table from the Northwind database as shown below. We aren't entering data into the ShipperID column which is an identity column.
2) Do a full database backup
In Enterprise Manager, right click on the Northwind Database. Choose "All Tasks" then "Backup Database..."
3) You are brought to the backup screen. Choose Northwind as the database to be backed up. Provide a name for your backup and optionally a description. Choose "Database - complete" under the "Backup Section". For Destination, choose an existing backup device - click the Add button to choose a device if none are listed. In the Overwrite section, we want to "Append to media". Next, click the "OK" button. You will get a notice showing how the backup is progressing followed by a message box telling you that the backup operation has completed successfully.
4) We are going to simulate the loss of a data file by actually deleting a data file 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 2). 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.
5) 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.
.
6) Delete the Northwind data file (or files). We deleted the file located at C:\Program Files\Microsoft SQL Server\MSSQL\Data\Northwind.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.
7) Start SQL Server by doing what was shown in step 5 only select 'MSSQLServer-Start'
8) In a typical restore and recovery situation, we would try to determine exactly what the issue is. In our case, we know we are missing a data file, so we are going to restore the database using the most recent full backup. Make sure that you have exclusive access to the database (for information on restricted modes, click here). Right click on "Databases" from Enterprise Manager. Then choose "All Tasks" and "Restore Database".
9) The "Restore Database" screen pops up. Restore the database as shown below. Choose the database that you wish to restore, then in the "Parameters" section, select the backups that you want to restore from. SQL Server has automatically chosen and displayed our most recent complete backup - the one that we performed earlier in this exercise.
Note that the backup that we just performed "Northwind Backup Complete" is shown. Make sure it is check marked. Then click "OK" to perform the backup. You will get a restore progress screen followed by a notification that the restore completed successfully.
10) Query the database to make sure that our data was restored
Our restore is complete and the database is fully operational yet again.
T-SQL Steps: (back to top)
Note that T-SQL is not recommended for restores due to the complex nature of the restore. These steps are presented here for your information, but I'm not recommending that you perform them.
1) In Query Analyzer, Insert Values into the shippers table from the Northwind database as shown below. We aren't entering data into the ShipperID column which is an identity column.
2) In Query Analyzer, do a full database backup to the productionbackups device
- Northwind is the database name
- productionbackups is the device name we are backing up to
- TSQLFULLRESTORE is the name of our backup set
3) While connected to Northwind in Query Analyzer, determine which file is the 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.
4) In Query Analyzer, shut down the database.
5) Delete the data file that we discovered in step 3.
6) You can restart the server by right clicking on the SQL Server icon in the systray and clicking "MSSQL Server - Start".
7) 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. The tricky part is finding the value for the FILE parameter shown in the next step (the RESTORE DATABASE statement). Use the RESTORE HEADERONLY statement to find the information we need. The position column shows us that the backup we took in step 2 titled 'TSQLRESTORE' is in position number 4. Note that many of the columns are compressed, but this is interesting output to look at. In addition to Position and backupname columns, there is date and time information to assist you with determining what backup set you need to restore.
8) Go into query analyzer. Connect to the master database and run the following command.
Note that if you didn't use the FILE parameter, SQL Server may not restore the most recent complete backup that you have performed. An earlier complete backup may be used instead, and you will lose data that has occurred between the backups.
9) At this point if you select from shippers, you will get the following:
The restore is completed.
