SQL server Snapshots - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

SQL server Snapshots

Author

Topic

dolphin123
Yak Posting Veteran

84 Posts

Posted - 2013-11-13 : 12:42:46

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

Prav4u
Starting Member

15 Posts

Posted - 2013-11-17 : 10:54:19

no problem you can rely on snapshot..we have database which is of size more than 600 GB, we to take snapshot before any major changes.
As per as linked server is concerned this information is stored in master database not in user database.

Praveen D'sa
MCITP - Database Administrator 2008
http://sqlerrors.wordpress.com

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-11-17 : 11:03:32

Drop the snapshot after you have verified your updates and decide that you don't need it anymore.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources