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

Active Directory in Windows

                                                                Active Directory in Windows  When a windows server is bought it comes with many capabilities like AD groups for security, IIS(Internet Information Services), ability to built own VPN and many more of which Active directories are created with a domain and the computers which access the network will be connecting to the domain controllers. Practically, there will be a cluster of domain controllers where data will be replicated all over the cluster.
Post a Comment
Read more

Internet.org

                                                        Internet.org Internet.org is a non-profitable oraganisation founded by facebook and six other companies to bring affordable access to internet services to less developed countries by increasing efficiency, and facilitating the development of new business models around the provision of Internet access. It has been criticized for violating net neutrality and favouring facebook's own services. An Indian journalist criticized it as" being just a Facebook proxy targeting India's poor" as it provides restricted Internet access to Reliance Telecom's subscribers in India.However, in early May 2015, Facebook announced that this platform would be opened to websites that met its criteria.           ...
Post a Comment
Read more