【MVS】SQLSERVER如何完整备份恢复

SQLSERVER如何完整备份恢复

示例完整数据库备份、结尾日志备份。随后示例还原完整数据库备份和日志备份、还原结尾日志备份，最后恢复数据库。

USE master;

GO

ALTER DATABASE AdventureWorks2022 SET RECOVERY FULL;

GO

-- Back up the AdventureWorks2022 database to new media set (backup set 1).

BACKUP DATABASE AdventureWorks2022

  TO DISK = 'Z:\SQLServerBackups\AdventureWorks2022FullRM.bak'

  WITH FORMAT;

### 需测试；

--Create a differential database backup.  差异备份；

BACKUP DATABASE AdventureWorks2022   

TO DISK = 'Z:\SQLServerBackups\AdventureWorks2022.bak'  

   WITH DIFFERENTIAL;  

GO  

 

GO

--Create a routine log backup (backup set 2). 备份事务日志

BACKUP LOG AdventureWorks2022 TO DISK = 'Z:\SQLServerBackups\AdventureWorks2022FullRM.bak';

GO

 

USE master;  

--Create tail-log backup. 备份结尾日志；

BACKUP LOG AdventureWorks2022   

TO DISK = 'Z:\SQLServerBackups\AdventureWorksFullRM.bak'    

   WITH NORECOVERY, NO_TRUNCATE;   

 

GO  

--Restore the full database backup (from backup set 1).  还原数据库备份或差异备份

RESTORE DATABASE AdventureWorks2022   

  FROM DISK = 'Z:\SQLServerBackups\AdventureWorksFullRM.bak'   

  WITH FILE=1,   

    NORECOVERY;  

  

--Restore the regular log backup (from backup set 2).  还原事务日志

RESTORE LOG AdventureWorks2022   

  FROM DISK = 'Z:\SQLServerBackups\AdventureWorksFullRM.bak'   

  WITH FILE=2,   

    NORECOVERY;  

  

--Restore the tail-log backup (from backup set 3).  还原结尾日志

RESTORE LOG AdventureWorks2022   

  FROM DISK = 'Z:\SQLServerBackups\AdventureWorksFullRM.bak'  

  WITH FILE=3,   

    NORECOVERY;  

GO  

--recover the database:  

RESTORE DATABASE AdventureWorks2022 WITH RECOVERY;  

GO