Pipboy level utility!
So sometimes i see the annoying practice of taking a full backup prior to rolling out database changes. This is annoying because some databases will take hours to backup, not only that, I run into quite a few PEEPOLES that have never heard of the copy only command and break my chain.
This is not necessary.
You can simply take a snapshot, roll out changes, test, rinse, repeat.
The following TSQL will allow this:
-- Create a snapshot
CREATE DATABASE ss_DATABASENAME_MMDDYYYY ON (
NAME = SBPD_ST_01_Data
, FILENAME = 'D:\Program Files\Microsoft SQL Server\MSSQL10\MSSQLDATALOGICALNAME.ss'
)
AS SNAPSHOT OF [DATABASENAME];
GO
One thing I see a lot of questions on forums about is the Name variable. This is the logical name of your database. Right click the database in ssms, go to files and read the logical name
Maintaining your snapshots is another issue. Since our reporting needs are more real time a snapshot serves no purpose in my life other than a quick testing method for database roll outs. Because of this I tend to only keep them around until QA on the roll out is completed.
If you need to revert to a snapshot you should refer to this post by SQL Superstar Paul S. Randal
http://www.sqlskills.com/blogs/paul/post/Database-snapshots-when-things-go-wrong.aspx
End of Line