Categories
DBA

Creating Database Snapshot

One of the exasperating things while creating a snapshot is that you cannot use SQL Server Management Studio (SSMS) instead you need to make use of T-SQL. I am creating a snapshot of the database Deepak as follows,

CREATE DATABASE Deepak_snapshot_2030 on
(NAME=Deepak,FILENAME='D:\TestDeepak_ss.ff')
AS SNAPSHOT OF Deepak

The Snapshot name is Deepak_snapshot_2030. It can be any name but its advisable to include the time @ which the snapshot was created, in my case it is 2030(i.e 8:30 pm) so that it will be easy to identify.While creating a snapshot a Sparse file is created in my case it is named as Deepak_ss.ff.

You need to ensure that the drive containing this sparse file must have NTFS file system else you will not be able to create the snapshot. Initially I tried to create a database snapshot in F:drive which has FAT file system and received the below error. Refer the below figure,

create_database_snapshot_1

create_database_snapshot_2

Subsequently I created the snapshot in D:drive which has NTFS File system. Refer the below figure for the same.

create_database_snapshot_3

By Deepak

Microsoft Most Valuable Professional (MVP) award-winning Database Designer with 5 years of experience in designing, administering, developing & performance tuning relational databases and data warehouse.

I possess strong leadership skills experience by serving as the Chapter Leader of Professional Association for SQL Server (PASS) which has around 50 members.

I am passionate about solving business problems using my technical skills and hence I am interested in the field of Technology Consulting in Information Management.

Leave a Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

*