automate daily backup

  • Hello all,

    instead of reinventing the wheel i wanted to find out if someone is already doing something like below if so can you share your script with me?

    We have our daily backup scheduled thru the agent, Sunday is full and rest of the days is incremental backup. In one of our environment new database is being created by app team almost every other day(during weekdays) and due to that our incremental backup fails as that newly created DB dose not have a FULL backup....

    So i wanted to find out if anyone has a script/proc that can be scheduled to do something like below...

    if a new database is created then go ahead and do a full backup the database to disk ... i tried searing here under the scripts location but was not able to find something similiar...

    any help would be much appreciated it.

  • I think Ola Hallengren's procedures will automatically take a full backup if one doesn't exist.

    John

  • hello,

    could you please explain or point me in the right direction when you says "Ola Hallengren's procedure" ?

  • http://www.google.com

    In the little window, type "Ola Hallengren".

    His web page will appear. All of the instructions are explained clearly on his page.

    And, it will automatically take a full backup if none exists.

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • On the internet you can find many custom scripts and solutions for database backup (many incuding database maintenance). Ola Hallengren https://ola.hallengren.com/ has a highly recommendedd solution, but you can find many others.

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • sorry about that. my mind went blank for a min when i posted that....i found that script below...but we dont want to schedule that to do our daily backup...only do backup one a new one is created otherwise dont(our job will take care of that)

    https://ola.hallengren.com/sql-server-backup.html

    the values i see is

    ValueDescription

    SYSTEM_DATABASESAll system databases (master, msdb, and model)

    USER_DATABASESAll user databases

    ALL_DATABASESAll databases

    Db1The database Db1

    Db1, Db2The databases Db1 and Db2

    USER_DATABASES, -Db1All user databases, except Db1

    %Db%All databases that have “Db” in the name

    %Db%, -Db1All databases that have “Db” in the name, except Db1

    ALL_DATABASES, -%Db%All databases that do not have “Db” in the name

  • You can put the execution of your DIFF backup statement inside a TRY...CATCH block. Check in the CATCH block if the error message is the missing FULL backup. If so, start the FULL backup procedure and if not, display the error message.

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • You didn't read the instructions down far enough.

    There is a parameter @ChangeBackupType. If this is set to 'Y', it will automatically change a diff or log to a full backup if there is not a current full backup.

    The command, it will need to be changed to fit your environment

    sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d DBName -Q "EXECUTE [dbo].[DatabaseBackup] @databases = 'USER_DATABASES', @Directory = N'Z:\Backups', @BackupType = 'DIFF', @ChangeBackupType = 'Y', @Verify = 'Y', @CleanupTime = 24, @checksum = 'Y', @LogToTable = 'Y'" -b

    The simple solution to this is to do a full backup every night, or, test for the existence of a backup.

    This code will list the DB's that have not had a full backup.

    SELECT D.name

    FROM sys.databases D

    WHERE NOT EXISTS (SELECT B.database_name FROM msdb.dbo.backupset B WHERE D.name = B.database_name AND B.type = 'D')

    A catch block seems a bit convoluted.

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply