Restore with relocate database files location in powershell

  • 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}

  • I am unsure of what you are asking. What is your problem? Specifically?

    Gaz

    -- Stop your grinnin' and drop your linen...they're everywhere!!!

  • 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

    }

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply