Best script to restore multiple databases with SQL Server 2012?

Question

I have to restore around 60 SQL databases of different sizes. I googled to find a script to restore all databases after each other , just picking it 1 by 1 from my folder. I was not that successful, probably because I am quite new to SQL etc.

It can be either done in powershell or sql command line, what I found for now was something like this:

RESTORE DATABASE [db1] FROM  DISK = N'C:\folder\db1.bak' WITH  FILE = 1,  NOUNLOAD,  STATS = 10
GO
RESTORE DATABASE [db2] FROM  DISK = N'C:\folder\db2.bak' WITH  FILE = 1,  NOUNLOAD,  STATS = 10
GO

So what I would do is get the filenames from a certain folder and replace "db1.bak" etc. with the names I just picked from my folder and then run the script.

I know that there must be a smarter way, or is it maybe possible to "load" many .bak files into SSMS so the program will run them after each other?

Answer 1

The following Powershell script worked best for me.

Source: Simon Osborne's SQL Blog

$backupRoot = Get-ChildItem -Path "D:\BAK_FILE_FOLDER"
$datafilesDest = "D:\DATA_FILE_FOLDER"
$logfilesDest = "D:\LOG_FILE_FOLDER"
$server = "server\inst" 

## For each folder in the backup root directory...
#
foreach($folder in $backupRoot)
{   
    # Get the most recent .bak files for all databases...
    $backupFiles = Get-ChildItem -Path $folder.FullName -Filter "*.bak" -Recurse | Sort-Object -Property CreationTime -Descending | Select-Object -First 1


    # For each .bak file...
    foreach ($backupFile in $backupFiles)
    {
        # Restore the header to get the database name...
        $query = "RESTORE HEADERONLY FROM DISK = N'"+$backupFile.FullName+"'"
        $headerInfo = Invoke-Sqlcmd -ServerInstance $server -Query $query
        $databaseName = $headerInfo.DatabaseName

        # Restore the file list to get the logical filenames of the database files...
        $query = "RESTORE FILELISTONLY FROM DISK = N'"+$backupFile.FullName+"'"
        $files = Invoke-Sqlcmd -ServerInstance $server -Query $query

        # Differentiate data files from log files...
        $dataFile = $files | Where-Object -Property Type -EQ "D"
        $logFile = $files | Where-Object -Property Type -EQ "L"

        # Set some variables...
        $dataFileName = $dataFile.LogicalName
        $logFileName = $logFile.LogicalName

        # Set the destination of the restored files...
        $dataFileFullPath = $datafilesDest+"\"+$dataFileName+".mdf"
        $logFileFullPath = $logfilesDest+"\"+$logFileName+".ldf"

        # Create some "Relocate" file objects to pass to the Restore-SqlDatabase cmdlet...
        $RelocateData = New-Object 'Microsoft.SqlServer.Management.Smo.RelocateFile, Microsoft.SqlServer.SmoExtended, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' -ArgumentList $dataFileName, $dataFileFullPath
        $RelocateLog = New-Object 'Microsoft.SqlServer.Management.Smo.RelocateFile, Microsoft.SqlServer.SmoExtended, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' -ArgumentList $logFileName, $logFileFullPath

        # Perform the database restore... and then go around the loop.
        Restore-SqlDatabase -ServerInstance $server -Database $databaseName -BackupFile $backupFile.FullName -RelocateFile @($RelocateData,$RelocateLog) -ReplaceDatabase
    }
} 

Answer 2

Script of Collet with some adaptations worked for me.

First enable xp_cmdshell by running the following SQL commands:

-- To allow advanced options to be changed.  
EXEC sp_configure 'show advanced options', 1;  
GO  
-- To update the currently configured value for advanced options.  
RECONFIGURE;  
GO  
-- To enable the feature.  
EXEC sp_configure 'xp_cmdshell', 1;  
GO  
-- To update the currently configured value for this feature.  
RECONFIGURE;  
GO 

Next, update the script below to restore the databases from the backup files. Replace C:\Backup\ with your local directory containing your .BAK files and replace C:\Microsoft SQL Server\SQLINSTANCE\MSSQL\DATA\ with your SQL Server data directory. This script also assumes the .BAK file names match the database names, otherwise the script will likely throw errors about logical file names not matching.

DECLARE @FilesCmdshell TABLE (
    outputCmd NVARCHAR (255)
)   
DECLARE @FilesCmdshellCursor CURSOR 
DECLARE @FilesCmdshellOutputCmd AS NVARCHAR(255)

INSERT INTO @FilesCmdshell (outputCmd) EXEC master.sys.xp_cmdshell 'dir /B  C:\Backup\*.bak'    
SET @FilesCmdshellCursor = CURSOR FOR SELECT outputCmd FROM @FilesCmdshell

OPEN @FilesCmdshellCursor
FETCH NEXT FROM @FilesCmdshellCursor INTO @FilesCmdshellOutputCmd
WHILE @@FETCH_STATUS = 0
BEGIN   
    DECLARE @sqlRestore NVARCHAR(MAX) = 'RESTORE DATABASE [' + SUBSTRING(@FilesCmdshellOutputCmd, 0, CHARINDEX('.', @FilesCmdshellOutputCmd)) + '] FROM  DISK = N''C:\Backup\' + SUBSTRING(@FilesCmdshellOutputCmd, 0, CHARINDEX('.', @FilesCmdshellOutputCmd)) + '.bak'' WITH  FILE = 1,  MOVE N''' + SUBSTRING(@FilesCmdshellOutputCmd, 0, CHARINDEX('.', @FilesCmdshellOutputCmd)) + ''' TO N''C:\Microsoft SQL Server\SQLINSTANCE\MSSQL\DATA\' + SUBSTRING(@FilesCmdshellOutputCmd, 0, CHARINDEX('.', @FilesCmdshellOutputCmd)) + '.mdf'',  MOVE N''' + SUBSTRING(@FilesCmdshellOutputCmd, 0, CHARINDEX('.', @FilesCmdshellOutputCmd)) + '_log'' TO N''C:\Microsoft SQL Server\SQLINSTANCE\MSSQL\DATA\' + SUBSTRING(@FilesCmdshellOutputCmd, 0, CHARINDEX('.', @FilesCmdshellOutputCmd)) + '_log.ldf'', NOUNLOAD,  STATS = 10'
    EXEC(@sqlRestore)

    FETCH NEXT FROM @FilesCmdshellCursor INTO @FilesCmdshellOutputCmd
END