Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Multiple backup at the same time Expand / Collapse
Author
Message
Posted Thursday, March 6, 2014 2:23 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 3, 2014 12:51 PM
Points: 12, Visits: 136
Can I start multiple database backup at the same time using a script? Example, I have the following stored in a table.

backup database ab to disk = 'c:\ab.bak'
backup database bc to disk = 'c:\bc.bak'
backup database cd to disk = 'c:\cd.bak'

I then want to use a sql scxript to read the information from the table and start all three backups at the same time instead of using some type of loop to run each one at a time. I would like to apply this concept to other types of scripts as well.

Thanks,
OBT
Post #1548491
Posted Thursday, March 6, 2014 3:04 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, September 11, 2014 2:28 PM
Points: 1,194, Visits: 2,219
obtllc (3/6/2014)

backup database ab to disk = 'c:\ab.bak'
backup database bc to disk = 'c:\bc.bak'
backup database cd to disk = 'c:\cd.bak'
Thanks,
OBT


This will be sequential. However you can start them parallely using separate jobs.

--
SQLBuddy


Post #1548510
Posted Thursday, March 6, 2014 8:25 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 3, 2014 12:51 PM
Points: 12, Visits: 136
Hi SQLBuddy,

Your suggestion gave me an idea and I did the following:

1. First I created a simple SQL job and scripted it out, the changed it into a stored procedure.
2. Next I created a script to generate the SQL command to backup.
3. From the script in step 2, I called the stored procedure in step 1 a new job to backup each database
4. I then used sp_start_job to start the job.

This way, I was able to run the backups in parallel. See scripts below. In my final output, I will be deleting the jobs once the execution completes.

This may not be the most elegant solution, but it works for now. Any suggestions to enhance this will be appreciated.



----- Stored procedure to create job

use msdb
go

alter procedure usp_CreateBackupJob(@jobname [sysname],@jobdescription nvarchar(500),@sql nvarchar(500))
as

/****** Object: Job [Backup Database] Script Date: 03/06/2014 17:39:57 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [Database Maintenance] Script Date: 03/06/2014 17:39:57 ******/
if exists (SELECT [name] FROM msdb.dbo.sysjobs_view WHERE name = @jobname)
Begin
EXEC msdb.dbo.sp_delete_job @job_name=@jobname,@delete_unused_schedule=1
End

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=@jobname,
@enabled=0,
@notify_level_eventlog=2,
@notify_level_email=0,
@notify_level_netsend=2,
@notify_level_page=2,
@delete_level=0,
@description=@jobdescription,
@category_name=N'Database Maintenance',
@owner_login_name=N'sa',
@job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Backup Database Step] Script Date: 03/06/2014 17:39:57 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Backup Database Step',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=@sql,
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO



----- Script to call stored procedure and execute job

DECLARE @ErrorMessage NVARCHAR(4000)
DECLARE @ErrorSeverity INT
DECLARE @ErrorState INT

declare @BackupDatabases table(dbnum int,dbname [sysname], dbbackupcmd nvarchar(1000))

declare @sql nvarchar(1000),
@dbname nvarchar(50),
@backuplocation nvarchar(200),
@numdb int, @dbcounter int,
@jobname nvarchar(200)

set @backuplocation = 'C:\Backup'

declare @database [sysname]

----------------------------------------------
-- Exclude system databases
----------------------------------------------
insert into @BackupDatabases
select Rn =Row_Number() Over(Order by (Select 1)),[name],'backup database [' + [name] + '] to disk=''' + @backuplocation +'\'
+ [name] + '_Full_' + cast(datepart(dw, getdate()) as varchar(2)) + '.bak'' with init, compression'
from sys.databases where database_id > 4

set @numdb = @@ROWCOUNT
set @dbcounter = 1
--select * from @BackupDatabases

--Only run Database backup if count of database is > 1
if @numdb > 0
Begin
begin try
while @dbcounter <= @numdb
Begin
select @sql=dbbackupcmd,@dbname=dbname from @BackupDatabases where dbnum = @dbcounter
set @jobname = 'Database backup for ' + @dbname
print @sql
exec msdb..usp_CreateBackupJob @jobname,@jobname,@sql
EXEC msdb..sp_start_job @jobname

--exec sp_executesql @sql
set @dbcounter = @dbcounter + 1
End
end try

begin catch
SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();

set @ErrorMessage = 'The Database log backup failed.'
+ char(13) + @ErrorMessage + CHAR(13)
+ 'Severity: ' + convert(nvarchar,@ErrorSeverity) + ' , '
+ 'State: ' + convert(nvarchar,@ErrorState) + CHAR(13)
+ @sql

