Easy Multiple Database Backups

• 
  In the next version, the backup syntax has been enhanced to support multiple database backups. E.g.
  
  BACKUP DATABASES [*] TO DISK = ....
  
  BACKUP DATABASES [pubs, northwind, master, msdb] TO DISK = ....
  
  BACKUP DATABASES EXCLUDE [master, model, msdb] TO DISK = ...
  
  There is a wizard to assist you in setting up a SQL Server Agent job to run these multiple database backup.

  petey December 24, 2005 03:16

  - 0 +
• 
  Currently we have seven SQL maintenance plans with 20-30 databases in each plan. Maintenance Plan 1 runs in Monday and does a full backup, Plan 2 runs on Tuesday and does a full backup, Plan 3 runs on Wednesday etc. Then every day we do incremental backups for all 7 plans.
  
  The reason for breaking up the backups so much is to limit the amount of time that the database backups run. I.E. We don't want a database backup consuming all the server resources for hours and hours at a time (even if it is during less busy hours). As an application service provider, our customers access our systems all hours of the day (and night) thus any prolonged hit in performance has to be kept to a minimum.
  
  Based on what you said below about the syntax it sounds like we would still be manually writing and maintaining 7 different backup queries per server. So for instance
  
  BACKUP DATABASES [database1,database 2,.....database20] TO DISK
  BACKUP DATABASES [database21,database 22,.....database40] TO DISK
  BACKUP DATABASES [database41,database 42,.....database60] TO DISK
  
  Think about how painful maintaining database lists would become using this approach. To make things even more painful, our databases have a naming convention: customer#_X_shortname. (For example A0001_S_redgate) Try typing 200 of those without making a mistake!
  
  It would take forever to get them all typed in. I suppose we could somehow export a list of names, reformat it, and break it up but it’s still going to be a major headache to maintain. On the other hand if you have a GUI with a list of databases with checkboxes it becomes much easier. So for instance:
  
  [X] A0001_S_redgate
  [X] A0001_T_redgate
  [X] A0002_S_microsoft
  [ ] A0002_T_microsot
  [X] A0003_S_netscape
  [X] A0003_T_netscape
  [X] A0004_S_sun
  [X] A0004_T_sun
  
  You can quickly tell in the list above that A0002_T_microsoft is not being backed up. And it’s super easy to just go through and add or remove databases. That’s what we are looking for not some clumsy hand written stored procedure.

  bbaker December 24, 2005 10:10

  - 0 +
• 
  I guess what I am saying is that for a small number of databases maintaining a handwritten stored procedure is fine. But for anybody with a sizable number of databases this approach becomes unwieldy and impractical.
  
  I hope at some point you will make multiple database backups as simple as single database backups with a point and click interface. If so then I would definitely be interested in trying out a demo and discussing pricing. Until then though I’m afraid we can't even consider your product.
  
  Best Regards
  Brad Baker

  bbaker December 24, 2005 10:20

  - 0 +
