SQL2008还原

ChangeFullBakName

move /y D:\CRMBAK\Full\CRM_backup_20*.bak D:\CRMBAK\Full\CRM_backup_full.bak

ChangeDiffBakName

move /y D:\CRMBAK\Diff\CRM_backup_20*.bak D:\CRMBAK\Diff\CRM_backup_diff.bak

SET NOCOUNT ON;
--step 10: 判断数据库是否存在
DECLARE @intDBCount int;
SELECT @intDBCount=COUNT(*) FROM sysdatabases WHERE name='CRM'; 

--step 11: 如果数据库存在，判断数据库上是否有session；
--step 12: 如果数据库不存在，则直接还原数据库(to step3)
--step 21: 如果session存在，杀掉所有session
--step 22: 如果session不存在，则直接还原数据库(to step3)
IF @intDBCount=1
BEGIN
    DECLARE @strSQL VARCHAR(100);
    DECLARE cursorSessions CURSOR FOR SELECT DISTINCT request_session_id FROM master.sys.dm_tran_locks 
    WHERE resource_type = 'DATABASE' AND resource_database_id = db_id(N'CRM')
    OPEN cursorSessions 
    DECLARE @intSPID INT FETCH NEXT FROM cursorSessions INTO @intSPID WHILE @@FETCH_STATUS =0
    BEGIN SET @strSQL='Kill ' + CONVERT(VARCHAR(10),@intSPID) + ';'
    EXEC (@strSQL) FETCH NEXT FROM  cursorSessions INTO @intSPID
    END
    CLOSE cursorSessions
    DEALLOCATE cursorSessions
END; 

--step 3: 还原数据库全备份
    RESTORE DATABASE CRM
    FROM DISK='D:\CRMBAK\Full\CRM_backup_full.bak' WITH NORECOVERY,
    MOVE 'CRM_Data' TO 'E:\DataBaseFolder\CRM_Data.mdf',
    MOVE 'CRM_Log' TO 'D:\DataBaseLogFolder\CRM_Log.ldf',
    REPLACE ; 

--step 4: 判断是否存在差异备份文件，如果存在，则还原数据库差异备份
    DECLARE @result INT;
    EXEC xp_fileexist 'D:\CRMBAK\diff\CRM_backup_diff.bak', @result OUTPUT;
    IF @result=1
    BEGIN
        RESTORE DATABASE CRM
        FROM DISK='D:\CRMBAK\diff\CRM_backup_diff.bak' WITH NORECOVERY,
        MOVE 'CRM_Data' TO 'E:\DataBaseFolder\CRM_Data.mdf',
        MOVE 'CRM_Log' TO 'D:\DataBaseLogFolder\CRM_Log.ldf',
        REPLACE ;
    END;

--step 5: 将数据库变成可用状态
RESTORE DATABASE CRM WITH RECOVERY;

DeleteDiffBak

del /q D:\CRMBAK\Diff\*