Multipe DBs on 1 Server backup schedule

is it best to kick off the backups at the same time, i.e. 1am, or to space
them out in 20 minute slots, 12.20, 12.40, 1am, 1.20 and 1.40?
Any links or articles welcome.
Thanks,
MPM
As always it depends. But in general if you can issue the backups at
different times you will most likely get better performance for each and
less impact on the general users. This is due to the large amounts of I/O
(and to some extent CPU) that backups can induce. If you run multiple
backups at the same time you run the risk of maximizing your I/O bandwidth
and even causing network bottlenecks if done remotely. If you are going to
backup multiple dbs one right after the other it is best to do them in a
loop vs. setting specific times. That is because you never know if the
first is finished before the second one starts and so on. If you kick them
off after the previous completes you never have to worry about adjusting the
times as DB sizes change. Here is a sample of that:
-- Backup all user Databases --
IF NOT EXISTS (SELECT * FROM master..sysdevices WHERE [Name] =
'DD_Pubs_Full')
EXEC sp_addumpdevice 'disk', 'DD_Pubs_Full',
'C:\Data\Backups\DD_Pubs_Full.bak'
IF NOT EXISTS (SELECT * FROM master..sysdevices WHERE [Name] =
'DD_Northwind_Full')
EXEC sp_addumpdevice 'disk', 'DD_Northwind_Full',
'C:\Data\Backups\DD_Northwind_Full.bak'
DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100)
DECLARE @.Name NVARCHAR(150), @.Descript NVARCHAR(255)
DECLARE cur_DBs CURSOR STATIC LOCAL
FOR SELECT Catalog_Name
FROM Information_Schema.Schemata
WHERE [Catalog_Name] NOT IN ('MASTER','MODEL','MSDB','TEMPDB')
OPEN cur_DBs
FETCH NEXT FROM cur_DBs INTO @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Device = N'DD_' + @.DBName + '_Full'
SET @.Name = @.DBName + N' Full Backup'
SET @.Descript = @.DBName + N' Full Backup - ' +
CONVERT(NVARCHAR(16),GETDATE(),112)
BACKUP DATABASE @.DBName TO @.Device WITH INIT, NOUNLOAD, NAME = @.Name,
DESCRIPTION = @.Descript, NOSKIP, STATS = 10, NOFORMAT
RESTORE VERIFYONLY FROM @.Device WITH FILE = 1
FETCH NEXT FROM cur_DBs INTO @.DBName
END
CLOSE cur_DBs
DEALLOCATE cur_DBs
Andrew J. Kelly SQL MVP
"MANCPOLYMAN" <MANCPOLYMAN@.discussions.microsoft.com> wrote in message
news:904F98D8-D08B-4B33-8561-65355775CE0F@.microsoft.com...
> is it best to kick off the backups at the same time, i.e. 1am, or to space
> them out in 20 minute slots, 12.20, 12.40, 1am, 1.20 and 1.40?
> Any links or articles welcome.
> Thanks,
> MPM
|||Andrew,
Thanks a lot for that excellent reply.
MPM
"Andrew J. Kelly" wrote:

> As always it depends. But in general if you can issue the backups at
> different times you will most likely get better performance for each and
> less impact on the general users. This is due to the large amounts of I/O
> (and to some extent CPU) that backups can induce. If you run multiple
> backups at the same time you run the risk of maximizing your I/O bandwidth
> and even causing network bottlenecks if done remotely. If you are going to
> backup multiple dbs one right after the other it is best to do them in a
> loop vs. setting specific times. That is because you never know if the
> first is finished before the second one starts and so on. If you kick them
> off after the previous completes you never have to worry about adjusting the
> times as DB sizes change. Here is a sample of that:
>
> -- Backup all user Databases --
> IF NOT EXISTS (SELECT * FROM master..sysdevices WHERE [Name] =
> 'DD_Pubs_Full')
> EXEC sp_addumpdevice 'disk', 'DD_Pubs_Full',
> 'C:\Data\Backups\DD_Pubs_Full.bak'
> IF NOT EXISTS (SELECT * FROM master..sysdevices WHERE [Name] =
> 'DD_Northwind_Full')
> EXEC sp_addumpdevice 'disk', 'DD_Northwind_Full',
> 'C:\Data\Backups\DD_Northwind_Full.bak'
>
> DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100)
> DECLARE @.Name NVARCHAR(150), @.Descript NVARCHAR(255)
> DECLARE cur_DBs CURSOR STATIC LOCAL
> FOR SELECT Catalog_Name
> FROM Information_Schema.Schemata
> WHERE [Catalog_Name] NOT IN ('MASTER','MODEL','MSDB','TEMPDB')
> OPEN cur_DBs
> FETCH NEXT FROM cur_DBs INTO @.DBName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.Device = N'DD_' + @.DBName + '_Full'
> SET @.Name = @.DBName + N' Full Backup'
> SET @.Descript = @.DBName + N' Full Backup - ' +
> CONVERT(NVARCHAR(16),GETDATE(),112)
> BACKUP DATABASE @.DBName TO @.Device WITH INIT, NOUNLOAD, NAME = @.Name,
> DESCRIPTION = @.Descript, NOSKIP, STATS = 10, NOFORMAT
> RESTORE VERIFYONLY FROM @.Device WITH FILE = 1
> FETCH NEXT FROM cur_DBs INTO @.DBName
> END
> CLOSE cur_DBs
> DEALLOCATE cur_DBs
>
> --
> Andrew J. Kelly SQL MVP
>
> "MANCPOLYMAN" <MANCPOLYMAN@.discussions.microsoft.com> wrote in message
> news:904F98D8-D08B-4B33-8561-65355775CE0F@.microsoft.com...
>
>