-- Raise an error
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
)
end catch
End
Post #1548563
Posted Friday, March 7, 2014 9:19 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, September 11, 2014 2:28 PM
Points: 1,194, Visits: 2,219
Looks good and nice implementation. But still we are starting the jobs in a sequential order not parallelly. This approach works as there is very minute lag between the start time of those individual jobs.

However to kick them off simultaneously, you can define the schedules of these jobs to let them kick-off at the same time. That way instead of manually kicking off (sp_startjob) they would start at the same time by the scheduler.

--
SQLBuddy
Post #1548780
Posted Friday, March 7, 2014 11:47 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, September 19, 2014 12:10 PM
Points: 10,191, Visits: 13,115
I would question why you'd want to run them in parallel if you are writing them all to the same drive, especially if all the databases are located on the same drive. You are adding extra IO Load to the whole system. I'd be more inclined to let them run sequentially to spread the IO load out over time. I might consider running them in parallel if I was writing each backup to separate locations.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1548842
Posted Friday, March 7, 2014 4:13 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 1:47 PM
Points: 35,215, Visits: 31,667
Jack Corbett (3/7/2014)
I would question why you'd want to run them in parallel if you are writing them all to the same drive, especially if all the databases are located on the same drive. You are adding extra IO Load to the whole system. I'd be more inclined to let them run sequentially to spread the IO load out over time. I might consider running them in parallel if I was writing each backup to separate locations.


+1000. Under the conditions you mention, it very well could be that the parallel backups might actually run slower than the sequential backups. At the very least, it's going to drive the R/W heads on the disks nuts.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1548923
Posted Monday, March 10, 2014 7:11 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 3, 2014 12:51 PM
Points: 12, Visits: 136

I agree with all the comments, mostly the high I/O that it is going to generate. One thing I am trying to do is reduce the amount of time the backups take, mostly on a server with many databases. A test so far cuts the amount of time it takes to backup three user’s databases and the system databases from about 30 minutes to 10 minutes. This is still in testing. I will implement SQLbuddy’s idea of scheduling the jobs instead of manually kicking them off. I will also add the option of staggering the backups if there are more than five databases on the server or even check the sizes of the databases first and decide if they should run in parallel or sequential.

Thanks,
OBT
Post #1549210
Posted Thursday, March 13, 2014 12:17 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 3, 2014 12:51 PM
Points: 12, Visits: 136
I modified the script to schedule the jobs for two seconds apart. Scheduling and starting all the backup at the same time was causing a deadlock. Also added the option to send an email out when there is an error. This is assumng that a mail profile has already been setup on the server. Mine is called PersonalEmail.

My next attempt will be to check the size of the database and decide if to run multiple database backup at a time or in a sequential mode, based on size.


/* 
====================================================================================
Name: usp_DBA_Maintenance_CreateBackupJob
Description: This procedure creates SQL Maintenance Backup job for one database at a time.
The job is deleted after execution because of @delete_level=3.
Setting @delete_level=0 will not delete the job after execution.

Input Parameters:
@jobname - Job Name
@jobdescription - Job Description
@sql - SQL script to be executed
@startdate - Start Date
@starttime - Start Time

Execution: MyDB..usp_DBA_Maintenance_CreateBackupJob @jobname,@jobname,@sqlblock,@startdate,@starttime

History:

====================================================================================
*/
USE [MyDB]
GO

CREATE procedure [dbo].[usp_DBA_Maintenance_CreateBackupJob](@jobname [sysname],@jobdescription nvarchar(500),@sql nvarchar(4000),@startdate int,@starttime int)
as

