What are the challenges you face when working across database platforms? Take the survey

Easy Multiple Database Backups

bbakerbbaker Posts: 7
edited December 28, 2005 2:41PM in SQL Backup Previous Versions
This really seems like a great solid product but I am baffled by the lack of easy multiple database backups. From what I gather it’s possible to backup multiple databases using a specialized stored procedure but this seems rather messy. With over 400 SQL databases, I'm not about to try to create 400 individualized backups!

Is “point and click” multiple database backup something that’s going to be added in a future release? As a potential customer I can say we would be much more interested and inclined to buy with such a feature. Without that key piece though I have to say that we are stuck using the MS SQL 2000 maintenance plans.

Brad Baker


  • Options
    peteypetey Posts: 2,358 New member
    In the next version, the backup syntax has been enhanced to support multiple database backups. E.g.


    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    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.
  • Options
    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
  • Options
    peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    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.

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

  • Options
    peteypetey Posts: 2,358 New member
    SQL Backup 4.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    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?

  • Options
    peteypetey Posts: 2,358 New member
    The screenshot was from the Schedule Backup wizard, available in Beta 2.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options

    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:
    	@BackupPath varchar(255),
    	@CopyToPath varchar(255),
    	@SvrName varchar(50),
    	@RetainDays tinyint=7,
    	@CompressLvl tinyint=3,
    	@SendCompletionMail bit=0,
    	@NotifyAddr varchar(100)='sqlserverdb@xxxxxxx.com'
    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 
    --declare Variables
    DECLARE @MyString VARCHAR(4000)
    DECLARE @Result VARCHAR(4000)
    DECLARE @failFlag bit
    DECLARE @failCount int
    --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
    WHILE @@fetch_status <> -1
    	--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 
     			INSERT INTO #ResultDatabase (DBName, BUResult,BeginTime,EndTime)
    			SELECT @DBName,message,@BeginTime,@EndTime FROM #resultstring  
        		SET @failFlag = 1  
     			INSERT INTO #ResultDatabase (DBName, BUResult,BeginTime,EndTime)
    			SELECT @DBName,'Backup Successful',@BeginTime,@EndTime FROM #resultstring
     	--Cleanup and get next record
    	DELETE FROM #ResultString 
    	FETCH NEXT FROM c1 INTO @dbname
    CLOSE c1
    --Do notifications
    If @SendCompletionMail=1 
    	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'
    --Clean up temporary tables
    DROP TABLE #ResultDatabase
    DROP TABLE #ResultString

    Good luck!
Sign In or Register to comment.