首页 > 代码库 > SQL Server 备份还原

SQL Server 备份还原

2024-09-03 15:39:45   220人阅读

SQL Server支持三种备份方式

  • 完全备份；
  • 差异备份
  • 事务日志备份

一般备份方式为，完全备份/每周，差异备份/每天，事务日志备份/按分钟计，这样可确保备份的高效性和可恢复性。

 1. 完全备份

  • 备份脚本
BACKUP DATABASE PCT TO DISK =‘D:\Company\Data\SQL Server\Backup\PCT.bak‘;

 

  • 还原备份
RESTORE DATABASE PCTFROM DISK = ‘D:\Company\Data\SQL Server\Backup\PCT.bak‘WITH RECOVERY;

2. 差异备份

  • 备份脚本
BACKUP DATABASE PCT TO DISK = ‘D:\Company\Data\SQL Server\Backup\PCT_Differential.bak‘ WITH DIFFERENTIAL;GO
  • 还原备份

在SQL Server中还原差异备份，需要先还原在差异备份时间点之前的一个完整备份，在还原完整备份时要加上NORECOVERY参数，SQL语句如下：

RESTORE DATABASE PCT FROM  DISK = ‘D:\Company\Data\SQL Server\Backup\PCT.bak‘WITH  FILE = 1,  NOUNLOAD,  STATS = 10, NORECOVERYGO

　　还原差异备份时，如果只有一个差异备份文件需要还原，无需使用NORECOVERY参数，SQL语句如下：

RESTORE DATABASE PCT FROM  DISK = ‘D:\Company\Data\SQL Server\Backup\PCT_Differential.bak‘WITH  FILE = 1,  NOUNLOAD,  STATS = 10GO

　　如果有多个差异备份文件需要还原，除了最后一个差异备份文件，其他都需要加上NORECOVERY参数

事务日志备份

  • 事务日志备份
BACKUP LOG PCT TO DISK =‘D:\Company\Data\SQL Server\Backup\PCT_Log.bak‘;
  • 尾日志备份
BACKUP LOG PCT TO DISK =‘D:\Company\Data\SQL Server\Backup\PCT_TailLog.bak‘ WITH NORECOVERY;
  • 完整恢复到失败点
USE master;BACKUP DATABASE PCTTO DISK =‘D:\Company\Data\SQL Server\Backup\PCT.bak‘WITH INIT;GO-- Perform a transaction log backup of the Test databaseBACKUP Log PCTTO DISK =‘D:\Company\Data\SQL Server\Backup\PCT_log.bak‘WITH INIT;GO-- ....<FAILURE OCCURS HERE>....-- Back up the tail of the log to prepare for restoreBACKUP Log PCTTO DISK =‘D:\Company\Data\SQL Server\Backup\PCT_taillog.bak‘WITH NORECOVERY, INIT;GO-- Restore the full backupRESTORE DATABASE PCTFROM DISK = ‘D:\Company\Data\SQL Server\Backup\PCT.bak‘WITH NORECOVERY;-- Apply the transaction log backupRESTORE LOG PCTFROM DISK = ‘D:\Company\Data\SQL Server\Backup\PCT_log.bak‘WITH NORECOVERY;-- Apply the tail log backupRESTORE LOG PCTFROM DISK = ‘D:\Company\Data\SQL Server\Backup\PCT_taillog.bak‘WITH NORECOVERY;-- Recover the databaseRESTORE DATABASE PCTWITH RECOVERY;GO
  • 恢复到上次正确日志备份的时间点
-- FULL BACKUP at 2:00 AMUSE master ;BACKUP DATABASE PCTTO DISK = ‘D:\Company\Data\SQL Server\Backup\PCT.bak‘WITH INIT ;GO-- LOG BACKUP 1 at 2:15 AMUSE master ;BACKUP LOG PCTTO DISK = ‘D:\Company\Data\SQL Server\Backup\PCT_log.bak‘WITH INIT ;GO-- LOG BACKUP 2 at 2:30 AMUSE master ;BACKUP LOG PCTTO DISK = ‘D:\Company\Data\SQL Server\Backup\PCT_log2.bak‘WITH INIT ;GO--有时候很遗憾，不能进行完整恢复：例如由于灾难当前事务日志不可用。这样的话，我们会需要还原数据库导最近日志备份的末尾--如果灾难性故障发生在上午2:30，我们将需要恢复数据库到上午2:30的尾日志备份状态。--因为不能进行尾日志备份，我们只能恢复到特定点，我们要使用STOPAT选项。--RESTORE Full backupRESTORE DATABASE PCTFROM DISK = ‘D:\Company\Data\SQL Server\Backup\PCT.bak‘WITH NORECOVERY;--RESTORE Log file 1RESTORE LOG PCTFROM DISK = ‘D:\Company\Data\SQL Server\Backup\PCT_log.bak‘WITH NORECOVERY, STOPAT = ‘Jan 01, 2020 12:00 AM‘;--RESTORE Log file 2RESTORE LOG PCTFROM DISK = ‘D:\Company\Data\SQL Server\Backup\PCT_Log2.bak‘WITH NORECOVERY, STOPAT = ‘Jan 01, 2020 12:00 AM‘;--Recover the databaseRESTORE DATABASE PCTWITH RECOVERY;GO

 

SQL Server 备份还原


联系
我们