/****** Object: Job [Backup Database] ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

if exists (SELECT [name] FROM msdb.dbo.sysjobs_view WHERE name = @jobname)
Begin
EXEC msdb.dbo.sp_delete_job @job_name=@jobname,@delete_unused_schedule=1
End

/****** Object: JobCategory [Database Maintenance] ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=@jobname,
@enabled=1,
@notify_level_eventlog=3,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=3,
--@notify_email_operator_name=N'PersonalEmail',
@description=@jobname,
@category_name=N'Database Maintenance',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Backup Database Step] ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Backup Database Step',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=@sql,
@database_name=N'DBA',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_jobschedule @job_name=@jobname, @name=N'Daily Backup Schedule',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=@startdate,
@active_end_date=99991231,
@active_start_time=@starttime,
@active_end_time=235959, @schedule_id = @schedule_id OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

/* 
====================================================================================
Name: usp_DBA_Maintenance_BackupData
Description: This procedure creates one backup job for each database on the server,
schedules them to run as follows:
First 6 databases: 2 seconds apart from the current time
Next 6 databases: 10 minutes from current time and 2 seconds apart
Next 6 databases: 20 minutes from current time and 2 seconds apart
and so on, depending on the amount of the databases to be backed up.
This is to reduce I/O load and prevent deadlocks.

It calls MyDB..usp_DBA_Maintenance_CreateBackupJob as follows to create the jobs:

MyDB..usp_DBA_Maintenance_CreateBackupJob @jobname,@jobname,@sqlblock,@startdate,@starttime

Parameter description are as follows:
@jobname - Job name
@jobname - Job Description
@sqlblock - SQL Script executed to backup to database
@startdate - Start Date
@starttime - Start Time

Execution: exec MyDB..usp_DBA_Maintenance_BackupData

History:

====================================================================================
*/

USE [MyDB]
GO

Alter procedure [dbo].[usp_DBA_Maintenance_BackupData]
as

DECLARE @ErrorMessage NVARCHAR(4000)
DECLARE @ErrorSeverity INT
DECLARE @ErrorState INT
declare @sqlblock nvarchar(4000)

declare @BackupDatabases table(dbnum int,dbname [sysname], dbbackupcmd nvarchar(1000))

declare @sql nvarchar(1000),@cmdstring nvarchar(1000),
@dbname nvarchar(50),
@backuplocation nvarchar(200),
@numdb int, @dbcounter int,
@jobname nvarchar(200),
@currentdatetime datetime,
@startdate int,
@starttime int,
@delaycount int

set @backuplocation = 'C:\MSSQL\SQLBackup\'

declare @database [sysname]

----------------------------------------------
-- Exclude databases with custom backup needs
----------------------------------------------
insert into @BackupDatabases
select Rn =Row_Number() Over(Order by (Select 1)),[name],'backup database [' + [name] + '] to disk=''' + @backuplocation + @@SERVERNAME + '\'
+ [name] + '_Full_' + cast(datepart(dw, getdate()) as varchar(2)) + '.bak'' with init, compression'
from sys.databases
where name not in (select databasename from DBA..DBA_Maintenance_CustomDatabases)

set @numdb = @@ROWCOUNT
set @dbcounter = 1
--select * from @BackupDatabases
--Only run Database backup if count of database is > 1
if @numdb > 0
Begin
begin try
while @dbcounter <= @numdb
Begin

select @sql=dbbackupcmd,@dbname=dbname from @BackupDatabases where dbnum = @dbcounter
set @jobname = 'Database backup for ' + @dbname
--set @sql = 'select 1/0' -- Used to test failure in the job

-- Format the backup command string so that it can be included in the error message
set @cmdstring = ''''+ REPLACE(@sql,'''','''''')+''''

-- Query that will be included in backup job
-- an email will be sent out to the DBA group if the job fails
set @sqlblock = 'DECLARE @ErrorMessage NVARCHAR(4000), @ErrorSeverity INT, @ErrorState INT'
+ CHAR(13)+CHAR(10) + 'Begin try' + CHAR(13)+CHAR(10)
+ ' ' + @sql + CHAR(13)+CHAR(10)
+ 'End try' + CHAR(13)+CHAR(10)
set @sqlblock = @sqlblock +
'begin catch
SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();

