SQL Server 2008 R2: Auto Restore Multiple Databases in One Go using Powershell (Windows Server 2008 R2)

I'm using the PS script available at https://gallery.technet.microsoft.com/scriptcenter/SQL-Server-Auto-Restore-08d8c77f#content to Auto Restore Multiple SQL Databases.... I've completed the steps to add the SQLPS module to the 2008 R2 server as advised at http://www.databasejournal.com/features/mssql/microsoft-sql-server-2012-sqlps.html ... However, I'm getting the below errors when executing the restore:

PS C:\Users\Administrator\Documents\WindowsPowerShell> ./mssql_AutoRestoreMultipleDatabasesInOneGo.ps1 "C:\BackupDB\" "M
RT-HSWS2008R2-" "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA" "C:\Program Files\Microsoft SQL S
erver\MSSQL10.MSSQLSERVER\MSSQL\Log"       (This is the command to launch the script)

(These are the errors on 2nd run – still troubleshooting)
New-Object : Cannot find type [Microsoft.SqlServer.Management.Smo.Server]: make sure the assembly containing this type
is loaded.
At C:\Users\Administrator\Documents\WindowsPowerShell\mssql_AutoRestoreMultipleDatabasesInOneGo.ps1:25 char:25
+     $server = New-Object <<<<      Microsoft.SqlServer.Management.Smo.Server($sqlserver)
    + CategoryInfo          : InvalidType: (:) [New-Object], PSArgumentException
    + FullyQualifiedErrorId : TypeNotFound,Microsoft.PowerShell.Commands.NewObjectCommand

New-Object : Cannot find type [Microsoft.SqlServer.Management.Smo.Restore]: make sure the assembly containing this type
is loaded.
At C:\Users\Administrator\Documents\WindowsPowerShell\mssql_AutoRestoreMultipleDatabasesInOneGo.ps1:27 char:26
+     $restore = New-Object <<<<      Microsoft.SqlServer.Management.Smo.Restore
    + CategoryInfo          : InvalidType: (:) [New-Object], PSArgumentException
    + FullyQualifiedErrorId : TypeNotFound,Microsoft.PowerShell.Commands.NewObjectCommand

More in the attachment...

Please advise. Thank you!
AutoRestoreMultipleDatabasesInOneGo.docx
Noah_Williams35Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
check this solution

Backup Systemdatabases with powershell (like master or model db)
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/9b02b0fd-0490-4df1-8ca4-5feb38f96de9/backup-systemdatabases-with-powershell-like-master-or-model-db?forum=sqltools


"You have to modify the line to point to the backup folder

$backupDirectory = "C:\SQL Backup"   ##"Path"
or

$backupDirectory = $args[0]
and pass the backup path as parameter to the script"
Noah_Williams35Author Commented:
I'm now getting this error:
The term 'Restore-SqlDatabase' is not recognized as the name of a cmdlet, funct
ion, script file, or operable program. Check the spelling of the name, or if a
path was included, verify that the path is correct and try again.
At C:\Users\Administrator\Documents\WindowsPowerShell\mssql_AutoRestoreMultiple
DatabasesInOneGo.ps1:156 char:20
+ Restore-SqlDatabase <<<<  -ServerInstance $sqlserver -Database $dbname -Backu
pFile $bkfilepath -RelocateFile $rfl -NoRecovery
    + CategoryInfo          : ObjectNotFound: (Restore-SqlDatabase:String) [],
    CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException

The term 'Restore-SqlDatabase' is not recognized as the name of a cmdlet, funct
ion, script file, or operable program. Check the spelling of the name, or if a
path was included, verify that the path is correct and try again.
At C:\Users\Administrator\Documents\WindowsPowerShell\mssql_AutoRestoreMultiple
DatabasesInOneGo.ps1:156 char:20
+ Restore-SqlDatabase <<<<  -ServerInstance $sqlserver -Database $dbname -Backu
pFile $bkfilepath -RelocateFile $rfl -NoRecovery
    + CategoryInfo          : ObjectNotFound: (Restore-SqlDatabase:String) [],
    CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException

The term 'Restore-SqlDatabase' is not recognized as the name of a cmdlet, funct
ion, script file, or operable program. Check the spelling of the name, or if a
path was included, verify that the path is correct and try again.
At C:\Users\Administrator\Documents\WindowsPowerShell\mssql_AutoRestoreMultiple
DatabasesInOneGo.ps1:156 char:20
+ Restore-SqlDatabase <<<<  -ServerInstance $sqlserver -Database $dbname -Backu
pFile $bkfilepath -RelocateFile $rfl -NoRecovery
    + CategoryInfo          : ObjectNotFound: (Restore-SqlDatabase:String) [],
    CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException

Open in new window

Noah_Williams35Author Commented:
It seems that I'm missing cmdlets:

Windows PowerShell
Copyright (C) 2009 Microsoft Corporation. All rights reserved.

PS C:\Users\Administrator> Get-Module -ListAvailable

ModuleType Name                      ExportedCommands
---------- ----                      ----------------
Script     sqlps                     {}
Manifest   ADRMS                     {}
Manifest   AppLocker                 {}
Manifest   BestPractices             {}
Manifest   BitsTransfer              {}
Manifest   PSDiagnostics             {}
Manifest   ServerManager             {}
Manifest   TroubleshootingPack       {}
Manifest   WebAdministration         {}


PS C:\Users\Administrator> Import-Module sqlps -DisableNameChecking
SQL Server Powershell extensions are loaded.

Type "cd SQLSERVER:\" to step into the provider.

For more information, type "help SQLServer".
PS C:\Users\Administrator> Get-Command -CommandType cmdlet -Module sqlps | group-object -Property verb

Count Name                      Group
----- ----                      -----
    1 Convert                   {Convert-UrnToPath}
    1 Decode                    {Decode-SqlName}
    1 Encode                    {Encode-SqlName}
    2 Invoke                    {Invoke-PolicyEvaluation, Invoke-Sqlcmd}


PS C:\Users\Administrator> Get-Command -Module sqlps

