sqlserver備份恢複指令

備份

-- A 完整備份

EXECUTE master.dbo.xp_create_subdir N'備份檔案存放目錄'

GO

BACKUP DATABASE [資料庫名稱] TO  DISK = N'備份檔案完整目錄+檔案名.bak' WITH NOFORMAT, NOINIT,  NAME = N'備份名稱', SKIP, REWIND, NOUNLOAD,  STATS = 10

-- B 差異備份

EXECUTE master.dbo.xp_create_subdir N'備份檔案存放目錄'

GO

BACKUP DATABASE [資料庫名稱] TO  DISK = N'備份檔案完整目錄+檔案名.bak' WITH  DIFFERENTIAL , NOFORMAT, NOINIT,  NAME = N'備份名稱', SKIP, REWIND, NOUNLOAD,  STATS = 10

-- C 事務日志備份

EXECUTE master.dbo.xp_create_subdir N'備份檔案存放目錄'

GO

BACKUP LOG [資料庫名稱] TO  DISK = N'備份檔案完整目錄+檔案名.trn' WITH NOFORMAT, NOINIT,  NAME = N'備份名稱', SKIP, REWIND, NOUNLOAD,  STATS = 10

恢複

-- A 資料庫改為離線，復原所有未完成的事務

ALTER DATABASE [資料庫名稱] SET OFFLINE WITH ROLLBACK IMMEDIATE 

-- 完整恢複 

restore database [資料庫名稱] from disk='備份檔案完整目錄+檔案名.bak' 

with  FILE = 1,  

replace,

NOUNLOAD,  

STATS = 10, 

NORECOVERY    -- 沒完成前必須加上此參數

-- 差異恢複  多個差異檔案時，重複執行此步驟

RESTORE DATABASE [資料庫名稱] 

FROM  DISK = N'備份檔案完整目錄+檔案名.bak' 

WITH  FILE = 1,  

NOUNLOAD,  

STATS = 10,

NORECOVERY     -- 沒完成前必須加上此參數

-- 事務日志恢複  多個事務日志時，重複執行此步驟，

RESTORE DATABASE [資料庫名稱] 

FROM  DISK = N'備份檔案完整目錄+檔案名.trn' 

WITH  FILE = 1,  

NOUNLOAD,  

STATS = 10

,NORECOVERY     -- 最後一個檔案才能去除此參數 否則無法恢複後續備份

-- 查詢所有資料庫最近備份資訊

SELECT D.name DatabaseName,  

    RestoreMode = CASE D.recovery_model_desc  

        WHEN 'SIMPLE' THEN 'Simple'  

        WHEN 'FULL' THEN 'Full'  

        ELSE 'Bulk record'  

    END,  

    ISNULL(CONVERT(varchar, BS.bdate, 120), 'Never Bakup') AS LastBakupDate,  

    BakupTypes = CASE BS.type  

        WHEN 'D' THEN 'Database'  

        WHEN 'I' THEN 'Differences'  

        WHEN 'L' THEN 'Recorde'  

        WHEN 'F' THEN 'Files or file groups'  

        WHEN 'G' THEN 'Differential file'  

        WHEN 'P' THEN 'Part'  

        WHEN 'Q' THEN 'Differences in part'  

        ELSE ''  

    END  

FROM sys.databases D LEFT JOIN    

(   

    SELECT database_name, MAX(backup_finish_date) bdate, type  

    FROM msdb.dbo.backupset  

    GROUP BY database_name, type  

) BS ON D.name = BS.database_name   

ORDER BY 1;  

-- 改為離線狀态

ALTER DATABASE [資料庫名稱] SET OFFLINE WITH ROLLBACK IMMEDIATE

 --殺程序 

declare @dbname varchar(50)

set @dbname='資料庫名稱'

declare @sql varchar(50)

declare cs_result cursor local for select 'kill '+cast(spid as varchar(50)) from sys.sysprocesses where db_name(dbid)[email protected] 

open cs_result

fetch next from cs_result into @sql

while @@fetch_status=0

begin

    execute(@sql)

    fetch next from cs_result into @sql

end

close cs_result

deallocate cs_result

-- 檢視備份的邏輯檔案名和資料庫名稱

RESTORE filelistonly   FROM  DISK = N'檔案完整路徑'

-- 恢複資料庫 

RESTORE DATABASE [資料庫名] FROM  DISK = N'檔案完整路徑' 

WITH   MOVE N'邏輯檔案名' TO N'資料資料檔案名', 

 MOVE N'邏輯檔案名' TO N'資料日志檔案名 '

 , replace 

GO