SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 SQL server Snapshots
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dolphin123
Yak Posting Veteran

USA
54 Posts

Posted - 11/13/2013 :  12:42:46  Show Profile  Reply with Quote
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

India
15 Posts

Posted - 11/17/2013 :  10:54:19  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 11/17/2013 :  11:03:32  Show Profile  Reply with Quote
Drop the snapshot after you have verified your updates and decide that you don't need it anymore.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000