Click here to monitor SSC
  • Av rating:
  • Total votes: 19
  • Total comments: 5
Allen White

Backup and Restore SQL Server with the SQL Server 2012 PowerShell cmdlets

03 May 2013

There are plenty of occasions when it makes a lot of sense to do backup and restore scripts in PowerShell. Microsoft have put effort into making it much easier, as Allen demonstrates.

One of the most common jokes you'll hear when talking about the most important tasks for a Database Administrator goes like this - a DBA needs one of two things, a good backup or a good resume. It's true. If you're not doing backups, and ensuring that you can recover databases from those backups, you're exposing yourself and your company to data loss.

The BACKUP DATABASE Transact-SQL command has been around a long time (preceded by the DUMP DATABASE command for those of us around SQL Server long enough to remember it.) It's still a great way to get your databases protected, but to help us to  move towards more automated ways of getting those everyday tasks done, there are now additional methods available to us. In this article, I’ll be describing, with practical examples, how backups can be scripted effectively from PowerShell via SMO and SQLPS, and how the scripting process has been greatly simplified by using the PowerShell Cmdlets introduced with SQL Server 2012.

In SQL Server 2012, Microsoft added four new cmdlets for Backup and Restore:

  • Backup-SqlDatabase
  • Restore-SqlDatabase
  • Backup-ASDatabase
  • Restore-ASDatabase

Why consider scripting backups from an external process.

When the backup process becomes more complex, more and more file-system work needs to be done. These jobs might require the naming and arranging of backups into directories, deleting old backups that are no longer required, copying them offsite, checking backup integrity, writing to logs etc. You might, possibly, need to backup, in one operation, several databases, on different servers even, or copy a database to several servers or VMs for testing.  At some point, a scripted backup and restore needs to be considered, and PowerShell is ideal for this.

Scripting with PowerShell and SMO 

In 2009 I published a whitepaper for Microsoft where I introduced a PowerShell script called backup.ps1 using the Server Management Objects (SMO) library to back up your databases, and since SMO is supported for SQL Server versions 2000 through 2012, it still works.  (Understanding and Using PowerShell Support in SQL Server 2008) In fact, Microsoft hasn't added a lot of functionality in this area of SMO in SQL Server 2012, so there's not much new to learn.

In the whitepaper I discussed the SQL Server snapins for PowerShell, and the SQLPS.exe "mini-shell" that's included in SQL Server 2008 and SQL Server 2008 R2.  While this was the way prescribed by the PowerShell team for extending PowerShell when SQL Server 2008 was introduced, the technology has changed, and the PowerShell team now promotes the "module" model for extending PowerShell.  As a result, PowerShell version 2.0 is a minimum requirement on a server before installing SQL Server 2012, and the SQLPS.exe program has been replaced by a new module called - wait for it - SQLPS. 

Microsoft provided a number of new cmdlets in the SQLPS module, most of which are specific to managing Availability Groups and High Availability/Disaster Recovery.  But besides these, they provided four new cmdlets specific to backup and restore operations - Backup-SqlDatabase, Restore-SqlDatabase, Backup-ASDatabase and Restore-ASDatabase.

 Installing the SQLPS module

When you start up PowerShell and want to work with the SQLPS module, you need to use the Import-Module cmdlet.  Starting with PowerShell version 2.0, Microsoft checks object names during the import process against a list of approved verbs.  Because the verbs Backup and Restore are not in the approved list, you'll get a message indicating that non-approved verbs exist in the module. You can avoid this error by including the -DisableNameChecking parameter to the Import-Module cmdlet when you import the SQLPS module.  (Note that when you import the SQLPS module your location will be set to the root of the SQL Server provider. This is expected behavior.)

Backing up databases with the Backup-SqlDatabase CmdLet

 Simple Backups with Backup-SQLDatabase

Once you've loaded the module you can easily create a backup for a database using a command like this:

{$dt = Get-Date -Format yyyyMMddHHmmss

$dbname = 'AdventureWorks'

Backup-SqlDatabase -ServerInstance TESTSQL -Database $dbname -BackupFile "E:\Backup\$($dbname)_db_$($dt).bak"

There are several ways to call this cmdlet, of course.  Another would be to make use of the provider, navigate to the Databases directory under your SQL Server instance, and use the Get-ChildItem cmdlet to iterate through your databases to back each one up.  Because you're already connected to your instance you don't need the -ServerInstance parameter.

At its simplest, if you are you can simply type

Set-Location SQLSERVER:\SQL\TESTSQL\DEFAULT\Databases

get-childitem|Backup-SqlDatabase

..which does a backup to the default backup directory and uses the name of the databases as the name of the backup file. If you need to specify the name of the backup file or any other of the many possible parameters, then you may want to do this..

Set-Location SQLSERVER:\SQL\TESTSQL\DEFAULT\Databases

foreach ($db in (Get-ChildItem))

    {

        $dbname = $db.Name

        $dt = Get-Date -Format yyyyMMddHHmmss

        Backup-SqlDatabase -Database $dbname -BackupFile "$($dbname)_db_$($dt).bak"

    }

This will back up each of your user databases to the default backup directory, and use the name of the database and the current date and time of the backup in the backup file name.  (Note that you can use the -Force parameter with the Get-ChildItem cmdlet in the foreach loop to include the system databases, but  just make sure you filter out the tempdb database.)

get-childitem -force|where name -ne 'TempDB'| Backup-SqlDatabase

You could also create a variable containing an SMO Server object and use the -InputObject parameter instead of the -ServerInstance object. Although we’ll show this happening for a single database, this is useful when you have a list of databases in different servers that need to be backed up.

$dt = Get-Date -Format yyyyMMddHHmmss

$dbname = 'AdventureWorks'

$svr =  new-object ('Microsoft.SqlServer.Management.Smo.Server') 'TESTSQL'

Backup-SqlDatabase -InputObject $svr -Database $dbname -BackupFile "E:\Backup\$($dbname)_db_$($dt).bak"

Or, you can use a variable containing the SMO Database object for the target database, and use the -DatabaseObject parameter.

$dt = Get-Date -Format yyyyMMddHHmmss

Set-Location SQLSERVER:\SQL\TESTSQL\DEFAULT\Databases\AdventureWorks

$db = Get-Item .

$dbname = $db.Name

Backup-SqlDatabase -DatabaseObject $db -BackupFile "E:\Backup\$($dbname)_db_$($dt).bak"

 

In each of these examples, I've used just the minimum of parameters to keep the examples simple. In your code, you should look at the other parameters to get the right backup solution for your needs. So what are these parameters, and how should they be used?

 SQL-Backup: The key parameters

A parameter I would always include is -BackupAction.  The possible values are Database, Files, or Log, and the default value if not included is Database. If you want to do a differential backup, specify the BackupAction as Database, and add the -Incremental parameter. If you want to backup individual files or file groups you'd use the Files option, and of course the Log option allows you to do transaction log backups. For the sake of space, these examples exclude that parameter so they create full database backups.

 For example, I would always include the -CompressionOption On parameter.   I'd also be certain to include the -ConnectionTimeout parameter and set it to a value of 0, because you don't want your backups terminated because the connection between the script and SQL Server is idle while the backup occurs.

If we expand out the list of parameters to the Backup-SqlDatabase cmdlet from the get-help output for that cmdlet, we get this list:

Backup-SqlDatabase

[-Database] <string>

[-BackupFile] <string[]>]

[-ServerInstance <string[]> ]

[-BackupAction <BackupActionType>]

[-BackupDevice <BackupDeviceItem[]>]

[-BackupSetDescription <string>]

[-BackupSetName <string>]

[-BlockSize <int>]

[-BufferCount <int>]

[-Checksum]

[-CompressionOption <BackupCompressionOptions>]

[-ConnectionTimeout <int>]

[-ContinueAfterError]

[-CopyOnly]

[-Credential <PSCredential>]

[-DatabaseFile <string[]>]

[-DatabaseFileGroup <string[]>]

[-ExpirationDate <DateTime>]

[-FormatMedia]

[-Incremental]

[-Initialize]

[-LogTruncationType <BackupTruncateLogType>]

[-MaxTransferSize <int>]

[-MediaDescription <string>]

[-MediaName <string>]

[-MirrorDevices <BackupDeviceList[]>]

[-NoRecovery]

[-NoRewind]

[-Passthru]

[-Restart]

[-RetainDays <int>]

[-Script]

[-SkipTapeHeader]

[-UndoFileName <string>]

[-UnloadTapeAfter]

[-Confirm]

[-WhatIf]

[<CommonParameters>]

Advantages over SMO

It's obvious to me that they've added functionality to this cmdlet over what is available in SMO.  Something that has been available via Transact-SQL and missing from SMO is the ability to set the block size and buffer count for backups, and they've enabled those properties. Here are the equivalent properties and methods available to us via the SMO Backup object:

(This is from the Object Browser in Visual Studio after loading the SMOExtended DLL, where the Backup and Restore objects live.)

Restoring databases with the Restore-SQLDatabase CmdLet

There are a lot of different reasons why we need to restore databases, so there are a lot more options with restores than there are with backups.

The easiest way to demonstrate a restore is to simply restore a database from a full backup, setting the option to overwrite the existing database.

Restore-SqlDatabase -ServerInstance TESTSQL -Database AdventureWorks `

-BackupFile "E:\Backup\AdventureWorks_db_20130420153024.bak" -ReplaceDatabase

One of the reasons I've had to restore databases frequently is to recover data from some table that a user inadvertently deleted, but other transactional changes had subsequently been made that couldn't be lost.  To accomplish this I'd restore the backup to another named database on the same server, usually with the original database name with the date of the backup appended to the name.  I could then copy the lost data from the backup directly into the original database's table and drop the copy when all is well again.

Restoring databases with SMO

Here's how we do this using SMO directly.

# Connect to the specified instance

$srv = new-object ('Microsoft.SqlServer.Management.Smo.Server') 'TESTSQL'

 

# Get the default file and log locations

# (If DefaultFile and DefaultLog are empty, use the MasterDBPath and MasterDBLogPath values)

$fileloc = $srv.Settings.DefaultFile

$logloc = $srv.Settings.DefaultLog

if ($fileloc.Length -eq 0) {

    $fileloc = $srv.Information.MasterDBPath

    }

if ($logloc.Length -eq 0) {

    $logloc = $srv.Information.MasterDBLogPath

    }

 

# Identify the backup file to use, and the name of the database copy to create

$bckfile = 'E:\Backup\AdventureWorks_db_20101016135438.bak'

$dbname = 'AdventureWorks_20101016'

 

# Build the physical file names for the database copy

$dbfile = $fileloc + '\'+ $dbname + '_Data.mdf'

$logfile = $logloc + '\'+ $dbname + '_Log.ldf'

 

# Use the backup file name to create the backup device

$bdi = new-object ('Microsoft.SqlServer.Management.Smo.BackupDeviceItem') ($bckfile, 'File')

 

# Create the new restore object, set the database name and add the backup device

$rs = new-object('Microsoft.SqlServer.Management.Smo.Restore')

$rs.Database = $dbname

$rs.Devices.Add($bdi)

 

# Get the file list info from the backup file

$fl = $rs.ReadFileList($srv)

foreach ($fil in $fl) {

    $rsfile = new-object('Microsoft.SqlServer.Management.Smo.RelocateFile')

    $rsfile.LogicalFileName = $fil.LogicalName

    if ($fil.Type -eq 'D'){

        $rsfile.PhysicalFileName = $dbfile

        }

    else {

        $rsfile.PhysicalFileName = $logfile

        }

    $rs.RelocateFiles.Add($rsfile)

    }

 

# Restore the database

$rs.SqlRestore($srv)

One of the potential issues when restoring a copy of an existing database to the same server is physical name conflicts, and to address that you use SMO RelocateFile objects. This is the equivalent of using the WITH MOVE clause in Transact-SQL.  Interestingly, the Restore-SqlDatabase cmdlets requires these same SMO objects to accomplish the same thing.

The only difference between using pure SMO and using the Restore-SqlDatabase cmdlet to accomplish this is how you store and pass the RelocateFile objects.  We need to create an empty collection, which we do right after reading the backup file list, and add each RelocateFile object to the collection, then we call the Restore-SqlDatabase cmdlet.  Here's just the last part of the same code, using the new cmdlet instead.

# Get the file list info from the backup file

$fl = $rs.ReadFileList($srv)

$rfl = @()

foreach ($fil in $fl) {

    $rsfile = new-object('Microsoft.SqlServer.Management.Smo.RelocateFile')

    $rsfile.LogicalFileName = $fil.LogicalName

    if ($fil.Type -eq 'D') {

        $rsfile.PhysicalFileName = $dbfile

        }

    else {

        $rsfile.PhysicalFileName = $logfile

        }

    $rfl += $rsfile

    }

 

# Restore the database

Restore-SqlDatabase -ServerInstance TESTSQL -Database $dbname `

 -BackupFile "E:\Backup\AdventureWorks_db_20101016135438.bak" `

 -RelocateFile $rfl

In this case, I'll stick with the pure SMO method, as I think it's cleaner.

Restoring databases to a point in time

The last example I'll share involves restoring a database to a point in time. Sometimes the problem that occurred causing you to restore was something that happened at a time you know, and you're able to decide to bring back the database to that moment.

In this scenario we store all our backup files in the E:\Backup directory on the local server. We have multiple full backups, multiple differential backups, and multiple transaction log backups, including log backups taken after our target point-in-time.  The file names follow the convention DatabaseName_type_datetime.ext, where type is db, diff or tran and ext is either bak or trn.

$dbname = 'AdventureWorks'

$restorept = '2013-04-20 15:30:00'

Set-Location 'E:\Backup'

$fullfile = Get-ChildItem -Filter "$($dbname)_db_*" | Where-Object {$_.LastWriteTime -lt $restorept} | Sort-Object LastWriteTime Desc | Select-Object -First 1

$difffile = Get-ChildItem -Filter "$($dbname)_diff_*" | Where-Object {$_.LastWriteTime -lt $restorept} | Sort-Object LastWriteTime Desc | Select-Object -First 1

$tranfile = Get-ChildItem -Filter "$($dbname)_tran_*" | Where-Object {$_.LastWriteTime -gt $difffile.LastWriteTime} | Sort-Object LastWriteTime Asc

 

Now the $fullfile variable contains the file information for the last full backup before the target time, the $difffile variable contains the file information for the last differential backup before the target time, and the $tranfile variable contains file information on all the transaction log backups taken since the differential backup identified in $difffile.

First, we need to restore the full backup, with the replace option and specify no recovery.

Restore-SqlDatabase -ServerInstance TESTSQL -Database $dbname `

-BackupFile $fullfile.FullName -ReplaceDatabase `

-NoRecovery

Next, we restore the latest differential, again specifying no recovery.

Restore-SqlDatabase -ServerInstance TESTSQL -Database $dbname `

-BackupFile $difffile.FullName -ReplaceDatabase `

-NoRecovery

Finally we restore the transaction log backups by looping through the files in the $tranfile variable. If the LastWriteTime property is less than our $restorept variable, then restore with no recovery and go on to the next one. The first log backup that was taken after our restore point is restored with the -ToPointInTime parameter, without the -NoRecovery paramater, and we set our indicator to keep us from attempting to restore any more transaction log backups.

$recovery = 0

foreach ($trnfile in $tranfile) {

     if ($trnfile.LastWriteTime -lt $restorept) {

         Restore-SqlDatabase -ServerInstance TESTSQL -Database $dbname `

-BackupFile $trnfile.FullName -ReplaceDatabase `

-NoRecovery

         }

    else {

         if ($recovery -eq 0) {

             Restore-SqlDatabase -ServerInstance TESTSQL -Database $dbname `

-BackupFile $trnfile.FullName -ReplaceDatabase `

-ToPointInTime $restorept

$recovery = 1       

            }

        }

    }

There's a lot of versatility here, and having direct access to operating system features makes automating these backups and restores much more convenient than attempting to work through Transact-SQL.

 Just for example, here are the SMO properties and methods for the Restore object:

Correspondingly, here is the help text from the Restore-SqlDatabase cmdlet showing the parameters:

Restore-SqlDatabase

[-Database] <string>

[[-BackupFile] <string[]>]

-ServerInstance <string[]>

[-BackupDevice <BackupDeviceItem[]>]

[-BlockSize <int>]

[-BufferCount <int>]

[-Checksum]

[-ClearSuspectPageTable]

[-ConnectionTimeout <int>]

[-ContinueAfterError]

[-Credential <PSCredential>]

[-DatabaseFile <string[]>]

[-DatabaseFileGroup <string[]>]

[-FileNumber <int>]

[-KeepReplication]

[-MaxTransferSize <int>]

[-MediaName <string>]

[-NoRecovery]

[-NoRewind]

[-Offset <Int64[]>]

[-Partial]

[-Passthru]

[-RelocateFile <RelocateFile[]>]

[-ReplaceDatabase]

[-Restart]

[-RestoreAction <RestoreActionType>]

[-RestrictedUser]

[-Script]

[-StandbyFile <string>]

[-StopAtMarkAfterDate <string>]

[-StopAtMarkName <string>]

[-StopBeforeMarkAfterDate <string>]

[-StopBeforeMarkName <string>]

[-ToPointInTime <string>]

[-UnloadTapeAfter]

[-Confirm]

[-WhatIf]

[<CommonParameters>]

 

Backing up Analysis Services with Backup-ASDatabase

Analysis Services databases need to be backed up as well, even though there are far fewer options to do so.  The first thing you'll need to do is to load the Analysis Services cmdlets, as they're in a different module.

Import-Module SQLASCMDLETS

Let's take a look at the parameters for the Backup-ASDatabase cmdlet.

Backup-ASDatabase

[-BackupFile] <string>

[-Name] <string>

[-AllowOverwrite <SwitchParameter>]

[-BackupRemotePartitions <SwitchParameter>]

[-ApplyCompression <SwitchParameter>]

[-FilePassword <SecureString>]

[-Locations <Microsoft.AnalysisServices.BackupLocation[]>]

[-Server <string>]

[-Credentials <PSCredential>]

[<CommonParameters>]

Notice there aren't options to do differential or transaction log backups here.  A simple backup of the AWDB database looks like this.

Backup-ASDatabase "E:\Backup\AWDB.abf" AWDB

If we want to overwrite the backup if it exists already we can add the -AllowOverwrite  parameter, if we want to compress the backup (always a good idea) we can add the -ApplyCompression parameter, and if we want to encrypt the backup we can add the -FilePassword parameter.

Restoring an Analysis Services database with Restore-ASDatabase

Similarly, there aren't a lot of options when we want to restore an Analysis Services database.

Restore-ASDatabase

[-RestoreFile] <string>

[-Name] <System.String>

[-AllowOverwrite <SwitchParameter>]

[-Locations <Microsoft.AnalysisServices.RestoreLocation[]>]

[-Security <Microsoft.AnalysisServices.RestoreSecurity>]

[-Password <System.SecureString>]

[-StorageLocation <System.String>]

[-Server <string>]

[-Credentials <PSCredential>] [<CommonParameters>]

To restore the AWDB database we can do so like this.

Restore-ASDatabase "E:\Backup\AWDB.abf" AWDB -Security:CopyAll

This restores the database and restores the roles and members from the backup as well.

Summary

Backup and restore are critical activities in managing any organizations data. Backup and restore tasks for administering SQL Server are often best scripted using PowerShell and SMO, especially when a DBA needs to do more complex, repeatable, jobs. Where databases must be highly available, for example, it is good practice to create scripts for restoring them, test them thoroughly and then use them for rehearsals. For this, the Backup and Restore cmdlets provided with SQL Server 2012 are ideal for making the task as simple as possible.


Allen White

Author profile:

Allen is a Practice Leader for Upsearch in Northeast Ohio. He's been both a developer and an administrator so understands both perspectives towards database technology, and loves sharing his experiences and helping people learn how to use SQL Server. Allen has spent over 35 years in IT and has been using SQL Server since 1992 and is certified MCITP in SQL Server and MCT. Allen has been awarded Microsoft’s MVP Award for the last six years. He's President of the Ohio North SQL Server User's Group and maintains a blog at http://sqlblog.com/blogs/allen_white/default.aspx.

Search for other articles by Allen White

Rate this article:   Avg rating: from a total of 19 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: Advice on SingleUser Mode?
Posted by: SlinkingFerret (view profile)
Posted on: Friday, May 24, 2013 at 8:59 PM
Message: My apologies up front. I'm only about a week into posh and all of my knowledge comes from your articles (mostly based around PS2, and thank you very much!) and a smattering of forum posts.

your example of the PS3.0 Restore-SqlDatabase worked great on some of my dev servers, but once I got into a server that has client applications attached, I got my familiar SingleUser error. I couldn't restore because there are connections on the database. Maybe it's rare elsewhere, but it's a normal fact of life on my database.

Either Drop/Restore or SingleUser/Restore.

So I spent the day piecing together a franken-posh code that eventually got the job done.

Its not clean. It doesn't have logic determining if I should apply Single UserAccess, it just does it and throws an error if the db is already in SingleUser

I attached my code so maybe you or someone else could show me a cleaner way to achieve what I'm attempting.

A. close out connections to database
B. Restore database
C. Since the posh interface shows a status bar that disapears when it's done, I like to verify that the backup actually took place by grabbing the last backup date.

After this, I'll have to do similar with Restore-ASDatabase, but I figured to nail the easy one first.

Script:
cls
#import SQL Server module
Import-Module SQLPS -DisableNameChecking

# Sets context for getting a db reference.
# I use the reference to change the user access and verify the backup date after the restore
Set-Location SQLSERVER:\SQL\(server)\(instance)\Databases\(database)

$dbName = "db"
$db = Get-Item .
echo $db.UserAccess

# The next few lines handle changing the databases user access mode which prevents restoring due to active connections
# I run this if the db is set to Multiple (it normally is)
#$db.UserAccess = "Multiple"
$db.UserAccess = "Single"

$db.Parent.KillAllProcesses($dbName)
$db.Alter()

Restore-SqlDatabase -Database $dbName -BackupFile "(backup path).bak" -ReplaceDatabase

# Here and below is my TSQL hammer approach to solve the problem of verifing the restore date on the DB we're attempting to restore.
# I'm sure that this can be done more elegantly within Posh and even using a TSQL query, this has to be a hacky way to display the output of a known schema result.
cls
$BackupTimeQuery = @"
SELECT TOP 1
destination_database_name As dbname,
restore_date,
user_name As RestoredBy
FROM msdb.dbo.restorehistory RH
WHERE RH.destination_database_name = '$dbName'
ORDER BY restore_date DESC;
"@

<# Commented out the StringCollection as I don't need it for a single query
# Setting up StringCollection to use with ExecuteWithResults for multiple queries.
$sqlQuery = new-object System.Collections.Specialized.StringCollection
Query to find the most recent backup of the DB being restored.
$sqlQuery.Add("")
#>

$ds = $db.ExecuteWithResults($BackupTimeQuery)

# Please don't anyone use this as an example, but please do suggest a better way to display multiple fields of a single row returned within Posh.
Foreach ($t in $ds.Tables)
{
Foreach ($r in $t.Rows)
{
Foreach ($c in $t.Columns)
{
Write-Host $c.ColumnName "=" $r.Item($c)
}
}
}

Subject: Sort-Object syntax
Posted by: KlaasV (view profile)
Posted on: Tuesday, June 04, 2013 at 8:06 AM
Message: Very well written and very useful article!
But did you really execute the included code? In PS V3 (and I think also in V2), the Sort-Object interprets 'Asc' and 'Desc' as column names. This cmdlet has a -Descending switch, but it needs a dash, and ascending is just the default sort. Maybe you put some T-SQL in?

Subject: Sort-Object syntax
Posted by: KlaasV (view profile)
Posted on: Tuesday, June 04, 2013 at 8:32 AM
Message: Very well written and very useful article!
But did you really execute the included code? In PS V3 (and I think also in V2), the Sort-Object interprets 'Asc' and 'Desc' as column names. This cmdlet has a -Descending switch, but it needs a dash, and ascending is just the default sort. Maybe you put some T-SQL in?

Subject: Found bugs in the SMO Restore when you have multiple files
Posted by: SlinkingFerret (view profile)
Posted on: Monday, June 17, 2013 at 1:28 PM
Message: foreach ($fil in $fl) works fine for most people. you have two files. One data, one log.

So when you add the filename:

#Outside of the loop
$dbfile = $fileloc + '\'+ $dbname + '_Data.mdf'

#Within the loop
$rsfile.PhysicalFileName = $dbfile
#keeps assigning "path\\dbname_data.mdf" to each data file.

Added Import-Module SQLPS to get the namespaces.
Added a counter and increment it for the filenames. Removed the extra backslash which resulted in a double backslash.
Added a Timeout setting because my restore was timing out.

Modified Script I have working:

cls
#import SQL Server module
Import-Module SQLPS -DisableNameChecking

# Connect to the specified instance
$srv = new-object ('Microsoft.SqlServer.Management.Smo.Server') 'ServerName'

# Long Backups/Restores will timeout without changing the timeout.
$srv.ConnectionContext.StatementTimeout = 0
# Identify the backup file to use, and the name of the database copy to create
$bckfile = 'BackupFilePath\Backup.bak'
$dbname = 'dbName'

# Get the default file and log locations
# (If DefaultFile and DefaultLog are empty, use the MasterDBPath and MasterDBLogPath values)
$fileloc = $srv.Settings.DefaultFile
$logloc = $srv.Settings.DefaultLog
if ($fileloc.Length -eq 0) {
$fileloc = $srv.Information.MasterDBPath
}
if ($logloc.Length -eq 0) {
$logloc = $srv.Information.MasterDBLogPath
}


# Build the physical file names for the database copy
# Neither Variables are used now due to setting the name within the loop.
#$dbfile = $fileloc + ''+ $dbname + '_Data.mdf'
#$logfile = $logloc + ''+ $dbname + '_Log.ldf'

# Use the backup file name to create the backup device
$bdi = new-object ('Microsoft.SqlServer.Management.Smo.BackupDeviceItem') ($bckfile, 'File')

# Create the new restore object, set the database name and add the backup device
$rs = new-object('Microsoft.SqlServer.Management.Smo.Restore')
$rs.Database = $dbname
$rs.Devices.Add($bdi)

# Get the file list info from the backup file
#Declare variable to increment
$cnt = 0
$fl = $rs.ReadFileList($srv)
foreach ($fil in $fl) {
$rsfile = new-object('Microsoft.SqlServer.Management.Smo.RelocateFile')
$rsfile.LogicalFileName = $fil.LogicalName
if ($fil.Type -eq 'D'){
#$rsfile.PhysicalFileName = $dbfile
$rsfile.PhysicalFileName = $fileloc + $dbname + '_' + $cnt.tostring() + '_Data.mdf'
}
else {
#$rsfile.PhysicalFileName = $logfile
$rsfile.PhysicalFileName = $fileloc + $dbname + '_' + $cnt.tostring() + '_Log.ldf'
}
$rs.RelocateFiles.Add($rsfile)
$cnt = $cnt + 1
}

# Restore the database
$rs.SqlRestore($srv)

Subject: Found bugs in the SMO Restore when you have multiple files
Posted by: SlinkingFerret (view profile)
Posted on: Monday, June 17, 2013 at 2:15 PM
Message: foreach ($fil in $fl) works fine for most people. you have two files. One data, one log.

So when you add the filename:

#Outside of the loop
$dbfile = $fileloc + '\'+ $dbname + '_Data.mdf'

#Within the loop
$rsfile.PhysicalFileName = $dbfile
#keeps assigning "path\\dbname_data.mdf" to each data file.

Added Import-Module SQLPS to get the namespaces.
Added a counter and increment it for the filenames. Removed the extra backslash which resulted in a double backslash.
Added a Timeout setting because my restore was timing out.

Modified Script I have working:

cls
#import SQL Server module
Import-Module SQLPS -DisableNameChecking

# Connect to the specified instance
$srv = new-object ('Microsoft.SqlServer.Management.Smo.Server') 'ServerName'

# Long Backups/Restores will timeout without changing the timeout.
$srv.ConnectionContext.StatementTimeout = 0
# Identify the backup file to use, and the name of the database copy to create
$bckfile = 'BackupFilePath\Backup.bak'
$dbname = 'dbName'

# Get the default file and log locations
# (If DefaultFile and DefaultLog are empty, use the MasterDBPath and MasterDBLogPath values)
$fileloc = $srv.Settings.DefaultFile
$logloc = $srv.Settings.DefaultLog
if ($fileloc.Length -eq 0) {
$fileloc = $srv.Information.MasterDBPath
}
if ($logloc.Length -eq 0) {
$logloc = $srv.Information.MasterDBLogPath
}


# Build the physical file names for the database copy
# Neither Variables are used now due to setting the name within the loop.
#$dbfile = $fileloc + ''+ $dbname + '_Data.mdf'
#$logfile = $logloc + ''+ $dbname + '_Log.ldf'

# Use the backup file name to create the backup device
$bdi = new-object ('Microsoft.SqlServer.Management.Smo.BackupDeviceItem') ($bckfile, 'File')

# Create the new restore object, set the database name and add the backup device
$rs = new-object('Microsoft.SqlServer.Management.Smo.Restore')
$rs.Database = $dbname
$rs.Devices.Add($bdi)

# Get the file list info from the backup file
#Declare variable to increment
$cnt = 0
$fl = $rs.ReadFileList($srv)
foreach ($fil in $fl) {
$rsfile = new-object('Microsoft.SqlServer.Management.Smo.RelocateFile')
$rsfile.LogicalFileName = $fil.LogicalName
if ($fil.Type -eq 'D'){
#$rsfile.PhysicalFileName = $dbfile
$rsfile.PhysicalFileName = $fileloc + $dbname + '_' + $cnt.tostring() + '_Data.mdf'
}
else {
#$rsfile.PhysicalFileName = $logfile
$rsfile.PhysicalFileName = $fileloc + $dbname + '_' + $cnt.tostring() + '_Log.ldf'
}
$rs.RelocateFiles.Add($rsfile)
$cnt = $cnt + 1
}

# Restore the database
$rs.SqlRestore($srv)

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

SQL Server XML Questions You Were Too Shy To Ask
 Sometimes, XML seems a bewildering convention that offers solutions to problems that the average... Read more...

Continuous Delivery and the Database
 Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.