SQL数据库备份-优快云博客

本文详细介绍了使用SQL Server进行数据库备份和还原的方法，包括如何在硬盘上创建备份设备、执行完全备份、差异备份及事务日志备份等操作，并演示了从备份文件中恢复数据库的具体步骤。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >


use master
go
--在硬盘上创建备份设备
exec sp_addumpdevice 'disk',mybackupfile,
'c:\Mybackupfile.bak'
--删除备份设备
exec sp_dropdevice mybackupfile

--备份数据库数据到临时设备
backup database northwind
to disk = 'c:\northwind.bak'

--完全备份数据库到备份设备
backup database northwind
to mybackupfile

--执行差异备份
backup database northwind
to mybackupfile
with differential

-- 备份日志文件到备份设备
exec sp_addumpdevice 'disk',mybackupLog,
'c:\MybackupLog.bak'
backup log northwind
to mybackupLog
-- 备份日志文件到临时备份设备
backup log northwind
to disk = 'c:\mybackupLog.bak'
--从备份设备还原数据库
restore database northwind
from mybackupfile

--从备份文件还原数据库
backup database northwind
to disk = 'c:\northwindback.bak'
backup database northwind
to disk = 'c:\northwindback.bak'
with differential
restore database northwind
from disk = 'c:\northwindback.bak'
with file = 1,recovery
restore database northwind
from disk = 'c:\northwindback.bak'
with file = 2,recovery
--事务日志还原
restore database northwind
from disk = 'c:\northwindback.bak'
with file = 1,norecovery
restore log northwind
from disk = 'c:\northwindLog.bak'
with file = 1,recovery
--时点还原
backup database northwind
to disk = 'c:\northwind.bak'
backup log northwind
to disk = 'c:\mybackupLog.bak'
restore database northwind
from disk = 'c:\northwind.bak'
with file = 1,norecovery
restore log northwind
from disk = 'c:\mybackupLog.bak'
with file = 1 ,stopat = '2006-4-7 9:54:00' ,recovery