In SQL Server, a snapshot backup is a type of backup that creates a read-only copy of a database at a specific point in time. Unlike traditional backups, which create a copy of the entire database, a snapshot backup only copies the pages that have changed since the last snapshot backup was taken. This makes snapshot backups much faster than traditional backups, and also reduces the amount of disk space required to store the backup.
Snapshot backups are created using the SQL Server Database Snapshot feature, which was introduced in SQL Server 2005. To create a snapshot backup, you first need to create a database snapshot. This creates a read-only, point-in-time view of the database that can be used to create a snapshot backup. Once the snapshot is created, you can use the BACKUP DATABASE command to create a backup of the snapshot.
Snapshot backups have several benefits over traditional backups, including:
Reduced backup times: Because snapshot backups only copy the pages that have changed, they are typically much faster than traditional backups, especially for large databases.
Reduced disk space requirements: Because snapshot backups only copy the changed pages, they require less disk space than traditional backups.
Improved backup reliability: Because snapshot backups are taken at a specific point in time, they provide a consistent view of the database, even if changes are being made to the database during the backup process.
However, there are also some limitations to snapshot backups that you should be aware of. For example:
Snapshot backups cannot be used as a stand-alone backup solution. They must be used in conjunction with traditional backups to ensure complete data protection.
Snapshot backups are not suitable for all types of databases. For example, databases with high rates of change or that use large amounts of temporary storage may not be good candidates for snapshot backups.
Snapshot backups can affect database performance. Creating a snapshot can temporarily reduce database performance and increase I/O activity. Additionally, because snapshot backups only copy the pages that have changed, restoring a snapshot backup can take longer than restoring a traditional backup.
Here are some scenarios where you might use a snapshot backup in SQL Server:
Report generation: If you have a database that is being heavily used for transaction processing, it can be difficult to generate reports from it without impacting performance. By creating a snapshot backup, you can generate reports from the snapshot without affecting the performance of the original database.
Testing and development: If you need to make changes to a database schema or application code, you can create a snapshot backup to provide a consistent view of the database for testing and development purposes. This allows you to test changes without impacting the production database.
Disaster recovery: If you need to restore a database to a specific point in time, a snapshot backup can be a useful tool. By creating a snapshot backup at regular intervals, you can have multiple restore points available to you, which can be useful in the event of a disaster.
Archiving: If you need to retain data for compliance or regulatory reasons, but don't need to access it regularly, you can create a snapshot backup and store it in a separate location. This allows you to retain a read-only copy of the data without impacting the performance of the production database.
Troubleshooting: If you are experiencing issues with a database, you can create a snapshot backup and use it for troubleshooting purposes. By examining the snapshot, you can get a better understanding of the state of the database at a specific point in time, which can help you diagnose issues more effectively.
How can I create a snapshot backups
To create a snapshot backup in SQL Server, you can use the CREATE DATABASE and BACKUP DATABASE commands. Here's how you can create a snapshot backup:
Create a database snapshot:
You can create a database snapshot using the CREATE DATABASE command, followed by the AS SNAPSHOT OF clause and the name of the database you want to create a snapshot of.
For example:
CREATE DATABASE [YourDB_Snapshot]
ON
(NAME = YourDB, FILENAME = 'C:\Snapshots\YourDB_Snapshot.ss')AS SNAPSHOT OF YourDB;
This will create a database snapshot called YourDB_Snapshot in the C:\Snapshots directory.
Backup the snapshot:
Once you've created the snapshot, you can use the BACKUP DATABASE command to create a backup of the snapshot. For example:
BACKUP DATABASE [YourDB_Snapshot]
TO DISK = 'C:\Backups\YourDB_Snapshot.bak'This will create a backup of the YourDB_Snapshot database to the C:\Backups directory.
Note that the backup file created by the BACKUP DATABASE command will be compressed by default, but you can use the WITH COMPRESSION option to explicitly enable compression, like this:
BACKUP DATABASE [YourDB_Snapshot]
TO DISK = 'C:\Backups\YourDB_Snapshot.bak'WITH COMPRESSION
This will create a compressed backup of the YourDB_Snapshot database.
How can I add a user that has access to the read only snapshot and not the read write original database ?
To add a user that has access to the read-only snapshot but not the read-write original database, you can create a new login for the user and grant them the db_datareader role in the snapshot database.
Here are the steps:
Create a new login: You can create a new login using the CREATE LOGIN command. For example, to create a login for a user called "SnapshotUser", you would run:
CREATE LOGIN SnapshotUser WITH PASSWORD = 'password';Replace 'password' with the password you want to use for the user.
Create a user in the snapshot database: Once you have created the login, you need to create a user for the login in the snapshot database. You can do this using the CREATE USER command, like this:
USE YourSnapshotDatabase;
CREATE USER SnapshotUser FOR LOGIN SnapshotUser;Replace 'YourSnapshotDatabase' with the name of your snapshot database.
Grant permissions: Finally, you need to grant the user the db_datareader role in the snapshot database so that they can read data from it. You can do this using the sp_addrolemember system stored procedure, like this:
EXEC sp_addrolemember 'db_datareader', 'SnapshotUser';
This will add the user to the db_datareader role, which allows them to read data from all user tables in the database.
Note that this user will not have access to the read-write original database unless you explicitly grant them access to it.