根據自己環境自定義了一個備份腳本！
說明:
使用方法:exec master.dbo.fullbackup1 's:\backup','suzhou','full'
0.備份類型只能是full、diff或log，數據庫名不能為空
1.通過新建一個歷史表記 錄每次備份內容
2.檢查數據庫版本是否為2005以上
3.檢查當前用戶是否有權 限完成備份
4.會自動檢查指定盤符是否存在
5.檢查指定格式是否為s:\
6.如果指定的備份目錄不存在proc將自動新建，根據備份類型為full、diff或 log
7.檢查備份數據庫名suzhou是否存在並聯機
8.檢查備份數據庫名suzhou不 能是臨時數據庫
9.如果備份類型為差異備份則檢查是否有完全備份存在並且備份的這 個文件存在於指定的目錄下，
如果備份歷史表有記錄但是該備份文件不存在將終止備份
10.如果備份類型為日志備份，先檢查數據庫恢復模式是否為完整，否則將停止備 份;
將進一步檢查備份歷史表和備份文件，必須存在完全備份或差異備份,否則終止備份
USE [msdb]
GO
if OBJECT_ID('backuphistory')is not null
drop table backuphistory
go
CREATE TABLE [dbo].[backuphistory](
[sid] [int] IDENTITY(1,1) NOT NULL primary key,
[dbname] [sysname] NOT NULL,
[backtype] [char](2) NOT NULL,
[lastbackup] [datetime] NOT NULL,
[backupdesc] [varchar](20) NOT NULL,
[backupfilename] [nvarchar](max) NULL,
)
GO
use master
go
create PROCEDURE [dbo].[fullbackup1]
(
@backupPath varchar(500),
@dbname sysname,
@backuptype varchar(100)
)
with encryption
as
declare @currentuser sysname
declare @role varchar(30)
select @currentuser=system_user
DECLARE @Version numeric(18,10)
DECLARE @Error int
declare @Directory nvarchar(100)
DECLARE @CheckDirectory nvarchar(4000)
DECLARE @DirectoryInfo TABLE (FileExists bit,
FileIsADirectory bit,
ParentDirectoryExists bit)
DECLARE @ErrorMessage nvarchar(max)
DECLARE @backupPath2 nvarchar(500)
DECLARE @DirTree TABLE (subdirectory nvarchar(255), depth INT)
DECLARE @FullPath varchar(1000)
declare @backupPath3 nvarchar(500)
declare @recovery_model_desc varchar(20)
declare @backtype varchar(100)
declare @backupdesc varchar(20)
declare @backupfilename varchar(max)
-----new
DECLARE @tmp TABLE (backupfilename varchar(3000),
backuptime datetime)
declare @fullbafile varchar(3000)
declare @result int
declare @log_start int
set nocount on
--檢查用戶權限
select @role=srvrole from
(
select SrvRole = g.name, MemberName = u.name
from sys.server_principals u, sys.server_principals g, sys.server_role_members m
where g.principal_id = m.role_principal_id
and u.principal_id = m.member_principal_id
and u.name=@currentuser
) c
- -order by 1, 2
if @role !='sysadmin' or @role is null or @role=''
begin
RAISERROR('當前用戶沒有需要的權限完成備份! ',16,1)
print '你可能是越權操作或其它!'+char(13)+'請聯系 DBA!'
SET @Error = @@ERROR
return
end
--檢查服務器 版本
SET @Error = 0
SET @Version = CAST(LEFT(CAST(SERVERPROPERTY ('ProductVersion') AS nvarchar(max)),
CHARINDEX('.',CAST (SERVERPROPERTY('ProductVersion') AS nvarchar(max))) - 1) + '.'
+ REPLACE(RIGHT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)),
LEN(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar (max))) - CHARINDEX('.',
CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)))),'.','') AS numeric(18,10))
IF @Version < 9
BEGIN
RAISERROR('該備份方案僅支持 SQL Server 2005, SQL Server 2008和SQL Server 2008 R2.',16,1)
SET @Error = @@ERROR
return
END
set @Directory=@backupPath
--判斷路徑格式
IF NOT (@Directory LIKE '[a-z]:\%' )
BEGIN
SET @ErrorMessage = '輸入的目錄'+@Directory+'格式 '+ '不支持！.'+' 參考類型如: s:\backup' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
return
END
--判斷輸入的數據庫名是 否存在
IF @dbname not in(select name from sys.databases)
BEGIN
SET @ErrorMessage = '數據庫名: '+@dbname+' 不存在!.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
return
END
--判斷輸入的盤符是否存在和是否 新建目錄
--檢查指定盤符是否存在
SET @CheckDirectory = substring (@Directory,1,3)
INSERT INTO @DirectoryInfo (FileExists, FileIsADirectory, ParentDirectoryExists)
EXECUTE [master].dbo.xp_fileexist @CheckDirectory
IF NOT EXISTS (SELECT * FROM @DirectoryInfo WHERE FileExists = 0 AND FileIsADirectory = 1 AND ParentDirectoryExists = 1)
BEGIN
SET @ErrorMessage = '服務器上不存在指定的盤符:'+upper(substring (@CheckDirectory,1,1)+ CHAR(13) + CHAR(10))
RAISERROR (@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
return
END
--判斷是否輸入備份數據庫名
IF @dbname IS NULL OR @dbname = ''
BEGIN
SET @ErrorMessage = '未輸 入任何備份數據庫名.' + CHAR(13) + CHAR(10)+'備份進程已終止!'
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
return
END
else if (@dbname='tempdb' or @dbname='TEMPDB')
begin
SET @ErrorMessage = '臨時數據庫不需要備份.' + CHAR(13) + CHAR(10)+'備份進 程已終止!'
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
return
end
else if (@dbname in ( select name from sys.databases where state_desc='OFFLINE' or state_desc='offline'))
begin
SET @ErrorMessage = '脫機的數據庫'+@dbname+'不需要備份.' + CHAR(13) + CHAR(10)+'備份進程已終止!'
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
return
end
--判斷輸入類型
if @backuptype not in ('full','diff','log')
begin
print '#########################嚴重警告###############嚴重警告 #################################'
print '不支持類型 '+@backuptype+'! 只能輸入(full:完全備份; diff:差異備份; log:日志備 份) '
print '有問題請聯系ocpyang!'
print '#########################嚴重警告###############嚴重警告 ###################################'
return
end
--判斷目錄是否存在
SET @backupPath2=@backupPath+'\'+@dbname
INSERT INTO @DirTree(subdirectory, depth)
EXEC master.sys.xp_dirtree @backupPath
IF NOT EXISTS (SELECT 1 FROM @DirTree WHERE subdirectory = @DBName)
begin
print '系統將新建目錄：'+@backupPath2+' ............'
EXEC master.dbo.xp_create_subdir @backupPath2
print '目 錄：'+@backupPath2+'新建成功!'
print ' '
delete from @DirTree
end
else
begin
print '--------------------------------------------------------------------- -- '
print '目錄：'+@backupPath2+'已經存在！'
print ' '+char(13)+'備份運行中 $$$$$$$$$$$$$$$$$$$$$$$$$$$$$'
print '------------------------------- ---------------------------------------- '
delete from @DirTree
end
--開始完全備份
if @backuptype='full'
begin
print '............................................................................ .'
print '開始完全備份.....請稍等'
print '............................................................................ .'
--隱藏檢查目錄
set @backupPath3=@backupPath2+'\'+'full'
INSERT INTO @DirTree(subdirectory, depth)
EXEC master.sys.xp_dirtree @backupPath3
IF NOT EXISTS (SELECT 1 FROM @DirTree WHERE subdirectory = @backupPath3)
EXEC master.dbo.xp_create_subdir @backupPath3
delete from @DirTree
set @FullPath = @backuppath3+'\'+@dbname+'_'+@backuptype+'_'+replace (replace(replace(convert(varchar,getdate(),20),'-',''),' ',''),':','')+ '.bak'
backup database @dbname to disk=@FullPath
WITH buffercount = 20, maxtransfersize = 2097152 ,
COMPRESSION,RETAINDAYS=15,NOFORMAT,NOINIT,
NAME=N'完整備份 ',SKIP,NOREWIND,
NOUNLOAD,STATS=10
set @backtype='D'
set @backupdesc='完全備份'
set @backupfilename=@FullPath
insert into msdb.dbo.backuphistory
(dbname,backtype,lastbackup,backupdesc,backupfilename)
values(@dbname, @backtype,GETDATE(), @backupdesc,@backupfilename)
SET @Error = @@ERROR
if @Error !=0
begin
SET @ErrorMessage = '數據庫'+@dbname+'完全備份未順利完成 ！: ' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
return
end
print ' '
print '------------------------------------------------------- ---------------- '
print @dbname+'完全備份 '+@FullPath+' 已經完 成!'
print '---------------------------------------------------------- ------------- '
return
end
--開始差異備份
else if @backuptype='diff'
begin
print ' '
print '............................................................................ .'
print '開始差異備份.....請稍等'
print '............................................................................ .'
--檢查是否有完全備份並存在
insert into @tmp
select top 1 a.backupfilename,
MAX(a.lastbackup) as backuptime from msdb.dbo.backuphistory a
where a.dbname=@dbname and a.backtype='D'
group by backupfilename
order by a.backupfilename desc
if not exists (select top 1 1 from @tmp )
begin
SET @ErrorMessage = '數據庫'+@dbname+'沒有完全備份歷史記錄!! ' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
return
end
else
begin
select @fullbafile=backupfilename from @tmp
exec xp_fileexist @fullbafile, @result output
if (@result=0 )
begin
SET @ErrorMessage = '數據庫'+@dbname+'完全備份文 件不存在!做差異備份無意義!' + CHAR(13) + CHAR(10)
RAISERROR (@ErrorMessage,16,1) WITH NOWAIT
return
end
end
--隱藏檢查目錄
set @backupPath3=@backupPath2+'\'+'diff'
INSERT INTO @DirTree(subdirectory, depth)
EXEC master.sys.xp_dirtree @backupPath3
IF NOT EXISTS (SELECT 1 FROM @DirTree WHERE subdirectory = @backupPath3)
EXEC master.dbo.xp_create_subdir @backupPath3
delete from @DirTree
set @FullPath = @backuppath3+'\'+@dbname+'_'+@backuptype+'_'+replace (replace(replace(convert(varchar,getdate(),20),'-',''),' ',''),':','')+ '.diff'
backup database @dbname to disk=@FullPath
WITH buffercount = 30, maxtransfersize = 2097152 ,
COMPRESSION, DIFFERENTIAL,RETAINDAYS=8,NOFORMAT,NOINIT,
NAME=N'差異備份 ',SKIP,NOREWIND,
NOUNLOAD,STATS=10
set @backtype='I'
set @backupdesc='差異備份'
set @backupfilename=@FullPath
insert into msdb.dbo.backuphistory
(dbname,backtype,lastbackup,backupdesc,backupfilename)
values(@dbname, @backtype,GETDATE(), @backupdesc,@backupfilename)
SET @Error = @@ERROR
if @Error !=0
begin
SET @ErrorMessage = '數據庫'+@dbname+'差異備份未順利完成！: ' + CHAR (13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
return
end
print ' '
print '--------------------------------------------------------------------- -- '
print @dbname+'差異備份 '+@FullPath+' 已經完成! '
print '------------------------------------------------------ ----------------- '
return
end
