备份命令行
--完整备份
Backup Database db_database To disk='D:\Backup\db_database_Full.bak'
--差异备份
Backup Database db_database To disk='D:\Backup\db_database_Diff.bak' With Differential
--日志备份,默认截断日志
Backup Log db_database To disk='G:\Backup\db_database_Log.bak'
--日志备份,不截断日志
Backup Log db_database To disk='G:\Backup\db_database_Log.bak' With No_Truncate
--截断日志不保留
Backup Log db_database With No_Log
--或者
Backup Log db_database With Truncate_Only
--截断之后日志文件不会变小
--有必要可以进行收缩
--文件备份
Exec Sp_Helpdb db_database --查看数据文件
Backup Database db_database
File='db_database' --数据文件的逻辑名
To disk='G:\Backup\db_database_File.bak'
--文件组备份
Exec Sp_Helpdb db_database --查看数据文件
Backup Database db_database
FileGroup='Primary' --数据文件的逻辑名
To disk='G:\Backup\db_database_FileGroup.bak'
With init
--分割备份到多个目标
--恢复的时候不允许丢失任何一个目标
Backup Database db_database
To disk='G:\Backup\db_database_Full_1.bak'
,disk='G:\Backup\db_database_Full_2.bak'
--镜像备份
--每个目标都是相同的
Backup Database db_database
To disk='G:\Backup\db_database_Mirror_1.bak'
Mirror
To disk='G:\Backup\db_database_Mirror_2.bak'
With Format --第一次做镜像备份的时候格式化目标
--镜像备份到本地和远程
Backup Database db_database
To disk='G:\Backup\db_database_Mirror_1.bak'
Mirror
To disk='\\192.168.1.200\Backup\db_database_Mirror_2.bak'
With Format
--每天生成一个备份文件
Declare @Path Nvarchar(2000)
Set @Path ='G:\Backup\db_database_Full_'
+Convert(Nvarchar,Getdate(),112)+'.bak'
Backup Database db_database
To disk=@Path
--从NoRecovery或者
--Standby模式恢复数据库为可用
Restore Database db_database_Bak
With Recovery
--查看目标备份中的备份集
Restore HeaderOnly
From Disk ='G:\Backup\db_database_Full.bak'
--查看目标备份的第一个备份集的信息
Restore FileListOnly
From Disk ='G:\Backup\db_database_Full_2.bak'
With File=1
--查看目标备份的卷标
Restore LabelOnly
From Disk ='G:\Backup\db_database_Full_2.bak'
--备份设置密码保护备份
Backup Database db_database
To disk='G:\Backup\db_database_Full.bak'
With Password = '123',init
Restore Database db_database
From disk='G:\Backup\db_database_Full.bak'
With Password = '123'
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26435490/viewspace-1436638/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26435490/viewspace-1436638/