PowerShell to automate multiple database SQL Server restores to refresh environments

By:   |   Comments (9)   |   Related: > PowerShell


From time to time, I need to restore databases on various Development and Test environments using production database backup files. However, when I start to do the restore, my restore often fails because of lack of disk space.

Two reasons for the failure are:

  • Development and Test environments are used by various teams, meaning they may create/generate lots of temporary new databases/files, which can quickly eat up disk space.
  • The production databases have been growing since the last restore, meaning more space is needed for the current restore.

So is there a way to help me achieve the following goals? (assuming I will only restore full backup files which in the same folder)

  1. Do multiple database restores by automatically using the correct backup files in a folder. For example, I need to restore only 20 databases from 100 database backup files.
  2. Before the restore starts, I need to know whether there is sufficient disk space for the restore. If there is not enough space, the restore will not proceed.
  3. My development/test databases may have different names from the production database names and the disk drives in the development/test environments are not the same as the production environment, I need the restore process to take care of this too.

Before we discuss the solution, we will make two assumptions, which are reality most of the time and if not the assumptions are easy to meet with minimal efforts.

  • First, we assume we only deal with full backup files under one folder and each source database has one and only one full backup file in the folder
  • Second, we assume the target databases (databases to be overwritten by the restore) already exist on the target SQL Server instances (i.e. Development/Test environments) and also each target database and the source database have the same number of data and log files with the same logical file names.

We need these assumptions, so we do not need to define various information regarding where to move the physical files of the restored databases when the target and source servers have different disk drive names. These assumptions will help to simplify the processing logic.

Even with these assumptions, we still face two challenges:

  • Finding the disk drives and free space. Note, the drives can be mount point drives, such as c:\MP\Data1\ or e:\sqllog\, i.e. the drive names are flexible instead of fixed
  • In calculating the free space, we need to calculate the current disk free space plus the space used by the existing target databases which will be overwritten by the restore

With these challenges in mind, here is the solution designed with PowerShell

  1. Use get-wmiobject -class win32_Volume to retrieve target server's disk drive information (i.e. drive name and free space), and dump this information to a SQL Server table on the target SQL Server instance  i.e. tempdb.dbo.tblDisk_Info
  2. Loop through each backup file in the folder and retrieve information such as the database name, the logical file name and the physical file size
  3. Loop through sys.master_files on the target SQL Server instance to retrieve the logical file names and physical file sizes of each target database, the logical file name links to the logical file name in step 2 and thus we know how large the new restored physical files will be
  4. With this info, we can calculate whether we have enough space for the restore to be successful and also we can restore the databases to where they currently reside

One nice feature of this PS script is setting $debug=$true which will generate a T-SQL restore script, which you can review and run directly in a SSMS window. If $debug=$false, the PS script will do the restore directly as long as there is sufficient disk space.

So here is the PowerShell code.

#requires -Version 3.0 
#Assume sql server SMO is installed, https://msdn.microsoft.com/en-us/library/ms162189(v=sql.110).aspx
add-type -AssemblyName "Microsoft.SQLServer.Smo, Version=, Culture=Neutral, PublicKeyToken=89845dcd8080cc91";
add-type -AssemblyName "Microsoft.SQLServer.SmoExtended, Version=, Culture=Neutral, PublicKeyToken=89845dcd8080cc91";

[boolean] $debug =$true; # $true = print out the t-sql; $false = execute the restore

[string] $bkup_folder = 'c:\Backup\*' #the folder where the backup files are located. Can be a network share
[string] $sql_instance = 'TP_W520'; #this is the destination sql instance where the restore will occur. Change it to your own.

[string[]]$src_db= 'AdventureWorks2012', 'AdventureWorksDW2012', 'TestDB'; ; #src db list, separated by comma
[string[]]$dest_db= 'AdventureWorks2012', 'AdventureWorksDW2012', 'TestDB2'; #dest db can have different name

