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

Automated Daily Backup

lionheartlionheart Posts: 7
edited November 22, 2005 12:33PM in SQL Backup Previous Versions
I'm just starting to use the software and want to have daily backups with each backup in it's own folder (or at least different file names). What is the easiest way to do this? I'm assuming there is some command line way to do it, or I was thinking of possibly job in SQL where I create a different one for each day of the week with different names.

Any tips or posts that cover this would be greatly appreciated.




  • Options
    peteypetey Posts: 2,358 New member
    You could create jobs for SQL Server Agent to back up each database. Set up the naming convention first, so that each backup file will have a unique name. Then, you can use the following syntax:

    master..sqlbackup '-sql "BACKUP DATABASE pubs TO DISK = [E:\Temp\Backups\<AUTO>]"'

    Or if you only use one backup folder, you can set up the default folder, and simply use

    master..sqlbackup '-sql "BACKUP DATABASE pubs TO DISK = [<AUTO>]"'

    In the next version, you need not set up separate jobs for each database, nor be limited to a single folder as we'll be enhancing the syntax a little to allow you to do things like this:

    master..sqlbackup '-sql "BACKUP DATABASES [pubs, northwind, master] TO DISK = [F:\Backups\<DATABASE>\<AUTO>]"'

    or this (the inverse)

    master..sqlbackup '-sql "BACKUP DATABASES EXCEPT [tempdb, msdb] TO DISK = [F:\Backups\<DATABASE>\<AUTO>]"'
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    Thanks. Is there any way to have it automatically remove any files that are before a certain date?
  • Options
    Nevermind, I think I found it.
  • Options
    I've been using SQLBackup since June 2005, and I LOVE IT. It's truly a great product.

    I'm very anxious for the new version, as it's been talked about since I started using the product. In particular the automated script abilities mentioned above.

    Is there any news on the new version? I'm on 3.2.0, is that the most up-to-date? Is there a place I can check for more current versions?

    Thank you for the great product and support.
  • Options

    The UI comes with a check for updates item in the Help menu, like all of Red Gate's products. You can always use this to locate the most current version of the software.

    The next version will be released in a few months and contain wizards for log shipping and scheduling backup jobs. The backup scheduling wizard is going to be able to backup a selection of databases or even all of the databases on a server automatically!
  • Options
    This response is to lionheart where you asked about detecting files of a certain age and deleting them. Very easy thing to do (see code below) - sorry about the format but you get the idea. Key is to use xp_getfiledetails Hope it helps Rich:

    INSERT #tmpFileDetails
    EXEC master..xp_getfiledetails @vchrBackupLocation

    -- Determine whether the file is too old to retain
    SELECT *
    FROM #tmpFileDetails
    ,CASE WHEN LEN(LastWrittenTime) = 6 THEN
    CONVERT(DATETIME,LastWrittenDate + ' '
    + LEFT(LastWrittenTime,2) + ':'
    + LEFT(RIGHT(LastWrittenTime,4),2) + ':'
    + RIGHT(LastWrittenTime,2))
    ELSE -- Take into account when the time is not in the desired time formate from xp_getfiledetails function
    CONVERT(DATETIME,LastWrittenDate + ' '
    + LEFT('000000',2) + ':'
    + LEFT(RIGHT('000000',4),2) + ':'
    + RIGHT('000000',2))
    ) >= @intRetainDays
    SET @vchrCmdLine = 'del ' + QUOTENAME(@vchrBackupFolder + '\' + @vchrDatabaseName + '\' + @oFileName,'"')
    EXEC @intErrorCode = master..xp_cmdshell @vchrCmdLine
  • Options
    This is what I wrote to backup all databases on a daily basis. It's a single stored procedure that you can schedule whenever. It will create a control table that lists the databases and defines whether they should be backed-up and for how long to retain previous backups.

    By default, backup = Y and retention period = 7 days. New databases are automatically added with these defaults (so you don't forget to add new databases to your backup schedule).

    The control table can then be manipulated manually, if you desire, to change the defaults. And, it could even be modified to include an encrpyt/decrypt value - the SP below automatically encrypts all databases for me.

    You'll have to generate your own password etc in the @sql parameter at the end of the stored procedure, and modify the TO DISK string accordingly.

    Create the SP in the master database. The control table will also reside master.

    Not sure if this helps, but it shows what you can do ... or what SQLBackup 4.0 will possibly provide going forward :D

    -- R.Grey
    -- 11-Nov-2005
    -- Stored Procedure: myspEncBackupDaily
    -- Procedure to be scheduled via SQL Server Agent as often as required
    -- to backup and encrypt all databases using SQL Backup 3.2.0
    -- Backup files are retained for the period specified in the
    -- myspEncBackupDaily table (default 7 days)
    -- Backup file format
    -- <dbname>_yyyymmdd_hhmmss.sqb
    -- Usage:
    -- exec myspEncBackup 'D:\SQLBackup\'

    CREATE Procedure myspEncBackupDaily
    @Path varchar(128)
    -- create _myspEncBackupDaily table
    if not exists (select * from dbo.sysobjects where id = object_id(N'[master].[dbo].[_myspEncBackupDaily]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    Create table [master].[dbo].[_myspEncBackupDaily]
    Name varchar(128) primary key nonclustered ,
    BackupFlag varchar(1) not null check (BackupFlag in ('Y','N')) ,
    RetentionPeriod int not null

    set nocount on
    declare @sql varchar(1000)

    -- Get all database names
    create table #DBName
    ID int identity (1,1) ,
    Name varchar(128) not null ,
    RetentionPeriod int null

    insert #DBName
    select name
    from master..sysdatabases

    -- Include any new databases in the backup
    insert _myspEncBackupDaily
    Name ,
    BackupFlag ,
    select #DBName.Name ,
    'Y' ,
    7 -- default 7 days
    from #DBName
    left outer join _myspEncBackupDaily
    on _myspEncBackupDaily.Name = #DBName.Name
    where _myspEncBackupDaily.Name is null
    and lower(#DBName.Name) not in ('tempdb','master','model','msdb')

    -- Remove any non-existant databases
    delete _myspEncBackupDaily
    where not exists
    select *
    from #DBName
    where #DBName.Name = _myspEncBackupDaily.Name

    delete #DBName

    -- loop through databases
    declare @Name varchar(128) ,
    @RetentionPeriod int ,
    @ID int ,
    @MaxID int

    insert #DBName
    (Name, RetentionPeriod)
    select Name, RetentionPeriod
    from _myspEncBackupDaily
    where BackupFlag = 'Y'

    select @MaxID = max(ID) ,
    @ID = 0
    from #DBName

    while @ID < @MaxID
    -- get next database to backup
    select @ID = min(ID) from #DBName where ID > @ID

    select @Name = Name ,
    @RetentionPeriod = RetentionPeriod
    from #DBName
    where ID = @ID

    declare @eID int ,
    @eMaxID int ,
    @eName varchar(128)

    -- now do the backup
    select @sql =

    TO DISK = ''' + @Path + '\' + @Name + '_' + convert(varchar(10),getdate(),112) + '_' + replace(convert(varchar(10),getdate(),108), ':', '') + '.sqb''
    WITH NAME = ''' + @Name + ' ' + replace(convert(varchar(10),getdate(),6),' ','-') + ' ' + convert(varchar(8),getdate(),108) + ''',
    DESCRIPTION = ''Backup on ' + replace(convert(varchar(10),getdate(),6),' ','-') + ' ' + convert(varchar(8),getdate(),108) + ' Database: ' + @Name + ''',
    ERASEFILES = ' + convert(varchar(3),@RetentionPeriod) + ',
    COMPRESSION = 3" -E'

    exec master..sqlbackup @sql

  • Options
    rgreyrgrey Posts: 3
    edited November 22, 2005 12:30PM
    Damn formatting ! The smilies at the end of the SP are actually an 8 followed by a )

    UPDATE: Aah ! Looks like Brian sorted it - my firefox (running with the MSIE user agent due to MS ISA Server rubbish) doesn't seem to like the WYSIWYG buttons for code block etc.

  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Musn't forget that 'disable smilies' setting! Or you can enclose the whole shebang in a code block!
  • Options
    scottcatescottcate Posts: 3
    edited November 22, 2005 12:28PM

    THANK YOU!!! Your post has caused me to get of my ??? and implement this strategy. Before today I was doing it manually daily.

    In my tests though, the remove portion isn't working.

    I have a value of 2, and it just keeps creating more and more files. If I run the backup sequentially, one right after the other, the file system just keeps building up. Is it because of the variable names changing with the date? Is it because the DateTime on the Files isn't > 2 days old?

    Can someone from Red-Gate look at the above script, and see why it's not removing the archives.

    Thank you!
  • Options
    Are you sure ?

    It's a day thing. If you've set the retention period to two days, you won't see anything being removed unless it is a backup from > 48 hours previously.

    And, considering I only posted this today, you can't have gone over two days yet :D

    My 7 day strategy is working good. The SP is scheduled to be run at 4am, 10am, 4pm and 10pm everyday. Looking at the directory right now (22 Nov 5.30pm), the earliest backup remaining is correctly (15 Nov 10pm) - this should be removed when the (22 Nov 10pm) backup is created as it will be greater than 7*24 hours old.
  • Options
    OK, I didn't know if the removal was by date, or by instance.

    Thank you.
Sign In or Register to comment.