SQL Server 備份遷移策略

pursuer.chen發表於2014-12-30
SQLServer

標籤：SQL SERVER/MSSQL SERVER/資料庫/DBA/xp_cmdshell/備份壓縮

概述

     當備份空間不是很充裕的情況下需要找方法將備份檔案拷貝到專用的備份機器上去，特別是儲存空間不夠優越的企業更是如此，接下來就分享兩種遷移備份檔案的方法。

 

目錄

伺服器配置選項

查詢伺服器配置

使用xp_cmdshell方法之前必須先開啟對應的伺服器配置選項，查詢系統中每個伺服器範圍的配置選項值使用以下語句。

--系統中每個伺服器範圍的配置選項值對應一行
SELECT * FROM sys.configurations
ORDER BY name DESC
GO

 開啟xp_cmdshell

---開啟xp_cmdshell
sp_configure 'show advanced options', 1;--開啟sp_configure系統儲存過程高階選項,預設為0
go
reconfigure;---指定如果配置設定不需要伺服器停止並重新啟動，則更新當前執行的值.RECONFIGURE 還會檢查新的配置值中是否有無效值(如果是RECONFIGURE WITH OVERRIDE則會跳過錯誤檢查)
go
sp_configure 'xp_cmdshell', 1;
go
reconfigure;
go
sp_configure 'show advanced options', 0;
go

開啟allow updates

在伺服器範圍的配置裡面如果‘allow updates’的vlues值是‘1’的話，會阻止對系統表的更新，即上面開啟xp_cmdshell會被阻止，所以必須先開啟allow updates

----開啟允許更新系統表功能(當更改系統配置選項時提示不執行對系統目錄即時更新時需要開啟改功能)
sp_configure 'show advanced options', 1;
GO
sp_configure 'allow updates',0 ---開啟允許更新系統表
go
sp_configure 'show advanced options', 0;
go
----關閉更新系統表功能
sp_configure 'show advanced options', 1;
GO
sp_configure 'allow updates',1
go
sp_configure 'show advanced options', 0;
go

xp_cmdshell方法

先建立一個備份資料庫的儲存過程，然後通過作業呼叫儲存過程執行備份。

USE [master] 
GO
CREATE PROCEDURE [dbo].[SP_bakupDataBase] 
(@dbname nvarchar(50)
)
as 
Declare @strCmdShell varchar(300) 
Declare @FullFileName Varchar(200) 
Declare @strPath NVARCHAR(200)
Declare @ToFileName varchar(200) 
Declare @SQLStr varchar(500) 
Declare @SQLStr2 varchar(500) 
Declare @FlagDel INT 
declare @backupSetId as int

SET @strPath = convert(NVARCHAR(19),getdate(),120)
SET @strPath = REPLACE(REPLACE(convert(NVARCHAR(19),getdate(),120), ':' , ''),' ','_')
SET @FullFileName='E:\'+@dbname+'\'+@dbname+'_backup_'+@strPath+'.bak' 
SET @ToFileName='\\192.168.1.1\E$\備份' 
SET @FlagDel=1
SET @SQLStr='copy '+@FullFileName+' '+@ToFileName 
SET @SQLStr2='del ' +@FullFileName 
BackUp DataBase @dbname To Disk= @FullFileName WITH NOINIT , NOUNLOAD , NOSKIP, NOFORMAT , STATS = 10,CHECKSUM,CONTINUE_AFTER_ERROR
---驗證備份集是否有效
select @backupSetId = position from msdb..backupset where database_name=@dbname and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=@dbname )
IF @backupSetId is null begin raiserror(N'驗證失敗。找不到資料庫資料庫的備份資訊。', 16, 1) end
RESTORE VERIFYONLY FROM  DISK = @FullFileName WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND
EXEC master.dbo.xp_cmdshell 'net use \\192.168.1.1\E$ 123456 /user:192.168.1.1\user' 
EXEC Master..xp_cmdshell @SQLStr 
IF (@FlagDel =1)
BEGIN 
EXEC master.. xp_cmdshell @SQLStr2
END

---圖片解釋

執行備份

USE [master] 
EXEC sp_bakupDataBase 'AdventureWorks2008R2'

 

dos方法

 如果遠端備份空間不夠充足，需要將備份的資料庫進行壓縮後再拷貝，先通過正常的備份將資料庫備份到本地，然後使用RAR.EXE壓縮軟體，壓縮並拷貝到遠端電腦，將下面的批處理儲存成bat字尾格式，然後可以通過任務計劃呼叫批處理進行定時操作。

@echo off
rem  為註釋語法 timeout 為window 2003版本才有的指定 xp不相容


setlocal enabledelayedexpansion
set /a t=0*3600
:start
for /f "delims=" %%a in ("dir /b 'D:\DataBase\person\person*'") do (
"c:\Program Files\WinRAR\winrar.exe" a -as -r -EP1 -ibck -df "\\192.168.1.1\DataBase\person\person_.rar"  -m3 -agyyyymmddhhmmss "D:\DataBase\person\p*"
rem del /q /f "D:\DataBase\person\p*"
)
rem timeout /t %t% /nobreak
rem goto :start

---圖片解釋

總結

凡事都有利有弊，對於第一種方案開啟XP_cmdshell存在一定的安全風險，當你的服務啟動賬戶的許可權過高的話別人就可以通過XP_cmdshell來進行很多危險的操作，包括新增賬戶、新建資料夾、刪除檔案等，所以在使用完之後建議關閉 XP_cmdshell。如果備份空間足夠充裕不需要這麼麻煩，第二種方法對伺服器的壓力比較大而且遷移時間也很長，不建議使用。

 

 

 

備註：

    作者：pursuer.chen

    部落格：http://www.cnblogs.com/chenmh

本站點所有隨筆都是原創，歡迎大家轉載；但轉載時必須註明文章來源，且在文章開頭明顯處給明連結，否則保留追究責任的權利。

《歡迎交流討論》

相關文章