| In SQL Server 2005, you have the ability to create a snapshot of your data. A database snapshot is essentially a placeholder of the time that it is taken. At any point going forward, you can revert to the snapshot and remove the changes that you have made to your data. Here is a short list of times you may find snapshots very helpful: During the development cycle of the database or its related application, you can take a snapshot to create a baseline set of data to work with. Of course, this also helps developers as they will not have to recover databases as often and they can store multiple snapshots with different changes so they can go back as far as they need to without totally scrapping their work up to that point. You can use snapshots when doing testing. Whether you are making schema changes or testing new data-load techniques, you can use a database snapshot as a known starting point to which you can revert before rerunning a test. You can use snapshots to recover from a flawed bulk load on your production system.. There are many times when a bulk load can fail and not properly roll back, leaving you with your database in an unknown state. Previously, you would need to restore from a backup, but if you schedule a snapshot before you execute the bulk load, you can revert to the snapshot and quickly get up and running again.
Tip  | You can also use snapshots for static reporting databases. For instance, if you want to do some reporting on data from the end of last year, you can create a snapshot of the data on December 31 or January 1 and use it for your year-end reporting. |
As you can see, database snapshots can be very beneficial in development, testing, and production environments. Important  | Database snapshots are only available in SQL Server 2005 Enterprise Edition and SQL Server 2005 Developer Edition. Most of your development and testing personnel can use the Developer Edition, but if you would like to implement this in your production environment, you will need to use the Enterprise Edition of SQL Server 2005. |
Creating a Database Snapshot Database snapshots can only be created using Transact-SQL. They cannot be created in SQL Server Management Studio. Here is the code for creating a database snapshot using the sample database AdventureWorks. (This code is included in the sample files as Create Snapshot.sql.) Creating a Snapshot 1. | From the Start menu, select All Programs | Microsoft SQL Server 2005 | SQL Server Management Studio.
| 2. | Open a New Query window. Enter and execute the following code to create a snapshot of AdventureWorks. Adjust the file path to suit your folder structure.
CREATE DATABASE AdventureWorks_SBSExample1 ON ( NAME = AdventureWorks_Data, FILENAME = 'C:\MySnapshotData\AdventureWorks_SBSExample1.snapshot' ) AS SNAPSHOT OF AdventureWorks; GO
|
The first thing you will notice about this CREATE statement is that it is the same statement used to create databases. This means that the user executing this statement must have the necessary permissions to create databases on the server they are working with. The next thing to note is that the snapshot must mirror all the files that are contained in the source database. In this case, AdventureWorks only has a single file AdventureWorks_Data. However, if more than one file or file group is used in the source database, you will need to add a NAME and FILENAME clause for each file. Important | You need to match the logical file name from the source database during the creation of your snapshot. If you use multiple files, those logical file names must be created with the source's logical name as well. |
The last piece of the code lets SQL Server know that you are creating a snapshot of the specified database. Remember that the snapshots and their source databases need to be on the same server. Additional Considerations When Creating a Snapshot Snapshot Names Use names that are meaningful when you choose the name of the snapshot. In the example above, SBSExample1 refers to this book, Step-by-Step. However, you will find it more beneficial to use dates and times in your snapshot names so users will be able to see easily what data is in the snapshot. File Names It is generally a good practice to generate the filename from the database or logical file name. Furthermore, the extension can be whatever makes sense to you. SQL Server Books Online uses the .ss file extension, and the example used the .snapshot extension. Use the naming convention that works best for you. Disk Space Usage You will need to account for the amount of disk space required by your snapshots. Snapshots only contain changes from the source at the time of the snapshot. This means that the more changes that are made on the source database, the more space will be required to store the snapshots recording these changes. Also, think about how many snapshots you want to keep. |
Reverting to a Database Snapshot As with creating a database snapshot, you will need to use T-SQL to revert to a database snapshot. To revert to the database snapshot, you use the RESTORE DATABASE command in T-SQL. Warning  | When you revert to a snapshot, ALL changes made from that point forward, both schema and data changes, will be lost. If you need to track the changes made, be sure to back up your source before reverting to the snapshot. |
Reverting to a Snapshot 1. | You will need to choose the snapshot you want to revert to. You can view the available snapshots in SQL Server Management Studio in the Database Snapshot folder. The location of this folder is illustrated below.
 | 2. | Once you have chosen your desired snapshot, you will need to remove any snapshots that have occurred after that snapshot. Once you revert, these will no longer be usable and SQL Server will not let you revert without removing them first. See the next section, titled "Deleting a Database Snapshot," for details on deleting snapshots.
Troubleshooting  | You will get the following errors if you try to revert to the previous snapshot without deleting the other snapshots: Msg 3137, Level 16, State 4, Line 1 Database cannot be reverted. Either the primary or the snapshot names are improperly specified, all other snapshots have not been dropped, or there are missing files. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.
|
| 3. | Enter and execute the following code, which uses the RESTORE DATABASE command to revert to your desired snapshot. (This code is included in the sample files as Restore FromSnapshot.sql.)
RESTORE DATABASE AdventureWorks FROM DATABASE_SNAPSHOT = 'AdventureWorks_SBSExample1'
Tip | If you are in development or testing, you can keep this snapshot and attempt your changes again. You will find that you can revert to this snapshot regularly and more quickly than restoring a backup, especially if your test database is rather large. |
| 4. | Now that you have finished restoring, you can use the source database as usual.
Tip | You can also use table partitioning to store history data. This can be done using a sliding window technique in which you rotate out file groups and archive them. See the SQL Server Books Online topic "Index and Table Partitioning" for details on using table partitioning. |
|
Deleting a Database Snapshot Unlike creating and reverting to a snapshot, you can delete a snapshot using either T-SQL or SQL Server Management Studio. As you have noticed by now, snapshots use variations of the database commands in T-SQL to perform their operations. This is also the case when deleting a snapshot. Deleting a Snapshot Using T-SQL Use the DROP DATABASE command when deleting a snapshot using T-SQL. The same restrictions exist for dropping a snapshot as with dropping any database. You will need to have all the connections cleared and have permission to drop databases in order to perform this action. To delete the snapshot we created, enter and execute the following code in a new query window. (This code is included in the sample files as DeleteSnapshot.sql.) DROP DATABASE AdventureWorks_SBSExample1;
|
Deleting a Snapshot Using SQL Server Management Studio In SQL Server Management Studio, you can delete a database snapshot the same way you would delete a normal database. 1. | Open SQL Server Management Studio.
| 2. | Expand the Database Snapshots folder in the Object Explorer.
| 3. | Select the snapshot you wish to delete.
| 4. | Right-click that snapshot and select Delete from the context menu. This will open the Delete Object dialog box, shown below:
 | 5. | You can choose to have SQL Server close connections from this dialog box by selecting the Close Existing Connections checkbox. The advantage of doing this is that the delete operation will not have to wait for transactions to complete before commencing. This is the equivalent of sending KILL statements to all the connections to the database.
| 6. | Click the OK button to delete the snapshot.
|
Impact on the Source Database The source database is impacted in the following ways when database snapshots are used with it: Database management You will not be able to drop or detach the source database while database snapshots exist. You will have to delete the snapshots first. You will not be able to restore the source database while database snapshots exist. Note  | Backup operations on the source database will continue to function normally. These operations are not affected when snapshots exist. |
If you revert to a snapshot, the log chain is broken and transactional log restores will not work as expected. Files cannot be dropped from the source database until the snapshots are deleted.
Database performance
|
|