CommandType     Name                                                Definition
-----------     ----                                                ----------
Cmdlet          Convert-UrnToPath                                   Convert-UrnToPath [-Urn] <String> [-Verbose] [-D...
Cmdlet          Decode-SqlName                                      Decode-SqlName [-SqlName] <String> [-Verbose] [-...
Cmdlet          Encode-SqlName                                      Encode-SqlName [-SqlName] <String> [-Verbose] [-...
Cmdlet          Invoke-PolicyEvaluation                             Invoke-PolicyEvaluation [-Policy] <PSObject> [-A...
Cmdlet          Invoke-Sqlcmd                                       Invoke-Sqlcmd [[-Query] <String>] [-ServerInstan...


PS C:\Users\Administrator> Get-Command -Module sqlps

CommandType     Name                                                Definition
-----------     ----                                                ----------
Cmdlet          Convert-UrnToPath                                   Convert-UrnToPath [-Urn] <String> [-Verbose] [-D...
Cmdlet          Decode-SqlName                                      Decode-SqlName [-SqlName] <String> [-Verbose] [-...
Cmdlet          Encode-SqlName                                      Encode-SqlName [-SqlName] <String> [-Verbose] [-...
Cmdlet          Invoke-PolicyEvaluation                             Invoke-PolicyEvaluation [-Policy] <PSObject> [-A...
Cmdlet          Invoke-Sqlcmd                                       Invoke-Sqlcmd [[-Query] <String>] [-ServerInstan...


PS C:\Users\Administrator> set-executionpolicy remotesigned -force
PS C:\Users\Administrator> import-module sqlps
WARNING: Some imported command names include unapproved verbs which might make them less discoverable.  Use the Verbose
 parameter for more detail or type Get-Verb to see the list of approved verbs.
PS C:\Users\Administrator> Get-Command -Module sqlps

CommandType     Name                                                Definition
-----------     ----                                                ----------
Cmdlet          Convert-UrnToPath                                   Convert-UrnToPath [-Urn] <String> [-Verbose] [-D...
Cmdlet          Decode-SqlName                                      Decode-SqlName [-SqlName] <String> [-Verbose] [-...
Cmdlet          Encode-SqlName                                      Encode-SqlName [-SqlName] <String> [-Verbose] [-...
Cmdlet          Invoke-PolicyEvaluation                             Invoke-PolicyEvaluation [-Policy] <PSObject> [-A...
Cmdlet          Invoke-Sqlcmd                                       Invoke-Sqlcmd [[-Query] <String>] [-ServerInstan...


PS C:\Users\Administrator> Get-Command -Module sqlps

CommandType     Name                                                Definition
-----------     ----                                                ----------
Cmdlet          Convert-UrnToPath                                   Convert-UrnToPath [-Urn] <String> [-Verbose] [-D...
Cmdlet          Decode-SqlName                                      Decode-SqlName [-SqlName] <String> [-Verbose] [-...
Cmdlet          Encode-SqlName                                      Encode-SqlName [-SqlName] <String> [-Verbose] [-...
Cmdlet          Invoke-PolicyEvaluation                             Invoke-PolicyEvaluation [-Policy] <PSObject> [-A...
Cmdlet          Invoke-Sqlcmd                                       Invoke-Sqlcmd [[-Query] <String>] [-ServerInstan...


PS C:\Users\Administrator>
Cloud as a Security Delivery Platform for MSSPs

Every Managed Security Service Provider (MSSP) needs a platform to deliver effective and efficient security-as-a-service to their customers. Scale, elasticity and profitability are a few of the many features that a Cloud platform offers. View our on-demand webinar to learn more!

EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
try this

PowerShell the SQL Server Way
http://sqlmag.com/powershell/powershell-sql-server-way
Noah_Williams35Author Commented:
I followed the steps, but its like I'm not getting the cmdlets I need during the process.... Restore-SQLDatabase in particular is not available on this SQLPS module:
MRT-HSWS2008R2-

Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64)   Sep 21 2011 22:45:45   Copyright (c) 1988-2008 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

38177492185275               13632886             4              2              5367701504         8796092891136  51104    252433  252433  2093056

ProductName    NULL     Microsoft SQL Server
ProductVersion                655360  10.0.5500.0
Language             1033       English (United States)
Platform              NULL     NT x64
Comments          NULL     SQL
CompanyName                NULL     Microsoft Corporation
FileDescription  NULL     SQL Server Windows NT - 64 Bit
FileVersion         NULL     2007.0100.5500.00 ((Katmai_PCU_Main).110921-2232)
InternalName    NULL     SQLSERVR
LegalCopyright  NULL     Microsoft Corp. All rights reserved.
LegalTrademarks              NULL     Microsoft SQL Server is a registered trademark of Microsoft Corporation.
OriginalFilename              NULL     SQLSERVR.EXE
PrivateBuild        NULL     NULL
SpecialBuild        360448000           NULL
WindowsVersion             498139398           6.1 (7601)
ProcessorCount                4              4
ProcessorActiveMask    4                             f
ProcessorType  8664       NULL
PhysicalMemory              5119       5119 (5367701504)
Product ID           NULL     NULL

THIS IS THE SCRIPT TO INITIALIZE SQLPS on the server:

#
# Initialize-SqlpsEnvironment.ps1
#
# Loads the SQL Server provider extensions
#
# Usage: Powershell -NoExit -Command "& '.\Initialize-SqlPsEnvironment.ps1'"
#
# Change log:
# June 14, 2008: Michiel Wories
#   Initial Version
# June 17, 2008: Michiel Wories
#   Fixed issue with path that did not allow for snapin\provider:: prefix of path
#   Fixed issue with provider variables. Provider does not handle case yet
#   that these variables do not exist (bug has been filed)

$ErrorActionPreference = "Stop"

$sqlpsreg="HKLM:\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps"

if (Get-ChildItem $sqlpsreg -ErrorAction "SilentlyContinue")
{
    throw "SQL Server Powershell is not installed."
}
else
{
    $item = Get-ItemProperty $sqlpsreg
    $sqlpsPath = [System.IO.Path]::GetDirectoryName($item.Path)
}


#
# Preload the assemblies. Note that most assemblies will be loaded when the provider
# is used. if you work only within the provider this may not be needed. It will reduce
# the shell's footprint if you leave these out.
#
$assemblylist =
"Microsoft.SqlServer.Smo",
"Microsoft.SqlServer.Dmf ",
"Microsoft.SqlServer.SqlWmiManagement ",
"Microsoft.SqlServer.ConnectionInfo ",
"Microsoft.SqlServer.SmoExtended ",
"Microsoft.SqlServer.Management.RegisteredServers ",
"Microsoft.SqlServer.Management.Sdk.Sfc ",
"Microsoft.SqlServer.SqlEnum ",
"Microsoft.SqlServer.RegSvrEnum ",
"Microsoft.SqlServer.WmiEnum ",
"Microsoft.SqlServer.ServiceBrokerEnum ",
"Microsoft.SqlServer.ConnectionInfoExtended ",
"Microsoft.SqlServer.Management.Collector ",
"Microsoft.SqlServer.Management.CollectorEnum"


foreach ($asm in $assemblylist)
{
    $asm = [Reflection.Assembly]::LoadWithPartialName($asm)
}

#
# Set variables that the provider expects (mandatory for the SQL provider)
#
Set-Variable -scope Global -name SqlServerMaximumChildItems -Value 0
Set-Variable -scope Global -name SqlServerConnectionTimeout -Value 30
Set-Variable -scope Global -name SqlServerIncludeSystemObjects -Value $false
Set-Variable -scope Global -name SqlServerMaximumTabCompletion -Value 1000

#
# Load the snapins, type data, format data
#
Push-Location
cd $sqlpsPath
Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100
Update-TypeData -PrependPath SQLProvider.Types.ps1xml
update-FormatData -prependpath SQLProvider.Format.ps1xml
Pop-Location

Write-Host -ForegroundColor Yellow 'SQL Server Powershell extensions are loaded.'
Write-Host
Write-Host -ForegroundColor Yellow 'Type "cd SQLSERVER:\" to step into the provider.'
Write-Host
Write-Host -ForegroundColor Yellow 'For more information, type "help SQLServer".'

This is the Powershell Restore Script:
I loaded these features http://www.microsoft.com/en-us/download/details.aspx?id=29065
I ran these commands http://sqlmag.com/powershell/powershell-sql-server-way
Bit, I’m not getting the Restore-SQLDatabases cmdlet and many others…

#*---------------------------------------------------------------------------------------------------------------------------- 
#  Filename       : mssql_AutoRestoreMultipleDatabasesInOneGo.ps1 
#  Purpose        : Script to restore all databases from a backup folder on to a SQL Server. 
#  Schedule       : Ad-Hoc 
#  Date           : 25-September-2014 
#  Author         : www.sherbaz.com/Sherbaz Mohamed 
#  Version        : 1 
# 
#  Important --arks:     
#  INPUT          : $path = Backup folder, $sqlserver = Destination SQL Server instance name, $datafolder = datafilelocation, $logfolder = logfilelocation 
#  VARIABLE       : NONE 
#  PARENT         : NONE 
#  CHILD          : NONE 
#  NOTE           : The database path will be retrieved from SQL Server database settings 
#---------------------------------------------------------------------------------------------------------------------------*/ 
# Usage: 
# ./mssql_AutoRestoreMultipleDatabasesInOneGo.ps1 "E:\database_Backup_Source_Folder\" "hostname\instancename" "destinationdatafolderpath" "destinationtransactionlogfolderpath" 
#  
param($path, $sqlserver, $datafolder, $logfolder) 
 
foreach($bkpfile in Get-ChildItem $path "*.bak" | Select-Object basename) 
{ 
    $bkpfile = $bkpfile.BaseName 
 
    $server = New-Object     Microsoft.SqlServer.Management.Smo.Server($sqlserver) 
 
    $restore = New-Object     Microsoft.SqlServer.Management.Smo.Restore 
 
    $restore.Devices.AddDevice($path+'\'+$bkpfile+'.bak',  
      [Microsoft.SqlServer.Management.Smo.DeviceType]::File) 
   
    $header = $restore.ReadBackupHeader($server) 
 
    if($header.Rows.Count -eq 1) 
    { 
      $dbname = $header.Rows[0]["DatabaseName"] 
    } 
 
 
 
    # .\001_restore.ps1 . $path"\"$bkpfile".bak" $dbname 
 
    # param($sqlserver, $bkfilepath, $dbname) 
        $bkfilepath = $path + "\"+ $bkpfile + ".bak" 
 
# Connect to the specified instance 
 
$srv = new-object ('Microsoft.SqlServer.Management.Smo.Server') $sqlserver 
 
  
 
# Get the default file and log locations 
 
# (If DefaultFile and DefaultLog are empty, use the MasterDBPath and MasterDBLogPath values) 
 
if(!$datafolder) 
{ 
    $fileloc = $srv.Settings.DefaultFile 
} 
else { $fileloc = $datafolder} 
 
if(!$logfolder) 
{ 
    $logloc = $logloc = $srv.Settings.DefaultLog 
} 
else { $logloc = $logfolder} 
 
 
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 = $bkfilepath 
 
$dbname = $dbname 
 
  
 
# Build the physical file names for the database copy 
if($fileloc -eq $logloc) 
{ 
 
    $dbfile = $fileloc + '\Data\'+ $dbname + '_Data.mdf' 
 
    $logfile = $logloc + '\Log\'+ $dbname + '_Log.ldf' 
} 
else 
{ 
    $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) 
 
$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 $sqlserver -Database $dbname -BackupFile $bkfilepath -RelocateFile $rfl -NoRecovery 
}


THESE ARE THE ERRORS BELOW:

PS SQLSERVER:\SQL\MRT-HSWS2008R2-\DEFAULT+sa\Databases\> cd C:\Users\Administrat
or\Documents\WindowsPowerShell
PS C:\Users\Administrator\Documents\WindowsPowerShell> ./mssql_AutoRestoreMultip
leDatabasesInOneGo.ps1 "C:\BackupDB\" "MRT-HSWS2008R2-" "C:\Program Files\Micros
oft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA" "C:\Program Files\Microsoft SQL S
erver\MSSQL10.MSSQLSERVER\MSSQL\Log"
Restore-SqlDatabase : The term 'Restore-SqlDatabase' is not recognized as the n
ame of a cmdlet, function, script file, or operable program. Check the spelling
of the name, or if a path was included, verify that the path is correct and tr
y again.
At C:\Users\Administrator\Documents\WindowsPowerShell\mssql_AutoRestoreMultiple
DatabasesInOneGo.ps1:156 char:20
+ Restore-SqlDatabase <<<<  -ServerInstance $sqlserver -Database $dbname -Backu
pFile $bkfilepath -RelocateFile $rfl -NoRecovery
    + CategoryInfo          : ObjectNotFound: (Restore-SqlDatabase:String) [],
    CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException

Restore-SqlDatabase : The term 'Restore-SqlDatabase' is not recognized as the n
ame of a cmdlet, function, script file, or operable program. Check the spelling
of the name, or if a path was included, verify that the path is correct and tr
y again.
At C:\Users\Administrator\Documents\WindowsPowerShell\mssql_AutoRestoreMultiple
DatabasesInOneGo.ps1:156 char:20
+ Restore-SqlDatabase <<<<  -ServerInstance $sqlserver -Database $dbname -Backu
pFile $bkfilepath -RelocateFile $rfl -NoRecovery
    + CategoryInfo          : ObjectNotFound: (Restore-SqlDatabase:String) [],
    CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException

Restore-SqlDatabase : The term 'Restore-SqlDatabase' is not recognized as the n
ame of a cmdlet, function, script file, or operable program. Check the spelling
of the name, or if a path was included, verify that the path is correct and tr
y again.
At C:\Users\Administrator\Documents\WindowsPowerShell\mssql_AutoRestoreMultiple
DatabasesInOneGo.ps1:156 char:20
+ Restore-SqlDatabase <<<<  -ServerInstance $sqlserver -Database $dbname -Backu
pFile $bkfilepath -RelocateFile $rfl -NoRecovery
    + CategoryInfo          : ObjectNotFound: (Restore-SqlDatabase:String) [],
    CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException

Restore-SqlDatabase : The term 'Restore-SqlDatabase' is not recognized as the n
ame of a cmdlet, function, script file, or operable program. Check the spelling
of the name, or if a path was included, verify that the path is correct and tr
y again.
At C:\Users\Administrator\Documents\WindowsPowerShell\mssql_AutoRestoreMultiple
DatabasesInOneGo.ps1:156 char:20
+ Restore-SqlDatabase <<<<  -ServerInstance $sqlserver -Database $dbname -Backu
pFile $bkfilepath -RelocateFile $rfl -NoRecovery
    + CategoryInfo          : ObjectNotFound: (Restore-SqlDatabase:String) [],
    CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException

Restore-SqlDatabase : The term 'Restore-SqlDatabase' is not recognized as the n
ame of a cmdlet, function, script file, or operable program. Check the spelling
of the name, or if a path was included, verify that the path is correct and tr
y again.
At C:\Users\Administrator\Documents\WindowsPowerShell\mssql_AutoRestoreMultiple
DatabasesInOneGo.ps1:156 char:20
+ Restore-SqlDatabase <<<<  -ServerInstance $sqlserver -Database $dbname -Backu
pFile $bkfilepath -RelocateFile $rfl -NoRecovery
    + CategoryInfo          : ObjectNotFound: (Restore-SqlDatabase:String) [],
    CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException

Restore-SqlDatabase : The term 'Restore-SqlDatabase' is not recognized as the n
ame of a cmdlet, function, script file, or operable program. Check the spelling
of the name, or if a path was included, verify that the path is correct and tr
y again.
At C:\Users\Administrator\Documents\WindowsPowerShell\mssql_AutoRestoreMultiple
DatabasesInOneGo.ps1:156 char:20
+ Restore-SqlDatabase <<<<  -ServerInstance $sqlserver -Database $dbname -Backu
pFile $bkfilepath -RelocateFile $rfl -NoRecovery
    + CategoryInfo          : ObjectNotFound: (Restore-SqlDatabase:String) [],
    CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException

Restore-SqlDatabase : The term 'Restore-SqlDatabase' is not recognized as the n
ame of a cmdlet, function, script file, or operable program. Check the spelling
of the name, or if a path was included, verify that the path is correct and tr
y again.
At C:\Users\Administrator\Documents\WindowsPowerShell\mssql_AutoRestoreMultiple
DatabasesInOneGo.ps1:156 char:20
+ Restore-SqlDatabase <<<<  -ServerInstance $sqlserver -Database $dbname -Backu
pFile $bkfilepath -RelocateFile $rfl -NoRecovery
    + CategoryInfo          : ObjectNotFound: (Restore-SqlDatabase:String) [],
    CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException

Restore-SqlDatabase : The term 'Restore-SqlDatabase' is not recognized as the n
ame of a cmdlet, function, script file, or operable program. Check the spelling
of the name, or if a path was included, verify that the path is correct and tr
y again.
At C:\Users\Administrator\Documents\WindowsPowerShell\mssql_AutoRestoreMultiple
DatabasesInOneGo.ps1:156 char:20
+ Restore-SqlDatabase <<<<  -ServerInstance $sqlserver -Database $dbname -Backu
pFile $bkfilepath -RelocateFile $rfl -NoRecovery
    + CategoryInfo          : ObjectNotFound: (Restore-SqlDatabase:String) [],
    CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException

Restore-SqlDatabase : The term 'Restore-SqlDatabase' is not recognized as the n
ame of a cmdlet, function, script file, or operable program. Check the spelling
of the name, or if a path was included, verify that the path is correct and tr
y again.
At C:\Users\Administrator\Documents\WindowsPowerShell\mssql_AutoRestoreMultiple
DatabasesInOneGo.ps1:156 char:20
+ Restore-SqlDatabase <<<<  -ServerInstance $sqlserver -Database $dbname -Backu
pFile $bkfilepath -RelocateFile $rfl -NoRecovery
    + CategoryInfo          : ObjectNotFound: (Restore-SqlDatabase:String) [],
    CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException

Restore-SqlDatabase : The term 'Restore-SqlDatabase' is not recognized as the n
ame of a cmdlet, function, script file, or operable program. Check the spelling
of the name, or if a path was included, verify that the path is correct and tr
y again.
At C:\Users\Administrator\Documents\WindowsPowerShell\mssql_AutoRestoreMultiple
DatabasesInOneGo.ps1:156 char:20
+ Restore-SqlDatabase <<<<  -ServerInstance $sqlserver -Database $dbname -Backu
pFile $bkfilepath -RelocateFile $rfl -NoRecovery
    + CategoryInfo          : ObjectNotFound: (Restore-SqlDatabase:String) [],
    CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException

Restore-SqlDatabase : The term 'Restore-SqlDatabase' is not recognized as the n
ame of a cmdlet, function, script file, or operable program. Check the spelling
of the name, or if a path was included, verify that the path is correct and tr
y again.
At C:\Users\Administrator\Documents\WindowsPowerShell\mssql_AutoRestoreMultiple
DatabasesInOneGo.ps1:156 char:20
+ Restore-SqlDatabase <<<<  -ServerInstance $sqlserver -Database $dbname -Backu
pFile $bkfilepath -RelocateFile $rfl -NoRecovery
    + CategoryInfo          : ObjectNotFound: (Restore-SqlDatabase:String) [],
    CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException

Restore-SqlDatabase : The term 'Restore-SqlDatabase' is not recognized as the n
ame of a cmdlet, function, script file, or operable program. Check the spelling
of the name, or if a path was included, verify that the path is correct and tr
y again.
At C:\Users\Administrator\Documents\WindowsPowerShell\mssql_AutoRestoreMultiple
DatabasesInOneGo.ps1:156 char:20
+ Restore-SqlDatabase <<<<  -ServerInstance $sqlserver -Database $dbname -Backu
pFile $bkfilepath -RelocateFile $rfl -NoRecovery
    + CategoryInfo          : ObjectNotFound: (Restore-SqlDatabase:String) [],
    CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException

Restore-SqlDatabase : The term 'Restore-SqlDatabase' is not recognized as the n
ame of a cmdlet, function, script file, or operable program. Check the spelling
of the name, or if a path was included, verify that the path is correct and tr
y again.
At C:\Users\Administrator\Documents\WindowsPowerShell\mssql_AutoRestoreMultiple
DatabasesInOneGo.ps1:156 char:20
+ Restore-SqlDatabase <<<<  -ServerInstance $sqlserver -Database $dbname -Backu
pFile $bkfilepath -RelocateFile $rfl -NoRecovery
    + CategoryInfo          : ObjectNotFound: (Restore-SqlDatabase:String) [],
    CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException

Restore-SqlDatabase : The term 'Restore-SqlDatabase' is not recognized as the n
ame of a cmdlet, function, script file, or operable program. Check the spelling
of the name, or if a path was included, verify that the path is correct and tr
y again.
At C:\Users\Administrator\Documents\WindowsPowerShell\mssql_AutoRestoreMultiple
DatabasesInOneGo.ps1:156 char:20
+ Restore-SqlDatabase <<<<  -ServerInstance $sqlserver -Database $dbname -Backu
pFile $bkfilepath -RelocateFile $rfl -NoRecovery
    + CategoryInfo          : ObjectNotFound: (Restore-SqlDatabase:String) [],
    CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException

Restore-SqlDatabase : The term 'Restore-SqlDatabase' is not recognized as the n
ame of a cmdlet, function, script file, or operable program. Check the spelling
of the name, or if a path was included, verify that the path is correct and tr
y again.
At C:\Users\Administrator\Documents\WindowsPowerShell\mssql_AutoRestoreMultiple
DatabasesInOneGo.ps1:156 char:20
+ Restore-SqlDatabase <<<<  -ServerInstance $sqlserver -Database $dbname -Backu
pFile $bkfilepath -RelocateFile $rfl -NoRecovery
    + CategoryInfo          : ObjectNotFound: (Restore-SqlDatabase:String) [],
    CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException

Restore-SqlDatabase : The term 'Restore-SqlDatabase' is not recognized as the n
ame of a cmdlet, function, script file, or operable program. Check the spelling
of the name, or if a path was included, verify that the path is correct and tr
y again.
At C:\Users\Administrator\Documents\WindowsPowerShell\mssql_AutoRestoreMultiple
DatabasesInOneGo.ps1:156 char:20
+ Restore-SqlDatabase <<<<  -ServerInstance $sqlserver -Database $dbname -Backu
pFile $bkfilepath -RelocateFile $rfl -NoRecovery
    + CategoryInfo          : ObjectNotFound: (Restore-SqlDatabase:String) [],
    CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException

Restore-SqlDatabase : The term 'Restore-SqlDatabase' is not recognized as the n
ame of a cmdlet, function, script file, or operable program. Check the spelling
of the name, or if a path was included, verify that the path is correct and tr
y again.
At C:\Users\Administrator\Documents\WindowsPowerShell\mssql_AutoRestoreMultiple
DatabasesInOneGo.ps1:156 char:20
+ Restore-SqlDatabase <<<<  -ServerInstance $sqlserver -Database $dbname -Backu
pFile $bkfilepath -RelocateFile $rfl -NoRecovery
    + CategoryInfo          : ObjectNotFound: (Restore-SqlDatabase:String) [],
    CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException

PS C:\Users\Administrator\Documents\WindowsPowerShell>

Open in new window

Noah_Williams35Author Commented:
As per my finding on the issue, the commands : Backup-SqlDatabase, Restore-SqlDatabase, Backup-ASDatabase & Restore-ASDatabase
 
Where introduced for SQL Server 2012 and Not for SQL Server 2008
 
So when we are trying to using these commands we are getting the error the commands are not available which is correct as they are not available for SQL Server 2008…
 
Article :
1.       Backup and Restore SQL Server with the SQL Server 2012 PowerShell cmdlets : https://www.simple-talk.com/sql/backup-and-recovery/backup-and-restore-sql-server-with-the-sql-server-2012-powershell-cmdlets/
2.       Import the SQLPS Module(Available for SQL 2012 / SQL 2016): https://msdn.microsoft.com/en-IN/library/hh231286(v=sql.110).aspx
3.       PowerShell the SQL Server Way(For SQL 2012): http://sqlmag.com/powershell/powershell-sql-server-way
 
Now even when I download  the module and try using it with SQL 2008 it will not work, and I tried various setups as recommended by sites working in SQL 2012, but that suggested unproven methods no one has demonstrated to work for SQL 2008. My conclusion is that the SQLPS method will not work with our version of SQL Server, however there are other options.
 
Now in-order to do a restore with PowerShell we can try this (I have not Tested It yet, this is the next step in troubleshooting):
1.       https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.restore.sqlrestore.aspx
2.       http://www.morgantechspace.com/2014/11/Powershell-script-to-Backup-and-Restore-SQL-Database.html

What do you think?
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
sounds good
try this example:

Restore-SqlDb Automate a SQL Server Database Restore improved with Powershell
https://www.mssqltips.com/sqlservertip/2218/restoresqldb-automate-a-sql-server-database-restore-improved-with-powershell/
Noah_Williams35Author Commented:
stackoverflow.com/questions/22207519/need-help-to-restore-multiple-databases-bak-files-from-one-folder

Then, I entered executed the SP as follows –

exec sp_RestoreFromAllFilesInDirectory 'C:\BackupDB\', 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\' , 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\'

OUTPUT
RESTORE DATABASE Acromil FROM DISK = 'C:\BackupDB\Acromil_FULL_COPY_ONLY_20150929_134612.bak' WITH MOVE 'CRIBMASTER' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\CRIBMASTER.mdf', MOVE 'CRIBMASTER_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\CRIBMASTER_log.ldf'
Processed 1824 pages for database 'Acromil', file 'CRIBMASTER' on file 1.
Processed 1 pages for database 'Acromil', file 'CRIBMASTER_log' on file 1.
RESTORE DATABASE successfully processed 1825 pages in 0.911 seconds (15.643 MB/sec).
RESTORE DATABASE AIR-ADV-TEST FROM DISK = 'C:\BackupDB\AIR-ADV-TEST_FULL_COPY_ONLY_20150929_134612.bak' WITH MOVE 'Blank_DATA' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Blank_DATA.mdf', MOVE 'Blank_LOG' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Blank_LOG.ldf'
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '-'.
Msg 319, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
RESTORE DATABASE AstroSpar FROM DISK = 'C:\BackupDB\AstroSpar_FULL_COPY_ONLY_20150929_134706.bak' WITH MOVE 'AstroSpar2' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\AstroSpar2.mdf', MOVE 'AstroSpar2_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\AstroSpar2_log.ldf'
Processed 1640 pages for database 'AstroSpar', file 'AstroSpar2' on file 1.
Processed 1 pages for database 'AstroSpar', file 'AstroSpar2_log' on file 1.
RESTORE DATABASE successfully processed 1641 pages in 0.809 seconds (15.838 MB/sec).
RESTORE DATABASE BucherHydraulics FROM DISK = 'C:\BackupDB\BucherHydraulics_FULL_COPY_ONLY_20150929_134707.bak' WITH MOVE 'BucherHydraulics' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\BucherHydraulics.mdf', MOVE 'BucherHydraulics_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\BucherHydraulics_log.ldf'
Processed 888 pages for database 'BucherHydraulics', file 'BucherHydraulics' on file 1.
Processed 1 pages for database 'BucherHydraulics', file 'BucherHydraulics_log' on file 1.
RESTORE DATABASE successfully processed 889 pages in 0.770 seconds (9.011 MB/sec).
RESTORE DATABASE CM10BETA1 FROM DISK = 'C:\BackupDB\CM10BETA1_FULL_COPY_ONLY_20150929_134707.bak' WITH MOVE '_Blank-9.6_DATA' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\_Blank-9.6_DATA.mdf', MOVE '_Blank-9.6_LOG' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\_Blank-9.6_LOG.ldf'
Processed 520 pages for database 'CM10BETA1', file '_Blank-9.6_DATA' on file 1.
Processed 1 pages for database 'CM10BETA1', file '_Blank-9.6_LOG' on file 1.
RESTORE DATABASE successfully processed 521 pages in 0.378 seconds (10.749 MB/sec).
RESTORE DATABASE CM10BETA2 FROM DISK = 'C:\BackupDB\CM10BETA2_FULL_COPY_ONLY_20150929_134707.bak' WITH MOVE '_Blank-9.6_DATA' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\_Blank-9.6_DATA.mdf', MOVE '_Blank-9.6_LOG' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\_Blank-9.6_LOG.ldf'
Msg 1834, Level 16, State 1, Line 1
The file 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\_Blank-9.6_DATA.mdf' cannot be overwritten.  It is being used by database 'CM10BETA1'.
Msg 3156, Level 16, State 4, Line 1
File '_Blank-9.6_DATA' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\_Blank-9.6_DATA.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 1834, Level 16, State 1, Line 1
The file 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\_Blank-9.6_LOG.ldf' cannot be overwritten.  It is being used by database 'CM10BETA1'.
Msg 3156, Level 16, State 4, Line 1
File '_Blank-9.6_LOG' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\_Blank-9.6_LOG.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
RESTORE DATABASE CM10TEST1 FROM DISK = 'C:\BackupDB\CM10TEST1_FULL_COPY_ONLY_20150929_134708.bak' WITH MOVE '_Blank-9.6_DATA' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\_Blank-9.6_DATA.mdf', MOVE '_Blank-9.6_LOG' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\_Blank-9.6_LOG.ldf'
Msg 1834, Level 16, State 1, Line 1
The file 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\_Blank-9.6_DATA.mdf' cannot be overwritten.  It is being used by database 'CM10BETA1'.
Msg 3156, Level 16, State 4, Line 1
File '_Blank-9.6_DATA' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\_Blank-9.6_DATA.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 1834, Level 16, State 1, Line 1
The file 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\_Blank-9.6_LOG.ldf' cannot be overwritten.  It is being used by database 'CM10BETA1'.
Msg 3156, Level 16, State 4, Line 1
File '_Blank-9.6_LOG' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\_Blank-9.6_LOG.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
RESTORE DATABASE CMPendingDemo FROM DISK = 'C:\BackupDB\CMPendingDemo_FULL_COPY_ONLY_20150929_134708.bak' WITH MOVE 'CMPendingDemo' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\CMPendingDemo.mdf', MOVE 'CMPendingDemo_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\CMPendingDemo_log.ldf'
Processed 184 pages for database 'CMPendingDemo', file 'CMPendingDemo' on file 1.
Processed 1 pages for database 'CMPendingDemo', file 'CMPendingDemo_log' on file 1.
RESTORE DATABASE successfully processed 185 pages in 0.172 seconds (8.363 MB/sec).
RESTORE DATABASE DemoRoom FROM DISK = 'C:\BackupDB\DemoRoom_FULL_COPY_ONLY_20150929_134709.bak' WITH MOVE 'CMDEMO' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\CMDEMO.mdf', MOVE 'CMDEMO_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\CMDEMO_log.ldf'
Processed 92936 pages for database 'DemoRoom', file 'CMDEMO' on file 1.
Processed 1 pages for database 'DemoRoom', file 'CMDEMO_log' on file 1.
RESTORE DATABASE successfully processed 92937 pages in 44.304 seconds (16.388 MB/sec).
RESTORE DATABASE GKN FROM DISK = 'C:\BackupDB\GKN_FULL_COPY_ONLY_20150929_134733.bak' WITH MOVE '2005Blank25' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\2005Blank25.mdf', MOVE '2005Blank25_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\2005Blank25_log.ldf'
Processed 14536 pages for database 'GKN', file '2005Blank25' on file 1.
Processed 1 pages for database 'GKN', file '2005Blank25_log' on file 1.
RESTORE DATABASE successfully processed 14537 pages in 7.089 seconds (16.019 MB/sec).
Msg 8114, Level 16, State 1, Line 1
Error converting data type bigint to int.
Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.
RESTORE DATABASE GRA-ADV FROM DISK = 'C:\BackupDB\GRA-ADV_FULL_COPY_ONLY_20150929_134736.bak' WITH MOVE 
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '-'.
Msg 319, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
RESTORE DATABASE HIT1-CUR FROM DISK = 'C:\BackupDB\HIT1-CUR_FULL_COPY_ONLY_20150929_135223.bak' WITH MOVE 'HIT1_DATA' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\HIT1_DATA.mdf', MOVE 'HIT1_LOG' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\HIT1_LOG.ldf'
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '-'.
Msg 319, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
RESTORE DATABASE HIT1-OLD FROM DISK = 'C:\BackupDB\HIT1-OLD_FULL_COPY_ONLY_20150929_135229.bak' WITH MOVE 'HIT1_DATA' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\HIT1_DATA.mdf', MOVE 'HIT1_LOG' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\HIT1_LOG.ldf'
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '-'.
Msg 319, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
RESTORE DATABASE MEX-ADV-1 FROM DISK = 'C:\BackupDB\MEX-ADV-1_FULL_COPY_ONLY_20150929_135235.bak' WITH MOVE 'MEX-ADV_DATA' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\MEX-ADV_DATA.mdf', MOVE 'MEX-ADV_LOG' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\MEX-ADV_LOG.ldf'
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '-'.
Msg 319, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
RESTORE DATABASE MEX-ADV-TEST FROM DISK = 'C:\BackupDB\MEX-ADV-TEST_FULL_COPY_ONLY_20150929_135239.bak' WITH MOVE 'MEX-ADV_DATA' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\MEX-ADV_DATA.mdf', MOVE 'MEX-ADV_LOG' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\MEX-ADV_LOG.ldf'
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '-'.
Msg 319, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
RESTORE DATABASE ShawSunland FROM DISK = 'C:\BackupDB\ShawSunland_FULL_COPY_ONLY_20150929_135244.bak' WITH MOVE 'Blank_DATA' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Blank_DATA.mdf', MOVE 'Blank_LOG' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Blank_LOG.ldf'
Processed 10208 pages for database 'ShawSunland', file 'Blank_DATA' on file 1.
Processed 1 pages for database 'ShawSunland', file 'Blank_LOG' on file 1.
RESTORE DATABASE successfully processed 10209 pages in 5.176 seconds (15.407 MB/sec).
RESTORE DATABASE _DBA FROM DISK = 'C:\BackupDB\_DBA_FULL_COPY_ONLY_20150929_134611.bak' WITH MOVE '_DBA' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\_DBA.mdf', MOVE '_DBA_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\_DBA_log.ldf'
Processed 352 pages for database '_DBA', file '_DBA' on file 1.
Processed 1 pages for database '_DBA', file '_DBA_log' on file 1.
RESTORE DATABASE successfully processed 353 pages in 0.749 seconds (3.678 MB/sec).

Open in new window


NOTE: I should see at least the successfully completed databases after I uncommented “EXEC(@sql)” in the SP, but after restarting localhost I still don’t see them. There are also some syntax errors on some of the databases. I’ll be troubleshooting and reporting on these with a further update. I feel that a solution must be near.
Noah_Williams35Author Commented:
I found the TSQL Script that works! http://www.karaszi.com/sqlserver/code/sp_RestoreFromAllFilesInDirectory_2008sp1.txt

The only problem now is I need an edit that will not give a syntax error on dashes (-) in the database names for the MDF and LDF files. Also, I get an error if the Logical Name of the database is the same as another database from backup, even when the MDF and LDF names are unique; Is there a way around this in the script?

The databases are backed up from a working server, so I'm hoping some edits to the script will repair the issues the duplicate Logical Names and dashes (-) in the MDF and LDF file names.

See the error:

exec sp_RestoreFromAllFilesInDirectory 'C:\BackupDB\', 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\' , 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\'

RESTORE DATABASE AIR-ADV-TEST FROM DISK = 'C:\BackupDB\AIR_ADV_TEST_FULL_20150928_152942.bak' WITH MOVE 'Blank_DATA' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Blank_DATA.mdf', MOVE 'Blank_LOG' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Blank_LOG.ldf'
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '-'.
Msg 319, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 8114, Level 16, State 1, Line 1
Error converting data type bigint to int.
Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.
RESTORE DATABASE GRA-ADV FROM DISK = 'C:\BackupDB\GRA_ADV_FULL_20150928_153035.bak' WITH MOVE
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '-'.
Msg 319, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
RESTORE DATABASE HIT1-CUR FROM DISK = 'C:\BackupDB\HIT1_CUR_FULL_20150928_153339.bak' WITH MOVE 'HIT1_DATA' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\HIT1_DATA.mdf', MOVE 'HIT1_LOG' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\HIT1_LOG.ldf'
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '-'.
Msg 319, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
RESTORE DATABASE HIT1-OLD FROM DISK = 'C:\BackupDB\HIT1_OLD_FULL_20150928_153344.bak' WITH MOVE 'HIT1_DATA' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\HIT1_DATA.mdf', MOVE 'HIT1_LOG' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\HIT1_LOG.ldf'
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '-'.
Msg 319, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
RESTORE DATABASE MEX-ADV-1 FROM DISK = 'C:\BackupDB\MEX_ADV_1_FULL_20150928_153348.bak' WITH MOVE 'MEX-ADV_DATA' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\MEX-ADV_DATA.mdf', MOVE 'MEX-ADV_LOG' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\MEX-ADV_LOG.ldf'
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '-'.
Msg 319, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
RESTORE DATABASE MEX-ADV-TEST FROM DISK = 'C:\BackupDB\MEX_ADV_TEST_FULL_20150928_153351.bak' WITH MOVE 'MEX-ADV_DATA' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\MEX-ADV_DATA.mdf', MOVE 'MEX-ADV_LOG' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\MEX-ADV_LOG.ldf'
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '-'.
Msg 319, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
RESTORE DATABASE CM10BETA2 FROM DISK = 'C:\BackupDB\_CM10BETA2_FULL_20150928_153016.bak' WITH MOVE '_Blank-9.6_DATA' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\_Blank-9.6_DATA.mdf', MOVE '_Blank-9.6_LOG' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\_Blank-9.6_LOG.ldf'
Msg 1834, Level 16, State 1, Line 1
The file 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\_Blank-9.6_DATA.mdf' cannot be overwritten.  It is being used by database 'CM10BETA1'.
Msg 3156, Level 16, State 4, Line 1
File '_Blank-9.6_DATA' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\_Blank-9.6_DATA.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 1834, Level 16, State 1, Line 1
The file 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\_Blank-9.6_LOG.ldf' cannot be overwritten.  It is being used by database 'CM10BETA1'.
Msg 3156, Level 16, State 4, Line 1
File '_Blank-9.6_LOG' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\_Blank-9.6_LOG.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
RESTORE DATABASE CM10TEST1 FROM DISK = 'C:\BackupDB\_CM10TEST1_FULL_20150928_153016.bak' WITH MOVE '_Blank-9.6_DATA' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\_Blank-9.6_DATA.mdf', MOVE '_Blank-9.6_LOG' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\_Blank-9.6_LOG.ldf'
Msg 1834, Level 16, State 1, Line 1
The file 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\_Blank-9.6_DATA.mdf' cannot be overwritten.  It is being used by database 'CM10BETA1'.
Msg 3156, Level 16, State 4, Line 1
File '_Blank-9.6_DATA' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\_Blank-9.6_DATA.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 1834, Level 16, State 1, Line 1
The file 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\_Blank-9.6_LOG.ldf' cannot be overwritten.  It is being used by database 'CM10BETA1'.
Msg 3156, Level 16, State 4, Line 1
File '_Blank-9.6_LOG' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\_Blank-9.6_LOG.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Below is the script:

CREATE PROC [dbo].[sp_RestoreFromAllFilesInDirectory]
@SourceDirBackupFiles nvarchar(200), @DestDirDbFiles nvarchar(200),@DestDirLogFiles nvarchar(200)
AS
--Originally written by Tibor Karaszi 2004. Use at own risk.
--Restores from all files in a certain directory. Assumes that:
--  There's only one backup on each backup device.
--  Each database uses only two database files and the mdf file is returned first from the RESTORE FILELISTONLY command.
--Sample execution:
-- EXEC sp_RestoreFromAllFilesInDirectory 'C:\Mybakfiles\', 'D:\Mydatabasesdirectory\' ,’C:\MylogDirectory\’
SET NOCOUNT ON

--Table to hold each backup file name in
CREATE TABLE #files(fname varchar(200),depth int, file_ int)
INSERT #files
EXECUTE master.dbo.xp_dirtree @SourceDirBackupFiles, 1, 1

--Table to hold the result from RESTORE HEADERONLY. Needed to get the database name out from
CREATE TABLE #bdev(
 BackupName nvarchar(128)
,BackupDescription nvarchar(255)
,BackupType smallint
,ExpirationDate datetime
,Compressed tinyint
,Position smallint
,DeviceType tinyint
,UserName nvarchar(128)
,ServerName nvarchar(128)
,DatabaseName nvarchar(128)
,DatabaseVersion int
,DatabaseCreationDate datetime
,BackupSize numeric(20,0)
,FirstLSN numeric(25,0)
,LastLSN numeric(25,0)
,CheckpointLSN numeric(25,0)
,DatabaseBackupLSN numeric(25,0)
,BackupStartDate datetime
,BackupFinishDate datetime
,SortOrder smallint
,CodePage smallint
,UnicodeLocaleId int
,UnicodeComparisonStyle int
,CompatibilityLevel tinyint
,SoftwareVendorId int
,SoftwareVersionMajor int
,SoftwareVersionMinor int
,SoftwareVersionBuild int
,MachineName nvarchar(128)
,Flags int
,BindingID uniqueidentifier
,RecoveryForkID uniqueidentifier
,Collation nvarchar(128)
,FamilyGUID uniqueidentifier
,HasBulkLoggedData int
,IsSnapshot int
,IsReadOnly int
,IsSingleUser int
,HasBackupChecksums int
,IsDamaged int
,BegibsLogChain int
,HasIncompleteMetaData int
,IsForceOffline int
,IsCopyOnly int
,FirstRecoveryForkID uniqueidentifier
,ForkPointLSN numeric(25,0)
,RecoveryModel nvarchar(128)
,DifferentialBaseLSN numeric(25,0)
,DifferentialBaseGUID uniqueidentifier
,BackupTypeDescription nvarchar(128)
,BackupSetGUID uniqueidentifier
,CompressedBackupSize nvarchar(128)
)

--Table to hold result from RESTORE FILELISTONLY. Need to generate the MOVE options to the RESTORE command
CREATE TABLE #dbfiles(
 LogicalName nvarchar(128)
,PhysicalName nvarchar(260)
,Type char(1)
,FileGroupName nvarchar(128)
,Size numeric(20,0)
,MaxSize numeric(20,0)
,FileId int
,CreateLSN numeric(25,0)
,DropLSN numeric(25,0)
,UniqueId uniqueidentifier
,ReadOnlyLSN numeric(25,0)
,ReadWriteLSN numeric(25,0)
,BackupSizeInBytes int
,SourceBlockSize int
,FilegroupId int
,LogGroupGUID uniqueidentifier
,DifferentialBaseLSN numeric(25)
,DifferentialBaseGUID uniqueidentifier
,IsReadOnly int
,IsPresent int
,TDEThumbprint nvarchar(128)
)


DECLARE @fname varchar(200)
DECLARE @dirfile varchar(300)
DECLARE @LogicalName nvarchar(128)
DECLARE @PhysicalName nvarchar(260)
DECLARE @type char(1)
DECLARE @DbName sysname
DECLARE @sql nvarchar(1000)

DECLARE files CURSOR FOR
SELECT fname FROM #files

DECLARE dbfiles CURSOR FOR
SELECT LogicalName, PhysicalName, Type FROM #dbfiles

OPEN files
FETCH NEXT FROM files INTO @fname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @dirfile = @SourceDirBackupFiles + @fname

--Get database name from RESTORE HEADERONLY, assumes there's only one backup on each backup file.
TRUNCATE TABLE #bdev
INSERT #bdev
EXEC('RESTORE HEADERONLY FROM DISK = ''' + @dirfile + '''')
SET @DbName = (SELECT DatabaseName FROM #bdev)

--Construct the beginning for the RESTORE DATABASE command
SET @sql = 'RESTORE DATABASE ' + @DbName + ' FROM DISK = ''' + @dirfile + ''' WITH MOVE '

--Get information about database files from backup device into temp table
TRUNCATE TABLE #dbfiles
INSERT #dbfiles
EXEC('RESTORE FILELISTONLY FROM DISK = ''' + @dirfile + '''')

OPEN dbfiles
FETCH NEXT FROM dbfiles INTO @LogicalName, @PhysicalName, @type
--For each database file that the database uses
WHILE @@FETCH_STATUS = 0
BEGIN
IF @type = 'D'
SET @sql = @sql + '''' + @LogicalName + ''' TO ''' + @DestDirDbFiles + @LogicalName  + '.mdf'', MOVE '
ELSE IF @type = 'L'
SET @sql = @sql + '''' + @LogicalName + ''' TO ''' + @DestDirLogFiles + @LogicalName  + '.ldf'''
FETCH NEXT FROM dbfiles INTO @LogicalName, @PhysicalName, @type
END

--Here's the actual RESTORE command
PRINT @sql
--Remove the comment below if you want the procedure to actually execute the restore command.
--EXEC(@sql)
CLOSE dbfiles
FETCH NEXT FROM files INTO @fname
END
CLOSE files
DEALLOCATE dbfiles
DEALLOCATE files
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
RESTORE DATABASE MEX-ADV-TEST FROM DISK = 'C:\BackupDB\MEX_ADV_TEST_FULL_20150928_153351.bak' WITH MOVE 'MEX-ADV_DATA' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\MEX-ADV_DATA.mdf', MOVE 'MEX-ADV_LOG' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\MEX-ADV_LOG.ldf'
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '-'.
Msg 319, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'with'.


you need to adjust this proc
by including [] for DB name with space

--Construct the beginning for the RESTORE DATABASE command
SET @sql = 'RESTORE DATABASE [' + @DbName + '] FROM DISK = ''' + @dirfile + ''' WITH MOVE '

-----

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
this one is self explaining : you need to modify location ...
RESTORE DATABASE CM10TEST1
Msg 1834, Level 16, State 1, Line 1
The file 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\_Blank-9.6_DATA.mdf' cannot be overwritten.  It is being used by database 'CM10BETA1
"


"
RESTORE DATABASE CM10TEST1 FROM DISK = 'C:\BackupDB\_CM10TEST1_FULL_20150928_153016.bak' WITH MOVE '_Blank-9.6_DATA' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\_Blank-9.6_DATA.mdf', MOVE '_Blank-9.6_LOG' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\_Blank-9.6_LOG.ldf'
Msg 1834, Level 16, State 1, Line 1
The file 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\_Blank-9.6_DATA.mdf' cannot be overwritten.  It is being used by database 'CM10BETA1
"
Noah_Williams35Author Commented:
EugeneZ, thanks to some of your recommendations I've edited and tweaked this script to the point now where it's working at about 98% perfection. I added a print statement to get output on the last error I'm getting on large file sizes above 2GB when restoring databases. I can't figure what to what's causing this error though... Can you give me direction? It on happens on large databases over 2GB!

Msg 8114, Level 16, State 1, Line 1
Error converting data type bigint to int.
Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.
RESTORE DATABASE [AIR-ADV-TEST] FROM DISK = 'C:\BackupDB\AIR-ADV-TEST_20151002_2.BAK' WITH MOVE
RESTORE DATABASE [BRA-ADV-TEST] FROM DISK = 'C:\BackupDB\BRA-ADV-TEST-20140502.bak' WITH MOVE 'Blank_DATA' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\BRA-ADV-TEST_Data.mdf', MOVE 'Blank_LOG' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\BRA-ADV-TEST_Log.ldf'
RESTORE DATABASE [BRA-ADV] FROM DISK = 'C:\BackupDB\BRA-ADV_FINAL.bak' WITH MOVE 'Blank_DATA' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\BRA-ADV_Data.mdf', MOVE 'Blank_LOG' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\BRA-ADV_Log.ldf'
RESTORE DATABASE [BucherHydr2T1] FROM DISK = 'C:\BackupDB\BucherHydr2_20140924.bak' WITH MOVE 'BucherHydr2T1' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\BucherHydr2T1_Data.mdf', MOVE 'BucherHydr2T1_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\BucherHydr2T1_Log.ldf'
RESTORE DATABASE [BuckeyeInd] FROM DISK = 'C:\BackupDB\BuckeyeInd_20151006.bak' WITH MOVE '_Blank-9.6_DATA' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\BuckeyeInd_Data.mdf', MOVE '_Blank-9.6_LOG' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\BuckeyeInd_Log.ldf'
RESTORE DATABASE [CNH1] FROM DISK = 'C:\BackupDB\CNH1-20140611.bak' WITH MOVE 'CNH1_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\CNH1_Data.mdf', MOVE 'CNH1_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\CNH1_Log.ldf'
RESTORE DATABASE [CNH1-TEST] FROM DISK = 'C:\BackupDB\CNH1-TEST_20150604.BAK' WITH MOVE 'CNH1_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\CNH1-TEST_Data.mdf', MOVE 'CNH1_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\CNH1-TEST_Log.ldf'
RESTORE DATABASE [CNH2] FROM DISK = 'C:\BackupDB\CNH2-20140708.bak' WITH MOVE 'CNH2_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\CNH2_Data.mdf', MOVE 'CNH2_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\CNH2_Log.ldf'
RESTORE DATABASE [CTS-ENG1] FROM DISK = 'C:\BackupDB\CTS-ENG1_FINAL.BAK' WITH MOVE 'ENG1_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\CTS-ENG1_Data.mdf', MOVE 'ENG1_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\CTS-ENG1_Log.ldf'
Msg 8114, Level 16, State 1, Line 1
Error converting data type bigint to int.
Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.
RESTORE DATABASE [GRA-ADV] FROM DISK = 'C:\BackupDB\GRA_ADV.bak' WITH MOVE

The 2 databases that error are the ONLY large databases.

Here is the improved script below:

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_RestoreFromAllFilesInDirectory]    Script Date: 10/14/2015 14:18:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[sp_RestoreFromAllFilesInDirectory]
@SourceDirBackupFiles nvarchar(200), @DestDirDbFiles
nvarchar(200),@DestDirLogFiles nvarchar(200)
AS
--Originally written by Tibor Karaszi 2004. Use at own risk.
--Restores from all files in a certain directory. Assumes that:
--  There's only one backup on each backup device.
--  Each database uses only two database files and the mdf file
--is returned first from the RESTORE FILELISTONLY command.
--Sample execution:
-- EXEC sp_RestoreFromAllFilesInDirectory 'C:\Mybakfiles\',
--'D:\Mydatabasesdirectory\' ,’C:\MylogDirectory\’
SET NOCOUNT ON

--Table to hold each backup file name in
CREATE TABLE #files(fname varchar(200),depth int, file_ int)
INSERT #files
EXECUTE master.dbo.xp_dirtree @SourceDirBackupFiles, 1, 1

--Table to hold the result from RESTORE HEADERONLY. Needed to get
--the database name out from backup
CREATE TABLE #bdev(
 BackupName nvarchar(128)
,BackupDescription nvarchar(255)
,BackupType smallint
,ExpirationDate datetime
,Compressed tinyint
,Position smallint
,DeviceType tinyint
,UserName nvarchar(128)
,ServerName nvarchar(128)
,DatabaseName nvarchar(128)
,DatabaseVersion int
,DatabaseCreationDate datetime
,BackupSize numeric(20,0)
,FirstLSN numeric(25,0)
,LastLSN numeric(25,0)
,CheckpointLSN numeric(25,0)
,DatabaseBackupLSN numeric(25,0)
,BackupStartDate datetime
,BackupFinishDate datetime
,SortOrder smallint
,CodePage smallint
,UnicodeLocaleId int
,UnicodeComparisonStyle int
,CompatibilityLevel tinyint
,SoftwareVendorId int
,SoftwareVersionMajor int
,SoftwareVersionMinor int
,SoftwareVersionBuild int
,MachineName nvarchar(128)
,Flags int
,BindingID uniqueidentifier
,RecoveryForkID uniqueidentifier
,Collation nvarchar(128)
,FamilyGUID uniqueidentifier
,HasBulkLoggedData int
,IsSnapshot int
,IsReadOnly int
,IsSingleUser int
,HasBackupChecksums int
,IsDamaged int
,BegibsLogChain int
,HasIncompleteMetaData int
,IsForceOffline int
,IsCopyOnly int
,FirstRecoveryForkID uniqueidentifier
,ForkPointLSN numeric(25,0)
,RecoveryModel nvarchar(128)
,DifferentialBaseLSN numeric(25,0)
,DifferentialBaseGUID uniqueidentifier
,BackupTypeDescription nvarchar(128)
,BackupSetGUID uniqueidentifier
,CompressedBackupSize nvarchar(128)
)

--Table to hold result from RESTORE FILELISTONLY. Need to
--generate the MOVE options to the RESTORE command
CREATE TABLE #dbfiles(
 LogicalName nvarchar(128)
,PhysicalName nvarchar(260)
,Type char(1)
,FileGroupName nvarchar(128)
,Size numeric(20,0)
,MaxSize numeric(20,0)
,FileId int
,CreateLSN numeric(25,0)
,DropLSN numeric(25,0)
,UniqueId uniqueidentifier
,ReadOnlyLSN numeric(25,0)
,ReadWriteLSN numeric(25,0)
,BackupSizeInBytes int
,SourceBlockSize int
,FilegroupId int
,LogGroupGUID uniqueidentifier
,DifferentialBaseLSN numeric(25)
,DifferentialBaseGUID uniqueidentifier
,IsReadOnly int
,IsPresent int
,TDEThumbprint nvarchar(128)
)


DECLARE @fname varchar(200)
DECLARE @dirfile varchar(300)
DECLARE @LogicalName nvarchar(128)
DECLARE @PhysicalName nvarchar(260)
DECLARE @type char(1)
DECLARE @DbName sysname
DECLARE @sql nvarchar(1000)

DECLARE files CURSOR FOR
SELECT fname FROM #files

DECLARE dbfiles CURSOR FOR
SELECT LogicalName, PhysicalName, Type FROM #dbfiles

OPEN files
FETCH NEXT FROM files INTO @fname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @dirfile = @SourceDirBackupFiles + @fname

--Get database name from RESTORE HEADERONLY, assumes there's
--only one backup on each backup file.
TRUNCATE TABLE #bdev
INSERT #bdev
EXEC('RESTORE HEADERONLY FROM DISK = ''' + @dirfile + '''')
SET @DbName = (SELECT DatabaseName FROM #bdev)

--Construct the beginning for the RESTORE DATABASE command
SET @sql = 'RESTORE DATABASE [' + @DbName + '] FROM DISK = ''' +
@dirfile + ''' WITH MOVE '

--Get information about database files from backup device into temp table
TRUNCATE TABLE #dbfiles
INSERT #dbfiles
EXEC('RESTORE FILELISTONLY FROM DISK = ''' + @dirfile + '''')

OPEN dbfiles
FETCH NEXT FROM dbfiles INTO @LogicalName, @PhysicalName, @type
--For each database file that the database uses
WHILE @@FETCH_STATUS = 0
BEGIN
IF @type = 'D'
SET @sql = @sql + '''' + @LogicalName + ''' TO ''' +
@DestDirDbFiles + @DbName  + '_Data.mdf'', MOVE '
ELSE IF @type = 'L'
SET @sql = @sql + '''' + @LogicalName + ''' TO ''' +
@DestDirLogFiles + @DbName  + '_Log.ldf'''
FETCH NEXT FROM dbfiles INTO @LogicalName, @PhysicalName, @type
END

--Here's the actual RESTORE command
PRINT @sql
--Remove the comment below if you want the procedure to
--actually execute the restore command.
--EXEC(@sql)
CLOSE dbfiles
FETCH NEXT FROM files INTO @fname
END
CLOSE files
DEALLOCATE dbfiles
DEALLOCATE files
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
for start
try
to replace in this proc
 "create table.."  column definition from

int to bigint data type

---
Noah_Williams35Author Commented:
That is exactly what I did last night and it worked! Thank you!
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
cool! np
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.