Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

We use a centralized SQL Server 2005 server for web development. The server has a maintenance plan to take a full backup each Sunday and incremental backups on all other nights. All databases use the Simple Recovery Model, so transaction logs are not a factor.

Additionally our programmers are asked to manually save a full backup whenever they make considerable changes to the database. The full backup is checked in to our source control repository.

It occurs to me that a manual backup will affect the daily incremental backups done by the maintenance plan. If I understand correctly, a programmer could take a manual full backup on Wednesday and the automated incremental on Thursday will be dependent on Wednesday's full backup instead of Sunday's. Not good.

Is it possible to save a full backup that does not affect the maintenance plan? My research suggests that a database snapshot might be appropriate, but I'm just looking for a backup without spawning a new database on the server.

We'll assume that the programmer can run/tailor a script if the functionality is not in SQL Management Studio.

Thanks!

share|improve this question

1 Answer 1

up vote 5 down vote accepted

Yes, they need to specify the copy_only option:

http://msdn.microsoft.com/en-us/library/ms191495.aspx

http://www.mssqltips.com/tip.asp?tip=1075

This option is not available in the GUI interface, so the backup would have to be done from a script or query window.

share|improve this answer
    
Perfect! Looks like I need to improve my Googling skills. Thanks! –  AKeller Jul 23 '09 at 18:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.