Solved

How t create s database snapshot?

Posted on 2010-08-25
4
552 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

733 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