sqlserver备份还原丢失dbo_sqlserver数据库备份与还原语句

sqlserver数据库备份与还原语句

--创建备份设备

USE [master]

GO

EXEC master.dbo.sp_addumpdevice @devtype = N'disk', @logicalname = N'beifen', @physicalname = N'D:\beifen.bak'

GO

--完整备份到备份设备中

BACKUP DATABASE [student] TO [beifen] WITH NOFORMAT, NOINIT, NAME = N'student-完整 数据库 备份', SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

--还原完整备份

RESTORE DATABASE [student] FROM [beifen] WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10

GO

--差异备份

BACKUP DATABASE [student] TO [beifen] WITH DIFFERENTIAL , NOFORMAT, NOINIT, NAME = N'student-差异 数据库 备份', SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

--事务日志备份

BACKUP LOG [student] TO [beifen] WITH NOFORMAT, NOINIT, NAME = N'student-事务日志 备份', SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

--还原到最近状态

RESTORE DATABASE [student] FROM [beifen] WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10

GO

RESTORE DATABASE [student] FROM [beifen] WITH FILE = 2, NORECOVERY, NOUNLOAD, STATS = 10

GO

RESTORE LOG [student] FROM [beifen] WITH FILE = 3, NOUNLOAD, STATS = 10

GO