0

I am trying to come up with a script to dynamically do 'Restore Database' in SQL Server 2019 with multiple BAK files that are located in one folder on a daily basis.

I will do the manual full backup first, and this task will be scheduled to run after on a daily basis.

The plan is go thru each BAK files (located in the same folder) sequentially (for example here: 04 --> 12).

There will be another Python script which will wipe out old files, so I am only focusing on execution of Restore database with BAK files that are located in this folder.

What should be the best approach?

I have attached a screenshot that has all BAK files.

enter image description here

I have some T-SQL code here (that I copied from other thread), but I am not sure how to loop over each BAK file sequentially.

And I am not sure whether I have to deal with mdf and ldf files as listed here.

RESTORE DATABASE [Test] FROM  DISK ='\\Folder\LOG...04.bak'
WITH
--what ever options... but likely a file move
MOVE 'data_file_1' TO 'E:\somefolder\data.mdf',
MOVE 'db_log' TO 'E:\somefolder\log.ldf',
REPLACE, --overwrites the database
RECOVERY --sets the DB to READ/WRITE. Use NORECOVERY if you need to restore 
logs / differentials
GO

RESTORE LOG Test
   FROM '\\Folder\Log...04.bak' --assuming this is a log
   WITH FILE = 1,  --this is the first log
   WITH NORECOVERY;  --keep in norecovery to restore other logs...
GO
Improve this question
5
  • Nope, very few do "best" answers because that depends on too many variables. But this seems to be highly similar goal to your previous question. You loop over the files that match a pattern in a directory and restore them. Seems you have an assumption about the type of backup each file represents. Commented Apr 28, 2021 at 19:08
  • @vonPryz It is only for one database. They are all transaction logs. Commented Apr 28, 2021 at 19:58
  • @SMor, What are variables in this case? Is it better doing it in Python? I thought T-SQL might do. Commented Apr 28, 2021 at 20:00
  • 1
    Please don't save transaction logs as .bak files. It is customary to use .bak for full backup, .dif for differential and .trn for logs. That way it is trivial to tell files apart. Commented Apr 28, 2021 at 21:13
  • @vonPryz I think the issue for me was I did not know the difference between bak, trn and dif file formats until you pointed out. Thanks for awakening. Please check bottom URL, and let me know if this is a good approach or not. mssqltips.com/sqlservertip/1584/… Since I only have BAK format, I will only use part of the script. Commented Apr 28, 2021 at 22:54
Related questions
7
Restore latest backup from bak file with script
0
SQL Server 2005 - How to restore multiple backups (.bak)
4
Seeking to restore from multiple bak files to a test SQL database
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.