SQL数据库备份

最新推荐文章于 2025-01-02 11:33:54 发布

最新推荐文章于 2025-01-02 11:33:54 发布 · 171 阅读

文章标签：

#SQL #C #C++ #C# #Go

sql 专栏收录该内容

2 篇文章

订阅专栏

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


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