sql backup process

generic

Hello

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?

Thanks.

ta5

Hi
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:
http://msdn.microsoft.com/en-us/library/ms191495.aspx

Hope this helps
Thomas

generic

Thanks for the info.

So from SMS when doing tasks->backup. I need to make sure "Copy only Backup" is checked.

bbrown

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.

David_Singleton

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