• 
  Do you mean something like this (http://www.yohz.com/sqb4backup.gif)?
  
  One limitation you might encounter is the length of the command that gets generated, which might exceed the maximum permissible length, depending on how long your database names are.

  petey December 24, 2005 11:13

  - 0 +
• 
  Yes. Essentially that’s what we are looking for. (The native Microsoft SQL maintenance plans have a similar system for selecting multiple databases.)
  
  We want to define multiple backup "jobs" each with their own set of databases, and have them run at diferent times.
  
  Brad

  bbaker December 25, 2005 19:19

  - 0 +
• 
  Is that screen shot from a competing product? a future version of Redgate SQL Backup? or something else entirely?
  
  Brad

  bbaker December 25, 2005 19:22

  - 0 +
• 
  SQL Backup 4.

  petey December 25, 2005 23:47

  - 0 +
• 
  I am running SQL Backup 4 Beta 2 and I don't see a screen that looks like that? As far as I can tell Beta 2 is the latest beta available on the red-gate ftp site. And when I attempted to download a trial the red-gate website indicated the latest version was 3.x. Am I missing something or is this in an unreleased beta?
  
  Brad

  bbaker December 26, 2005 09:43

  - 0 +
• 
  The screenshot was from the Schedule Backup wizard, available in Beta 2.

  petey December 26, 2005 11:48

  - 0 +
• 
  Brad,
  
  Yes, it would be easier if it was in the interface but I too have over 300 databases on my primary SQL Server (several others have over 100 databases each) and I didn't want a custom procedure for each one. Also, I do a full backup every night for my primary server and it only takes a few hours. The script I use is generic and I use it on all my servers for their backups. You should be able to modify it fairly easily for your use.
  
  With that said would it make more sense to do a full backup once a week and then differential backups every night if you don't want to impact performance very much?
  
  My script that is on each server in the Master DB and called from a scheduled job:

  CREATE  PROCEDURE usp_SQLBackup
  	@BackupPath varchar(255),
  	@CopyToPath varchar(255),
  	@SvrName varchar(50),
  	@RetainDays tinyint=7,
  	@CompressLvl tinyint=3,
  	@SendCompletionMail bit=0,
  	@NotifyAddr varchar(100)='sqlserverdb@xxxxxxx.com'
  AS
  /*********************************************************
  This stored procedure gets all of the databases
  and generates the necessary text for executing
  Red Gate's SQL Backup utillity v3.x.  
  
  @BackupPath is the path to put the backup files
  @CopyToPath is the path to copy backup files after done
  @SvrName name of the server this should report
  @RetainDays is the number of days to retain (Max 255)
  @CompresssLvl 1=Default, 2=High, 3=Max
  @SendCompletionMail 0=Don't Send, 1=Send notice this uses EasyMail SMTP components
  @NotifyAddr is the email address to notify 
  
  HISTORY:
  
  
  ***********************************************************/
  --declare Variables
  DECLARE @dbName SYSNAME
  DECLARE @MyString VARCHAR(4000)
  DECLARE @Result VARCHAR(4000)
  DECLARE @BeginTime DATETIME
  DECLARE @EndTime DATETIME
  DECLARE @failFlag bit
  DECLARE @failCount int
  
  SET NOCOUNT ON
  
  --Create temporary tables for use below
  CREATE TABLE #ResultDatabase (DBName sysname, BUResult varchar(4000),BeginTime datetime,EndTime datetime)
  CREATE TABLE #ResultString (message varchar (4000))  
  
  
  --Initialize variables
  SET @failFlag = 0  
  SET @failCount=0
  SELECT @BeginTime=Getdate()
  
  --Open Cursor of databases to backup
  DECLARE c1 CURSOR FOR SELECT [Name] FROM master.dbo.sysdatabases WHERE [Name] NOT IN ('pubs', 'tempdb', 'Northwind') ORDER BY [Name]
  OPEN c1
  FETCH NEXT FROM c1 INTO @dbName
  WHILE @@fetch_status <> -1
  	BEGIN
  	--Generate string for BackupSQL
  	SELECT @MyString= '-SQL "BACKUP DATABASE [' + @dbName + '] TO DISK = ''' + @BackupPath + '<AUTO>'' 
           WITH NAME = ''Database (' + @dbName + '), ' + cast(getdate() as varchar(30)) + ''', DESCRIPTION = ''Full Backup on ' + cast(getdate() as varchar(30)) + ' Database: ' + @dbName + ' Instance: (local)  Server: ' + @SvrName + ''', 
           PASSWORD = ''mypasswordNOT'', VERIFY, ERASEFILES = ' + cast(@RetainDays as varchar(3)) + ', MAILTO_ONERROR = ''' + @NotifyAddr + ''', COMPRESSION = ' + cast(@CompressLvl as varchar(1)) + '" -E'
  
  	SELECT @MyString
  		--Execute backup of specified database
  		INSERT INTO #ResultString
  		EXEC master..sqlbackup @Mystring
  		
  		--Check results of backup
  		SELECT @EndTime=GetDate()
  		SELECT @failCount = count(*) FROM #ResultString WHERE patindex('%error%', message) > 0  
  		
  		--If successful insert sucess entry, otherwise set failFlag and note in table
  		IF @failCount > 0 
  		  BEGIN  
   			INSERT INTO #ResultDatabase (DBName, BUResult,BeginTime,EndTime)
  			SELECT @DBName,message,@BeginTime,@EndTime FROM #resultstring  
      		SET @failFlag = 1  
  	      END  
      	ELSE 
            BEGIN  
   			INSERT INTO #ResultDatabase (DBName, BUResult,BeginTime,EndTime)
  			SELECT @DBName,'Backup Successful',@BeginTime,@EndTime FROM #resultstring
            END  
   	--Cleanup and get next record
  	DELETE FROM #ResultString 
  	FETCH NEXT FROM c1 INTO @dbname
  	END
  CLOSE c1
  DEALLOCATE c1
  
  --Do notifications
  If @SendCompletionMail=1 
  	BEGIN
  	EXEC Spammer.dbo.usp_InsertSpam2 'SJ','sqlserverdb@xxxxxxxx.com',@SvrName,'sqlserveragent@xxxxxxxx.com',@SvrName,'Full Backup done processing please check',@ProjectID =0,@DateAdded ='1/1/1900'
  	END
  
  --Clean up temporary tables
  DROP TABLE #ResultDatabase
  DROP TABLE #ResultString
  
  
  GO
  
  

  
  Good luck!

  swjohnson December 28, 2005 14:41

  - 0 +

Add comment

Please sign in to leave a comment.