Using PowerShell to Automate Database Restores

You've automated the SQL database backup process; now, let's restore in auto mode. Here's how to do it with PowerShell.

In my last post, I demonstrated a script that can execute backups for a server. However, the backup is useless unless you can restore it. So let's take a look at a script that can restore a database using PowerShell.

#Performs a restore of a single database.
#Accepts server name as an input parameter.
#Accepts database path AND name as an input parameter.
#Assumes new database name is the same as the name
#in the backup file!
#Load the DLLs if not using SQLPS
   'Microsoft.SqlServer.SMO') | out-null
   'Microsoft.SqlServer.SmoExtended') | out-null

#input parameters
$servername = $args[0]
$backupFile = $args[1]

#we'll get the "proper" db name below. otherwise,
#load server, backup, and restore objects
$server = new-object(
  "Microsoft.SqlServer.Management.Smo.Server") $servername
$dbRestore = new-object(

#settings for the restore
$dbRestore.Action = "Database"
$dbRestore.NoRecovery = $false;
$dbRestore.ReplaceDatabase = $true;
$dbRestorePercentCompleteNotification = 5;

#get the db name
$dbRestoreDetails = $dbRestore.ReadBackupHeader($server)

#print database name
"Database Name from Backup File : "

#give a new database name
$dbRestore.Database =$dbRestoreDetails.Rows[0]["DatabaseName"]

#specify new data and log files (mdf and ldf)
$dbRestoreFile = new-object(
$dbRestoreLog = new-object(

#set file names; use the default database directory
$dbRestoreFile.LogicalFileName =
$dbRestoreFile.PhysicalFileName =
  $server.Information.MasterDBPath +
  "\" + $dbRestore.Database + "_Data.mdf"
$dbRestoreLog.LogicalFileName =
  $dbRestoreDetails.Rows[0]["DatabaseName"] + "_Log"
$dbRestoreLog.PhysicalFileName =
  $server.Information.MasterDBLogPath +
  "\" + $dbRestore.Database + "_Log.ldf"

#execute the restore!
write-host "Restore of "
  $dbRestoreDetails.Rows[0]["DatabaseName"] "Complete"

As in other scripts in this series, we start by loading the SQL extensions. Then we define our input parameters; in this case, we'll take the server name and the database name that we need to restore. Then we instantiate the server and restore objects using Microsoft.SqlServer.Management.Smo.Server and Microsoft.SqlServer.Management.Smo.Restore.

Now, we have access to the various details of a restore, including the recovery status, whether or not we are replacing a databases, and can even add the percent notification complete (for those larger database restores). We also have to define the device from which we will restore, using Devices.AddDevice. This class takes the actual file path (which we've defined in $backupFile) and a parameter telling it what format the device is (in our case, it is a File). Then we query the BackupHeader to find the database name, and provide some visual output.

Next, we specify the files for the database on disk, including both the physical and logical file names. We also use the path to the master database (Information.MasterDBPath and information.MasterDBLogPath) to determine where we can place the database files; this could be replaced with any path you choose. We have to specify the .RelocateFiles property to tell the script that we are placing the files in a specific directory (this is where you'd put the WITH MOVE clause in a T-SQL based restore). Finally, we execute the restore using .SqlRestore, and provide some final output with write-host.

Hopefully, this can give you a good starting point for further automating your backup and restore process. Have fun!

About the Author

Joshua Jones is co-author of A Developer's Guide to Data Modeling for SQL Server: Covering SQL Server 2005 and 2008 (Addison-Wesley Professional, 2008) and is a principal with Denver-based Consortio Services LLC.


  • Google IDs on Azure Active Directory B2B Service Now at 'General Availability'

    Microsoft announced on Wednesday that users of the Google identity and access service can use their personal log-in IDs with the Azure Active Directory B2B service to access resources as "guests."

  • Top 4 Overlooked Features of a Data Backup Strategy

    When it comes to implementing an airtight backup-and-recovery plan, these are the four must-have features that many enterprises nevertheless tend to forget.

  • Microsoft Bolsters Kubernetes with Azure Confidential Computing

    Microsoft on Tuesday announced various developments concerning the use of Kubernetes, an open source container orchestration solution fostered by Google.

  • Windows Will Have Support for Encrypted DNS

    Microsoft announced this week that the Windows operating system already has support for an encrypted Domain Name System option that promises to add greater privacy protections for Internet connections.

comments powered by Disqus

Office 365 Watch

Sign up for our newsletter.

Terms and Privacy Policy consent

I agree to this site's Privacy Policy.