Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about how to achieve data recovery in SQL. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

What is a snapshot?

Database snapshots are a new feature of sql server 2005. It is defined on MSDN as:

A database snapshot is a read-only static view of a database (called the source database). At creation time, each database snapshot is transactionally consistent with the source database. When you create a database snapshot, the source database usually has open transactions. Before the snapshot can be used, the open transaction is rolled back to make the database snapshot transactional consistent.

The client can query the database snapshot, which is useful for writing reports based on the data when the snapshot was created. Also, if the source database is corrupted later, you can restore the source database to the state it was in when the snapshot was created.

Let's practice the creation and use of database snapshots.

Create a sample database

Use MASTER GO CREATE DATABASE [Snapshot_Test] ON PRIMARY (NAME = nicked shotgun testings,-- database name FILENAME = NumbC:\ Program Files\ Microsoft SQL Server\ MSSQL13.MSSQLSERVER\ MSSQL\ DATA\ Snapshot_Test.mdf',-- datafile storage location and datafile name SIZE = 3072KB,-- initial capacity MAXSIZE = UNLIMITED,-- maximum capacity FILEGROWTH = 1024KB-- growth capacity) LOG ON (NAME = NumberSnapshotball Testlog' FILENAME = NumbC:\ Program Files\ Microsoft SQL Server\ MSSQL13.MSSQLSERVER\ MSSQL\ DATA\ Snapshot_Test_log.ldf', SIZE = 504KB, MAXSIZE = UNLIMITED, FILEGROWTH = 10%) COLLATE Chinese_PRC_CI_AS GO EXEC dbo.sp_dbcmptlevel @ dbname=N'Snapshot_Test', @ new_cmptlevel=130 GO USE [Snapshot_Test] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo]. [test] ([id] [int] NOT NULL [name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL) ON [PRIMARY] GO

(hint: you can swipe the code left and right)

After creating the database, we immediately create a snapshot, which is also created using the CREATE DATABASE statement as follows:

Create a database snapshot

Create database Snapshot_Test_shot ON (--is the logical name of the source database Name = Snapshot_Test,-- snapshot file address FileName ='D:\ SqlData\ Snapshot_Test_shot.ss') AS SNAPSHOT OF Snapshot_Test

Results:

Now the snapshot should be the same as our newly-built database. You can view the database snapshot through sql server's object browser, and we can query the database snapshot through USE [snapshot library name].

Use Snapshot_Test_shot; go SELECT * FROM dbo.test

Results:

After the above statement is executed, there is no data in the dbo.test. Next, we insert a few pieces of data into the source database table.

Use snapshot_Test; go INSERT INTO TEST (id,name) values (1); INSERT INTO TEST (id,name) values (2); INSERT INTO TEST (id,name) values (3); INSERT INTO TEST (id,name) values (4); GO SELECT * FROM dbo.test

Results:

We query the data in the snapshot database again

Use Snapshot_Test_shot; go SELECT * FROM dbo.test

Results:

There is still no data, because once a snapshot is created, it cannot write data to it. It is a read-only file.

Restore a database using a snapshot

Restore a database from a database snapshot

Use master; GO RESTORE DATABASE Snapshot_Test from DATABASE_SNAPSHOT = 'Snapshot_Test_shot'

After the recovery, the data that has just been inserted is gone, so we can query it.

Use Snapshot_Test; SELECT * FROM dbo.test

Results:

This proves that the snapshot restores the database to the moment when the snapshot was created.

Delete database snapshot

The syntax is the same as deleting a database

DROP DATABASE Snapshot_Test_shot

Results:

The above is how to achieve data recovery in the SQL shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.