Monday, November 19, 2012

Know your Snapshot database in SQL Server

A snapshot database is a read-only purpose and  static view of source database. The database snapshot is transactionally consistent with the source database as of the moment of the snapshot's creation.

1.       How do you create snapshot Database? Using
      T-SQL on Query Analyser

CREATE DATABASE [AdventureWorks_dbss1843] ON
( NAME = N'AdventureWorks_Data', FILENAME = N'E:\MSSQL\Data\AdventureWorks_data_1800.ss' )
AS SNAPSHOT OF [AdventureWorks]
GO

2.       Does snapshot database contain log file?
No

3.       If my snapshot database become suspect can you fix it ?
No

4.       Can you backup/restore snapshot database?
No

5.       Can you change recovery model for snapshot database?
No

6.       Can i create table/view in the snapshot database?
No

7.       Can i  use SSMS to create a snapshot database?
No

Source: Microsoft & Paul S Randal(SQLSkills)