Issue
A database snapshot is a read-only, static view of the source database. Multiple snapshots can exist on a source database and always reside on the same server instance as the database. Each database snapshot is transaction consistent with the source database as of the moment of the snapshot's creation. A snapshot persists until it is explicitly dropped by the database owner.
Snapshots can be used for reporting purposes. Also, in the event of a user error on a source database, you can revert the source database to the state it was in when the snapshot was created. Data loss is confined to updates to the database since the snapshot's creation.
Product Line
SQL Server
Environment
SQL Server Management Studio, SSMS
Cause
Reasons to take database snapshots include:
Resolution
*Warning: Irreparable database damage can occur. This procedure should only be performed by users familiar with SQL Server Management Studio. Databases should be backed up prior to performing this procedure.*
1. This Transact-SQL creates a snapshot of the database:
CREATE DATABASE ION_Data_Snap
ON (name='ION_Data_Data', filename='C:\ION\Database\ION_Data.ds')
AS SNAPSHOT of ION_Data;
GO
Where:
2. This Transact-SQL restores from a snapshot:
RESTORE DATABASE ION_Data
FROM DATABASE_SNAPSHOT = 'ION_Data_Snap';
GO
3. This Transact-SQL drops the snapshot database:
DROP DATABASE ION_Data_Snap
GO
A database snapshot is a read-only, static view of the source database. Multiple snapshots can exist on a source database and always reside on the same server instance as the database. Each database snapshot is transaction consistent with the source database as of the moment of the snapshot's creation. A snapshot persists until it is explicitly dropped by the database owner.
Snapshots can be used for reporting purposes. Also, in the event of a user error on a source database, you can revert the source database to the state it was in when the snapshot was created. Data loss is confined to updates to the database since the snapshot's creation.
Product Line
SQL Server
Environment
SQL Server Management Studio, SSMS
Cause
Reasons to take database snapshots include:
- Maintaining historical data for report generation.
- Safeguarding data against administrative error.
- Safeguarding data against user error. By creating database snapshots on a regular basis, you can mitigate the impact of a major user error, such as a dropped table.
- To recover from a user error, you can revert the database to the snapshot immediately before the error.
- Managing a test database.
Resolution
*Warning: Irreparable database damage can occur. This procedure should only be performed by users familiar with SQL Server Management Studio. Databases should be backed up prior to performing this procedure.*
1. This Transact-SQL creates a snapshot of the database:
CREATE DATABASE ION_Data_Snap
ON (name='ION_Data_Data', filename='C:\ION\Database\ION_Data.ds')
AS SNAPSHOT of ION_Data;
GO
Where:
- name: refers to the logical name for ION_Data database (which is ION_Data_Data)
- filename: name and location of the snapshot (in this example the extension is 'ds')
- filename: name and location of the snapshot (in this example the extension is 'ds')
2. This Transact-SQL restores from a snapshot:
RESTORE DATABASE ION_Data
FROM DATABASE_SNAPSHOT = 'ION_Data_Snap';
GO
3. This Transact-SQL drops the snapshot database:
DROP DATABASE ION_Data_Snap
GO