SQL BACKUP Database

Source: Internet
Author: User
Tags db2 rtrim

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.