Hello,
I have been running Schema and data changes scripts on Production database during a new releases. I always take a full backup and when a script errors out, I restore the full backup. but the databases have grown to be 3, 4 TBs now and this practice is very time consuming.
I read on snapshots and it sounds so good, even too good to be true.
I tested this on a small database I created. That is, created a DB and a table, took a snapshot, deleted data from the DB, restored from a snapshot and it is all good.
Do people use this in a scenario like mine? My production databases have indexes, computed tables, linked server, triggers, SPs.. Possibly every thing SQL server offers. If I restore from a snapshot because of an error, will I loose anything on the original database?
I am going to do simple as this.
1. Before I run any schema updates, Create the snapshot
CREATE DATABASE SnapshotDB ON
(Name ='RegularDB',
FileName='c:\SSDB.ss1')
AS SNAPSHOT OF RegularDB;
GO
2. No error - delete the snapshots.
3. If there are any errors, run this
USE master
GO
RESTORE DATABASE RegularDB
FROM DATABASE_SNAPSHOT = 'SnapshotDB';
GO
Do you see any problem doing this in a large Production Database instances?
Thanks for your time -
D
SQL server Snapshots
Posted - 2013-11-13 : 12:42:46
