Automate Restore of Databae Backups - Full and Log - Backup pro 10

jigar_patel

I'm received weekly full backup and hourly log backup files. is there a easy way to restore hourly log backups and weekly full backups. or it has to be scheduled separately? I'm restoring backups to SQL Server 2022 

petey2

There will need to be 2 jobs.

For the hourly log backups, look into the RESTORE LOG command in SQL Backup.  It allows you to specify a search pattern for the transaction log backup files, which it will then restore automatically for you in sequential order, then move the files to a different folder so that it will not be restored again. 

So it's one T-SQL job that may look something like this:

EXEC master..sqlbackup '-sql "RESTORE LOG mydb FROM DISK = [\\backupfiles\mydb\logs\*.sqb] WITH STANDBY = [g:\dbundo\mydb.und], MOVETO = [\\backupfiles\mydb\logs\restored\]"'

For the weekly full backup, look into using the LATEST_FULL option.  This instructs SQL Backup to look for and restore the latest full backup file(s) in a given folder.  It's a T-SQL job that may look something like this:

EXEC master..sqlbackup '-sql "RESTORE DATABASE mydb FROM DISK = [\\backupfiles\mydb\full\*.sqb] LATEST_FULL WITH STANDBY = [g:\dbundo\mydb.und]"'