Solved

How t create s database snapshot?

Posted on 2010-08-25
4
518 Views
Last Modified: 2012-05-10
Hi All  
How 2 create a database snapshot in SQL Server 2008?
are there any benifits of taking a snapshot over a database full backup?
will they serve the same purpose, can snapshot be used to restore a database in case of disaster?
0
Comment
Question by:saratcm
4 Comments
 
LVL 6

Accepted Solution

by:
Kailash Aghera earned 250 total points
ID: 33528315
Snapshot will help you to revert your database as of the moment of the snapshot's creation. However, if the database is corrupted or offline, you will not be able to revert the database from snapshot. To create snapshot, you can refer the following:

http://msdn.microsoft.com/en-us/library/ms175876.aspx

It is highly recommended to take regular full backup of your databases.

- Kailash
0
 
LVL 5

Assisted Solution

by:ThakurVinay
ThakurVinay earned 250 total points
ID: 33528682
CREATE DATABASE AdventureWorks_dbss1800 ON
( NAME = AdventureWorks_Data, FILENAME =
'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_data_1800.ss' )
AS SNAPSHOT OF AdventureWorks;
GO

snapshot database will require only mdf file. and it will be a copy of the database the time when you take snapshot.

HTH
Vinay
0
 

Author Closing Comment

by:saratcm
ID: 33530945
Thanks!!!
0
 
LVL 14

Expert Comment

by:wolfman007
ID: 33530964
You create database snapshots using a Transact-SQL statement, you cannot create a database snapshot using SQL Server Management Studio.

Have a look at the following website for instructions on creating a database snapshot.

How to: Create a Database Snapshot (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms175876.aspx

A database snapshot is a read-only, static view of a database they can be used for reporting purposes i.e. I want a report on what was our data like at 12pm today.

Also, in the event of a user error on a source database, you can revert the source database to the state it was in when the snapshot was created.

But they are not intended to be used as a replacement for regular database backups.

Database snapshots are not complete copies of the database they only contain a copy of the original data if the data has been changed in the source database. So if the source database has not been altered since the snapshot was taken, then the snapshot is empty.

If you then lose your source database you cannot recover from the snapshot because it would be completely empty.

So always take backups.

Finally database snapshots are available only in the Enterprise editions of SQL Server 2005, SQL Server 2008, and SQL Server 2008 R2.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now