SQL Server type automatic decision and conditional check backup script

Customize a backup script based on your environment!

Description

How to use: Exec master.dbo.fullbackup1 ' s:\backup ', ' Suzhou ', ' full '

0. Backup type can only be full, diff or log, database name cannot be empty

1. Record each backup by creating a new history table

2. Check whether the database version is more than 2005

3. Check whether the current user has permission to complete the backup

4. Automatically checks whether the specified letter exists

5. Check that the specified format is s:\

6. If the specified backup directory does not exist proc will be automatically created, depending on the backup type of full, diff, or log

7. Check that the backup database name Suzhou exists and is online

8. Check BACKUP database name Suzhou cannot be a staging database

9. If the backup type is a differential backup, check that a full backup exists and that the file that is backed up exists in the specified directory.

If the Backup history table has a record but the backup file does not exist it will terminate the backup

10. If the backup type is log backup, first check that the database recovery mode is complete, otherwise the backup will stop;

The Backup history table and backup files will be checked further, a full or differential backup must exist, or the backup should be terminated

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] () 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

--Checking user permissions

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 (' The current user does not have the required permissions to complete the backup! ', 16, 1)

print ' You may be ultra vires operation or otherwise! ' +char (13) + ' please contact dba! '

SET @Error = @ @ERROR

Return

End

--Check Server version

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 (' This backup scenario only supports SQL Server, SQL Server 2008 and SQL Server 2008 R2. ', 16, 1)

SET @Error = @ @ERROR

Return

End

Set @Directory = @backupPath

--Judgment path format

IF not (@Directory like ' [a-z]:\% ')

BEGIN

SET @ErrorMessage = ' entered directory ' + @Directory + ' format ' + ' does not support!. ' + ' reference type such as: S:\backup ' + char (+) + char (10)

RAISERROR (@ErrorMessage, 16,1) with nowait

SET @Error = @ @ERROR

Return

End

--Determine if the entered database name exists

IF @dbname not in (select name from sys.databases)

BEGIN

SET @ErrorMessage = ' database name: ' + @dbname + ' does not exist!. ' + char (+ char) (10)

RAISERROR (@ErrorMessage, 16,1) with nowait

SET @Error = @ @ERROR

Return

End

--Determine if the entered letter is present and whether to create a new directory

--Check to see if the specified drive character exists

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 = ' The specified disk character does not exist on the server: ' +upper (substring (@CheckDirectory, 1,1) + char (+ char (10))

RAISERROR (@ErrorMessage, 16,1) with nowait

SET @Error = @ @ERROR

Return

End

--to determine whether to enter the backup database name

IF @dbname is NULL OR @dbname = '

BEGIN

SET @ErrorMessage = ' No backup database name was entered. ' + char (+ char (10) + ' backup process terminated! '

RAISERROR (@ErrorMessage, 16,1) with nowait

SET @Error = @ @ERROR

Return

End

else if (@dbname = ' tempdb ' or @dbname = ' tempdb ')

Begin

SET @ErrorMessage = ' Staging database does not require backup. ' + char (+ char (10) + ' backup process terminated! '

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 = ' offline database ' + @dbname + ' does not require backup. ' + char + char (10) + ' backup process terminated! '

RAISERROR (@ErrorMessage, 16,1) with nowait

SET @Error = @ @ERROR

Return

End

--Judging input types

If @backuptype not in (' Full ', ' diff ', ' Log ')

Begin

print ' ######################## #严重警告 ############## #严重警告 ################################# '

print ' does not support type ' + @backuptype + '! can only enter (full: complete backup; diff: differential backup; log: Log backup) '

print ' Have questions please contact ocpyang! '

print ' ######################## #严重警告 ############## #严重警告 ################################### '

Return

End

--To determine whether a directory exists

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

The print ' system will create a new directory: ' + @backupPath2 + ' ... '

EXEC Master.dbo.xp_create_subdir @backupPath2

print ' directory: ' + @backupPath2 + ' new success! '

print '

Delete from @DirTree

End

Else

Begin

print '-----------------------------------------------------------------------'

print ' directory: ' + @backupPath2 + ' already exists! '

print ' +char (13) + ' backup run $$$$$$$$$$$$$$$$$$$$$$$$$ $$$$'

print '-----------------------------------------------------------------------'

Delete from @DirTree

End

--Start Full backup

If @backuptype = ' full '

Begin

Print ' ... ..... ..... ....... ....... ...... ..... ... ............................

Print ' Begins full backup ... Please wait a moment '

Print ' ... ..... ..... ....... ....... ...... ..... ... ............................

--Hide Check Directory

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 (Varchar,getdate (), 20 ), ', ', ', ', ', ', ', ', ', ', ', '. Bak '

Backup Database @dbname to disk= @FullPath

with buffercount = maxTransferSize = 2097152,

Compression,retaindays=15,noformat,noinit,

Name=n ' full backup ', Skip,norewind,

nounload,stats=10

Set @backtype = ' D '

Set @backupdesc = ' Full backup '

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 = ' database ' + @dbname + ' full backup not completed successfully! : ' + char (+) + char (10)

RAISERROR (@ErrorMessage, 16,1) with nowait

Return

End

print '

print '-----------------------------------------------------------------------'

Print @dbname + ' full backup ' + @FullPath + ' has been completed! '

print '-----------------------------------------------------------------------'

Return

End

--Start differential backup

else If @backuptype = ' diff '

Begin

print '

Print ' ... ..... ..... ....... ....... ...... ..... ... ............................

print ' starts differential backup .... Please wait a moment '

Print ' ... ..... ..... ....... ....... ...... ..... ... ............................

--Check for a full backup and exist

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 = ' database ' + @dbname + ' does not have a full backup history!! ' + char (+) + 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 = ' database ' + @dbname + ' full backup file does not exist! Do differential backups meaningless! ' + char (+) + char (10)

RAISERROR (@ErrorMessage, 16,1) with nowait

Return

End

End

--Hide Check Directory

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 (Varchar,getdate (), 20 ), ', ', ', ', ', ', ', ', ', ', '. diff '

Backup Database @dbname to disk= @FullPath

with buffercount = maxTransferSize = 2097152,

COMPRESSION, Differential,retaindays=8,noformat,noinit,

Name=n ' differential backup ', Skip,norewind,

nounload,stats=10

Set @backtype = ' I '

Set @backupdesc = ' differential backup '

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 = ' database ' + @dbname + ' differential backup has not been successfully completed! : ' + char (+) + char (10)

RAISERROR (@ErrorMessage, 16,1) with nowait

Return

End

print '

print '-----------------------------------------------------------------------'

Print @dbname + ' differential backup ' + @FullPath + ' has been completed! '

print '-----------------------------------------------------------------------'

Return

End