手动备份请参考:http://wenku.baidu.com/view/d57f283f453610661ed9f46a.html?from=search
准备:
1.完整备份数据库
BACKUP DATABASE CollectionDB TO DISK = 'E:\20150609_75\bak\Test_bak_full.bak' WITH INIT
2.差异备份数据库
BACKUP DATABASE CollectionDB TO DISK = 'E:\20150609_75\bak\Test_bak_diff.bak' WITH INIT, DIFFERENTIAL --加上DIFFERENTIAL代表差异备份
3.备份日志文件
BACKUP LOG CollectionDB TO DISK='c:\db_log.bak' WITH INIT
3.备份事务日志,文件名中包含当前时间,适合定时备份
DECLARE @strbackup NVARCHAR(100)
--改为日期加时间的
SET @strbackup = 'E:\20150609_75\bak\Test_bak_log_'
+ REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), '-', ''), ' ', ''), ':', '') + '.bak'
BACKUP LOG Test_Bak TO DISK = @strbackup WITH INIT;
GO
一、还原完整+差异备份
1.备份完整备份文件
USE master;
RESTORE DATABASE CollectionDB
FROM DISK='D:\Data\MSSQLSERVER\MSSQL10.MSSQLSERVER\MSSQL\Backup\CollectionDB_backup_2016_07_31_040002_0771951.bak'
WITH STATS = 10, REPLACE,NORECOVERY
GO
2.备份差异备份文件
USE master;
RESTORE DATABASE CollectionDB
FROM DISK='D:\Data\MSSQLSERVER\MSSQL10.MSSQLSERVER\MSSQL\Backup\CollectionDB_backup_2016_08_04_040001_5320399.bak'
WITH STATS = 10, RECOVERY
GO
二、还原完整备份+差异备份+日志文件
1.还原完整备份文件
USE master;
RESTORE DATABASE CollectionDB
FROM DISK='D:\Data\MSSQLSERVER\MSSQL10.MSSQLSERVER\MSSQL\Backup\CollectionDB_backup_2016_07_31_040002_0771951.bak'
WITH STATS = 10, REPLACE,NORECOVERY
GO
2.还原差异备份文件
(只有一个差异备份的情况,当还原多个差异备份时,通常前面的都用WITH NORECOVERY,还原最后一个差异备份才能用WITH RECOVERY)
USE master;
RESTORE DATABASE CollectionDB
FROM DISK='D:\Data\MSSQLSERVER\MSSQL10.MSSQLSERVER\MSSQL\Backup\CollectionDB_backup_2016_08_04_040001_5320399.bak'
WITH STATS = 10, NORECOVERY
GO
3.还原日志备份
RESTORE LOG CollectionDB
FROM DISK = N'C:\db_log.bak'
WITH STATS = 10, RECOVERY ,STOPAT='2016-08-04 14:30:00'
GO
三、还原完整备份+日志文件
1.还原完整备份文件
USE master;
RESTORE DATABASE CollectionDB
FROM DISK='D:\Data\MSSQLSERVER\MSSQL10.MSSQLSERVER\MSSQL\Backup\CollectionDB_backup_2016_07_31_040002_0771951.bak'
WITH STATS = 10, REPLACE,NORECOVERY
GO
2.还原日志备份
RESTORE LOG CollectionDB
FROM DISK = N'C:\db_log.bak'
WITH STATS = 10, RECOVERY ,STOPAT='2016-08-04 14:30:00'
GO
参数说明:
WITH MOVE TO:重新指定文件的路径,WITH MOVE TO数量取决于数据库文件数量
STATS = 10:没完成10%显示一条记录
REPLACE:覆盖现有数据库
NORECOVERY:不对数据库进行任何操作,不回滚未提交的事务