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

This article introduces the relevant knowledge of "the method of SqlServer backup and recovery". Many people will encounter such a dilemma in the operation of actual cases, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

SqlServer backup and recovery

Backup

-create a test database chen20181123

Create database chen20181123

On

(name=chen_data

Filename='D:\ hrtest\ DB\ testdata\ chen20181123_data.mdf'

Size=10MB

Filegrowth=1MB)

Log on

(name=chen_log

Filename='D:\ hrtest\ DB\ testdata\ chen20181123_log.ldf'

Size=1MB

Filegrowth=10MB)

-create test data

Use chen20181123

Create table T1 (id int,a varchar)

Insert into T1 values (1)

Insert into T1 values (2)

Insert into T1 values (3 recordings)

-complete database

BACKUP DATABASE chen20181123

TO DISK='D:\ hrtest\ DB\ testdata\ backup\ chen20181123_full.bak' WITH COMPRESSION

GO

Insert into T1 values (4)

Insert into T1 values (5meme')

-differential backup of database

BACKUP DATABASE chen20181123

TO DISK='D:\ hrtest\ DB\ testdata\ backup\ chen20181123_1.bak' WITH COMPRESSION,DIFFERENTIAL

GO

Insert into T1 values (7)

Insert into T1 values (8)

-Database log backup

BACKUP LOG chen20181123 TO DISK='D:\ hrtest\ DB\ testdata\ backup\ chen20181123_2.trn' WITH COMPRESSION

Insert into T1 values (9)

Insert into T1 values (10)

-19:51

Delete t1

Restore the scen

-complete recovery + differential backup recovery

Restore filelistonly from disk='D:\ hrtest\ DB\ testdata\ backup\ chen20181123_full.bak'

RESTORE DATABASE chen20181123_1

FROM DISK ='D:\ hrtest\ DB\ testdata\ backup\ chen20181123_full.bak'

WITH NORECOVERY

MOVE 'chen_data' TO' D:\ hrtest\ DB\ testdata\ chen20181123_1_data.mdf'

MOVE 'chen_log' TO' D:\ hrtest\ DB\ testdata\ chen20181123_1_log.ldf'

RESTORE DATABASE chen20181123_1 from disk='D:\ hrtest\ DB\ testdata\ backup\ chen20181123_1.bak'

WITH RECOVERY

Select * from chen20181123_1.dbo.t1;-5

-recovery complete + differential backup + log backup recovery

USE MASTER

-drop database chen20181123_2

RESTORE DATABASE chen20181123_2

FROM DISK ='D:\ hrtest\ DB\ testdata\ backup\ chen20181123_full.bak'

WITH NORECOVERY

MOVE 'chen_data' TO' D:\ hrtest\ DB\ testdata\ chen20181123_2_data.mdf'

MOVE 'chen_log' TO' D:\ hrtest\ DB\ testdata\ chen20181123_2_log.ldf'

RESTORE DATABASE chen20181123_2 from disk='D:\ hrtest\ DB\ testdata\ backup\ chen20181123_1.bak'

WITH NORECOVERY

RESTORE LOG chen20181123_2 from disk='D:\ hrtest\ DB\ testdata\ backup\ chen20181123_2.trn'

WITH RECOVERY

Select * from chen20181123_2.dbo.t1;-7

-20:33

BACKUP LOG chen20181123 TO DISK='D:\ hrtest\ DB\ testdata\ backup\ chen20181123_3.trn' WITH COMPRESSION

-recovery complete + differential backup + log backup + new log backup recovery

USE MASTER

RESTORE DATABASE chen20181123_3

FROM DISK ='D:\ hrtest\ DB\ testdata\ backup\ chen20181123_full.bak'

WITH NORECOVERY

MOVE 'chen_data' TO' D:\ hrtest\ DB\ testdata\ chen20181123_3_data.mdf'

MOVE 'chen_log' TO' D:\ hrtest\ DB\ testdata\ chen20181123_3_log.ldf'

RESTORE DATABASE chen20181123_3 from disk='D:\ hrtest\ DB\ testdata\ backup\ chen20181123_1.bak'

WITH NORECOVERY

RESTORE LOG chen20181123_3 from disk='D:\ hrtest\ DB\ testdata\ backup\ chen20181123_2.trn'

WITH NORECOVERY

RESTORE LOG chen20181123_3 from disk='D:\ hrtest\ DB\ testdata\ backup\ chen20181123_3.trn'

WITH RECOVERY

Select * from chen20181123_3.dbo.t1;-0

-full recovery + differential backup + log backup + new log backup + incomplete recovery based on point in time

USE MASTER

-drop database chen20181123_5

RESTORE DATABASE chen20181123_5

FROM DISK ='D:\ hrtest\ DB\ testdata\ backup\ chen20181123_full.bak'

WITH NORECOVERY

MOVE 'chen_data' TO' D:\ hrtest\ DB\ testdata\ chen20181123_5_data.mdf'

MOVE 'chen_log' TO' D:\ hrtest\ DB\ testdata\ chen20181123_5_log.ldf'

RESTORE DATABASE chen20181123_5 from disk='D:\ hrtest\ DB\ testdata\ backup\ chen20181123_1.bak'

WITH NORECOVERY

RESTORE LOG chen20181123_5 from disk='D:\ hrtest\ DB\ testdata\ backup\ chen20181123_2.trn'

WITH NORECOVERY

RESTORE LOG chen20181123_5 from disk='D:\ hrtest\ DB\ testdata\ backup\ chen20181123_3.trn'

WITH RECOVERY,STOPAT='2018-11-23 1914 50 purl 00'

Select * from chen20181123_5.dbo.t1;-9

This is the end of SqlServer backup and recovery methods. Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.