SQL BACKUP Database

Create proc Sp_backupdatabase
@bak_path nvarchar (4000) = "--Backup path;
, @baktype int = null--Backup type is full, 1 is differential, 2 is log backup
, @type int = null--Sets the library to be backed up, 0 for all libraries, 1 for the system library, 2 for all user libraries, 3 for the specified library, and 4 for the exclusion of the specified library;
, @dbnames nvarchar (4000) = "-Database to be backed up or excluded, separated, when @type=3 or 4 o'clock is active
, @overdueDay int = null--Sets the number of days to expire, the default days;
, @compression int = 0-whether a compressed backup with sql2008, 0 for No, and 1 for compression
As
--sql Server 2005/2008 Backup/delete outdated backup T-SQL Version v1.0
/*
Author:perfectaction
date:2009.04
Desc: For sql2005/2008 backup, auto-Generate library folder, can customize backup type and backup library name, etc.
You can customize the number of days a backup expires
Deleting an outdated backup feature does not delete the last backup, even if it has expired
If a library is no longer backed up, it will not delete previously expired backups
Please correct me if there is any mistake, thank you.
*/

SET NOCOUNT ON
--Open xp_cmdshell support
exec sp_configure ' show advanced options ', 1
Reconfigure with override
exec sp_configure ' xp_cmdshell ', 1
Reconfigure with override
exec sp_configure ' show advanced options ', 0
Reconfigure with override
Print char (+ '------------------------')

