[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

How t create s database snapshot?

Posted on 2010-08-25
4
Medium Priority
?
578 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 1000 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 1000 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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

590 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