SQL Server Performance

automatically backup new created database once

Discussion in 'SQL Server 2005 General DBA Questions' started by avipenina, Dec 26, 2007.

  1. avipenina New Member

    Hi,
    I've sql 2005 std sp2,and i'm working with a program that create every view month's a new database automatically.
    my question is - how can i make a full backup of the newly created database automatically only one time when it's created?
    Thx
  2. martins New Member

    Hi,
    I am using SQL2k, so I don't know if there is a better way in SQL2k5...
    In the master database there is a table called sysdatabases, which has a field crdate that tells you when a database was ceated. You can schedule a job that runs every night and query this database to see if any databases were created during that day...and of course then perform a full backup on that database.
  3. avipenina New Member

    can anyone point me how to start the tsql statement to achieve this backup new created database.
    Thx
  4. martins New Member

    You will have to write dynamic sql to insert the database name retrieved from the system tables into the backup statement. Read up on backup statements and dynamic sql in BOL, and you should find enough information to write it.
  5. dineshasanka Moderator

    You can add a Server DDL trigger for Database Creation to create database backup
  6. ghemant Moderator

    Hi,
    you may schedule the job as Martin suggested, i.e. schedule a job which take full backup on 1st of every month;
  7. jn4u Member

    Understand you are using a dotnet program to make the database? Or is schedule sql? If it’s a program I wouldstart using SMO. Just after calling the “create database”. Check this article.
    http://www.sqldbatips.com/showarticle.asp?ID=37
    I have used is for somesolutions like it better the trie to make program logic in sql.

Share This Page