Skip to main content

Database Snapshots in SQL Server





Database Snapshots in SQL Server


What is DB Snapshot?

Snapshots are read only and are a very good option for reporting purposes. All the reporting related hits could be made on the snapshot instead of on the the actual database, and the burden on the active database could be minimized. Also, as snapshots are read only, chances of any undesired updates by the subscriber (who reads data for reports etc.) could be negated.

A database snapshot is a read-only, static view of a SQL Server database (the source database).It is transactionally consistent with the source database as of the moment of the snapshot's creation and always resides on the same server instance as its source database. As the source database is updated, the database snapshot is updated. Therefore, the longer a database snapshot exists, the more likely it is to use up its available disk space. 

Multiple snapshots can exist on a given source database. Each database snapshot persists until it is explicitly dropped by the database owner. 

Database snapshots operate at the data-page level. Before a page of the source database is modified for the first time, the original page is copied from the source database to the snapshot. The snapshot stores the original page, preserving the data records as they existed when the snapshot was created. The same process is repeated for every page that is being modified for the first time.

 To the user, a database snapshot appears never to change, because read operations on a database snapshot always access the original data pages, regardless of where they reside.
To store the copied original pages, the snapshot uses one or more sparse files. Initially, a sparse file is an essentially empty file that contains no user data and has not yet been allocated disk space for user data. As more and more pages are updated in the source database, the size of the file grows.

Below is the link to code for creating a DB snapshot and also dropping a existing DB snapshot

Create and Drop Snapshot

References:

https://www.codeproject.com/Articles/211100/Database-Snapshot-in-MS-SQL-Server
https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-snapshots-sql-server
 

Labels:

Comments

Post a Comment

Popular posts from this blog

SQL Server

                                                                     SQL Server  Its been a while that I have updated my blog. Though Databases and SQL was something that I used to stay away as much as possible because for some reasons, I got to work on these all the day for 8-9 hours and sometimes haunting in the nights and weekends as well. However, it has been a good journey so far and I found some interesting stuff in SQL Server. So, this triggered an idea a couple of weeks back to share some cool stuff that I am learning in SQL Server over numerous sources. Hope I can make this a good series. As there are not much visitors to my blog, let me start with wishing me a good luck. So, here starts the SQL Server Series!!!!!!
4 comments
Read more

An TLS 1.2 connection request was received from a remote client application, but none of the cipher suites supported by the client application are supported by the server. The TLS connection request has failed.

If the certificate being used on the server was generated using the Legacy Key option in the certificate request form, the private key for that certificate will be stored in Microsoft's legacy Cryptographic API framework. When the web server tries to process requests using its new, Cryptographic Next Generation (CNG) framework, it appears that something related to the RSA private key stored in the legacy framework is unavailable to the new framework. As a result, the use of the RSA cipher suites is severely limited. To avoid the issue, you can try to generate the certificate request using the CNG Key template in the custom certificate request wizard.
Post a Comment
Read more

Remote Login Failure on Server

I ran into an interesting situation when executing a Remote Stored Procedure on Linked   Server using four part query.    Vista-3139 which is a SQL Server 2005 instance is linked to the SERV2003\SQLDUMMY which is a SQL Server 2000 named instance. When I tried to execute a stored procedure: EXEC [SERV2003\SQLDUMMY].SQLAdmin.dbo.sp_ddlevents   Got the following error message. Could not connect to server SERV2003\SQLDUMMY ' because '' is not defined as a remote login at the server. Verify that you have specified the correct login name. I checked the Security settings of the linked server and it was set to “Be made using the login's current security context", which is valid considering that the user has all the permissions to login to the remote SQL server. Then I changed the security context to use a SQL authentication, but still it failed with the similar error message as described above. To troubleshoot this further, I ran
Post a Comment
Read more