Restore-LatestBackup.ps1

Question

Post reply

Restore-LatestBackup.ps1

SQLDuck

SSC-Addicted

Points: 418
More actions
April 1, 2013 at 7:45 pm

#275929

Comments posted to this topic are about the item Restore-LatestBackup.ps1

Viewing 9 posts - 1 through 8 (of 8 total)

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

vijay.mane1911 Grasshopper Points: 17 More actions · Answer 1

vijay.mane1911

Grasshopper

Points: 17

April 2, 2013 at 4:29 am

#1602735

Thank you

alan.spillert Mr or Mrs. 500 Points: 513 More actions · Answer 2

I put together this script to copy a production database backup to a development server and restore it. The job will run at night, and keeps the development data parallel to production. We are using SQL Server 2012 SP2 (build 11.0.5058).

[font="Courier New"]import-module SQLPS -DisableNameChecking

#

#knock off existing connections

Invoke-Sqlcmd "ALTER DATABASE DB_VIEWS SET OFFLINE WITH ROLLBACK IMMEDIATE;"

Invoke-Sqlcmd "ALTER DATABASE DB_VIEWS SET ONLINE;"

#

# find the latest full backup to be restored

$a = Get-ChildItem Filesystem::\\RC-DBPROD\Backup\DB_VIEWS\DB_VIEWS*.bak | sort LastWriteTime | select -last 1

#

# production DB uses different disks then this development server

$RelocateData = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("DB_VIEWS", "C:\MSSQL\DB_VIEWS.mdf")

$RelocateLog = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("DB_VIEWS_log", "C:\MSSQL\DB_VIEWS_log.ldf")

Restore-SqlDatabase -ServerInstance RC-DBUPD58 -Database DB_VIEWS -BackupFile $a -RelocateFile @($RelocateData,$RelocateLog) -replacedatabase

[/font]

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 3

akljfhnlaflkj

SSC Guru

Points: 76202

May 2, 2016 at 3:42 pm

#1876064

Thanks for the script.

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 4

alan.spillert (8/27/2014)
I put together this script to copy a production database backup to a development server and restore it. The job will run at night, and keeps the development data parallel to production. We are using SQL Server 2012 SP2 (build 11.0.5058).
[font="Courier New"]import-module SQLPS -DisableNameChecking
#
#knock off existing connections
Invoke-Sqlcmd "ALTER DATABASE DB_VIEWS SET OFFLINE WITH ROLLBACK IMMEDIATE;"
Invoke-Sqlcmd "ALTER DATABASE DB_VIEWS SET ONLINE;"
#
# find the latest full backup to be restored
$a = Get-ChildItem Filesystem::\\RC-DBPROD\Backup\DB_VIEWS\DB_VIEWS*.bak | sort LastWriteTime | select -last 1
#
# production DB uses different disks then this development server
$RelocateData = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("DB_VIEWS", "C:\MSSQL\DB_VIEWS.mdf")
$RelocateLog = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("DB_VIEWS_log", "C:\MSSQL\DB_VIEWS_log.ldf")
Restore-SqlDatabase -ServerInstance RC-DBUPD58 -Database DB_VIEWS -BackupFile $a -RelocateFile @($RelocateData,$RelocateLog) -replacedatabase
[/font]

Thanks for the extra insight.

SQLDuck SSC-Addicted Points: 418 More actions · Answer 5

Nice, thanks! Been meaning to update this guy to use the newer sqlps module instead.

sqlfriend SSC Guru Points: 52769 More actions · Answer 6

At Luke's powershell script , at bottom lines of the code as below, if I want the source server and destination server has different data file physical path how can we rewrite below codes? For example the source server physical path is E:\MSSQL\Data\
The destination is E:\MSSQL12\Data

# Loop through backup file and configure data and log file names.

$logicalFilename = $null;

$physicalFilename = $null;

foreach ($dbFile IN $backupFiles.Rows){

$logicalFilename = $dbFile.LogicalName;

$physicalFilename = $dbFile.PhysicalName.Replace($sourceInstance, $destInstance).Replace($dbName, $newDbName);

$smoMoveFile = New-Object('Microsoft.SqlServer.Management.Smo.RelocateFile');

$smoMoveFile.LogicalFileName = $logicalFilename;

$smoMoveFile.PhysicalFileName = $physicalFilename;

$smoNewDB.RelocateFiles.Add($smoMoveFile);

}

#Restore db

$smoNewDB.SqlRestore($destServerConnection);

SQLDuck SSC-Addicted Points: 418 More actions · Answer 7

sqlfriends - Monday, September 25, 2017 4:00 PM
At Luke's powershell script , at bottom lines of the code as below, if I want the source server and destination server has different data file physical path how can we rewrite below codes? For example the source server physical path is E:\MSSQL\Data\
The destination is E:\MSSQL12\Data

You could add another replace() to the line that sets the physical filename, like this:
$physicalFilename = $dbFile.PhysicalName.Replace($sourceInstance, $destInstance).Replace($dbName, $newDbName).Replace('MSSQL', 'MSSQL12');

sqlfriend SSC Guru Points: 52769 More actions · Answer 8

Thank you, that works!

Thanks for the script, this is the one of the best scripts used to store db to another sever.