SQL Server database snapshot is an amazing use.

What do you do if you want to analyze data in the database at a specific time? For example, what measures do you take if you want to analyze the data at pm? The most frequently used method is to create a scheduled task, perform a backup at, and back up the current database to the server with a new name, then, analyze the data in the backup database. The problem is that if the database is large, it will take a lot of time and disk space to back up. If you need to back up data during peak hours, it will cost enough resources to bring your server down. However, in SQL server2005, there is a new feature named database snapshot, which allows you to easily handle similar problems. In addition to the advantages mentioned above, there are many other advantages for using database snapshots. This article will focus on the advantages of database snapshots. Database snapshots are not very portable, which means that many DBAs and developers have not noticed their existence. 　　What is a database snapshot? Database snapshots are read-only static views of the current database, excluding those transactions that have not yet been committed. Transactions that have not been committed are rolled back to ensure transaction consistency in the database. 　　Working Principle Generally, we do not need to know how a function works when using it. However, knowing how it works will bring great benefits to our work. Once you create a database snapshot, the snapshot database will be allocated to an idle file. When the original data page changes, the page will be moved to the idle file. When you access the database snapshot, you will access the idle files and those unchanged data pages on the original database. We can see how database snapshots work. 　　Figure 1 (Source: SQL Server 2005 online manual) 　　Use Database snapshots First, you need to create a database snapshot. There are two ways to create a database snapshot. Create Database ssadventureworks_dbss2230 on (Name = adventureworks_data, filename = 'C: \ Program Files \ Microsoft SQL Server \ mssql.1 \ MSSQL \ data \ adventureworks_data_2230.ss ') As snapshot of adventureworks; Go In the preceding example, adventurework_data_2230.ss is an idle file. The extension CC is an arbitrary value. It is neither a default nor a required value. The method for accessing database snapshots is the same as that for accessing a common database: Select * From [ssadventureworks_dbss2230]. DBO. Employees Like accessing a database, I can also delete database snapshots like deleting a common database. Drop database [ssadventureworks_dbss2230] You can choose to store the database snapshot on the current database. In this way, the database snapshot becomes a backup of the current database. Restore database adventureworks from Database_snapshot = 'ssadventureworks _ dbss2230 '; Go For database snapshots, only the unique option is available, which means that we cannot back up a database snapshot or store it on a database snapshot. 　　Timed database Snapshot Timing database snapshots is very important, but in SQL Server Management Tools, database snapshot nodes do not have this direct option. However, we can use the SQL Server proxy job to create a scheduled job to create a database snapshot. The following script creates a scheduled job in two steps. The first step is to delete the database snapshot, and the second step is to recreate a database snapshot. This job will create a database snapshot 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_class = 1) Begin Exec @ returncode = MSDB. DBO. sp_add_category @ class = n'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, @ Policy_level_eventlog = 0, @ Policy_level_email = 0, @ Policy_level_netsend = 0, @ Policy_level_page = 0, @ Delete_level = 0, @ Description = n' no description available .', @ Category_name = n' [uncategorized (local)] ', @ Owner_login_name = n'dinesh-Mob \ Dinesh ', @ 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 Files \ Microsoft SQL Server \ mssql.1 \ MSSQL \ data \ adventureworks_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 Most of the time, we know that you can view the Database List in the System View of SYS. databases. The database snapshot is also in this list. In this view, there are two columns related to database snapshots. A column is source_database_id, which indicates the source database ID or snapshot. The other column is is_read_only. When a snapshot database is a read-only database, the value of this column is 1. Select name, Database_id, Source_database_id, Is_read_only From SYS. Databases 　　Advantages 1. The biggest advantage of a database snapshot is that it can be used as a report database. Because the database snapshot is a read-only copy of the primary database, the execution of a report on a database snapshot can greatly reduce the loading time. 2. Only a few feature values are required for database snapshots to restore the source database. 　　Disadvantages 1. The main drawback of database snapshot is that it can only be used on SQL Server Enterprise Edition. We all know that the Enterprise Edition cost well, so not everyone can use this function. 2. Database snapshots are attached to the primary database, so they cannot be used independently. 3. Full-text search is not supported in database snapshots. 　　Conclusion Database snapshots are a convenient feature in SQL Server Enterprise Edition. However, it should be emphasized that database snapshots cannot replace database backup. If you want to make good use of this useful feature, you can use it more in data reports.