[SQL Server] 主数据文件损坏(或丢失)情况下，如何备份尾部事务日志.

末蓝、 2022-09-30 11:43 95阅读 0赞

本文描述： 主数据文件损坏(或丢失)， 而事务日志文件未损坏的情况下， 如何备份尾部事务日志（自上次备份日志，至发生损坏这段时间的事务日志）.    (当然，这么做的目的，是可以恢复数据库至损坏前的最近状态)。

转自：[http://sudeeptaganguly.wordpress.com/2011/04/15/taillogbackupwithoutdatafile/][http_sudeeptaganguly.wordpress.com_2011_04_15_taillogbackupwithoutdatafile]

/\* Create Tail-log backup after a crash, if the primary data file is missing

\------------------------------------------------------------------------------

\*/

\-- 1.创建测试数据库, 设置为"完整"恢复模式

use\[master\]

ifexists(select 1 from sys.databases where name = 'TestDB')

dropdatabase TestDB;

\-- Create database TestDB

use\[master\]

createdatabase \[TestDB\] ON Primary

(name= 'TestDB\_Data', filename = 'C:/TestDB\_Data.MDF',

size= 10MB, filegrowth = 10MB)

LOGON

(name= 'TestDB\_Log', filename = 'C:/TestDB\_Log.MDF',

size= 10MB, filegrowth = 10MB);

\-- Changing the Recovery Model to full

use\[master\]

alterdatabase \[TestDB\] set recovery full;

\-- Initiate a Full backup of the database

use\[master\]

Backupdatabase \[TestDB\] to disk = 'C:/TestDB.bak'

\-- Create a table in the database

use\[TestDB\]

createtable dbo.demotable1(

demoidint identity(1,1),

demodatedatetime default getdate()

);

\-- Insert base data

use\[TestDB\]

insertinto dbo.demotable1 default values;

go 10

\-- verify base data

select\* from TestDB.dbo.demotable1;

\-- Initiate the 1st T-log backup

use\[master\]

backuplog \[TestDB\] to disk = 'c:/testDB1.trn';

\-- Insert some additional data

use\[TestDB\]

insertinto dbo.demotable1 default values;

go 15

\-- verify all the data in the database

selectCOUNT(\*) from TestDB.dbo.demotable1;

/\*(total = 25, till log backup it was 10)\*/

\-- 手动模拟故障: 关闭实例, 手动删除主数据文件, 再启动实例

\-- initiate the crash, for this demo, I've shutdown the server and

\-- delete the data file manually, to verify whether the tail-log backup is

\-- possible or not if the data file is not available

\-- try to access the table

\-- 备份尾部事务日志

use\[TestDB\]

selectCOUNT(\*) from TestDB.dbo.demotable1;

\-- Database 'TestDB' cannot be opened due to inaccessible files

\-- or insufficient memory or disk space

/\* Initiate the tail log backup\*/

use\[master\]

BACKUPlog \[testDB\] to disk = 'C:/TestDB\_Tail\_Log.trn'

withNo\_Truncate, Norecovery;

\-- 恢复至故障发生前的最新状态.

/\* This complete the tail log backup without the primary data file \*/

/\* The script will not be successful, if we can't restore the tail log.

Thus, we are going to restore the database and verify the data before cleanup \*/

use\[master\]

Restoredatabase \[TestDB\]

fromdisk = 'C:/TestDB.bak'

withreplace, norecovery;

RestoreLog \[TestDB\]

fromdisk = 'c:/testDB1.trn'

withreplace, norecovery;

RestoreLog \[TestDB\]

fromdisk = 'c:/TestDB\_Tail\_Log.trn'

withrecovery;

\-- Verify the data

Select\* from TestDB.dbo.demotable1;

\-- 清除测试数据

use\[master\]

dropdatabase \[TestDB\]

[http_sudeeptaganguly.wordpress.com_2011_04_15_taillogbackupwithoutdatafile]: http://sudeeptaganguly.wordpress.com/2011/04/15/taillogbackupwithoutdatafile/