在SQL SEVER 2000中还原数据库到指定POINT。

如下方法是从网上找来的。源出自：http://dbarecovery.com/stopatmark.html

我自己并没有实验成功。当restore dabase之后，进行restore log是，总提示我还有一个更早的backup可用，但是实际上是没有这个所谓的更早的backup的。

使用是需要注意两点：

1. 数据库必须是Full Reconvery模式的，可以在数据库的属性页中设置。

2. 必须在server的管理中的backup节点创建一个backup device。

SQL Server Exercises / SQL Server 2000 / Using STOPATMARK and STOPBEFOREMARK in SQL Server 2000

Using STOPATMARK and STOPBEFOREMARK in SQL Server 2000

Restore Your Database to a Point in Time At a Transactional Level

• Description
• Requirements
• Setup
• GUI Steps
• T-SQL Steps

Description:

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.

(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

(back to top)

Setup:

None.

(back to top)

GUI Steps:

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.

(back to top)

T-SQL Steps:

 

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.