Introduction
If you needed to analyse a database at a given time, what would you do? For example, say you wanted to analyse a database at 12:00PM, how would you do this? Most probably, you would create a schedule to create a backup at 12:00 PM and then restore the database into a database server giving it another name. Then you could analyse the restored database. The problem with this method is that, if you have a huge database it may require a large amount of disk space and restoring the database will take a long period of time. Also, if you need to analyse data at peak times, then creating a backup will take a lot of resources which may result in your server being unavailable. With SQL Server 2005, there is a new feature called Database Snapshot that allows you to perfrom this with ease.
Apart from the above mentioned benefit, there are other benefits from database snapshots which we will discuss during this article. Database Snapshots are a under utilised feature, meaning that many DBA’s and developers are unaware of it.
What is a Database Snapshot
Database snapshots consist of a read-only static view of the database without including the uncommitted transactions. The uncommitted transactions are rolled back to make the database snapshot transactionally consistent.
How it works
As always, it is not essential to learn how it works to use it. However, it will be helpful to know how it works.
Just after you create the snapshot database, that database will be allocated to an empty sparse file. Whenever there are changes to the original data pages, the original page are moved to the spare file. If you access the database snapshot, it will be read from the sparse file and from the original database data pages which have not changed. We can see how this works from the following image.
Source: SQL Server 2005 books online
Working with Database Snapshots
First you need to create a database snapshot. There are two ways of creating database snapshot.
CREATE DATABASE ssAdventureWorks_dbss2230 ON
( NAME = AdventureWorks_Data, FILENAME =
‘C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataAdventureWorks_data_2230.ss’ )
AS SNAPSHOT OF AdventureWorks;
GO
In the above example, AdventureWork_data_2230.ss is the sparse file. The extension ss is just an arbitrary value and it is not a default or required.
Accessing this database snapshot is as same as any other database.
SELECT *
FROM [ssAdventureWorks_dbss2230].dbo.Employees
Like accessing a database, dropping the database snapshot is the same as a normal database drop.
DROP DATABASE [ssAdventureWorks_dbss2230]
You have the option of restoring a database snapshot into the current database. In that case, database snapshots can be treated as a database backup.
RESTORE DATABASE AdventureWorks from
DATABASE_SNAPSHOT = ‘ssAdventureWorks_dbss2230’;
GO
These are the only database options that are available with database snapshots. This means that you cannot take backups of database snapshot or restore them to database snapshot.
Scheduling Database Snapshot
Scheduling a database snapshot is very important as there is no direct options from the database snapshot node in the SQL Server Management Studio. However, by using a SQL Server agent job you can create a schedule to create database snapshot.
The following script will create a job with two steps. The first step is to drop the database snapshot while the second is to create a database snapshot again. This job will create a database snapshot once every hour.
USE [msdb]
GO
/****** Object: Job [Database snapshot] Script Date: 01/29/2008 16:39:31 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 01/29/2008 16:39:31 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]’ AND category_)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @’JOB’, @type=N’LOCAL’, @name=N'[Uncategorized (Local)]’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N’Database snapshot’,
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N’No description available.’,
@category_name=N'[Uncategorized (Local)]’,
@owner_login_name=N’Dinesh-Mobdinesh’, @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [drop database snapshot] Script Date: 01/29/2008 16:39:32 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’drop database snapshot’,
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=3,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N’TSQL’,
@command=N’DROP DATABASE [ssAdventureWork_dbss2230]’,
@database_name=N’master’,
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Create Snapshot] Script Date: 01/29/2008 16:39:32 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’Create Snapshot’,
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N’TSQL’,
@command=N’CREATE DATABASE ssAdventureWorks_dbss2230 ON
( NAME = AdventureWorks_Data, FILENAME =
”C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataAdventureWorks_data_2230.ss” )
AS SNAPSHOT OF AdventureWorks;
‘,
@database_name=N’master’,
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N’Schedule’,
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=8,
@freq_subday_interval=1,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20080129,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
Other Information
As most of you are aware you can view the list of databases from the sys.databases system view. Database snapshots are also in the same list. In this view there are two columns which are relevant to database snapshots. One is source_database_id which tells you the source database id or the original database of the database snapshot. The other field is is_read_only. As a snapshot database is a read only database, this column is set to 1.
SELECT NAME,
database_id,
source_database_id,
is_read_only
FROM sys.databases
Advantages
- The biggest advantage is that a databases snapshot can be used as a reporting database. As a database snapshot is a read-only copy of the primary database, by directing reports to a database snapshot we can reduce the load on the primary database.
- Databases snapshots can be used as a recovery database with limited features.
Disadvantages
- The main disadvantage of database snapshots is that they are available only with SQL Server Enterprise edition. As we know Enterprise edition costs more money hence not everyone will be able to use this valuable feature.
- Database snapshots are attached to the primary database. Hence you cannot detach it from the primary database.
- Full-text indexing is not supported in database snapshots.
Conclusion
Database snapshot is a handy feature which can be used in SQL Server Enterprise edition. However, it need to be stressed that a database snapshot is not a replacement to SQL Server backups. Use database snapshot for reporting and let me know your experience on this valuable feature.
]]>