Restore a database using Powershell

Standard

/home/sstad/domains/sqlstad.nl/public html/wp content/uploads/2015/05/150504 backup restore

Recently I had a situation where I had to restore several full backups of multiple databases on a SQL Server instance daily. The databases would already be there and a simple restore with replace option would suffice. As I’m a fan of Powershell I wanted to restore a database using Powershell.

As lazy as I am I don’t want to make a script for each of the databases, I just want to supply a few parameters to a script and that the script does the rest for me.

I wanted the script to do the following:

  • Get the latest backup file from a directory
  • Restore the database with replace option
  • Be able to use the script in a SQL Server Agent job

The script would have to have the following parameters to do everything:

  • Directory: Directory to search through to get the latest file.
  • SQL Server instance: Which instance the server needed to restore to
  • Database: The database to restore to
  • Filter: Which extension to search for like “BAK”

Eventually the script looks like this

The catches are commented due the fact that the SQL Server job can’t cope with these kind of error messages. The script assumes that the database is already present. In a later version I will make it possible to adjust this accordingly but for now the functionality is just to execute a replace.

To make the job go to SQL Server and create a new job and job step and fill it in like below:

job_restore_step

Make sure the field Type is set on “Operating system (CmdExec)” and that the command is set up like this:

The parameters:

  • -d: Directory to search
  • -s: SQL Server instance
  • -db: Database to replace
  • -f: Filter

I used the “*.bak” filter to get all the bak-files but you could use a different extension if needed as long as it is for a full backup.

Replace the values between the brackets and give the job a spin.

 

 

Leave a Reply