SQL Server 备份还原

SQL Server支持三种备份方式

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

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

1. 完全备份

  • 备份脚本
BACKUP DATABASE PCT TO DISK ='D:\Company\Data\SQL Server\Backup\PCT.bak';
  • 还原备份
RESTORE DATABASE PCT
FROM 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,
NORECOVERY
GO

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

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

  如果有多个差异备份文件需要还原,除了最后一个差异备份文件,其他都需要加上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 PCT
TO DISK ='D:\Company\Data\SQL Server\Backup\PCT.bak'
WITH INIT;
GO -- Perform a transaction log backup of the Test database
BACKUP Log PCT
TO 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 restore
BACKUP Log PCT
TO DISK ='D:\Company\Data\SQL Server\Backup\PCT_taillog.bak'
WITH NORECOVERY, INIT;
GO -- Restore the full backup
RESTORE DATABASE PCT
FROM DISK = 'D:\Company\Data\SQL Server\Backup\PCT.bak'
WITH NORECOVERY; -- Apply the transaction log backup
RESTORE LOG PCT
FROM DISK = 'D:\Company\Data\SQL Server\Backup\PCT_log.bak'
WITH NORECOVERY; -- Apply the tail log backup
RESTORE LOG PCT
FROM DISK = 'D:\Company\Data\SQL Server\Backup\PCT_taillog.bak'
WITH NORECOVERY; -- Recover the database
RESTORE DATABASE PCT
WITH RECOVERY;
GO
  • 恢复到上次正确日志备份的时间点
-- FULL BACKUP at 2:00 AM
USE master ;
BACKUP DATABASE PCT
TO DISK = 'D:\Company\Data\SQL Server\Backup\PCT.bak'
WITH INIT ;
GO -- LOG BACKUP 1 at 2:15 AM
USE master ;
BACKUP LOG PCT
TO DISK = 'D:\Company\Data\SQL Server\Backup\PCT_log.bak'
WITH INIT ;
GO -- LOG BACKUP 2 at 2:30 AM
USE master ;
BACKUP LOG PCT
TO DISK = 'D:\Company\Data\SQL Server\Backup\PCT_log2.bak'
WITH INIT ;
GO --有时候很遗憾,不能进行完整恢复:例如由于灾难当前事务日志不可用。这样的话,我们会需要还原数据库导最近日志备份的末尾
--如果灾难性故障发生在上午2:30,我们将需要恢复数据库到上午2:30的尾日志备份状态。
--因为不能进行尾日志备份,我们只能恢复到特定点,我们要使用STOPAT选项。 --RESTORE Full backup
RESTORE DATABASE PCT
FROM DISK = 'D:\Company\Data\SQL Server\Backup\PCT.bak'
WITH NORECOVERY; --RESTORE Log file 1
RESTORE LOG PCT
FROM DISK = 'D:\Company\Data\SQL Server\Backup\PCT_log.bak'
WITH NORECOVERY, STOPAT = 'Jan 01, 2020 12:00 AM'; --RESTORE Log file 2
RESTORE LOG PCT
FROM DISK = 'D:\Company\Data\SQL Server\Backup\PCT_Log2.bak'
WITH NORECOVERY, STOPAT = 'Jan 01, 2020 12:00 AM'; --Recover the database
RESTORE DATABASE PCT
WITH RECOVERY;
GO
上一篇:SQL Server备份还原数据库中的小把戏


下一篇:shiro中unauthorizedUrl不起作用