User Database Backups - Individual Backup Jobs or Single Backup Job?
-
Hi
I've been tasked with rejigging our SQL Server farm backup plan as it's been pretty hit and miss so far as no one was really responsible for it.
We have a mixture of 7.0, 2000 & 2005 servers across about 20 or so servers with a hundred or so databases across those and obviously this is a mixture of Production and development/test.
My question is really about whether its best to have separate backup jobs for each of the databases or a single job to backup all the user databases.
Obviously a single backup job is simpler to administer and setup, but my problem is that on some servers we have 30 or so databases and so the backup task will backup these databases sequentially which obviously takes some time to complete, therefore I'm thinking a separate job for each db would be better as this would allow simultaneous backups of all the databases and also means should we wish to perform a specific action for one of the databases this can be done very easily by just executing its specific job.
A downside of individual jobs rather than the catch all user databases option is that if a user creates a new database (quite often do on the dev boxes) we would have to make sure that we set up a new batch of maintenance sql jobs to catch it.
Any advice/experience very much appreciated
-
The answer to your question is: It depends. I know, good answer - right
On some systems, I have used a single plan to backup all user databases because I had the maintenance window and the databases aren't that large.
Other systems, I have multiple plans. In some cases, I separate them between daily and weekly backups, or between simple recovery model and full recovery model - or by unit/dept/etc...
Really, what you need to consider is how much time do you have to complete your maintenance and what can be completed in that time frame? I would consider that before I consider which one is easier to manage.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
I have to agree, it does depend. For critical LOB databases, I prefer individual backup plans. For smaller database, I'd be more willing to use a single plan if the databases share the same recovery model.
-
Would like to add 2 cents here .
If you execute them serially in one job ,it will take more time .
You need to calculate that time and if its acceptable to your client .
If you execute them in parallel ,not only you will be keep on doing as new databases will be added but you will consume a lot more resources .
I had a case where we had (before i joined that Co) 900 databases and the all 900 jobs run at the same time
.Choice is yours . You can have a mix as well .
create 3-4 jobs having few databases each that run at the same time .
HTH
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i) -
Find attached backup script will help to take the backup of multiple databases, specific databases , all user databases.
We can update the parameters based on our requirement. The same script will schedule in job also for adhoc backup.
DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileName1 VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
-- specify database backup directory
SET @path = 'D:\Test\' -- Backup location
-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR READ_ONLY FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name IN ('DB1','DB2') -- these databases
--WHERE name NOT IN ('master','model','msdb','tempdb') -- Excluded these databases
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_CO_' + @fileDate + '.BAK''' + ' with compression,Copy_only,stats=10,buffercount=400'
Set @filename= 'BACKUP DATABASE '+@name +' TO DISK = '''+@fileName
print (@filename)
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
**************************************
DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileName1 VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
DECLARE @Servername VARCHAR(50)='ServerName' -- Server Name
-- specify database backup directory
SET @path = 'D:\Test\' -- Backup location
-- specify filename format
--SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
SELECT @fileDate =convert(char(8),getdate(),112)+ltrim(replace(convert(char(5),getdate(),108),':',''))
DECLARE db_cursor CURSOR READ_ONLY FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name IN ('DB1','DB2') -- these databases
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @Servername+ '_' + @name + '_CO_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName with compression,Copy_only,stats=10,buffercount=900
-- print (@filename)
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
-
I've done both, and I like the advice above from Jeffrey, Lynn, and Abhay.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply