数据库备份与还原常用,但通常是通过数据库管理器界面配置即可,用不着以下列出的sql命令。但是如果你的应用系统中存在相关的数据库还原备份的功能,就需要掌握这些基本的SQL语法了。
此外,在实际的生产环境,需要一些简单的自动化脚本实现数据库的备份,可能还需要了解数据库提供的数据库还原相关的shell命令了。
-- 备份整个数据库
BACKUP DATABASE NRCHRDBDev TO DISK = 'F:\NRCHRDBDev.bak' WITH FORMAT;
-- 实现对数据库与日志的单独备份(要实现日志备份,需将数据库设置完整恢复模式)
ALTER DATABASE AdventureWorks2012 SET RECOVERY FULL; --设置完整恢复模式
EXEC sp_addumpdevice 'disk', 'AdvWorksData','Z:\SQLServerBackups\AdvWorksData.bak';
EXEC sp_addumpdevice 'disk', 'AdvWorksLog', 'Z:\SQLServerBackups\AdvWorksLog.bak';
BACKUP DATABASE AdventureWorks2012 TO AdvWorksData;
BACKUP LOG AdventureWorks2012 TO AdvWorksLog;
-- 还原数据库
RESTORE DATABASE AdventureWorks2012 FROM AdventureWorks2012Backups;
-- 还原完整数据库备份和差异数据库备份
RESTORE DATABASE AdventureWorks2012
FROM DISK = 'Z:\SQLServerBackups\AdventureWorks2012.bak'
WITH FILE = 6, -- 还原备份上的第6个备份集
STOPAT = 'Apr 15, 2020 12:00 AM',-- 恢复到指定时间
NORECOVERY;
RESTORE DATABASE AdventureWorks2012
FROM DISK = 'Z:\SQLServerBackups\AdventureWorks2012.bak'
WITH FILE = 9, -- 还原差异备份上的第9个备份集
RECOVERY;
-- 还原数据并移动文件
RESTORE DATABASE AdventureWorks2012
FROM AdventureWorksBackups
WITH NORECOVERY,
MOVE 'AdventureWorks2012_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\NewAdvWorks.mdf',
MOVE 'AdventureWorks2012_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\NewAdvWorks.ldf';
RESTORE LOG AdventureWorks2012
FROM AdventureWorksBackups
WITH RECOVERY;