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