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

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

 

转自：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]

go

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

drop database TestDB;

go

-- Create database TestDB

use [master]

go

create database [TestDB] ON Primary

(name = 'TestDB_Data', filename = 'C:/TestDB_Data.MDF',

size = 10MB, filegrowth = 10MB)

LOG ON

(name = 'TestDB_Log', filename = 'C:/TestDB_Log.MDF',

size = 10MB, filegrowth = 10MB);

go

-- Changing the Recovery Model to full

use [master]

alter database [TestDB] set recovery full;

go

 

-- Initiate a Full backup of the database

use [master]

Backup database [TestDB] to disk = 'C:/TestDB.bak'

go

-- Create a table in the database

use [TestDB]

create table dbo.demotable1 (

demoid int identity(1,1),

demodate datetime default getdate()

);

go

-- Insert base data

use [TestDB]

insert into dbo.demotable1 default values;

go 10

-- verify base data

select * from TestDB.dbo.demotable1;

go

-- Initiate the 1st T-log backup

use [master]

backup log [TestDB] to disk = 'c:/testDB1.trn';

go

-- Insert some additional data

use [TestDB]

insert into dbo.demotable1 default values;

go 15

-- verify all the data in the database

select COUNT(*) from TestDB.dbo.demotable1;

go

/*(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]

select COUNT(*) from TestDB.dbo.demotable1;

go

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

-- or insufficient memory or disk space

/* Initiate the tail log backup*/

use [master]

BACKUP log [testDB] to disk = 'C:/TestDB_Tail_Log.trn'

with No_Truncate, Norecovery;

go

 

 

 

 

 

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

 

/* 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]

Restore database [TestDB]

from disk = 'C:/TestDB.bak'

with replace, norecovery;

go

Restore Log [TestDB]

from disk = 'c:/testDB1.trn'

with replace, norecovery;

go

Restore Log [TestDB]

from disk = 'c:/TestDB_Tail_Log.trn'

with recovery;

go

-- Verify the data

Select * from TestDB.dbo.demotable1;

Go

 

 

 

 

-- 清除测试数据

use [master]

drop database [TestDB]

go