SQL Server 2000 Point in Time Recovery
Restore and Recover Your SQL Server Database to a Precise Moment in Time
Description:
In this exercise, we are going to do a full database restore, and then bring the database forward to a given point in time.
(back to top)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:
(back to top)GUI Steps:
1) Get a full database backup of the Northwind database. Right click on Northwind then choose "All Tasks", "Backup Database".
2) The SQL Server Backup screen appears. Choose Northwind, and click"Database - complete" radio button. Back it up to a device, append it to media and click the "OK" button.
3) Take a transaction log backup by doing exactly what was done in steps 1 and 2, except choose "Transaction Log" instead of "Database - complete" from the SQL Server Backup screen. Also, you may wish to give it a more descriptive name like "TLOG1". In order to restore to a point in time, you need to make use of the transaction log backup.
4) Add a row to the shippers table in the Northwind database as shown below. Note that we also get the current date and time by doing a SELECT GETDATE().
5) Add another row to the shippers table as follows:
6) Add a final row to the shippers table.
7) Backup the transaction log for Northwind doing what was done in step 3.
8) Right click on Northwind and select properties
9) Note the locations of the data files and transaction logs, we'll be deleting these soon.
10) Stop SQL Server by right clicking on the SQL Server icon in the systray.
11) Delete the Northwind files that we found in step 9.
12) Start SQL Server by right clicking on the SQL Server icon in the systray just like in step 10. However, this time you will choose "MSSQLServer - Start".
13) Go into Enterprise Manager and right click on the Northwind database. Choose "All Tasks", "Restore Database".
14) The "Restore Database" screen pops up. For our example, we only want to restore the first two inserts, but not the last insert. Leave all of the defaults intact except click on "Point in time restore" (where the arrow is pointing).
15) The following screen pops up. We have to the second precision. We are going to restore to 14:10:18. Our second transaction was committed by 14:10:17. Our third transaction was committed by 14:11:11. So we enter 2:10:18 as our restore time.
16) We are now brought back to the Restore database screen. Note that our last transaction log backup occurred after the point in time that we are restoring to (14:11:54) and we are restoring to over a minute and a half before that backup completed. Click the "OK" button.
17) You'll get notification that the backup went successfully, click ok.
18) Now we go to query analyzer to see if we have the first two transactions, but not the third. Query the shippers table and you'll see that we only have the results of the first two transactions, but not the third. Our restore is complete.
(back to top)
T-SQL Steps:
1) Get a full database backup of Northwind
2) Get a transaction log backup
3) Insert rows as shown below
4) Insert more rows
5) Insert more rows
6) Back up the transaction log one more time.
7) While connected to Northwind in Query Analyzer, determine which files we are going to delete by using exec sp_helpfile. We're going to delete both the transaction log and the data file.
8) In Query Analyzer, shut down the database.
9) In the operating system, delete the files that we found in step 6.
10) Right click on the SQL Server icon in the systray and select "MSSQL Server - Start"
11) Use the RESTORE HEADERONLY statement in Query Analyzer to determine what file numbers we need to use in the RESTORE database statement. From the backupname column, we know we need the files in positions 14, 15, and 16. We named our backups NORTH FULL, NORTH LOG1, and NORTH LOG2, and that corresponds to the backup set position within the backup device. Other columns are compressed from the output, but there are also datetime columns giving us information on when the backup occurred - in other words there are more ways than just using the name to get the backup file position.
12) Like in the GUI example, we want to restore only our first two inserts. Restoring to 14:51:30 will allow us to preserve the first two transactions, but not the third, which completed about 40 seconds later. Connect to the master database in query analyzer and run the following command.
13) Run a query against the Northwind database to make sure we restored the first two inserts but not the third. Our restore is successful and the T-SQL exercise is over.