set @ErrorMessage = ''The Database backup failed.''
+ char(13) + @ErrorMessage + CHAR(13)
+ ''Severity: '' + convert(nvarchar,@ErrorSeverity) + CHAR(13)
+ ''State: '' + convert(nvarchar,@ErrorState)+ CHAR(13)
+ ''Command: '' + ' + @cmdstring + '' + CHAR(13)+CHAR(10)+ '
declare @subjectline nvarchar(100)
select @subjectline = ''Database Backup Failed on '' + @@SERVERNAME + '' / ' + @dbname + '''' + CHAR(13)+CHAR(10)+
' exec msdb.dbo.sp_send_dbmail
@profile_name = ''PersonalEmail'',
@recipients = ''YOUREMAIL@YOURDOMAIN.COM'',
@body = @ErrorMessage,
@subject = @subjectline

-- Raise an error
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
)
end catch'
-- End query that will be included in backup job

--Get start date time of job
select @currentdatetime = dateadd(mi,case
when (@dbcounter/7 = 0) then 1
else (@dbcounter/7) * 2 -- Used 2 minutes for testing. 10 for prod.
end,GETDATE())

set @starttime = cast(replace(cast(CONVERT(VARCHAR(8), @currentdatetime, 108) as nvarchar),':','') as int) -- HHMMSS format
set @startdate = cast(CONVERT(VARCHAR(10), @currentdatetime, 112) as int) -- YYYYMMDD format

set @delaycount = 0

--Execute stored procedure to create the job
exec dba..usp_DBA_Maintenance_CreateBackupJob @jobname,@jobname,@sqlblock,@startdate,@starttime

-- Make sure the job is created before continuing
while not exists (SELECT [name] FROM msdb.dbo.sysjobs_view WHERE name = @jobname)
Begin
print 'In delay for ' + @jobname
waitfor delay '00:00:05'
if @delaycount > 6
Begin -- been waiting for 30 seconds, so do someting. Hopefully this will never happen
set @sql = 'Command: dba..usp_DBA_Maintenance_CreateBackupJob @jobname,@jobname,@sqlblock,@startdate,@starttime'
RAISERROR ('It is taking too long to create SQL job while executing the following command', -- Message text.
16, -- Severity.
1 )-- State.
End
set @delaycount = @delaycount + 1
End

--Wait two second to continue. This spaces the jobs out every two seconds
waitfor delay '00:00:02'

-- Start the backup job. This will start the job immediately
--EXEC msdb..sp_start_job @jobname

--Start the backup for the database. This will backup one database at a time
--exec sp_executesql @sql
set @dbcounter = @dbcounter + 1
End
end try

begin catch
SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();

set @ErrorMessage = 'The Database backup failed.'
+ char(13) + @ErrorMessage + CHAR(13)
+ 'Severity: ' + convert(nvarchar,@ErrorSeverity) + ' , '
+ 'State: ' + convert(nvarchar,@ErrorState) + CHAR(13)
+ @sql

declare @subjectline nvarchar(100)
select @subjectline = 'Database Backup Failed on ' + @@SERVERNAME + ' / ' + @dbname
exec msdb.dbo.sp_send_dbmail
@profile_name = 'PersonalEmail',
@recipients = 'YOUREMAIL@YOURDOMAIN.COM',
@body = @ErrorMessage,
@subject = @subjectline

-- Raise an error
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
)
end catch
End

Post #1550873
Posted Thursday, March 13, 2014 3:22 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, September 11, 2014 2:28 PM
Points: 1,194, Visits: 2,219
obtllc (3/13/2014)
I modified the script to schedule the jobs for two seconds apart. Scheduling and starting all the backup at the same time was causing a deadlock. Also added the option to send an email out when there is an error. This is assumng that a mail profile has already been setup on the server. Mine is called PersonalEmail.

My next attempt will be to check the size of the database and decide if to run multiple database backup at a time or in a sequential mode, based on size.


Looks Good. How about picking up databases based on their previous backup execution times instead of first N, Next N etc .. ?

--
SQLBuddy
Post #1550934
Posted Saturday, March 15, 2014 3:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 3, 2014 12:51 PM
Points: 12, Visits: 136
I modified the code to backup the database based on size using groups.

I will use a basketball analogy in explaining what I tried to accomplish.
Let’s say there are 32 basketball players (number of tables) all with different skill level (database size) and I want to divide them into five evenly skilled teams.

To divide them as evenly as possible among the teams, I did the following:

If (32 % 5) > 1, then (32/5)+1=7, else #players/5

If I have 30 players, it will simply be 30/5 = 6. Simple.

The teams are grouped as follows:
Best player (largest db) - group 1
Second best (second largest db) - group 2
Third best (third largest db) – group 3
Fourth best (fourth largest db) – group 4
Fifth best (fifth largest db) – group 5
Sixth best (sixth largest db) group 6
Seventh best (seventh largest db) group 1
Etc, etc.

Two of the groups will end up with seven players (seven databases). The seventh players will be low ranking players (small databases) and may not have a huge impact on the team.

The code can use some improvement.

So, if I have a server with 46 databases (46 players) and I want 12 databases to be backed up at a time (12 players per team), I will end up with four group (four teams) with two groups/team having 12 databases/players and the other two with 11 databases/players.


/* 
====================================================================================
Name: usp_MyDB_Maintenance_BackupData
Description: This procedure creates one backup job for each database on the server,
schedules them to run as follows:
First 6 databases: 2 seconds apart from the current time
Next 6 databases: 10 minutes from current time and 2 seconds apart
Next 6 databases: 20 minutes from current time and 2 seconds apart
and so on, depending on the amount of the databases to be backed up.
This is to reduce I/O load and prevent deadlocks.

It calls MyDB..usp_MyDB_Maintenance_CreateBackupJob as follows to create the jobs:

usp_MyDB_Maintenance_CreateBackupJob @jobname,@jobname,@sqlblock,@startdate,@starttime

Parameter description are as follows:
@jobname - Job name
@jobname - Job Description
@sqlblock - SQL Script executed to backup to database
@startdate - Start Date
@starttime - Start Time

Execution: exec MyDB..usp_MyDB_Maintenance_BackupData

History:
Added option to group the databases by size and stagger the start time of the backup.

Example, if there are three groups, the
first largest database will be assigned to group one,
second largest db will be assigned to group two
third largest will be assigned to group 3
fourth largest database will be assigned to group one,
the fifth to group two,
etc, etc.

This way, all the large databases are not in one group
====================================================================================
*/

USE [MyDB]
GO

ALTER procedure [dbo].[usp_DBA_Maintenance_BackupData]
as

declare @ErrorMessage NVARCHAR(4000)
declare @ErrorSeverity INT
declare @ErrorState INT
declare @sqlblock nvarchar(4000)

declare @sql nvarchar(1000),@cmdstring nvarchar(1000),
@dbname nvarchar(50),
@backuplocation nvarchar(200),
@numdb int, @dbcounter int,
@jobname nvarchar(200),
@currentdatetime datetime,
@startdate int,
@starttime int,
@delaycount int,
@group_interval_mi int, -- start interval of jobs between groups in minutes
@job_interval_sec int, -- start interval of jobs in the same group in seconds
@waittimesec datetime -- time to wait in seconds based on start interval of jobs (@job_interval_sec) in the same group in seconds
declare @database [sysname]
declare @totaldb int -- total databases to backup
declare @numgroup int -- total number of groups
declare @jobgroup int -- Total Number of jobs to run at the same time

set @backuplocation = 'C:\mssql\SQLBackup\'

set @jobgroup = 2
set @job_interval_sec = 1
set @group_interval_mi = 1

set @waittimesec = CONVERT(VARCHAR(8), DATEADD(ss,@job_interval_sec,'00:00:00'), 108)
--select @waittimesec

;with fs
as
(
select database_id, type, size * 8.0 / 1024 size
from sys.master_files
)
select
database_id,name,
(select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeMB,
(select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeMB
into #databases
from sys.databases db
-- Exclude system databases
where database_id > 4
order by DataFileSizeMB desc

--select * from #databases order by DataFileSizeMB desc
--drop table #databases

set @totaldb = @@ROWCOUNT -- Total # of databases to be backed up

-- Set total number of groups or backup set that will be created
-- The number of groups is based on the number of databases to be backed up
-- divided by total number of jobs to be run at almost the same time.
-- If there is remender in the number, add one to the total number of group

set @numgroup = case
when (@totaldb % @jobgroup > = 1) then (@totaldb/@jobgroup) + 1
else @totaldb/@jobgroup
end

--select @totaldb as TotalDB,@jobgroup as NumJobs,@numgroup as NumGroup

/*
Assign databases to a group
Example, if there are three groups, the
first largest database will be assigned to group one,
second largest db will be assigned to group two
third largest will be assigned to group 3
fourth largest database will be assigned to group one,
etc, etc.

This way, all the large databases are not in one group
*/

declare @databases table(rn int identity(1,1),database_id int, dbname sysname,DataFileSizeMB float, LogFileSizeMB float)

insert into @databases
select * from #databases
order by DataFileSizeMB desc

--- Cleanup
drop table #databases

--declare final table that will hold databases to be backed up and include the backup command

declare @BackupDatabases table(dbnum int identity(1,1),dbname sysname,DataFileSizeMB float, LogFileSizeMB float,groupnum int,dbbackupcmd nvarchar(1000))

insert into @BackupDatabases
select dbname,DataFileSizeMB,LogFileSizeMB,groupnum = case
when (rn % @numgroup = 0) then @numgroup
else rn % @numgroup
end,
'backup database [' + [dbname] + '] to disk=''' + @backuplocation + @@SERVERNAME + '\'
+ [dbname] + '_Full_' + cast(datepart(dw, getdate()) as varchar(2)) + '.bak'' with init, compression'
from @databases
order by groupnum

