question

PythonLearner avatar image
0 Votes"
PythonLearner asked RobSewell commented

Script to restore SQL Server database with multiple BAK files

I am trying to dynamically restore a database with multiple BAK files (about 9+) that are located in one folder on a daily basis.
So, updating one database with multiple BAK files..

What should be the best approach?
How should I write a cursor , stored procedure or powershell script?
Basically, all BAK files would be located in one folder, and the task is grab each file and restore to one database.

I am thinking SQL Server Agent will do the job (like running Powershell or run a stored procedure), but I don't know how to express the logic.
Any guidance would be appreciated.

Thanks


sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
1 Vote"
OlafHelper-2800 answered

So you have one full and several differential backup?
You have to restore the full backup and the latest differential backup in order. Is there a timestamp in the backup file name?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

PythonLearner avatar image
0 Votes"
PythonLearner answered PythonLearner edited

@OlafHelper-2800


Thanks for help.

Those log files are only differential backup for each hour.
I will do the full back up manually before this task takes place.

I am bringing in only log files files that are stamped as of today.
But, task is grabbing all BAK files that are located in the same folder and process them by the file number (for example 04 --> 12).
92169-4log.jpg



4log.jpg (63.5 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

Powershell certainly sounds like a better option, since you need to loop of files in the file system. Not fun to do from SQL .

You should check out http://dbatools.io. This free repository of SQL Powershell scripts may very well have something that fits your need.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

PythonLearner avatar image
0 Votes"
PythonLearner answered PythonLearner edited

I just realized that I was not clear about difference between bak, dif and trn files.
So, I only have bak format for all transaction files.

Please let me know bottom approach would be a good solution.
https://www.mssqltips.com/sqlservertip/1584/auto-generate-sql-server-restore-script-from-backup-files-in-a-directory/

I will probably use one section out of three (bak, dif and trn) sections.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

PythonLearner avatar image
0 Votes"
PythonLearner answered RobSewell commented

@ErlandSommarskog

Thanks for your help.

The closest code that I found was from the site was:
https://docs.dbatools.io/#Restore-DbaDatabase
Example 9:

 PS C:\> $File = Get-ChildItem c:\backups, \\server1\backups -recurse
 PS C:\> $File | Restore-DbaDatabase -SqlInstance Server1\Instance -UseDestinationDefaultDirectories

How do I express to go thru each files sequentially with the file name? For example ("..04.bak --> "12.bak")


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Alas, I am not very good at PowerShell myself, and nor am I well acquainted with there actually is in dbatools.io.

For this particular thing it may be easier to use the T-SQL you found (but I am not going to look into how to modify it for your needs), but you can also see it as an investment for the future. T-SQL is a good hammer for nailing queries, but it should not be used for everything. As a DBA, you should have some other language in your belt, and Powershell certainly is a good choice if you don't know something else.

Me? I'm more a developer than a DBA. And I know Perl well, so I would use that as long as it is only for my own use. (But if I would set something up that other people would maintain, I would struggle with Powershell or Python, which are more widely used.)

0 Votes 0 ·

Hi, restore-dbadatabase, when pointed at a directory still go through all the files and use the LSN to restore to to the latest available for each database (if you do not specify a stop at time)

Rob Sewell
Co-Author dbatools in a month of lunches

0 Votes 0 ·
Criszhan-msft avatar image
0 Votes"
Criszhan-msft answered Criszhan-msft edited

Hi @JustinDoh-1889,

SQL Server has no mandatory requirements for the extension of the backup file. Usually people will use bak to indicate a full database backup file, diff to indicate a differential backup, and trn for the transaction log backup. Multiple extensions help to easily determine the type of backup. If you just use one file extension to represent various backup types, it is not so convenient to distinguish them.

Writing Powershell scripts you may need to consult an experienced person in this field.

If you are considering keeping the data synchronization between the databases on the two servers, you can consider some HA DR technologies, such as Always On availability group, log shipping, of which log shipping is a simpler way, but it cannot achieve complete data synchronization and there will be a delay. But using the above technologies, the secondary server(receiving updates) is only allowed to be readable at most, and cannot be updated.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

PythonLearner avatar image
0 Votes"
PythonLearner answered Criszhan-msft commented

@Criszhan-msft

Thanks for feedback.

I am leaning toward using T-SQL as Powershell is so new to me.
Do you have a good example of code that I could use?

Please let me know T-SQL code listed on the bottom ULR would be a good solution.
https://www.mssqltips.com/sqlservertip/1584/auto-generate-sql-server-restore-script-from-backup-files-in-a-directory/

92693-image.png
92694-image.png
92712-image.png



image.png (36.6 KiB)
image.png (35.5 KiB)
image.png (31.5 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi,

I don't have a good and tested T-SQL script that restores the database from backup files in a directory.

For the code published in the above article or any useful scripts found on the Internet, you may need to test in your test environment to verify whether it is applicable in your environment and modify it according to your specific needs.

Note that the xp_cmdshell is a very powerful feature and disabled by default. xp_cmdshell can be enabled by executing sp_configure.

1 Vote 1 ·