lm备份还原操作.sql

该系统是留言版系统

/***************** 备份 LeaveMessage 数据库 *******************/
use master;
--==========================

--创建一个名为LMBakup的命名备份设备 for DataBase
exec sp_addumpdevice 'disk','LMBakup','H:\DBBackups\LMBackup.bak';
--创建一个名为LMBackLog的命名备份设备 for Log
exec sp_addumpdevice 'disk','LMBackLog','H:\DBBackups\LMBackLog.bak';

--备份 数据库 和 事务日志
backup database [LeaveMessage] to LMBakup;
backup log [LeaveMessage] to LMBackLog;
--==========================



--对数据库 LeaveMessage 执行一次差异备份
backup database [LeaveMessage] to 
 disk = 'H:\DBBackups\LMDiffBack.bak'
 with differential
--==========================



--创建一个名为LMMirrorBack的命名备份设备 for DataBase Mirror（镜像）
exec sp_addumpdevice 'disk','LMMirrorBack','H:\DBBackups\LMMirrorBack.bak';

--将 数据库 备份到 LMBakup 设备 并 将此设备镜像到 LMMirrorBack 设备
backup database [LeaveMessage]
 to LMBakup
 mirror to LMMirrorBack
 with format
--==========================



--执行一个名为LMTail的尾日志备份，将数据库还原到故障点
backup log [LeaveMessage]
 to disk = 'H:\DBBackups\LMTail.bak'
-- with norecovery, no_truncate
--==========================



--对LeaveMessage数据库创建名为 LM_dbss2008 的数据库快照，
--指定其稀疏文件的名称为LM_data_2008.ss
--示例中随意使用了扩展名 .ss
create database LM_dbss2008 on(
 name = LeaveMessage,
 filename = 'G:\DBBackups\LM_data_2008.ss')		--确保所在磁盘（这里指G盘）为NTFS格式
 as snapshot of LeaveMessage;
/*------------------------ 备份 OVER -----------------------------*/







/***************** 还原 LeaveMessage 数据库 *******************/
use master




--从完整备份LMBakup还原数据库LeaveMessage
restore database [LeaveMessage]
 from LMBakup
 with norecovery, replace					--如果没有加replace，将提示备份日志尾部
 --还原尚未完成，继续下面的差异备份还原
restore database [LeaveMessage]
 from disk = 'H:\DBBackups\LMDiffBack.bak'
 with recovery




--从镜像LMMirrorBack还原数据库LeaveMessage
restore database [LeaveMessage]
 from LMMirrorBack
 with recovery, replace




--从 数据库快照 恢复数据（恢复被删除的行）
use LeaveMessage;
delete from LeaveMessage.dbo.LM01
insert into LeaveMessage.dbo.LM01
select * from LM_dbss2008.dbo.LM01
/*------------------------ 还原 OVER -----------------------------*/




--查看
use LeaveMessage;
select * from LM01