| Ever wanted to make a read-only point-in-time copy | | | | restoring from a snapshot renders all other snapshots |
| of a database, and wondered which technique to use? | | | | unusable - they should be deleted and re-created if |
| Microsoft SQL Server 2005 provides a plethora of | | | | required. |
| ways to do this, including database backup/restore, | | | | How do Database Snapshots work? |
| database detach/re-attach, log-shipping, replication, | | | | A Database Snapshot looks like an ordinary read-only |
| mirroring, and so forth. However, one method available | | | | database, from the user's point of view; it can be |
| in the Enterprise edition, the Database Snapshot, is | | | | accessed with a USE statement, and can be browsed |
| new to SQL Server 2005, and is worth taking a closer | | | | from within Management Studio. However, it initially |
| look. | | | | occupies almost no disk space, and so can be created |
| Why are Database Snapshots Useful? | | | | almost instantly. This magic is achieved via an NTFS |
| There are many applications where a point-in-time | | | | feature, sparse files. A sparse file is a file that may |
| snapshot is useful. Microsoft suggest the following use | | | | appear to be large, but in fact only occupies a portion |
| cases: | | | | of the physical space allocated to it. |
| - Reporting up to a specific time period, ignoring later | | | | Now, because a database snapshot presents a |
| data | | | | read-only view of your source database, it need not |
| - Reporting against mirror or standby databases that | | | | store a copy of every page. Instead, SQL Server |
| are otherwise unavailable | | | | performs a copy-on-write operation; in the source |
| - Insuring against user or administrator error, providing a | | | | database, the first time a data page changes after the |
| quick way to revert to an older version of the | | | | creation of a snapshot, a copy of the original page is |
| database | | | | placed in the sparse file. The snapshot serves data |
| - Managing test databases, particularly during rapid | | | | from the snapshot copies where source data has |
| feature and schema development Of course, these | | | | changed, and the original source pages when they are |
| needs could be served by a database backup or | | | | unchanged. |
| attached copy of a database, but the key benefit of | | | | Best Practices |
| choosing a snapshot over one of the other methods is | | | | Sometimes you will choose a copy of a backup over |
| simple: creating a database snapshot is fast. | | | | a snapshot, sometimes it'll be a detached copy of the |
| Creating and Using Database Snapshots | | | | data file. However, for many situations your best bet is |
| Creating database snapshots is easy - it's a CREATE | | | | a database snapshot, so it's worth keeping some |
| DATABASE statement, specifying only the logical and | | | | points in mind. In particular: |
| physical filenames. Remember it's a read-only | | | | - The file size will look considerably larger than the |
| snapshot, so we don't need to add autogrowth or | | | | space it consumes on disk, and should be clearly |
| transaction log settings. Here's the code: | | | | marked as a snapshot for this reason. Use explicit |
| CREATE DATABASE | | | | naming conventions to make it clear to administrators. |
| AdventureWorks_Snapshot_Monday ON | | | | - Snapshots are at their best when young and fresh, |
| ( | | | | and don't take up too much space. If you need to |
| NAME=AdventureWorks_Data, | | | | keep a snapshot for any length of time, consider using |
| FILENAME='C:SnapshotsAD2K_Monday.ss' | | | | another method to create your read-only copies. |
| ) | | | | - As snapshots persist until deleted, you will need to |
| AS SNAPSHOT OF AdventureWorks | | | | explicitly rotate snapshots, either manually or with a |
| Snapshot creation is not supported by the Object | | | | script. |
| Explorer interface in Management Studio; you must | | | | - Performing index operations such as |
| use a CREATE DATABASE statement as above, | | | | defragmentation or index rebuilding will modify so many |
| with the AS SNAPSHOT OF clause indicating the | | | | pages that the snapshot will likely contain a complete |
| source database. Also, note that only the Enterprise | | | | copy of the source data for that index. The more |
| edition of SQL Server 2005 supports database | | | | snapshots there are, the more copies will exist. |
| snapshots. | | | | - If the disk containing a snapshot fills up, and a page |
| The snapshot contains a version of the data as it | | | | write fails, the snapshot will become useless, as it will |
| existed at its creation, having rolled back uncommitted | | | | not contain all necessary pages. Make sure the disk |
| transactions. This means that otherwise unavailable | | | | can't fill up! Database snapshots are a worthwhile |
| databases, such as mirrors and standby servers, can | | | | addition to the arsenal of any SQL Server DBA, and fit |
| be used to create snapshots. | | | | well with other techniques, particularly when you may |
| Having created a snapshot, you can now use it as you | | | | need to quickly revert a database, or if you need to |
| would any other read-only database; all objects are | | | | maintain rolling snapshots. Remember the key |
| exposed in exactly the same way, via Object | | | | advantages: high speed and low physical size. But also |
| Explorer, scripts, or reporting tools. | | | | remember that these advantages diminish as the |
| Reverting a database to the version stored in the | | | | snapshot ages and grows, and if the number of |
| snapshot is similarly easy: | | | | snapshots increases. |
| RESTORE DATABASE AdventureWorks FROM | | | | Above all, database snapshots are fast and easy to |
| ot_Monday' | | | | use; it won't cost you anything to try them out, and you |
| This returns the database to the state it was in when | | | | will probably find them very useful indeed. If all you |
| the snapshot was created, minus any uncommitted | | | | need to do with a point-in-time copy is select from it, or |
| transactions - remember that a snapshot is | | | | possibly revert to it, then a database snapshot is likely |
| transactionally consistent at its creation. Note that | | | | the best choice available. |