SQL Server Exercise: Recovering the Database Using STOPATMARK and STOPBEFOREMARK
Description: (back to top)
In this exercise we are going to do a point in time recovery using the STOPATMARK and STOPBEFOREMARK clauses. This will allow us to recover the database right down to an individual transaction - or immediately prior to a given transaction. This probably won't be used very much in production environments, but in test and development environments, these RESTORE clauses can be handy to take the database to a point in time prior to an event that had negative results.
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)
There are no GUI steps for this exercise. This exercise is T-SQL intensive, and I don't believe that there is a way to recover to marks using solely GUI interfaces.
T-SQL Steps: (back to top)
1) Backup the Northwind database as shown below
2) Get a Transaction Log backup of the database
3) In Query Analyzer, add multiple rows to the shippers table. Two of the inserts below are within a marked transaction. SQL Server 2000 is autocommit by default - but when you are using marked transactions, you must explicitly end any transaction (usually with a COMMIT or ROLLBACK) which is why we have a COMMIT statement.
4) Query the shippers table so that we know what the table currently looks like
5) Get another transaction log backup so that we'll be able to recover right up to the mark or right after the mark
6) 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.
7) In Query Analyzer, shut down the database.
8) In the operating system, delete the files that we found in step 6.
9) Right click on the SQL Server icon in the systray and select "MSSQL Server - Start"
10) Find the file numbers on the device that we need to restore. Note that the only backups on the marking device are the ones that we just did.
11) Now we're going to do the first of our two restores. First restore using STOPBEFOREMARK. We are using the STANDBY statement to put the database in read-only mode, but we'll still be able to perform further transaction log restores.
12) Now query the shippers table. From Step 3, you can see that Company1 and Company2 were inserted before the TIMETOMARK transaction. They are both present as we expected they would be.
13) Now we're going to move further on and use STOPATMARK and leave the database operational after our recovery.
14) Querying the shippers table, we can see that STOPATMARK has restored all rows that were committed during the STOPATMARK transaction, but no transactions that occurred after this point. Company5 is missing from the result set as we knew it would be when we included the STOPATMARK in our restore statement.
Our STOPBEFOREMARK, STOPATMARK operations are complete.
