.::: Backup Database Full & Transaction & Restore Database SQL Server :::.

correlation with https://teguhth.blogspot.com/2023/06/backup-database-full-transaction.html

 

A. check status backup and restore

SELECT command, percent_complete,total_elapsed_time, estimated_completion_time, start_time
  FROM sys.dm_exec_requests
  WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')
  

B. Backup Database  
1. Backup Database Full

BACKUP DATABASE [AdventureWorks2019] TO  DISK = N'C:\tmp\backupfull.bak' WITH NOFORMAT, NOINIT,  
NAME = N'AdventureWorks2019-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
 

2. backup transaction
BACKUP LOG [AdventureWorks2019] TO  DISK = N'C:\tmp\backuptran.trn' WITH NOFORMAT, NOINIT,  
NAME = N'AdventureWorks2019-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

 

C. Restore Database
1. restore

 
USE [master]
RESTORE DATABASE [RestoreDB] FROM  DISK = N'C:\tmp\backupfull.bak' WITH  FILE = 1,  MOVE N'AdventureWorks2017' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\RestoreDB.mdf',  MOVE N'AdventureWorks2017_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\RestoreDB_log.ldf',  NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 5
RESTORE LOG [RestoreDB] FROM  DISK = N'C:\tmp\backuptran.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [RestoreDB] FROM  DISK = N'C:\tmp\backuptran.trn' WITH  FILE = 2,  NOUNLOAD,  STATS = 5

GO
 

 

D. Capture after restore