Monday, September 25, 2017
 
SQL Server Script Repository
Facebook Twitter LinkedIn Google+ Addthis
 
     

SQL Script - Database Backup Solution

By Saleem Hakani on 8/24/2014

This script allows you to Perform Full/Log or Differential backups of User databases. It also allows you to perform full System database backups from your local SQL Server instance.
 
Syntax: Exec SQLOPS_BackupDatabases <@cmd>, <@dbname>, <@dbType>, <@backupType>, <@BackupLocation>
@cmd = <Help | Execute>
@dbName = <database_name | ALL> *You can specify ALL to perform database backup of all user databases
@dbType = < User | System > *User: Will perform backup of user database, *System: will perform backup of system database(s)
@backupType = <Full | Diff | Log>
@backupLocation = <Location of where backups would be stored>

 

--Author: Saleem Hakani (http://sqlcommunity.com)

Create Procedure [dbo].[SQLOPS_BackupDatabases] (@cmd Varchar(20)=null, @DBName Varchar(255)=null, @DBType Varchar(50)=null, @BackupType Varchar(20)=null, @Location Varchar(1000)=null, @Copy_Only Varchar(5)='False', @MultiDevice int=1)

as

--Author: Saleem Hakani (Http://sqlcommunity.com)

--Description: Script to perform database backups. (Support SQL Server 2008, 2008 R2, 2012, & 2014)

SET NOCOUNT ON

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

Declare @BackupDate Varchar(50), @SQLOpsCommand NVarchar(4000)

SET @BackupDate = REPLACE(REPLACE(CONVERT(VARCHAR(20),GETDATE(),113),' ','_'),':','_')

If (@Cmd is null or @cmd <> 'Execute')

Begin

Print 'SQLOPS_BackupDatabases Allows you to Perform Full/Log/Diff backups of User databases and full backups of system databases.'

Print '=========================================================================================================================='

Print 'Author: Saleem Hakani (http://sqlcommunity.com)'

Print ' '

Print 'Syntax: Exec SQLOPS_BackupDatabases <@cmd>, <@dbname>, <@dbType>, <@backupType>, <@BackupLocation>'

Print '@cmd = <Help | Execute>'

Print '@dbName = <database_name | ALL> *You can specify ALL to perform database backup of all user databases'

Print '@dbType = < User | System > *User: Will perform backup of user database, *System: will perform backup of system database(s)'

Print '@backupType = <Full | Diff | Log>'

Print '@backupLocation = <Location of where backups would be stored>'

Print '@MultiDevice = <Number of files you want to create for your FULL database backup (Only two values supported: 1 or 4)'

Print '@Copy_Only = <TRUE | FALSE> TRUE allows you to perform COPY_ONLY backup and FALSE performs regular database backup'

Print ' '

Print 'How to take a backup of a user database called SQLOPS'

Print 'Exec SQLOPS_BackupDatabases ''Execute'',''SQLOPS'',''USER'',''FULL'',''Z:\MSSQL\Backups'';'

Print ' '

Print 'How to take a COPY_ONLY backup of a user database called SQLOPS'

Print 'Exec SQLOPS_BackupDatabases ''Execute'',''SQLOPS'',''USER'',''FULL'',''Z:\MSSQL\Backups'',@COPY_ONLY=''True'';'

Print ' '

Print 'How to take a backup of a user database called SQLOPS and split the backup in to 4 files '

Print 'Exec SQLOPS_BackupDatabases ''Execute'',''SQLOPS'',''USER'',''FULL'',''Z:\MSSQL\Backups'', 4;'

Print ' '

Print 'How to perform backup of all user databases'

Print 'Exec SQLOPS_BackupDatabases ''Execute'',''ALL'',''USER'',''FULL'',''Z:\MSSQL\Backups'';'

Print ' '

Print 'How to perform COPY_ONLY backup of all user databases'

Print 'Exec SQLOPS_BackupDatabases ''Execute'',''ALL'',''USER'',''FULL'',''Z:\MSSQL\Backups'',@Copy_Only=''True'';'

Print ' '

Print 'How to perform log backup of a user database (Ex: SQLOPS)'

Print 'Exec SQLOPS_BackupDatabases ''Execute'',''SQLOPS'',''USER'',''Log'',''Z:\MSSQL\Backups'';'

Print ' '

Print 'How to perform log backup of all user databases'

Print 'Exec SQLOPS_BackupDatabases ''Execute'',''ALL'',''USER'',''Log'',''Z:\MSSQL\Backups'';'

Print ' '

Print 'How to perform Diffrential backup of a user database (Ex: SQLOPS)'

Print 'Exec SQLOPS_BackupDatabases ''Execute'',''SQLOPS'',''USER'',''Diff'',''Z:\MSSQL\Backups'';'

Print ' '

Print 'How to perform Diffrential backup of all user databases '

Print 'Exec SQLOPS_BackupDatabases ''Execute'',''ALL'',''USER'',''Diff'',''Z:\MSSQL\Backups'';'

Print ' '

Print 'How to perform backup of all user databases and split each database backup in to 4 backup files (for best performance)'

Print 'Exec SQLOPS_BackupDatabases ''Execute'',''SQLOPS'',''USER'',''FULL'',''Z:\MSSQL\Backups'',4;'

Print ' '

Print 'How to perform backup of all system databases'

Print 'Exec SQLOPS_BackupDatabases ''Execute'',''ALL'',''SYSTEM'',''FULL'',''Z:\MSSQL\Backups'';'

Print ' '

Print 'How to obtain this help'

Print 'Exec SQLOPS_BackupDatabases ''Help'';'

Print ' '

Return

End

If (@cmd='Execute' and @DBName is null)

Begin

Print '@DBName cannot be blank. Please provide the name of the database that you would like backed up or enter ''ALL'' to backup all databases'

Return

End

If (@DBName <> 'ALL')

Begin

If Not Exists (Select Name from Sys.Databases Where Name = @DBName)

Begin

Print 'Database ['+@DBName+'] cannot be found. Please enter a valid database name or type ''ALL'' to backup all databases'

RETURN

End

End

IF (@DBType NOT IN ('User','System') or @DBType is null)

Begin

Print 'Invalid @DBType value. Please enter a valid @DBType value (Valid Values are: ''USER'' or ''SYSTEM'')'

Return

End

If (@DBName <> 'ALL' and @DBType = 'System')

Begin

If (@DBName not in ('Master','Model','MSDB','Distribution'))

Begin

Print 'Database ['+@DBName+'] supplied is not a system database. Valid system databases are (''Master'',''Model'',''MSDB'',''Distribution'' Only)'

RETURN

End

End

If (@DBName <> 'ALL' and @DBType = 'User')

Begin

If (@DBName in ('Master','Model','MSDB','Distribution'))

Begin

Print 'Database ['+@DBName+'] supplied is a system database. For backups on system databases, please change the value of @DBType from ''User'' to ''System'''

RETURN

End

End

IF (@BackupType NOT IN ('FULL','DIFF', 'LOG') or @BackupType is null)

Begin

Print '@BackupType cannot be blank. Please enter a valid @BackupType value (Valid values are: ''FULL'',''DIFF'',''LOG'')'

Return

End

If (@cmd='Execute' and @Location is null)

Begin

Print '@Location value cannot be blank. Please enter a valid location where database backup(s) can be written to.'

Return

End

IF (@Copy_Only NOT IN ('TRUE','FALSE') or @Copy_Only is null)

Begin

Print 'Please enter a valid @COPY_ONLY value. (Valid values are: ''TRUE'' (COPY_ONLY) or ''FALSE'' (REGULAR BACKUP)) '

Return

End

IF (@BackupType NOT IN ('Full','Diff','Log' ) or @BackupType is null)

Begin

Print '@BackupType value cannot be ['+@BackupType+']. Valid values are ''FULL'' or ''DIFF'' or ''LOG''. Please try again.'

RETURN

End

If (@Location is null)

Begin

Print '@Location value cannot be NULL. Please supply a valid location where the backup should be stored.'

RETURN

End

IF (@DBType ='System' and @BackupType <> 'Full')

Begin

Print 'Only ''FULL'' value is supported for @Backuptype option when backing up System Databases. Please change the option and try again.'

RETURN

End

IF (@MultiDevice is null or @MultiDevice NOT IN (1 ,4))

Begin

Print 'Please enter a valid @MultiDevice value. (Valid values are 1 or 4 only)'

RETURN

End

IF (@BackupType='Log' and DatabasePropertyEX(@DBName,'Recovery')='Simple')

Begin

Print 'Database ['+@DBName+'] is in SIMPLE recovery mode therefore you cannot perform Log backup on this database'

Return

End

--Fix Location

Declare @Loc1 int=Len(@Location)

If Substring(@Location,@Loc1,@Loc1) <> '\'

Begin

Set @Location=@Location+'\'

End

IF (@Copy_Only NOT IN ('TRUE','FALSE' ))

Begin

Print '@COPY_ONLY value cannot be ['+@COPY_ONLY+']. Valid values are ''TRUE'' or ''FALSE''. Please retry the command.'

RETURN

End

Declare @BackupStr Varchar(25)

If (@Copy_Only='True')

Begin

Set @BackupStr='COPY_ONLY, NOFORMAT'

End

If (@Copy_Only='False')

Begin

Set @BackupStr='NOFORMAT'

End

If (@DBName<> 'ALL' and @DBType='User' and @BackupType='Full' and @MultiDevice=4)

Begin

SET @SQLOpsCommand ='BACKUP DATABASE ' + '['+LTRIM(RTRIM(@DBName)) +'] TO DISK = N'''+ @Location + @DBName +'_'+Upper(@BackupType)+'_BACKUP_'+@BackupDate+'_'+Convert(Varchar,SERVERPROPERTY('Machinename'))+'_File_1_of_4.BAK'',

DISK = N'''+ @Location + @DBName +'_'+Upper(@BackupType)+'_BACKUP_'+@BackupDate+'_'+Convert(Varchar,SERVERPROPERTY('Machinename'))+'_File_2_of_4.BAK''

,DISK = N'''+ @Location + @DBName +'_'+Upper(@BackupType)+'_BACKUP_'+@BackupDate+'_'+Convert(Varchar,SERVERPROPERTY('Machinename'))+'_File_3_of_4.BAK''

,DISK = N'''+ @Location + @DBName +'_'+Upper(@BackupType)+'_BACKUP_'+@BackupDate+'_'+Convert(Varchar,SERVERPROPERTY('Machinename'))+'_File_4_of_4.BAK''

' + ' WITH '+@BackupStr+', NOINIT, CHECKSUM, SKIP, COMPRESSION'

Print 'Backup of ['+@DBName+'] Database Submitted'

EXECUTE SP_ExecuteSQL @SQLOpsCommand

End

If (@DBName<> 'ALL' and @DBType='User' and @BackupType='Full' and @MultiDevice=1)

Begin

SET @SQLOpsCommand ='BACKUP DATABASE ' + '['+LTRIM(RTRIM(@DBName)) +'] TO DISK = N'''+ @Location + @DBName +'_'+Upper(@BackupType)+'_BACKUP_'+@BackupDate+'_'+Convert(Varchar,SERVERPROPERTY('Machinename'))+'.BAK''' + 'WITH '+@BackupStr+', NOINIT, CHECKSUM, SKIP, COMPRESSION'

Print 'Backup of ['+@DBName+'] Database Submitted'

EXECUTE SP_ExecuteSQL @SQLOpsCommand

End

If (@DBName <> 'ALL' and @DBType='System' and @BackupType='Full')

Begin

SET @SQLOpsCommand ='BACKUP DATABASE ' + '['+LTRIM(RTRIM(@DBName)) +'] TO DISK = N'''+ @Location + @DBName +'_'+Upper(@BackupType)+'_BACKUP_'+@BackupDate+'_'+Convert(Varchar,SERVERPROPERTY('Machinename'))+'.BAK''' + 'WITH '+@BackupStr+', NOINIT, CHECKSUM, SKIP, COMPRESSION'

Print 'Backup of ['+@DBName+'] Database Submitted'

EXECUTE SP_ExecuteSQL @SQLOpsCommand

End

If (@DBName <> 'ALL' and @DBType='User' and @BackupType='DIFF')

Begin

SET @SQLOpsCommand ='BACKUP DATABASE ' + '['+LTRIM(RTRIM(@DBName)) +'] TO DISK = N'''+ @Location + @DBName +'_'+Upper(@BackupType)+'_BACKUP_'+@BackupDate+'_'+Convert(Varchar,SERVERPROPERTY('Machinename'))+'.BAK''' + 'WITH DIFFERENTIAL, CHECKSUM, SKIP, COMPRESSION'

Print 'Backup of ['+@DBName+'] Database Submitted'

EXECUTE SP_ExecuteSQL @SQLOpsCommand

End

If (@DBName <> 'ALL' and @DBType='User' and @BackupType='Log')

Begin

SET @SQLOpsCommand ='BACKUP LOG ' + '['+LTRIM(RTRIM(@DBName)) +'] TO DISK = N'''+ @Location + @DBName+'_'+Upper(@BackupType)+'_BACKUP_'+@BackupDate+'_'+Convert(Varchar,SERVERPROPERTY('Machinename'))+'.BAK''' + 'WITH COMPRESSION'

Print 'Backup of ['+@DBName+'] Database Submitted'

EXECUTE SP_ExecuteSQL @SQLOpsCommand

End

-- All User Databases (Full backup) Includes both single and multi device

If (@DBName = 'ALL' and @DBtype='User' and @BackupType='Full')

Begin

Declare @dbALL Varchar(255)

Declare CheckDB Cursor for Select Name from Master.sys.Databases Where State=0 and Name not in ('Master','Model','MSDB','Distribution','TempDB')

Open CheckDB

Fetch Next from CheckDB into @dbALL

While (@@FETCH_STATUS=0)

Begin

If (@MultiDevice=4)

Begin

SET @SQLOpsCommand ='BACKUP DATABASE ' + '['+LTRIM(RTRIM(@dbAll)) +']

TO DISK = N'''+ @Location + @dbAll +'_'+Upper(@BackupType)+'_BACKUP_'+@BackupDate+'_'+Convert(Varchar,SERVERPROPERTY('Machinename'))+'_File_1_of_4.BAK'',

DISK = N'''+ @Location + @dbAll +'_'+Upper(@BackupType)+'_BACKUP_'+@BackupDate+'_'+Convert(Varchar,SERVERPROPERTY('Machinename'))+'_File_2_of_4.BAK'',

DISK = N'''+ @Location + @dbAll +'_'+Upper(@BackupType)+'_BACKUP_'+@BackupDate+'_'+Convert(Varchar,SERVERPROPERTY('Machinename'))+'_File_3_of_4.BAK'',

DISK = N'''+ @Location + @dbAll +'_'+Upper(@BackupType)+'_BACKUP_'+@BackupDate+'_'+Convert(Varchar,SERVERPROPERTY('Machinename'))+'_File_4_of_4.BAK''' + ' WITH '+@BackupStr+',

NOINIT, CHECKSUM, SKIP, COMPRESSION'

End

If (@MultiDevice=1)

Begin

SET @SQLOpsCommand ='BACKUP DATABASE ' + '['+LTRIM(RTRIM(@dbAll)) +']

TO DISK = N'''+ @Location + @dbAll +'_'+Upper(@BackupType)+'_BACKUP_'+@BackupDate+'_'+Convert(Varchar,SERVERPROPERTY('Machinename'))+'.BAK''' + ' WITH '+@BackupStr+', NOINIT, CHECKSUM, SKIP, COMPRESSION'

End

Print 'Backup of ['+@dbALL+'] Database Submitted'

Execute SP_ExecuteSQL @SQLOpsCommand

Fetch Next from CheckDB into @dbALL

End

Close CheckDB

Deallocate CheckDB

End

-- All System Databases (Full backup)

If (@DBName = 'ALL' and @DBtype='System' and @BackupType='Full')

Begin

Declare @dbAllSystem Varchar(255)

Declare CheckDB Cursor for Select Name from Master.sys.Databases Where State=0 and Name in ('Master','Model','MSDB','Distribution')

Open CheckDB

Fetch Next from CheckDB into @dbAllSystem

While (@@FETCH_STATUS=0)

Begin

SET @SQLOpsCommand ='BACKUP DATABASE ' + '['+LTRIM(RTRIM(@dbAllSystem)) +'] TO DISK = N'''+ @Location + @dbAllSystem +'_'+Upper(@BackupType)+'_BACKUP_'+@BackupDate+'_'+Convert(Varchar,SERVERPROPERTY('Machinename'))+'.BAK''' + 'WITH NOFORMAT,NOINIT, CHECKSUM, SKIP, COMPRESSION'

Print 'Backup of ['+@dbALLSystem+'] Database Submitted'

Execute SP_ExecuteSQL @SQLOpsCommand

Fetch Next from CheckDB into @dbAllSystem

End

Close CheckDB

Deallocate CheckDB

End

 

-- All User Databases (DIFF backup)

If (@DBName = 'ALL' and @DBtype='User' and @BackupType='Diff')

Begin

Declare CheckDB Cursor for Select Name from Master.sys.Databases Where State=0 and Name not in ('Master','Model','MSDB','TempDB')

Open CheckDB

Fetch Next from CheckDB into @dbALL

While (@@FETCH_STATUS=0)

Begin

SET @SQLOpsCommand ='BACKUP DATABASE ' + '['+LTRIM(RTRIM(@dbAll)) +'] TO DISK = N'''+ @Location + @dbAll +'_'+Upper(@BackupType)+'_BACKUP_'+@BackupDate+'_'+Convert(Varchar,SERVERPROPERTY('Machinename'))+'.BAK''' + 'WITH DIFFERENTIAL, CHECKSUM, SKIP, COMPRESSION'

Print 'Backup of ['+@dbALL+'] Database Submitted'

Execute SP_ExecuteSQL @SQLOpsCommand

Fetch Next from CheckDB into @dbALL

End

Close CheckDB

Deallocate CheckDB

End

-- All User Databases (LOG backup)

If (@DBName = 'ALL' and @DBtype='User' and @BackupType='Log')

Begin

Declare CheckDB Cursor for Select Name from Master.sys.Databases Where State=0 and Name not in ('Master','Model','MSDB','Distribution','TempDB')

Open CheckDB

Fetch Next from CheckDB into @dbALL

While (@@FETCH_STATUS=0)

Begin

IF DatabasePropertyEX(@DBALL,'Recovery')='Simple'

Begin

Print '**Skipping Database ['+@dbALL+'] since the database is set to ''Simple Recovery''**'

End

Else

Begin

SET @SQLOpsCommand ='BACKUP LOG ' + '['+LTRIM(RTRIM(@dbAll)) +'] TO DISK = N'''+ @Location + @dbAll+'_'+Upper(@BackupType)+'_BACKUP_'+@BackupDate+'_'+Convert(Varchar,SERVERPROPERTY('Machinename'))+'.BAK''' + 'WITH COMPRESSION'

Print 'Backup of ['+@dbALL+'] Database Submitted'

Execute SP_ExecuteSQL @SQLOpsCommand

End

Fetch Next from CheckDB into @dbALL

End

Close CheckDB

Deallocate CheckDB

End

GO

Backup Database
Compressed Backups
Database Backups
Diffrential Database Backup
Full Database Backup
Multi Device Backup
Transaction Log Backup
Minimize
Script Categories
HOME   |   SQL ARTICLES   |   SCRIPTS   |   DISCUSSIONS   |   SQL TEAMS   |   JOBS   |   ABOUT US
Copyright [2014] by SQLCOMMUNITY.COM