/***************** 備份 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