?
Solved

How t create s database snapshot?

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

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

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…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

752 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