1. Forum Rules (PLEASE CLICK HERE TO READ BEFORE POSTING) Click the link to access ADSM.ORG Acceptable Use Policy and forum rules which should be observed when using this website. Violators may be banned from this website. This message will disappear after you have made at least 12 posts. Thank you for your cooperation.

Automate daily (newly created database) full backups; script help

Discussion in 'Microsoft SQL Server' started by microosoft, Mar 22, 2012.

Thread Status:
Not open for further replies.
  1. microosoft

    microosoft New Member

    Joined:
    Aug 23, 2009
    Messages:
    8
    Likes Received:
    0
    Hello all, thanks for reading this post.

    I have a server which has Microsoft SQl Server and TSM TDPSql installed on it. i have around 50 SQL server databases on it with active usage. I run a full backup every month and differential backups every day.

    But when a new SQL database is created, its backup fails because 'its full backup does not exist'. So I would like to make a little change to my sqldiff schedule script. Right now all it says is

    %sql_dir%\tdpsqlc backup master full /tsmoptfile=%sql_dir%\dsm.opt /logfile=%sql_dir%\sqldiff.log >> %sql_dir%\sqldiffsched.log
    %sql_dir%\tdpsqlc backup * diff

    I would like to add a little script here that would take a full backups of the databases created that particular day along with full backup of master. And then, run the differential.

    Something like select * from sys.databases where create date is 'today'. Can anyone help?

    Thanks,
    Ben
     
  2.  
  3. moon-buddy

    moon-buddy Moderator

    Joined:
    Aug 24, 2005
    Messages:
    6,114
    Likes Received:
    273
    Occupation:
    Electronics Engineer, Security Professional
    Location:
    Somewhere in the US
    My two cents

    Just run the full backup manually unless you create new databases every hour! If you create new databases once a day, don't bother automating it.

    On the other hand, I question why you have just one FULL backup a month - scary!
     
  4. microosoft

    microosoft New Member

    Joined:
    Aug 23, 2009
    Messages:
    8
    Likes Received:
    0
    Thanks for your reply.

    My application creates databases every hour.

    Thanks,
    Ben
     
  5. moon-buddy

    moon-buddy Moderator

    Joined:
    Aug 24, 2005
    Messages:
    6,114
    Likes Received:
    273
    Occupation:
    Electronics Engineer, Security Professional
    Location:
    Somewhere in the US
    When you say create every hour - do you mean a new instance on an existing database?
     
  6. microosoft

    microosoft New Member

    Joined:
    Aug 23, 2009
    Messages:
    8
    Likes Received:
    0
    Sorry , I should have been more clear. I have this application that is used by (public) users. Whenever a new user registers on our webpage, Application creates a new mssql database for him/her. We have a recent upsurge in our application usage and more and more users are registering. Hence more database creations, around 20-30 a day.

    So, as I was saying before, I run all the database backups on to tape through TSM. I have no problem with the ones that have full backup, as the differentials run fine. But the ones that are newly created are failing on full backups. Right now, I am running full backups for the newly created ones manually every day. I was wondering if there is a way to automate it.

    Thanks,
    Ben
     
  7. moon-buddy

    moon-buddy Moderator

    Joined:
    Aug 24, 2005
    Messages:
    6,114
    Likes Received:
    273
    Occupation:
    Electronics Engineer, Security Professional
    Location:
    Somewhere in the US
    microosoft likes this.
  8. microosoft

    microosoft New Member

    Joined:
    Aug 23, 2009
    Messages:
    8
    Likes Received:
    0
Thread Status:
Not open for further replies.

Share This Page