Automate SQL Server install and restore DB

Question

I’ve been tasked to automate our sql server express installation and with that, create a database from a .bak file. I found a way to automate the install using a Configuration.ini file which works great. The part I’m having trouble with is restoring a database as part of (or after) the process.

Does anyone know if this is even possible? I know 3rd party tools like Red Gate can probably do it but they are rather expensive and the company I work for does not have a big budget for development tools.

Answer 1

A great choice is dbatools. They have a command (among many, many others) that can help you with this:

Restore-DbaDatabase

An example,

Restore-DbaDatabase -SqlServer server1\instance1 -Path \\server2\backups\$

It can do a lot more complicated stuff, too! Check out the documentation for more info.