Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

PowerShell to automate multiple database SQL Server restores to refresh environments


By:   |   Last Updated: 2015-02-18   |   Comments (7)   |   Related Tips: More > PowerShell

Problem

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.
Solution

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=11.0.0.0, Culture=Neutral, PublicKeyToken=89845dcd8080cc91";
add-type -AssemblyName "Microsoft.SQLServer.SmoExtended, Version=11.0.0.0, 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] [email protected]{}
    )
    try {
            $conn = New-Object System.Data.SqlClient.SqlConnection ("Server=$SQLInstance; Database=$DestinationDB; trusted_connection=TRUE");
            $conn.Open();
            $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

            $bulkcopy.WriteToServer($SourceDataTable);
    }
    catch {
        Write-Error $error[0].Message;
    }
    finally {
        $conn.Close();
    }
    return;
} # Save-DataTable


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

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

$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"; 
        break;
    }
}


## 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)
)
"@;

$srv.Databases['tempdb'].ExecuteNonQuery($qry);

#create a DataTable

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

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

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

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)
"@;

$srv.Databases['tempdb'].ExecuteNonQuery($qry);

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

$col_mapping.Add('LogicalName','LogicalName');
$col_mapping.Add('Size','Size');

$col_mapping.Add('FileType','FileType');

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]);
$dt.Columns.Add($c);

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

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

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

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


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;
        $dt.rows.Add($r);
    };
    $rs.Devices.RemoveAt(0);
}

$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)
"@;
$srv.Databases['tempdb'].ExecuteNonQuery($qry);

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

$col_mapping.Add('LogicalName','LogicalName');
$col_mapping.Add('BkupFile','BkupFile');
$col_mapping.Add('Size','Size');
$col_mapping.Add('FileType','FileType');
 
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
)
"@;
$srv.Databases['tempdb'].ExecuteNonQuery($qry);

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

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


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;
    $r.FreeSpace=$_.FreeSpace;
    $dt.Rows.Add($r);
}


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

$col_mapping.Add('DiskSize','Size');
$col_mapping.Add('FreeSpace','FreeSpace');

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)
begin
 set @sqlcmd ='';

 select @sqlcmd = @sqlcmd + 'move ''' + d.logicalName + ''' to ''' + d.PhysicalName + ''',' + @crlf
 from dbo.tblDB_Info d
 where [email protected];
 set @sqlcmd = 'restore database ' + @db + ' from disk = ''' + @bkupfile + ''' ' + @crlf + 'with ' + @sqlcmd + 'replace;' + @crlf;
 set @sqlcmd2 = @sqlcmd2 + @sqlcmd;
 fetch next from curD into @db, @bkupfile;
 
end
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)";
}
else
{
  $srv.Databases['master'].ExecuteNonQuery($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;
"@;
$srv.databases['tempdb'].ExecuteNonQuery($qry);

Example

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


Last Updated: 2015-02-18


get scripts

next tip button



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.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

 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.

 

Thanks,

Jeff_yao


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

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?

Thanks

Kaka

 

 


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

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

Hi @rd,

Please make the following one-line change

 

dir -path $bkup_folder -Include *.bak | 

to

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.

Thanks,
Jeff


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

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

ect

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

 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

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

 

 


Learn more about SQL Server tools