[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 575
  • Last Modified:

How t create s database snapshot?

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
saratcm
Asked:
saratcm
2 Solutions
 
Kailash AgheraCommented:
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
 
ThakurVinayCommented:
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
 
saratcmAuthor Commented:
Thanks!!!
0
 
wolfman007Commented:
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 quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now