How to backup log after MDB file broken.

CREATE DATABASE BackupDemo
GO
USE BackupDemo
GO
CREATE TABLE dbo.T1
(
    LogDesc NVARCHAR(200),
    LogTime DATETIME DEFAULT GETDATE()
)
GO
INSERT INTO dbo.T1(LogDesc)
SELECT 'Before full backup'
GO
BACKUP DATABASE [BackupDemo] TO  DISK = N'I:\Backup\BackupDemo_Full.bak' 
WITH NOFORMAT, NOINIT,  NAME = N'BackupDemo-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
INSERT INTO dbo.T1(LogDesc)
SELECT 'after full backup'
GO
--Stop SQL SERVER AND Delete the mdb file and restart SQL SERVER
GO
--备份为日志,一定要使用NO_TRUNCATE,否则无法备份
BACKUP LOG [BackupDemo] TO  DISK = N'I:\Backup\BackupDemo_LOG.bak' 
WITH NOFORMAT, NOINIT,NO_TRUNCATE
GO
--恢复数据库
RESTORE DATABASE [BackupDemo] FROM  DISK = N'I:\Backup\BackupDemo_Full.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO
RESTORE LOG [BackupDemo] FROM  DISK = N'I:\Backup\BackupDemo_LOG.bak' WITH  FILE = 1,  NOUNLOAD,  STATS = 10
GO
--检查数据库
USE BackupDemo
GO
SELECT * from dbo.T1

 

在实例和数据库发生崩溃时：

1.将数据库相关文件复制保存，以防止后续错误修改损坏文件；

2.在新服务器上创建同名数据库(确保文件名与原文件名相同)，关闭服务器，删除数据文件（MDF）,将原有LDF文件覆盖新创建的日志文件，重启服务器

3.使用NO_TRUNCATE来备份尾日志文件。

4.使用原有备份文件和尾日志文件还原数据库。