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!
Best Answer
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.