最近公司剛剛搭建了一台安防綜合平台,綜合平台服務器上安裝了SQL Server 2005,由於沒有備份服務器,所以只能異地備份數據庫了。在網上找了很多的資料,自己整合了一下 。
一、開啟SQL Agent服務,並設置為“自動”
二、備份
1、SQL代理-作業-新建作業,這是在SQL2005下的操作,其實SQL2000也是大同小異(管理→SQL Server代理→右鍵作業→新建作業)
圖1
2、在常規中輸入名字
圖2
3、“步驟”項—>新建,輸入名稱,選中數據,輸入以下語句,
圖3
語句如下:
--在SQL Server2005中cmdshell命令默認是關閉的,首先要開啟cmdshell命令:
--顯示高級選項(僅需執行一次)
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
--允許執行xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO
declare @sql varchar(4000)
declare @backupfile varchar(2000)
declare @now datetime
declare @retaindays int
declare @deletefiles varchar(2000)
declare @cmd varchar(2000)
declare @i int
declare @User varchar(2000)
declare @Pwd varchar(2000)
declare @Store varchar(2000)
declare @IPPart varchar(2000)
declare @IP varchar(2000)
-------------以下自行修改-----------
set @Store='EmployeeInfo' --------數據庫名-----------
set @User ='liuhs' ----------用戶名----------要管理員權限
set @Pwd ='winter' ----------密碼----------密碼不要有符號
set @IPPart='d$\back' -----路徑-----
set @IP='192.168.3.42' -----服務器IP或主機名--
set @retaindays=1 ---要保留備份的天數
------------以上自行修改------------
--添加映射驅動器
declare @string nvarchar(200)
set @string = 'net use z: \\'+@IP+'\'+@IPPart+' "winter" /user:hp-liuhs\'+@User
exec master..xp_cmdshell @string
set @now=getdate()
---刪除以前的備份,這個還沒有實現好,有時間再修改了
set @i=0
while (@i < 30)
begin
set @deletefiles = 'z:\data'+@Store+ '_db_' +convert(varchar(8),dateadd(dd,-@retaindays- @i,@now),112)++'.BAK'
set @cmd='del ' + @deletefiles
exec master..xp_cmdshell @cmd
set @i = @i +1
end
----開始備份
set @backupfile='z:\data'+ @Store + '_db_'+ replace(replace(replace(convert(varchar,getdate(),20),'-',''),' ',''),':','')+'.BAK'
set @sql='backup database ' + @Store + ' to disk='''+@backupfile+''' with retaindays='+convert(varchar(10),@retaindays)
exec (@sql)
set @backupfile='z:\data'+ @Store +'_tlog_'+replace(replace(replace(convert(varchar,getdate(),20),'-',''),' ',''),':','')+'.TRN'
set @sql='backup LOG ' + @Store + ' TO DISK='''+@backupfile+''' with retaindays='+convert(varchar(10),@retaindays)
exec (@sql)
---刪除映射
exec master..xp_cmdshell 'net use z: /delete'
4、新建作業計劃,在指定時間內備份
圖4
5、最後查詢備份服務器是否備份成功
如果出現了“系統錯誤 5, 拒絕訪問”:
首先要確定對備份服務器要有足夠的權限,最好是“Administrators”組賬戶。
其次 到備份服務器上,打開組策略->計算機配置->Windows設置->安全設置->本地策略->安全選項->網絡訪問:本地賬戶的共享和安全模式->經典-本地用戶以自己的身份驗證。