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