August 17, 2015 at 2:54 pm
Hi,
Never had a need to use snapshots before.
I have a dev SQL2014 server. My devs would like to take a DB snapshot then run there code, then use that snaphot to revert back to the way the db was before they ran the snapshot.
How do I do this?
August 17, 2015 at 11:09 pm
krypto69 (8/17/2015)
Hi,Never had a need to use snapshots before.
I have a dev SQL2014 server. My devs would like to take a DB snapshot then run there code, then use that snaphot to revert back to the way the db was before they ran the snapshot.
How do I do this?
Quick cheat-sheet
😎
USE master;
GO
/* Create snapshot */
CREATE DATABASE [SNAPSHOR_NAME] ON (NAME = [SOURCE_LOGICAL_FILE_NAME],FILENAME = '[PATH_AND_FILE_NAME]') AS SNAPSHOT OF [SOURCE_DB];
GO
/* Restore from snapshot */
RESTORE DATABASE [SOURCE_DB] FROM DATABASE_SNAPSHOT = 'SNAPSHOR_NAME';
GO
Suggest you read up on the subject BOL Database Snapshots (SQL Server)
August 18, 2015 at 6:21 am
Thanks for the cheat sheet.
Does creating the snap create a full bak or full mdf and ldf files? Im concerned with space - it's a large DB
August 18, 2015 at 6:55 am
Does creating the snap create a full bak or full mdf and ldf files? Im concerned with space - it's a large DB
No it initially creates a data file (sparse file) with no data and when data is changed in the source database the data pages are copied (before applying the changes) to that file. If there is huge amount of activity (updates/inserts/deletes) it can grow quite quickly.
😎
August 20, 2015 at 6:11 am
if every page in the source database is modified then the sparse files can be as large as the database files so you'll need to plan for the potential space usage
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply