sql backup process

genericgeneric Member Posts: 511
edited 2010-12-20 in SQL General

A client of mine has setup backup process the following way.

Doing a full backup nightly
and doing differential backup hourly.

They are in process of creating a new test environment and I suggested to do a sql backup and restore, which would be faster than native.

One of their IT wrote back that we should make a copy of the db instead of backup, because it will break the backup cycle they have setup. Is this true?
How does a separate backup effect their nightly and differential backup they have scheduled?



  • ta5ta5 Member Posts: 1,164
    In fact taking backups can break the backup chain. The reason is that the diff. backup is the diff to the last full backup. If someone else just takes a full backup you are in big troubles.

    But there is a parameter "copy_only" which does just that.
    More details here:

    Hope this helps
  • genericgeneric Member Posts: 511
    Thanks for the info.

    So from SMS when doing tasks->backup. I need to make sure "Copy only Backup" is checked.
  • bbrownbbrown Member Posts: 3,268
    Wouldn't it just be easier to use an existing backup to create the test DB?

    What is the recovery model of the database? If it is FULL, then Full and transaction logs backups should be considered the minimum. With the addition of differentials as an option.

    I would also consider 1 hour a rather short timeframe for a differential backup.
    There are no bugs - only undocumented features.
  • David_SingletonDavid_Singleton Member Posts: 5,479
    bbrown wrote:
    Wouldn't it just be easier to use an existing backup to create the test DB?

    That was my first thought also.

    My second thought was "why didn't the IT guy suggest this?" :-k
    David Singleton
Sign In or Register to comment.