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 desc

3、查看还原记录

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

官方恢复文档：

https://docs.microsoft.com/zh-cn/sql/t-sql/statements/restore-statements-transact-sql?view=sql-server-2017