--Determine if the path is filled
If IsNull (@bak_path, ") ="
Begin
Print (' ERROR: Please specify backup path ')
Return
End

--Determine whether to specify a library to be backed up
If IsNull (LTrim (@baktype), ') = '
Begin
Print (' ERROR: Please specify backup type aa:0 to full, 1 for differential, 2 for log backup ')
Return
End
Else
Begin
If @baktype not between 0 and 2
Begin
Print (' ERROR: Specify backup type only, 1,2:0 is full, 1 is differential, 2 is log backup ')
Return
End
End
--Determine whether to specify a library to be backed up
If IsNull (LTrim (@type), ') = '
Begin
Print (' ERROR: Specify the library to be backed up, 0 for all libraries, 1 for the system library, 2 for all user libraries, 3 for the specified library, and 4 for exclude specified libraries ')
Return
End
Else
Begin
If @type not between 0 and 4
Begin
Print (' ERROR: Specify the library to be backed up, 0 for all libraries, 1 for the system library, 2 for all user libraries, 3 for the specified library, and 4 for exclude specified libraries ')
Return
End
End

--whether to fill in the library name when judging the specified library or excluding the library
If @type >2
If @dbnames = ' '
Begin
Print (' ERROR: The backup type is ' +ltrim (@type) + ', you need to specify the @dbnames parameter ')
Return
End

--Determining the specified expiration time specified
If IsNull (LTrim (@overdueDay), ') = '
Begin
Print (' ERROR: Must specify the backup expiration time in days, 0 to never expire ')
Return
End

--Determine if compression is supported
If @compression =1
If charindex (' + ', @ @version) =0 or charindex (' Enterprise ', @ @version) =0
Begin
Print (' ERROR: Compressed backup only supports SQL2008 Enterprise Edition ')
Return
End

--Determine if the disk exists
Declare @drives table (drive varchar (1), [size] varchar (20))
INSERT INTO @drives exec (' master.dbo.xp_fixeddrives ')
If not exists (select 1 from @drives where Drive=left (@bak_path, 1))
Begin
Print (' ERROR: The disk does not exist: ' +left (@bak_path, 1))
Return
End

--Formatting parameters
Select @bak_path =rtrim (LTrim (@bak_path)), @dbnames =rtrim (LTrim (@dbnames))
If Right (IsNull (@bak_path, '), 1)! = ' \ ' Set @[email protected]_path+ ' \ '
If IsNull (@dbnames, ')! = ' Set @dbnames = ', ' [email protected]+ ', '
Set @dbnames =replace (@dbnames, ",")

--Define variables
declare @bak_sql nvarchar (max), @del_sql nvarchar (max), @i int, @maxid int
Declare @dirtree_1 table (ID int identity (primary) key,subdirectory nvarchar (+), depth int,files int)
Declare @dirtree_2 table (ID int identit Y (+) primary key,subdirectory nvarchar (x), depth int,files int,
dbname varchar (+), Baktime Datetime,islastbak int)
declare @createfolder nvarchar (max), @delbackupfile nvarchar (max), @delbak nvarchar (max)

--Get the library name that needs to be backed up--------------------start
declare @t table (ID int identity (primary) key,name nvarchar (max))
declare @sql nvarchar (max)
Set @sql = ' Select name from sys.databases where state=0 and name!= ' tempdb ' "
+ Case W Hen @baktype =2 Then ' and recovery_model!=3 ' else ' end
+ case @type if 0 then ' and 1=1 '
when 1 then ' and data Base_id<=4 '
When 2 Then ' and Database_id>4 '
while 3 then ' and charindex (', ' +name+ ', ' ', ' [email  protected]+ ') >0 '
when 4 Then ' and charindex (', ' ' +name+ ', ' ', ' ' [email protected]+ ') ' =0 and Database_ Id>4 '
Else ' 1>2 ' end
INSERT into @t exec (@sql)
--Get the name of the library that needs to be backed up---------------------end

--Get the folder you want to create------------------start
INSERT INTO @dirtree_1 exec (' master.dbo.xp_dirtree ' [email protected]_path+ ', 0,1 ')
Select @createfolder =isnull (@createfolder, ') + ' exec master.dbo.xp_cmdshell ' MD ' [email protected]_path+ ' +name+ ' , No_output ' +char (13)
From @t as a left join @dirtree_1 as B in A.name=b.subdirectory and b.files=0 and depth=1 where b.id is null
--Get the folder you want to create-------------------end

--Generate SQL statements that process outdated backups-------------start
If @overdueDay >0
Begin
Insert into @dirtree_2 (subdirectory,depth,files) exec (' master.dbo.xp_dirtree ' [email protected]_path+ ', 0,1 ')
If @baktype =0
Delete from @dirtree_2 where depth=1 or files=0 or charindex (' _full_bak_ ', subdirectory) =0
If @baktype =1
Delete from @dirtree_2 where depth=1 or files=0 or charindex (' _diff_bak_ ', subdirectory) =0
If @baktype =2
Delete from @dirtree_2 where depth=1 or files=0 or charindex (' _log_bak_ ', subdirectory) =0
if exists (select 1 from @dirtree_2)
Delete from @dirtree_2 where IsDate (
Left (right (subdirectory,19), 8) + "+ substring (right (subdirectory,20), 11,2) + ': ' +
SUBSTRING (right (subdirectory,20), 13,2) + ': ' +substring (Right (subdirectory,20), 15,2)
) =0
if exists (select 1 from @dirtree_2)
Update @dirtree_2 Set dbname = @baktype =0 then left (Subdirectory,charindex (' _full_bak_ ', subdirectory)-1)
When @baktype =1 and left (Subdirectory,charindex (' _diff_bak_ ', subdirectory)-1)
When @baktype =2 and left (Subdirectory,charindex (' _log_bak_ ', subdirectory)-1)
Else ' End
, Baktime=left (Right (subdirectory,19), 8) + "+ substring (right (subdirectory,20), 11,2) + ': ' +
SUBSTRING (right (subdirectory,20), 13,2) + ': ' +substring (Right (subdirectory,20), 15,2)
From @dirtree_2 as a
Delete @dirtree_2 from @dirtree_2 as a left join @t as B on b.name=a.dbname where b.id is null
Update @dirtree_2 Set islastbak= case when (select Max (baktime) from @dirtree_2 where Dbname=a.dbname) =baktime
Then 1 else 0 end from @dirtree_2 as a
Select @delbak =isnull (@delbak, ') + ' exec master.dbo.xp_cmdshell ' del ' [email protected]_path+ ' +dbname+ ' \ '
+subdirectory+ ", No_output ' +char (from @dirtree_2 where islastbak=0 and DateDiff (Day,baktime,getdate ()) >@ Overdueday
End
--Generate SQL statements that process outdated backups--------------end

Begin try
Print (@createfolder)--Create the required folder for backup
EXEC (@createfolder)--Create the required folder for backup
End Try
Begin Catch
print ' ERR: ' +ltrim (Error_number ())
print ' ERR: ' +error_message ()
Return
End Catch

Select @i=1, @maxid =max (ID) from @t
While @i<[email protected]
Begin
Select @bak_sql = "+char" + ' backup ' + case @baktype =2 Then ' log ' else ' database ' end
+quotename (Name) + ' to disk= ' [email protected]_path + name+ ' \ ' +
name+ case when @baktype =0 then ' _full_bak_ ' when @baktype =1 then ' _diff_bak_ '
When @baktype =2 then ' _log_bak_ ' else null end + case when @compression =1 then ' compression_ ' Else ' end+
Replace (replace (varchar (), GETDATE (), 120), '-', '), ', ' _ '), ': ', ') +
When the case is @baktype =2 then '. trn ' when @baktype =1 and then '. Dif ' else '. Bak ' End + '
+ Case if @compression =1 or @baktype =1 then ' with ' else ' end
+ case where @compression =1 then ' compression, ' else ' end
+ case where @baktype =1 then ' differential, ' else ' end
+ Case if @compression =1 or @baktype =1 then ' noformat ' else ' end
from @t where [email protected]
Set @[email protected]+1
Begin try
Print (@bak_sql)--loop to perform backup
EXEC (@bak_sql)--loop to perform backup
End Try
Begin Catch
print ' ERR: ' +ltrim (Error_number ())
print ' ERR: ' +error_message ()
End Catch
End

Begin try
Print (@delbak)--Delete extended backups
EXEC (@delbak)--Delete extended backups
End Try
Begin Catch
print ' ERR: ' +ltrim (Error_number ())
print ' ERR: ' +error_message ()
End Catch

--Close xp_cmdshell support
--exec sp_configure ' show advanced options ', 1
--reconfigure with Override
--exec sp_configure ' xp_cmdshell ', 1
--reconfigure with Override
--exec sp_configure ' show advanced options ', 0
--reconfigure with Override

/*
Invocation Example:
--Backup system library (fully prepared)
EXEC master.dbo.sp_backupdatabase
--Backup path;
@bak_path = ' D:\temp\dbbak '
--Backup type is full, 1 is differential, 2 is log backup
, @baktype = 0
--Set up a library to be backed up, 0 for all libraries, 1 for the system library, 2 for all user libraries, 3 for the specified library, and 4 for the exclusion of designated libraries;
, @type = 1
-a database that needs to be backed up or excluded, separated, when @type=3 or 4 o'clock takes effect
, @dbnames = ' '
--Set expiration days, default days;
, @overdueDay = 31
--whether to use the sql2008 compression backup, 0 is no, 1 is the use of compression
, @compression =0

--Backup User library (fully prepared)--recommended once a week
EXEC master.dbo.sp_backupdatabase
--Backup path;
@bak_path = ' D:\temp\dbbak '
--Backup type is full, 1 is differential, 2 is log backup
, @baktype = 0
--Set up a library to be backed up, 0 for all libraries, 1 for the system library, 2 for all user libraries, 3 for the specified library, and 4 for the exclusion of designated libraries;
, @type = 3
-a database that needs to be backed up or excluded, separated, when @type=3 or 4 o'clock takes effect
, @dbnames = ' db1,db2 '
--Set expiration days, default days;
, @overdueDay = 15
--whether to use the sql2008 compression backup, 0 is no, 1 is the use of compression
, @compression =0

--Backup User library (diff)-Recommended once a day
EXEC master.dbo.sp_backupdatabase
--Backup path;
@bak_path = ' D:\temp\dbbak '
--Backup type is full, 1 is differential, 2 is log backup
, @baktype = 1
--Set up a library to be backed up, 0 for all libraries, 1 for the system library, 2 for all user libraries, 3 for the specified library, and 4 for the exclusion of designated libraries;
, @type = 3
-a database that needs to be backed up or excluded, separated, when @type=3 or 4 o'clock takes effect
, @dbnames = ' db1,db2 '
--Set expiration days, default days;
, @overdueDay = 15
--whether to use the sql2008 compression backup, 0 is no, 1 is the use of compression
, @compression =0

--Backup User library (log preparation)--1 hours recommended
EXEC master.dbo.sp_backupdatabase
--Backup path;
@bak_path = ' D:\temp\dbbak '
--Backup type is full, 1 is differential, 2 is log backup
, @baktype = 2
--Set up a library to be backed up, 0 for all libraries, 1 for the system library, 2 for all user libraries, 3 for the specified library, and 4 for the exclusion of designated libraries;
, @type = 3
-a database that needs to be backed up or excluded, separated, when @type=3 or 4 o'clock takes effect
, @dbnames = ' db1,db2 '
--Set expiration days, default days;
, @overdueDay = 15
--whether to use the sql2008 compression backup, 0 is no, 1 is the use of compression
, @compression =0
*/

SQL BACKUP Database