#This Save-DataTable is used to write a datatable into a sql server table
function Save-DataTable {
    param ( [parameter(Mandatory=$true)]
        [string] $SQLInstance,

        [parameter (Mandatory=$true)] [System.Data.DataTable]$SourceDataTable,

        [parameter (mandatory=$true)] [string] $DestinationDB,   

        [parameter (mandatory=$true)]
        [string] $DestinationTable,  # can be two-part naming convention, i.e. [schema_name].[table_name]

        [hashtable] $ColumnMapping=@{}
    try {
            $conn = New-Object System.Data.SqlClient.SqlConnection ("Server=$SQLInstance; Database=$DestinationDB; trusted_connection=TRUE");
            $bulkcopy = New-Object System.Data.SqlClient.SqlBulkCopy($conn);
            $bulkcopy.DestinationTableName=$DestinationTable; #you may change to your own table

            if ($ColumnMapping.count -gt 0) {
                $ColumnMapping.keys | % {$bc_mapping = new-object System.Data.SqlClient.SqlBulkCopyColumnMapping($_, $ColumnMapping[$_]); $bulkcopy.ColumnMappings.Add($bc_mapping); } | Out-Null;
            }# mapping columns needed

    catch {
        Write-Error $error[0].Message;
    finally {
} # Save-DataTable

[string] $machine = $sql_instance.split('\')[0];

if ($src_db.Count -ne $dest_db.Count) {
    write-error 'source dbs mismatch destination dbs';

$srv = New-Object "microsoft.sqlserver.management.smo.Server" $sql_instance;
$rs = new-object "microsoft.sqlserver.management.smo.restore";

# make sure the $dest_db exists on the $sql_instance
$dest_db | 
% {
    if ($_ -notin $srv.databases.name) {
        Write-Error "The destination db [$_] does not exist on [$sql_instance], please create it first"; 

## we first create a src-dest db name referece table, which will be deleted at the end of the script
[string]$qry = @"
if object_id('dbo.tblDB_Ref') is not null
    drop table dbo.tblDB_Ref;
create table dbo.tblDB_Ref
( Src_DBName varchar(60)
,  Dest_DBName varchar(60)


#create a DataTable

$dt = new-object "system.data.DataTable";
$c = new-object "System.Data.DataColumn"('Src_DBName', [System.String]);
$c = new-object "System.Data.DataColumn"('Dest_DBName', [System.String]);

0..($src_db.count -1) | 
% {
    $r = $dt.NewRow();
    $r.Src_DBName= $src_db[$_];
    $r.Dest_DBName = $dest_db[$_];

$col_mapping = @{};
$col_mapping.Add('Src_DBName','Src_DBName'); # in the format of (sourceColumn, destinationColumn)

Save-DataTable -SQLInstance $sql_instance -SourceDataTable $dt -DestinationDB 'tempdb' -DestinationTable 'dbo.tblDB_Ref' -ColumnMapping $col_mapping;

[string]$dest_db_list ="'"+ [System.string]::join("','", $dest_db) + "'";

[string]$qry = @"
select DBName=db_name(database_id), LogicalName=name
, Physical_name
, Size=size*cast(8*1024 as bigint)
, FileType = case [type] when 0 then 'D' else 'L' end  
from master.sys.master_files
where db_name(database_id) in ($dest_db_list)

$ds = $srv.Databases['master'].ExecuteWithResults($qry);

$dt = $ds.Tables[0];

$qry = @"
if object_id('dbo.tblDB_Info') is not null
    drop table dbo.tblDB_Info;
create table dbo.tblDB_Info
( DBName varchar(60)
, LogicalName varchar(60)
, PhysicalName varchar(256)
, FileType char(1)
, Size bigint)


$col_mapping = @{};
$col_mapping.Add('DBName','DBName'); # in the format of (sourceColumn, destinationColumn);



Save-DataTable -SQLInstance $sql_instance -SourceDataTable $dt -DestinationDB 'tempdb' -DestinationTable 'dbo.tblDB_Info' -ColumnMapping $col_mapping;

$dt = new-object "system.data.DataTable";
$c = new-object "System.Data.DataColumn"('DBName', [System.String]);

$c = new-object "System.Data.DataColumn"('LogicalName', [System.String]);

$c = new-object "System.Data.DataColumn"('BkupFile', [System.String]);

$c = new-object "System.Data.DataColumn"('FileType', [System.String]); # 'D' = Data File; 'L'= Log File

$c = new-object "System.Data.DataColumn"('Size', [System.Int64]);

dir -path $bkup_folder -Include *.bak | 
% { 
    $rs.devices.AddDevice($_.fullname,[Microsoft.SqlServer.Management.Smo.DeviceType]::File );
    [string]$dbname = ($rs.ReadBackupHeader($srv)).databaseName;
    [string]$bkup_file = $_.FullName;
    $rs.ReadFileList($srv) | 
    % { 
        $r = $dt.NewRow(); 
        $r.DBName = $dbname;
        $r.LogicalName = $_.LogicalName; 
        $r.BkupFile =$bkup_file; 
        $r.size = $_.size; 
        $r.FileType = $_.Type;

$qry = @"
if object_id('dbo.tblBkup_Info') is not null
    drop table dbo.tblBkup_Info;
create table dbo.tblBkup_Info
( DBName varchar(60)
, LogicalName varchar(60)
, BkupFile varchar(200)
, FileType char(1)
, Size bigint)

$col_mapping = @{};
$col_mapping.Add('DBName','DBName'); # in the format of (sourceColumn, destinationColumn)

Save-DataTable -SQLInstance $sql_instance -SourceDataTable $dt -DestinationDB 'tempdb' -DestinationTable 'dbo.tblBkup_Info' -ColumnMapping $col_mapping;

# create a disk info table
$qry = @"
if object_id('dbo.tblDisk_Info') is not null
    drop table dbo.tblDisk_Info;
create table dbo.tblDisk_Info
( Drive varchar(60)
, Size bigint
, FreeSpace bigint

$dt = new-object "system.data.DataTable";
$c = new-object "System.Data.DataColumn"('DriveName', [System.String]);

$c = new-object "System.Data.DataColumn"('DiskSize', [System.Int64]);
$c = new-object "System.Data.DataColumn"('FreeSpace', [System.Int64]);

gwmi -class win32_volume -ComputerName $machine | 
? { ($_.name -match '^\w.+') -and ($_.Capacity -gt 0)} | 
SELECT NAME, Capacity, FreeSpace  |
% { 
    $r = $dt.NewRow();
    $r.DriveName = $_.name;
    $r.DiskSize = $_.capacity;

#dump $dt to dbo.tblDisk_Info
$col_mapping = @{};
$col_mapping.Add('DriveName','Drive'); # in the format of (sourceColumn, destinationColumn)


Save-DataTable -SQLInstance $sql_instance -SourceDataTable $dt -DestinationDB 'tempdb' -DestinationTable 'dbo.tblDisk_Info' -ColumnMapping $col_mapping;

#we need to check whether the [disk free space] + [existing db occupied space] > [the needed space for the restore]
[string]$qry = @"
-- in a folder we may have lots backup files for many different dbs
-- while we may use only a few of them for the restore.
-- Since we scanned all backup files, we have to delete those that are not used
delete from b
from dbo.tblBkup_info b
where dbname not in ( select src_dbname from dbo.tblDB_Ref)

-- CTE c will link [PhysicalName] with [Drive]
-- c1 will get the real drive for each PhysicalName using [ml] (which is max(len(d.drive))
-- at the end, we need to make sure any drive that has [Space_After_Restore] column to be positive
-- if [Space_After_Restore] > 0, it means there is no enough space
; with c as (
select db.DBname, db.LogicalName, db.physicalname, NewFileSize=b.Size, db.size, ml=max(len(d.drive)) 
from dbo.tblDB_info db
inner join dbo.tblDisk_info d
on db.physicalname like (d.drive + '%')
inner join dbo.tblDB_Ref r
on r.Dest_DBName=db.DBName
inner join dbo.tblBkup_Info b
on b.DBName = r.Src_DBName
and b.LogicalName = db.LogicalName
group by db.DBName, db.LogicalName, db.PhysicalName, b.size, db.size
, c1 as (
select  d.drive, FreeSpace_MB=d.FreeSpace/1024/1024, NeededSpace_MB=sum(c.NewFileSize)/1014/1024
, ExistingSpace_MB=sum(c.size)/1024/1024
from c
inner join dbo.tblDisk_info d
on c.physicalName like (d.drive + '%')
and c.ml = len(d.Drive) 
group by d.drive, d.FreeSpace
) select drive, Space_After_Restore=freeSpace_mb+ExistingSpace_MB - NeededSpace_mb 
from c1;

$ds = $srv.Databases['tempdb'].ExecuteWithResults($qry);

$dt = $ds.Tables[0];

# loop through each row (each row contains one unique disk drive) and see whether there is any space left after restore
# if the value is negative, it means no space left, and thus should return error and exit
foreach ($r in $dt.rows)
    if ($r.Space_After_Restore -lt 0)
        Write-Error "The drive [$($r.Drive)] does not have enough space, it lacks [$($r.Space_After_Restore)] MB free space."; 
        if (-not $debug)
        { return; }

# if disk freespace is OK,we can proceed with the restore

$qry = @"
declare @sqlcmd varchar(max), @crlf char(2)=char(0x0d) + char(0x0a);
declare @db varchar(100), @bkupfile varchar(200);
declare @sqlcmd2 varchar(max)='';

declare curD cursor for
select distinct DBName=r.Dest_DBName, BkupFile 
from dbo.tblBkup_Info b
inner join dbo.tblDB_Ref r
on r.Src_DBName = b.DBName
open curD;
fetch next from curD into @db, @bkupfile;
while (@@fetch_status = 0)
 set @sqlcmd ='';

 select @sqlcmd = @sqlcmd + 'move ''' + d.logicalName + ''' to ''' + d.PhysicalName + ''',' + @crlf
 from dbo.tblDB_Info d
 where d.dbname=@db;
 set @sqlcmd = 'restore database ' + @db + ' from disk = ''' + @bkupfile + ''' ' + @crlf + 'with ' + @sqlcmd + 'replace;' + @crlf;
 set @sqlcmd2 = @sqlcmd2 + @sqlcmd;
 fetch next from curD into @db, @bkupfile;
close curD;
deallocate curD;
select SQLCMD = @sqlcmd2;
$ds = $srv.Databases['tempdb'].ExecuteWithResults($qry);

[string]$sqlcmd= $ds.Tables[0].rows[0].sqlcmd;

if ($debug)
{ write-output "-- the restore script is: `r`n$($sqlcmd)";

## cleanup the temp tables
$qry = @"
if object_id('dbo.tblDB_Ref') is not null
    drop table dbo.tblDB_Ref;

if object_id('dbo.tblDB_Info') is not null
    drop table dbo.tblDB_Info;

if object_id('dbo.tblDisk_Info') is not null
    drop table dbo.tblDisk_Info;

if object_id('dbo.tblBkup_Info') is not null
    drop table dbo.tblBkup_Info;


In my c:\Backup\ folder, I have 8 full backup files, I only need to use three backup files to do three database restores.

backup folder status

After I execute the PS script from a PowerShell IDE window, it will generate the T-SQL restore script (when $debug=$true).

restore script

If there is not enough disk space on the target server, you will see an error message like below (note:[D:\Data\SQLSAS01\] in the error message is actually a mount point drive name).

Error no space
Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Jeffrey Yao Jeffrey Yao is a senior SQL Server consultant, striving to automate DBA work as much as possible to have more time for family, life and more automation.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Tuesday, October 5, 2021 - 6:27:18 PM - jeff_yao Back To Top (89309)
@Rohit Pal, thanks for reading the tip, I think you may need to print out $sqlcmd and then try to put that script into an SSMS query window to run it and see what exactly error message you will get.

Tuesday, October 5, 2021 - 4:14:25 PM - Rohit Pal Back To Top (89308)
I'm able to generate the restore script by keeping the option as true.
But getting error message as below while performing the direct restore of databases. Can you please check

Exception calling "ExecuteNonQuery" with "1" argument(s): "ExecuteNonQuery failed for Database 'master'. "
At D:\DatabaseRestoreScript\Restore.ps1:342 char:3
+ $srv.Databases['master'].ExecuteNonQuery($sqlcmd);
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : FailedOperationException

Wednesday, August 24, 2016 - 12:27:29 PM - jeff_yao Back To Top (43181)

 Hi Kaka,

Thanks for reading the tip.

You raise a good question, my generic approach is always "notify people via email before hand and take the db offline at the scheduled time and then start the restore". 

Of course, if you want to add some logic to ensure the destination database is NOT in use before the restore starts, you can put the logic before the disk space check section in the script.




Tuesday, August 23, 2016 - 3:34:17 PM - kaka Back To Top (43172)

Hi Jeffrey,

During the restore if there any logic, Where it going to check if the destination database is in Use? So, the restore might not fail?





Thursday, April 14, 2016 - 5:27:10 PM - rd Back To Top (41234)

Fixed it.

Added -Recurse in the following line in this script.

dir -path $bkup_folder -Recurse -Include *.bak

Thursday, April 14, 2016 - 5:00:00 PM - jeff_yao Back To Top (41232)

Hi @rd,

Please make the following one-line change


dir -path $bkup_folder -Include *.bak | 


dir -path $bkup_folder -Include *.bak -recurse |

and then try.

I think it should work (though I have not got a chance to do a full test), but if not, just let me know, I'll do some debugging later.


Thursday, April 14, 2016 - 4:40:33 PM - rd Back To Top (41231)

Thanks for the awesome script.

Is there a way for this script to traverse through child directory for .bak files ?

My backups are located in structure like below

\\cifsshare\backup\instance name\database1\full\database1 name_date.bak

\\cifsshare\backup\instance name\database2\full\database2 name_date.bak


Would be nice if i can just point it to \\cifsshare\backup\

Appreciate any help on this . Thanks.

Friday, March 11, 2016 - 2:34:13 PM - jeff_yao Back To Top (40917)

 Hi @Matt, I do not know why I never get notified there is a comment on this tip, so sorry for not answering your comment early.

But for your issue, I believe it is because you have not pre-created the destination databases on the target sql instance. This is a pre-requirement as I mentioned in the [Solution] section. 

Second, we assume the target databases (databases to be overwritten by the restore) already exist on the target SQL Server instances 

Hope this helps.


Tuesday, December 22, 2015 - 5:34:10 AM - Matt Back To Top (40294)

Hi, I am having problems with your script. (I've replaced the DB and instance name) but the ones i'm using are correct. When I run the script I'm getting the following error:  

The destination db [database_name] does not exist on [sql_Instance], please create it first

At C:\Users\user\Documents\restore scripts\restore script.ps1:62 char:3

+ % {

+   ~

    + CategoryInfo          : NotSpecified: (:) [Write-Error], WriteErrorException

    + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException



get free sql tips
agree to terms