select * from @BackupDatabases

set @numdb = @@ROWCOUNT
set @dbcounter = 1
--select * from @BackupDatabases
--Only run Database backup if count of database is > 1
if @numdb > 0
Begin
begin try
while @dbcounter <= @numdb
Begin

select @sql=dbbackupcmd,@dbname=dbname from @BackupDatabases where dbnum = @dbcounter
set @jobname = 'Database backup for ' + @dbname
--set @sql = 'select 1/0'

-- Format the backup command string so that it can be included in the error message
set @cmdstring = ''''+ REPLACE(@sql,'''','''''')+''''

-- Query that will be included in backup job
-- an email will be sent out to the DBA group if the job fails
set @sqlblock = 'DECLARE @ErrorMessage NVARCHAR(4000), @ErrorSeverity INT, @ErrorState INT'
+ CHAR(13)+CHAR(10) + 'Begin try' + CHAR(13)+CHAR(10)
+ ' ' + @sql + CHAR(13)+CHAR(10)
+ 'End try' + CHAR(13)+CHAR(10)
set @sqlblock = @sqlblock +
'begin catch
SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();

set @ErrorMessage = ''The Database backup failed.''
+ char(13) + @ErrorMessage + CHAR(13)
+ ''Severity: '' + convert(nvarchar,@ErrorSeverity) + CHAR(13)
+ ''State: '' + convert(nvarchar,@ErrorState)+ CHAR(13)
+ ''Command: '' + ' + @cmdstring + '' + CHAR(13)+CHAR(10)+ '
declare @subjectline nvarchar(100)
select @subjectline = ''Database Backup Failed on '' + @@SERVERNAME + '' / ' + @dbname + '''' + CHAR(13)+CHAR(10)+
' exec msdb.dbo.sp_send_dbmail
@profile_name = ''PersonalEmail'',
@recipients = ''YOUREMAIL@YOURDOMAIN.COM'',
@body = @ErrorMessage,
@subject = @subjectline

-- Raise an error
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
)
end catch'
-- End query that will be included in backup job

--Get start date time of job
--Get start date time of job
select @currentdatetime = dateadd(mi,case
when (@dbcounter/@jobgroup = 0) then 1
else (@dbcounter/@jobgroup) * @group_interval_mi
end,GETDATE())


set @starttime = cast(replace(cast(CONVERT(VARCHAR(8), @currentdatetime, 108) as nvarchar),':','') as int)
set @startdate = cast(CONVERT(VARCHAR(10), @currentdatetime, 112) as int)

set @delaycount = 0

--Execute stored procedure to create the job
exec MyDB..usp_DBA_Maintenance_CreateBackupJob @jobname,@jobname,@sqlblock,@startdate,@starttime

-- Make sure the job is created before continuing
while not exists (SELECT [name] FROM msdb.dbo.sysjobs_view WHERE name = @jobname)
Begin
print 'In delay for ' + @jobname
waitfor delay '00:00:05'
if @delaycount > 6
Begin -- been waiting for 30 seconds, so do someting. Hopefully this will never happen
set @sql = 'Command: MyDB..usp_DBA_Maintenance_CreateBackupJob @jobname,@jobname,@sqlblock,@startdate,@starttime'
RAISERROR ('It is taking too long to create SQL job while executing the following command', -- Message text.
16, -- Severity.
1 )-- State.
End
set @delaycount = @delaycount + 1
End

--Wait specified time in second to continue. This spaces the jobs out every two seconds
waitfor delay @waittimesec

-- Start the backup job. This will start the job immediately
--EXEC msdb..sp_start_job @jobname

--Start the backup for the database. This will backup one database at a time
--exec sp_executesql @sql
set @dbcounter = @dbcounter + 1
End
end try

begin catch
SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();

set @ErrorMessage = 'The Database backup failed.'
+ char(13) + @ErrorMessage + CHAR(13)
+ 'Severity: ' + convert(nvarchar,@ErrorSeverity) + ' , '
+ 'State: ' + convert(nvarchar,@ErrorState) + CHAR(13)
+ @sql

declare @subjectline nvarchar(100)
select @subjectline = 'Database Backup Failed on ' + @@SERVERNAME + ' / ' + @dbname
exec msdb.dbo.sp_send_dbmail
@profile_name = 'PersonalEmail',
@recipients = 'YOUREMAIL@YOURDOMAIN.COM',
@body = @ErrorMessage,
@subject = @subjectline

-- Raise an error
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
)
end catch
End

Post #1551454
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse