Perform SQL Server Restore and Recovery Using Database Backup, Differential Backups, and Transaction Logs
Description:
We will end up destroying all data and transaction log files in a production database, then using a database backup, a differential backup, and a transaction log backup to perform a full restore. The destruction of the files will simulate a loss of the disks in which the files were residing on.
Requirements:
- 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:
None.
GUI Steps:
1) In Query Analyzer, insert a row into the shippers table and then select from the table to make sure that our insert was accepted
2) 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".
3) 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. Finally, click on the "OK" button to perform the backup.
4) The full backup is complete. We're now going to add another row. In Query Analyzer, insert a row into the shippers table and then select from the table to make sure our insert was accepted.
5) Prepare to perform a differential database backup, by doing exactly what we did in step 2 to bring up the backup screen. (In Enterprise Manager, right click on the Northwind Database, then choose "All Tasks" and "Backup Database").
6) The backup screen comes up. Perform a differential backup. Choose the Northwind Database and name the backup "Northwind Differential". For a backup, choose "Database - Differential". Click on the "OK" button to perform the backup. (You could click on the options tab if you wished to verify the backup as shown below, but we're just going to move along and click the "OK" button)
7) The full and differential backups are now complete. We're going to insert a row to store in our transaction log backup. Do the following within Query Analyzer.
8) Prepare to perform a transaction log backup, by doing exactly what we did in step 2 to bring up the backup screen. (In Enterprise Manager, right click on the Northwind Database, then choose "All Tasks" and "Backup Database").
9) 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 "OK" button to perform the backup.
10) We are going to simulate the loss of a data file and transaction log file by actually deleting both files 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. Check under the Data Files and Transaction Log tabs to find the files we need to delete. (Only the Data Files tab is pictured below).
11) 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.
.
12) Delete the Northwind data files and transaction log files that we found the file locations for earlier in this exercise. (or files). All database files are now completely gone. The backup that we took earlier in this exercise must be restored in order to get our data back.
13) Start SQL Server by doing what was shown in step 11 only select 'MSSQLServer-Start'
14) In a typical restore and recovery situation, we would try to determine exactly what the issue is. If you're using the full recovery model, you'd also want to take a transaction log backup right now. In our case, we know we are missing a data file, so we are going to start our restore process. In Enterprise manager, right click on "Databases" then choose "All Tasks" and "Restore Database"
15) 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 differential backup, followed by the transaction log backup. These are hierarchical, note that the size of a Full database backup is much larger than a differential, which is significantly larger than a transaction log backup. Normally, we would just click on the "OK" button at this point to do a full restore and recovery. But we're going to do an incremental recovery.
16) Uncheck the transaction log option - we're going to restore the transaction log later. Keep the Full and differential backups checked. Then click on the options tab.
17) On the options tab, check "Leave database read-only and able to restore additional transaction logs. This will allow us to see how the incremental restore is progressing. Click ok and the Full and differential logs are restored.
18) In query analyzer, check our status. Note that the full and differential backups have been successfully restored. Make sure you aren't connected to Northwind in Query Analyzer so that we may complete our restore.
19) Restore the remaining transaction log. Repeat step 14 to bring up the "Restore Database" screen. We're going to take all of the defaults. SQL Server recommends that we now restore the transaction log, which we will do. Also, if you go to the Options tab, you will see that the radio button "Leave database operational. No additional transaction logs can be restored" has been checked. This is what we want. Hit the "OK" button on either screen and our restore is ready for access.
20) Validate that the restore is complete by selecting from the shippers table
The restore is complete.
T-SQL Steps:
1) In Query Analyzer, insert a row into the shippers table and then select from the table to make sure that our insert was accepted
2) Still in Query Analyzer, back up the database using the following command.
3) Insert another row into the shippers table as shown below.
4) Create a differential backup to record the change that was made in the previous step.
5) Add a final row to the table.
6) Perform a transaction log backup as shown below
7) 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. We're going to delete both files.
8) In Query Analyzer, shut down the database.
9) Delete both files in the OS.
10) You can restart the server by right clicking on the SQL Server icon in the systray and clicking "MSSQL Server - Start".
11)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. Use the RESTORE HEADERONLY on our backup device TSQLBACKUP to find what file numbers we need (from the position column)
We now know the file numbers for each of the backups that we performed. This will allow us to perform our incremental restore
12) While connected to the master database in query analyzer, perform an incremental restore by issuing the following statements.
The use of STANDBY allows the database to be opened in read only mode, but still allows us to apply more transaction logs. We are using the file values provided in the query results from step 11. We have restored the full and differential backups and are able to query the results since we are in read only mode. Querying the results at this point will show that the first two rows that we added (IDs 22 and 23) have been restored, but shipperID 24 has yet to be restored.
13) Finish the restore operation
14) Query the database to ensure everything has been restored to the way it was.
Our restore has completed successfully.