Restoring multiple database in SQL Server from .bak files
So I’ve been doing a lot of Green field development on ASP. Net core on the past year and I was on charge of two projects involving many developers working together with high posibilites of losing a developer to another project and having to welcome a new developer into the project(don’t ask me why). So I needed to make sure that each new developer had clears and easy instructions to set up everything and be able to write their first line of code in 2 hours max because I figured out that this was a determining factor for the attitude of the developers later on “Happy set up, Happy coding”.
So in short it came down to a few steps:
- Install dependencies such as (git, Dotnet SDK , visual studio code, Nodejs , etc)
- Replace template configuration files with your local development settings(connection string etc.)
- Restore packages
- Run Database migrations and Seeding (we would normally use code first)
- Run the app and explore!
I really loved that and I know it make the developers happy when they saw that it was breeze to actually get the project running so that they could start exploring by them self.
So what does that have to do with restoring multiple databases from .bak files? Well not always you get to live the dream of doing green field development, in fact most of the times you have to deal with old legacy code and in the dotnet in the enterprise world DBAs are scared of code first approach, so instead you use database first.
Now considering you do not go for an approach of centralized database (where all developers will be connecting to the same database, Madness!) and you decide “you know what, is better if we isolate each developer, let’s make them each have their own database locally and we figure out way to sync if needed” Well if you are not using migrations how you do that? well…
- You could generate T-SQL scripts that includes the database schema and some starting data and make each developer run the scripts while they are getting set up, if you add something like DbUp you end up with something very similar to migrations but a little harder to maintain.
- And the infamous .bak files being passed around from one place to another restoring those things, SQL server version incompatibility problems, “ops that was not the right .bak file”. Yep that’s what I remember from my good old days doing .Net enterprise development and such was my case not so long ago when while getting setting up in a new project I received a link pointing to 2.2Gb of .bak files that I needed to restore on my local SQL Server Instance.
Sure it it would have been just one .bak file It would not have been shocking and I would not be here writing this but after unzipping:
It was seven .bak files. Yes Seven! But not only that I though: “Man if I’m going to have to go trough this for each project. I’m going to go nuts!” My lazy mode had been triggered.
So ok, what choice do we got? Do we star annoying the developer in charge? the DBAs? that they should instead do SQL Script files? They probably already have restore plans that generate these files so that would be a long shoot. Don’t get me wrong I might still try it later on but I though What if I can do something about it? How do I make restoring multiple .back files painless not only for me but for other developers that might join this project at some point.
Well after some google search there are some T-SQL that do this, but I just was not into it. What I’m determined to have is a command that I can run something like: “restore-databases”
I found a Power Shell utility that looked very promising dbatools, I’m no powershell expert but it looked pretty straightforward. Ok, let’s fire up power shell
Note: Always run power shell with administrative privilegies
A quick search in the functions page:
Ran into my first trouble while trying to run the command
Just followed the instructions to run “Import-Module dbatools” but I ran into another problem
A simple google search told me how to fix it: and then I was able to explore the documentation of the command
So after reading a few basic about how to do a Power Shell Script I though: “ok, Time to make my first Power Shell script” Fire up Power Shell ISE
And here it is so you can copy & paste:
That’s it, now Save the script somewhere and run it from power shell(You can also run it directly from Power Shell ISE) and it start doing it’s magic, and while it was running I decided to look what was going on on SQL Server:
And once it was done voila!
Now, it might not sound that Impressive to you, but think about it, we modify the Power Shell script to accept parameters such as:
- SQL Server Instance Name
- Credentials if you want to use SQL login
- Directory where your .bak files are
Then we place it somewhere in the repository and add instructions on the prerequisites that you must install dbatools which is jut a simple command.
Now Developers can run this command after having the .bak files somewhere, you can even try and have a shared resource on the network so that people won’t have to download the files and just point to them!
- .Bak files must always have the same name by project otherwise you would have to update the script before running it :(
- When running Restore-DbaDatabase the path must accesible by your local Sql Server Service account otherwise you will run into errors
You still not buying it? Well…I had fun anyways doing it, and I did my first Power Shell script. I’m looking forward to hear if this works for you, how do you handle such scenarios? can you think of how we can improve this? Leave your comments below