Alias Names in SQL ServerBasic DDL Operations in MS SQL ServerDynamic SQLDynamic SQL PivotInstalling SQL Server on WindowsJSON in SQL ServerManaging Azure SQL DatabaseMicrosoft SQL Server Advanced optionsMicrosoft SQL Server Aggregate FunctionsMicrosoft SQL Server Analyzing a QueryMicrosoft SQL Server Backup and Restore DatabaseMicrosoft SQL Server bcp (bulk copy program) UtilityMicrosoft SQL Server BULK ImportMicrosoft SQL Server CASE StatementMicrosoft SQL Server CLUSTERED COLUMNSTOREMicrosoft SQL Server COALESCEMicrosoft SQL Server Common Language Runtime IntegrationMicrosoft SQL Server Common Table ExpressionsMicrosoft SQL Server Computed ColumnsMicrosoft SQL Server Converting data typesMicrosoft SQL Server CREATE VIEWMicrosoft SQL Server cross applyMicrosoft SQL Server CursorsMicrosoft SQL Server Data TypesMicrosoft SQL Server Database permissionsMicrosoft SQL Server Database SnapshotsMicrosoft SQL Server DatesMicrosoft SQL Server DBCCMicrosoft SQL Server DBMAILMicrosoft SQL Server Delimiting special characters and reserved wordsMicrosoft SQL Server Drop KeywordMicrosoft SQL Server Dynamic data maskingMicrosoft SQL Server EncryptionMicrosoft SQL Server Export data in txt file by using SQLCMDMicrosoft SQL Server File GroupMicrosoft SQL Server FilestreamMicrosoft SQL Server for JSONMicrosoft SQL Server for XML PATHMicrosoft SQL Server Foreign KeysMicrosoft SQL Server Full-Text IndexingMicrosoft SQL Server Generating a range of datesMicrosoft SQL Server GROUP byMicrosoft SQL Server IF...ELSEMicrosoft SQL Server In-Memory OLTP (Hekaton)Microsoft SQL Server IndexMicrosoft SQL Server InsertMicrosoft SQL Server INSERT INTOMicrosoft SQL Server IntroductionMicrosoft SQL Server Isolation levels and lockingMicrosoft SQL Server JoinMicrosoft SQL Server Last Inserted IdentityMicrosoft SQL Server Limit Result SetMicrosoft SQL Server Logical FunctionsMicrosoft SQL Server Management Studio Shortcut KeysMicrosoft SQL Server MERGEMicrosoft SQL Server MigrationMicrosoft SQL Server Modify JSON textMicrosoft SQL Server Move and copy data around tablesMicrosoft SQL Server Natively compiled modules (Hekaton)Microsoft SQL Server NULLsMicrosoft SQL Server OPENJSONMicrosoft SQL Server ORDER byMicrosoft SQL Server over ClauseMicrosoft SQL Server PaginationMicrosoft SQL Server ParsenameMicrosoft SQL Server PartitioningMicrosoft SQL Server Permissions and SecurityMicrosoft SQL Server PHANTOM readMicrosoft SQL Server PIVOT / UNPIVOTMicrosoft SQL Server Primary KeysMicrosoft SQL Server Privileges or PermissionsMicrosoft SQL Server Queries with JSON dataMicrosoft SQL Server Query HintsMicrosoft SQL Server Query StoreMicrosoft SQL Server Querying results by pageMicrosoft SQL Server Ranking FunctionsMicrosoft SQL Server Resource GovernorMicrosoft SQL Server Retrieve information about the databaseMicrosoft SQL Server Retrieve Information about your InstanceMicrosoft SQL Server Row-level securityMicrosoft SQL Server Scheduled Task or JobMicrosoft SQL Server SchemasMicrosoft SQL Server SCOPE IDENTITY()Microsoft SQL Server SELECT statementMicrosoft SQL Server SequencesMicrosoft SQL Server Service brokerMicrosoft SQL Server Sorting/ordering rowsMicrosoft SQL Server Spatial DataMicrosoft SQL Server SQLCMDMicrosoft SQL Server Stored Procedures

Microsoft SQL Server Database Snapshots

From WikiOD

Remarks[edit | edit source]

A database snapshot is a read-only, static view of a SQL Server database which is transactionally consistent with the source database as of the moment of the snapshot's creation.

A database snapshot always resides on the same server instance as its source database. As the source database is updated, the database snapshot is updated.

A snapshot differs from a backup since the process of snapshot creation is instantaneous and the snapshot occupies space only as changes in the source database are applied. A backup on the other hand stores a full copy of the data as on the time of backup creation.

Additionally, a snapshot gives an instant read only copy of the database, while a backup needs to be restored to a server in order to be readable (and once restored can be written to as well)

Database snapshots are only available in the Enterprise and Developer editions.

Create a database snapshot[edit | edit source]

A database snapshot is a read-only, static view of a SQL Server database (the source database). It is similar to backup, but it is available as any other database so client can query snapshot database.

CREATE DATABASE MyDatabase_morning -- name of the snapshot
ON (
     NAME=MyDatabase_data, -- logical name of the data file of the source database
     FILENAME='C:\SnapShots\' -- snapshot file; 
AS SNAPSHOT OF MyDatabase; -- name of source database

You can also create snapshot of database with multiple files:

    (NAME=MyMultiFileDb_ft, FILENAME='C:\SnapShots\'),
    (NAME=MyMultiFileDb_sys, FILENAME='C:\SnapShots\'),
    (NAME=MyMultiFileDb_data, FILENAME='C:\SnapShots\'),
    (NAME=MyMultiFileDb_indx, FILENAME='C:\SnapShots\')

Restore a database snapshot[edit | edit source]

If data in a source database becomes damaged or some wrong data is written into database, in some cases, reverting the database to a database snapshot that predates the damage might be an appropriate alternative to restoring the database from a backup.


Warning: This will delete all changes made to the source database since the snapshot was taken!

DELETE Snapshot[edit | edit source]

You can delete existing snapshots of database using DELETE DATABASE statement:

DROP DATABASE Mydatabase_morning

In this statement you should reference name of the database snapshot.