Restore database to the point in time in SQL Server

最新推荐文章于 2024-04-14 10:00:00 发布

envykok

最新推荐文章于 2024-04-14 10:00:00 发布

阅读量231

点赞数

分类专栏： SQL 经典问题 Best Practice 文章标签： database sql server db2 transactions disk 2010

本文链接：https://blog.csdn.net/envykok/article/details/5879296

版权

SQL 经典问题同时被 2 个专栏收录

34 篇文章 0 订阅

订阅专栏

Best Practice

27 篇文章 0 订阅

订阅专栏

Before restore our database to the point in time, we need to make sure at least :

our database recovery model is full
we do have a latest database full backup
we do have a few transaction log backup

For #3, we can use Maintenance plan of SQL Server Management Studio to get. Here is the process steps.

1.Create a new maintenance plan

2. Add 'back up database task'

3. Select backup type - transaction log for specific database

4. Add Schedular to task

5. View the history

Here is 3 steps to restore your database to the point in time.

Perform a tail-log backup.
Restore the latest full backup with NORECOVERY .
Restore all the log bakcup in order .

USE master GO -- The first step in the process is to perform a tail-log backup BACKUP LOG TEST_DB2 TO DISK='C:/Program Files/Microsoft SQL Server/MSSQL10.MSSQLSERVER/MSSQL/Backup/Test_DB2_TailLOG.bak' WITH NORECOVERY GO --The second step is restore the latest full backup. RESTORE DATABASE TEST_DB2 FROM DISK = 'C:/Program Files/Microsoft SQL Server/MSSQL10.MSSQLSERVER/MSSQL/Backup/Test_DB2_FULL.bak' WITH MOVE 'TEST_DB2' TO 'C:/Program Files/Microsoft SQL Server/MSSQL10.MSSQLSERVER/MSSQL/DATA/TEST_DB2.mdf', MOVE 'TEST_DB2_log' TO 'C:/Program Files/Microsoft SQL Server/MSSQL10.MSSQLSERVER/MSSQL/DATA/TEST_DB2_Log.ldf', NORECOVERY --IMPORTANT!!! GO --The third step is restore all the log bakcup in order. /* A transaction log backup keeps track of all transactions that have occurred since the last transaction log backup; it also allows you to restore your database to a point in time before a database error occurred. Transaction log backups occur in sequence, creating a chain. When restoring a sequence of transaction log backups to a point in time, it is required that the transaction log files are restored in order. */ RESTORE LOG TEST_DB2 FROM DISK = 'C:/Program Files/Microsoft SQL Server/MSSQL10.MSSQLSERVER/MSSQL/Backup/TEST_DB2_backup_2010_09_12_155624_9843780.trn' WITH NORECOVERY--IMPORTANT!!! GO RESTORE LOG TEST_DB2 FROM DISK = 'C:/Program Files/Microsoft SQL Server/MSSQL10.MSSQLSERVER/MSSQL/Backup/TEST_DB2_backup_2010_09_12_160002_2935036.trn' WITH STOPAT = N'2010-09-12 15:52:59.940', RECOVERY--IMPORTANT!!! GO