RDS For SQL Server备份恢复到本地
RDS备份恢复到本地的方法
1、下载备份
注意，第一个要下载还原的必须是全备，增量备份需要在全备还原的基础上才能还原的。
2、解压备份
3、查看数据库文件的物理路径
restore filelistonly from disk='解压后bak文件的绝对路径'4、全量还原和增量还原
--全量还原
restore database 数据库名 from disk='解压后bak文件的绝对路径'
with move '逻辑数据文件名' to '数据库文件的绝对路径\逻辑数据文件名.mdf',
move '逻辑日志文件名' to '数据库文件的绝对路径\逻辑日志文件名.ldf',
norecovery --如果不需要增量还原，这个选项可以去掉
--增量还原
restore database jinpengtest from disk='解压后bak文件的绝对路径'
with recovery补充：
1、备份还原进度查询
select
session_id,
start_time,
dateadd(mi ,estimated_completion_time/60000,getdate ()) as ETC,
convert(varchar(5), cast((percent_complete ) as decimal ( 4,1 )))+'%' as [completed],
command,
q.[text] as TSQL
from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(r.sql_handle) as q
where command in ('BACKUP DATABASE','BACKUP LOG','RESTORE DATABASE','RESTORE LOG')2、查看备份记录
select distinct s.first_lsn,
s.last_lsn,
s.database_backup_lsn,
s.backup_finish_date,
s.type,
y.physical_device_name
from msdb..backupset as s inner join
msdb..backupfile as f on f.backup_set_id=s.backup_set_id inner join
msdb..backupmediaset as m on s.media_set_id=m.media_set_id inner join
msdb..backupmediafamily as y on m.media_set_id=y.media_set_id
where s.database_name='数据库名'
order by s.backup_finish_date desc3、查看还原记录
select distinct bus.server_name as 'server',rh.restore_date,bus.database_name as 'database',
rh.[restore_type],
bus.first_lsn,
bus.last_lsn,
database_backup_lsn,
differential_base_lsn
FROM msdb.dbo.backupset bus
INNER JOIN msdb.dbo.restorehistory rh ON rh.backup_set_id=bus.backup_set_id
and bus.database_name='数据库名'
and restore_date>'2018-07-01' --时间最好限制下
order by restore_date官方恢复文档：