Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Restore with relocate database files location in powershell Expand / Collapse
Author
Message
Posted Monday, August 4, 2014 8:52 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 12:03 AM
Points: 2, Visits: 69
Please help me to modify this script as relocate database files location means like with move ... this will help me

write-host "

Restoring Databases"

foreach ($database in $databases){
write-host $database
invoke-sqlcmd -Query "USE [master]
ALTER DATABASE [$database] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [$database] FROM DISK = N'H:\KMTemp\$database.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 5
ALTER DATABASE [$database] SET MULTI_USER

GO

" -ServerInstance $destinationserver -QueryTimeout 10000}
Post #1599452
Posted Tuesday, August 5, 2014 3:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:06 AM
Points: 5,469, Visits: 3,251
I am unsure of what you are asking. What is your problem? Specifically?

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!
Post #1599516
Posted Monday, August 25, 2014 12:01 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 1:25 PM
Points: 160, Visits: 971
Not sure what you are looking for, but I use the below code to generate a SQL script. Could be modified to do interactive restore. We also use Litespeed, but modifying it to use native backup should be fairly straight forward. Most of our databases have one data and one log, so the code to generate multiple move statements is commented out.

clear
#set up for smo
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

$SaveDir = "C:\Temp\"
$SaveFile = $SaveDir + "ScriptName_Restore.sql"
$RestoreFromDir = "C:\MSSQL\Backups"
$RestoreToDir = "C:\MSSQL\Data"
$RestoreLogToDir = "C:\MSSQL\Logs"

$SkipDBs = "master","msdb","model","tempdb","distribution"

IF(Test-Path $SaveFile)
{
Remove-Item $SaveFile
}

#Generate scripts, from Instance
$instance = "InstanceName"

$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance
# $srv.ConnectionContext.LoginSecure = $False # set to true for Windows Authentication
# $srv.ConnectionContext.Login = "sa"
# $srv.ConnectionContext.Password = "password"

#Process Databases
$dbs = $srv.Databases | Where-Object {$SkipDBs -notcontains $_.Name.ToString().ToLower() }

$FirstDirSet = "Y"
ForEach ($db in $dbs)
{
$dbName = $db.Name
# if ($FirstDirSet -eq "Y")
# {
# $RestoreToDir = "F:\MSSQL\Data"
# $RestoreLogToDir = "H:\MSSQL\Logs"
# $FirstDirSet = "N"
# }
# else
# {
# $RestoreToDir = "G:\MSSQL\Data"
# $RestoreLogToDir = "I:\MSSQL\Logs"
# $FirstDirSet = "Y"
# }

" " | Out-File $SaveFile -Append
"---- Kill all current connections" | Out-File $SaveFile -Append
"DECLARE @cmdKill VARCHAR(50) ; " | Out-File $SaveFile -Append
"DECLARE killCursor CURSOR FOR " | Out-File $SaveFile -Append
"SELECT 'KILL ' + Convert(VARCHAR(5), p.spid) " | Out-File $SaveFile -Append
"FROM master.dbo.sysprocesses AS p " | Out-File $SaveFile -Append
"WHERE p.dbid IN(db_id('$dbName') ) ; " | Out-File $SaveFile -Append
"OPEN killCursor ; " | Out-File $SaveFile -Append
"FETCH killCursor INTO @cmdKill ; " | Out-File $SaveFile -Append
"WHILE 0 = @@fetch_status " | Out-File $SaveFile -Append
"BEGIN " | Out-File $SaveFile -Append
"EXECUTE (@cmdKill) ; " | Out-File $SaveFile -Append
"FETCH killCursor INTO @cmdKill ; " | Out-File $SaveFile -Append
"END " | Out-File $SaveFile -Append
"CLOSE killCursor ; " | Out-File $SaveFile -Append
"DEALLOCATE killCursor ; " | Out-File $SaveFile -Append
"-----------------------" | Out-File $SaveFile -Append
" " | Out-File $SaveFile -Append
"exec master.dbo.xp_restore_database @database = N'$dbName', " | Out-File $SaveFile -Append
"@filename = N'$RestoreFromDir\" + $dbName + "_Backup.BAK', " | Out-File $SaveFile -Append
"@filenumber = 1, " | Out-File $SaveFile -Append
"@with = N'REPLACE', " | Out-File $SaveFile -Append
"@with = N'STATS = 10', " | Out-File $SaveFile -Append
$fgs = $db.FileGroups
$FileCnt = 0
foreach ($fg in $fgs)
{
$files = $fg.Files
foreach($file in $files)
{
$FileCnt++
$FileName = $file.Name
if ($FileCnt -eq 1)
{
"@with = N'MOVE N''$FileName'' TO N''$RestoreToDir\$dbName.mdf''', " | Out-File $SaveFile -Append
}
else
{
"@with = N'MOVE N''$FileName'' TO N''$RestoreToDir\$dbName_$FileName.ndf''', " | Out-File $SaveFile -Append
}
}
}
$logfiles = $db.LogFiles
$LogCnt = 0
foreach ($LogFile in $logfiles)
{
$LogCnt++
$LogName = $LogFile.Name
if ($LogCnt -eq 1)
{
"@with = N'MOVE N''$LogName'' TO N''$RestoreLogToDir\" + $dbName + "_Log.ldf''', " | Out-File $SaveFile -Append
}
else
{
"@with = N'MOVE N''$LogName'' TO N''$RestoreLogToDir\" + $dbName + "_" + $LogName + ".ldf''', " | Out-File $SaveFile -Append
}
}

"@affinity = 0, " | Out-File $SaveFile -Append
"@logging = 0 " | Out-File $SaveFile -Append
"GO " | Out-File $SaveFile -Append
"" | Out-File $SaveFile -Append


}

Post #1607160
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse