Day 12 – Moving and renaming files with Restore-DbaDatabase – 31 days of dbatools backup and restores

Moving and Renaming files with Restore-DbaDatabase

Yesterday we looked at renaming databases while restoring them. The observant may have wondered how we can avoid filename conflicts as well. We’ll be covering that today

There are quiet a number of reasons why you’d want to change filenames or paths for data file while you’re restoring them:

• Avoid a filename conflict
• Original FilePaths don’t exist on target server
• Different disk layouts
• Just because you’ve never liked the naming schema

We’ll be going through a lot of examples today so this will be quite a long post.

One caveat. Due to SQL Server’s restore not supporting it we can only change the Physical name of the data file during the restore. To change the logical name you need to use the Rename-DbaDatabse function after the restore has completed.

Let’s look at some examples:

SQL Server Instance Default Paths

Each SQL Server instance has a set of default folders, which are where data files will be created if you don’t specify anywhere else.

On my test SQLExpress instance the defauls are:

    ComputerName : WIN-0USQQH9BDOG
    InstanceName : SQLEXPRESS
    SqlInstance  : WIN-0USQQH9BDOG\SQLEXPRESS
    Data         : C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA
    Log          : C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\LOGFILES
    Backup       : C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\Backup
    ErrorLog     : C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\Log

And on my Developer edition instance they are:

    ComputerName : WIN-0USQQH9BDOG
    InstanceName : MSSQLSERVER
    SqlInstance  : WIN-0USQQH9BDOG
    Data         : C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA
    Log          : C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\LOGFILES
    Backup       : C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup
    ErrorLog     : C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log

So if I tried restoring a backup from Express to Enterprise I’d get an error as the file paths don’t exist.

A quick fix for this is to use the DestinationDefaultDirectories switch. This will cause Restore-DbaDatabase to move the files so that they’ll now be in the default folder for the destination:

    Restore-DbaDatabase -SqlInstance localhost -Path c:\backups\sqlexpress -DestinationDefaultDirectories

So now all restored database files would be moved into in to ‘C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\LOGFILES’ or ‘C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA’ depending on which type of file they are.

This is the default action for Restore-DbaDatabase. Specifying it explicitly on the command line makes it easier for others to understand what’s going on if they’re not up to speed with Restore-DbaDatabase

Destination by file type

Perhaps you’ve added extra storage to the target server, and rather than setting the default paths you just know that you want to make files to:

    D:\DataFiles
    E:\LogFiles
    F:\FileStream

In that case we have the DestinationDataDirectory, DestinationLogDirectory and DestinationFileStreamDirectory parameters for you. Each one does what it says on the tin. You use them to specify the path you want each particular type of file to.

    Restore-DbaDatabase -SqlInstance localhost -Path c:\backups\sqlexpress -DestinationDataDirectory D:\DataFiles -DestinationLogDirectory E:\LogFiles -DestinationFileStreamDirectory F:\FileStream

As a fallback, any file that isn’t identified as a Transaction Log file or a FileStream file will end up in the DestinationDataDirectory

They can be used independantly of each other. For example, if you wanted to use the default paths for log and data files, but put FileStream data into it’s own folder you’d use:

    Restore-DbaDatabase -SqlInstance localhost -Path c:\backups\sqlexpress -DestinationFileStreamDirectory F:\FileStream

And it’ll all get sorted out in the background

FileName Suffix and Prefix

It might be that you don’t want to so anything more that alter the filename to avoid a clash. Perhaps you’re wanting to restore all of the prod databases to create dev instances, and because you’re tight on resources you want to do it do the same SQL Server instance (I won’t judge!).

What we’ll do is,

Restore all the databases from their latest backup
Rename all the databases so their name is prefixed with Dev
Rename all the database files so there filename is prefixed with Dev

This is easily done with the following command:

    Restore-DbaDatabase -SqlInstance localhost -Path c:\backups\ -RestoredDatabaseNamePrefix Dev -DestinationFilePrefix Dev

and we’re done. This is also good for building up instances with lots of databases for testing. Lets say we wanted 5 times as many databases in an instance for some load testing:

    1..5 | Foreach {Restore-DbaDatabase -SqlInstance localhost -Path c:\backups\ -RestoredDatabaseNamePrefix $_ -DestinationFilePrefix $_}

If you’ve not met the construct ‘1..5’ before, this is PowerShell shorthand for creating the number seriess 1,2,3,4,5. Give any pair of numbers x..y it will create the integers between x and y, including x and y. If x is greater than y, then it will generate them in a descending seriess

In this case it will restore all the databases with the databasename and files prefixed with 1, then again prefixed with 2 and so on until it’s done it 5 times.

Or if you’ve a group of developers, Alice, Bob, Carol, Dan, and Elaine who all want their own dev database:

('Alice', 'Bob', 'Carol', 'Dan', 'Elaine') | Foreach {Restore-DbaDatabase -SqlInstance localhost -Path c:\backups\ -RestoredDatabaseNameSuffix "_$_" -DestinationFilePrefix $_}

You’ve now given them all their own database identiied with their name

FileMapping

FileMapping gives you the most options for renaming or moving files. You can rename whichever files you want to whatver you want them to be. However, it is the most complex to setup up.

The FileMapping parameter expects a PowerShell hashtable, mapping logical filenames to physical filenames. This could look like:

    $fileMap = @{
        'DataFile1' = 'd:\data\datafile_1.mdf'
        'LogFile'   = 'e:\logs\logfile.ldf'
    }

If you’ve not come across one before a hashtable is a key/value array. In the above example the Logical filename is the Key and the physical filename is the value.

In $fileMap we’re telling Restore-DbaDatabase that we want the file with Logical filename ‘DataFile1’ to be restored to ‘d:\data\datafile_1.mdf’, and the one with the logical filename ‘LogFile’ to be restored to the physical location ‘e:\logs\logfile.ldf’

You can have as many entries in the Filemapping hashtable as you want:

    $fileMap = @{
        'DataFile1' = 'd:\data\datafile_1.mdf'
        'DataFile2' = 'd:\data\datafile_2.mdf'
        'DataFile3' = 'f:\data\accounts_1.mdf'
        'LogFile'   = 'e:\logs\logfile.ldf'
    }

Any files not in the hash table will go to the default location on the target server.

Creating the hashtable may look like a lot of work, especially if you’re moving an instance with a lot of files or databases. But thanks to the wonders of PowerShell and dbatools, we can do this pretty simply.

If you want to just create the FileMapping hashtable for a single database, this will do you:

$hashTable = @{}
Get-DbaDbFile -SqlInstance server1\instance -Database roles1 | Select-Object LogicalName, PhysicalName | ForEach {$ht[$_.LogicalName] = $_.PhysicalName}

Firs we create an emtpy HashTable called $ht. Get-DbaDbFile will list all of the files used by a specific database, we take LogicalName and PhysicalName from the output, and then for each file returned we add it to $ht.

If you want to do it for everydatabase on the instances, then just omit the Database parameter:

$hashTable = @{}
Get-DbaDbFile -SqlInstance server1\instance -Database roles1 | Select-Object LogicalName, PhysicalName | ForEach {$ht[$_.LogicalName] = $_.PhysicalName}

This will include the files for tempdb, master, model and msdb, but as you won’t be restoring those they’ll be ignored.

If you’re not happy with editing a hashtable directly, we can dump it to JSON, edit in the text editor of your choice, and then convert it back:

$hashTable | Convert-ToJson | Out-File ./hashtable.json
code ./hashtable.json
# Edit json, save file when done
$hashTable = ConverFrom-Json -InputObject (Get-Content ./hashtable.json -raw)

and you’re good to go.

Conclusions

Restore-DbaDatabase makes it simple to rename files during restore. We’ve tried to provide as many logical ways as we can to rename your files, and hopefully we’ve caught every way you’d like to do it. If not, let us know via Slack or Github Issues

All posts in this series can be found at 31 Days of dbatools Backup and Restores