Click above SQL database development, pay attention to get SQL video tutorial

SQL column

Summary of SQL basic knowledge

SQL advanced knowledge summary

The data recovery feature we are going to introduce today is called snapshot.

What is a snapshot \

Database snapshot is a new feature in SQL Server 2005. It is defined in MSDN as:

A database snapshot is a read-only static view of a database, called the “source database.” When created, each database snapshot is transactionally consistent with the source database. When a database snapshot is created, the source database usually has open transactions. Before the snapshot is available, open transactions are rolled back to make the database snapshot transaction consistent.

Clients can query database snapshots, which can be useful for writing reports based on the data at the time the snapshot was created. Also, if the source database becomes corrupted later, it can be restored to the state it was in when the snapshot was created.

Let’s practice creating and using database snapshots.

Creating a sample database

Use MASTER;
GO
CREATE DATABASE [Snapshot_Test] ON  PRIMARY
( NAME = N'Snapshot_Test', -- Database name FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\Snapshot_Test.mdf', -- Data file location and data file name SIZE =3072KB, -- Initial capacity MAXSIZE = UNLIMITED, -- Maximum capacity FILEGROWTH =1024KB: increases the capacity. LOG ON (NAME = N'Snapshot_Test_log',
FILENAME = N'C:\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
Copy the code

(Tip: Slide code left and right)

After creating the DATABASE, we immediately CREATE a snapshot using the CREATE DATABASE statement as follows:

Create 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;
Copy the code

Results:

Now the snapshot should look exactly like our new database. The database snapshot can be viewed through the object browser of SQL Server. We can query the database snapshot by using [snapshot library name]

use Snapshot_Test_shot;
go
SELECT * FROM dbo.test;
Copy the code

Results:

Dbo.test (‘ dbo.test ‘, ‘dbo.test’, ‘dbo.test’, ‘dbo.test’

use snapshot_Test;
go
INSERT INTO TEST (id,name)values(1.'hello 1');
INSERT INTO TEST (id,name)values(2.'hello 2');
INSERT INTO TEST (id,name)values(3.'hello 3');
INSERT INTO TEST (id,name)values(4.'hello 4');
GO
SELECT * FROM dbo.test;
Copy the code

Results:

We query the data in the snapshot database again

use Snapshot_Test_shot;
go
SELECT * FROM dbo.test;
Copy the code

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 database with snapshot \

Restore database \ from database snapshot

use master;
GO
RESTORE DATABASE Snapshot_Test from 
DATABASE_SNAPSHOT = 'Snapshot_Test_shot';
Copy the code

After restore, the data just inserted is gone, we can query.

use Snapshot_Test;
SELECT * FROM dbo.test
Copy the code

Results:

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

Deleting a Database Snapshot

Same syntax as deleting a database \

DROP DATABASE Snapshot_Test_shot
Copy the code

Results:

If no snapshot exists under the database snapshot, it has been deleted.

Application scenarios

The typical application of database snapshot in MSDN is

1) Maintain historical data to generate reports.

2) Unload reports using mirror databases maintained for availability goals.

3) Protect data from management errors.

4) Protect data from user error.

-- End -- background reply keyword:1024, to obtain a carefully organized technical dry goods background reply keywords: into the group, take you into the master like clouds of communication group. Half of our programmers went crazy when we parachuted in an expert... A free database management tool that is even better than Navicat, GitHub's most motivational computer self-learning tutorial just ran a "Hello World" with Huawei Hongmon! SQL learning roadmap, first public this is a technology to learn the public number, welcome to pay attention toCopy the code

Click on the”Read the original“Learn about SQL boot camp