1

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!

5

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.

  • Perfect! Looks like I need to improve my Googling skills. Thanks! – WimpyProgrammer Jul 23 '09 at 18:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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