Post reply

Backup only newest database.

  • March 7, 2012 at 9:12 am

    #252054

    Have an app that creates databases about once every other day a new DB will be created. Once a new Db is created the old DB's are kept for 60 days and are not changed. Is there some way to program a backup to only backup the newest DB so I dont have to backup all the DB's every night? The name of the newest will change and count up. EX: WSlogdb64, wslogdb65. No the numbers should count up in increments of one but that is also not a guarantee as currently I have a mismatch of #'s. Any help or point in right direction would be appreciated,

    Thanks,

    Jeff

  • March 7, 2012 at 9:35 am

    #1456097

    I would record database names in a table and then backup only the databases that are not in that table.

    Another possible solution: since you don't want database backups for existing databases, I suppose they don't get modified, so you could take a backup of read/write databases and then set all databases read only.

    -- Gianluca Sartori

  • March 7, 2012 at 10:42 am

    #1456138

    You can check if your database ever was backed-up:

    SELECT Database_Name,

    CONVERT( SmallDateTime , MAX(Backup_Finish_Date)) as Last_Backup

    FROM MSDB.dbo.BackupSet

    WHERE Type = 'd'

    GROUP BY Database_Name

    ORDER BY Database_Name

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • March 8, 2012 at 7:10 am

    #1456508

    Gianluca Sartori (3/7/2012)

    Another possible solution: since you don't want database backups for existing databases, I suppose they don't get modified, so you could take a backup of read/write databases and then set all databases read only.

    I believe that would be the perfect solution for two reasons.

    1) It would be easy to write the script to do this.

    2) It will prevent updates to databases that aren't being backed up.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • March 8, 2012 at 8:14 am

    #1456555

    Thanks, I like the read, read/write solution as the old DB are for reporting only so no writes. I will work on getting this up and working.. Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

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