How to Create a SQL Server 2005 Database Snapshot

Microsoft SQL Server administrators has a new feature with the SQL Server 2005 Edition which is database snapshots. Database snapshots are new with SQL Server 2005 and only available with Microsoft SQL Server 2005 Enterprise Edition. SQL Server database administration and development with SQL Server especially for sql reporting will be easier with ms sql server database snapshots.

A database snapshot can be described as a photo of a database. Snapshots are read-only so we can easily say that their main usage areas cover mostly the reporting applications. Database snaphots are one to one the same of the original database at the time when the snapshot is created. After a snapshot is created it stays as a static view of the original database at the time of snapshot creation. A snapshot can persists until it is dropped. Also multiple snapshots can be created with different names of a database called as source database.

In order to create a database snapshot, CREATE DATABASE statement is used.



How to Create a SQL Server 2005 Database Snapshot

Microsoft SQL Server administrators has a new feature with the SQL Server 2005 Edition which is database snapshots. Database snapshots are new with SQL Server 2005 and only available with Microsoft SQL Server 2005 Enterprise Edition. SQL Server database administration and development with SQL Server especially for sql reporting will be easier with ms sql server database snapshots.

A database snapshot can be described as a photo of a database. Snapshots are read-only so we can easily say that their main usage areas cover mostly the reporting applications. Database snaphots are one to one the same of the original database at the time when the snapshot is created. After a snapshot is created it stays as a static view of the original database at the time of snapshot creation. A snapshot can persists until it is dropped. Also multiple snapshots can be created with different names of a database called as source database.

In order to create a database snapshot, CREATE DATABASE statement is used.




Here is a sample database snapshot create script of the sample database AdventureWorks

First, in order to place the data files of the snapshot, we should define the physical path of the container folder

We can use the source database's data folder for the same purpose for snapshot database

select physical_name from sys.database_files WHERE file_id = 1

The above query will display where the .mdf file exists. So we can use the same folder. I'm going to use "D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\"

Considering the below note from Books On Line;

When you create a database snapshot, the CREATE DATABASE statement cannot reference log files, offline files, restoring files, and defunct files.

I run the below select statement to get a list of data files that I should reference

select name from sys.database_files WHERE type_desc = 'ROWS'

The returned row set only contains the "AdventureWorks_Data"

Now, we are ready to create a snapshot of the source database.
CREATE DATABASE AdventureWorks_SS ON
(
NAME = AdventureWorks_Data,
FILENAME = 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.ss'
)
AS SNAPSHOT OF AdventureWorks

After the above command is completed successfully, we can see the snapshot database in the Database Snapshots folder of the SQL Server Instance

In order to delete or remove a database snapshot after it is not needed any more, you can DROP it by using the DROP DATABASE command

DROP DATABASE AdventureWorks_SS

Note that if database snapshot creation fails, the snapshot is in suspect status and it should be deleted or dropped.

I believe, sql server programmers as well as database administrators will benefit more from Microsoft database snapshots. Especially when sql server performance is an issue than sql server snapshots may be a solution especially for distinguishing reporting databases from the transactional production databases. Administrators or developers do not need to backup and restore sql databases for creating a second database for sql server reporting services, for instance.