Powershell Day 23

Day 23 SMO programming Part 4

 AS we can work with Server, Database,  Table, Column. You can get the similar information about

Views, Storedprocedures…

$database1.Views

$database1.Storedprocedures

$database1.logfiles

$database1.rules

$database1.schemas

$database1.roles

$database1.triggers

$tables1.triggers

If you observed on this, cmdlets are always in single where as properties are always with (s)

like for properties – databases, tables, columns, views, storedprocudures, Triggers

 So all the properties are having “s” with it. But cmdlets are always in singular

Get-member, get-command, etc…

 So all the objects associated with that object can be having (s).

[System.IO.Directory]::createdirectory(“c:\Sql_Bkp”)

To create a folder as  

Backup & Restore:

So far we have discussed about the database objects , create and manage it.

Now here we will talk about the Backup and restore – a very important for high Availability.

Backup: backup is of three types

Full Backup

Tlog Backup

Differential Backup

Backup is also is having objects, we have to create an object of type (“Microsoft.SqlServer.Management.Smo.Backup”) Backup and Restore classes are in SMOExtended.dll. for that we need to resister Extended assembly .

 

FULL Backup:

PS C:\> [System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SmoExtended”)
PS C:\> $bck=New-Object “Microsoft.SqlServer.Management.Smo.backup”
PS C:\> $bck.Action = ‘Database’
PS C:\> $bck.Database =”DB1″
PS C:\> $bck.devices.adddevice(“C:\db1.bak”,”File”)
PS C:\> $bck.sqlbackup($server1)
 

TLog Backup:

PS C:\> [System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SmoExtended”)
PS C:\> $bck=New-Object “Microsoft.SqlServer.Management.Smo.backup”
PS C:\> $bck.Action = ‘Log’
PS C:\> $bck.Database =”DB1″
PS C:\> $bck.devices.adddevice(“C:\db1.log”,”File”)
PS C:\> $bck.sqlbackup($server1)
 

Differential Backup:

PS C:\> [System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SmoExtended”)
PS C:\> $bck=New-Object “Microsoft.SqlServer.Management.Smo.backup”
PS C:\> $bck.incremental = 1
PS C:\> $bck.Database =”DB1″
PS C:\> $bck.devices.adddevice(“C:\db1_1.diff”,”File”)
PS C:\> $bck.sqlbackup($server1)

 

Remember to follow all the steps sequentially.

Restore on existing database:-

PS C:\> [System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SmoExtended”)
PS C:\> $restore1= New-Object (‘Microsoft.SqlServer.Management.Smo.Restore’)
PS C:\> $restore1.Database = “db1″
PS C:\> $restore1.Action = database
PS C:\> $restore1.ReplaceDatabase = $true
PS C:\> $restore1.Devices.AddDevice(“c:\vin\db1.bak”, “File”)
PS C:\> $restore1.NoRecovery = $false
PS C:\> $server1.KillAllProcesses(“db1″)
PS C:\> $restore1.SqlRestore($server1)
 

Restore on new database:-

Restore on new database requires relocation of data and log file to a new location. Which requires a new “Relocation” objects for data and log files.

Logical and physical file name and file path.

 

[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SMO”) | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SmoExtended”) | Out-Null
$Server1=new-object (“Microsoft.SqlServer.Management.Smo.Server”) ‘L-108326-B\SQLSERVER2008SE’
$restore1= New-Object (‘Microsoft.SqlServer.Management.Smo.Restore’)
$relocateData1 = New-Object(“Microsoft.SqlServer.Management.Smo.RelocateFile”)
$relocateLog1 = New-Object(“Microsoft.SqlServer.Management.Smo.RelocateFile”)
$restore1.Devices.AddDevice(“c:\db1.bak”,”File”)
$header1=$restore1.ReadBackupHeader($server1)
$restore1.Database = “db2″
$relocateData1.LogicalFileName = $header1.Rows[0][“DatabaseName”]
$relocateData1.PhysicalFileName = “c:\DB2_Data.mdf”
$relocateLog1.LogicalFileName = $header1.Rows[0][“DatabaseName”] + “_Log”
$relocateLog1.PhysicalFileName = “c:\DB2_Log.ldf”
$restore1.RelocateFiles.Add($relocateData1)
$restore1.RelocateFiles.Add($relocateLog1)
$restore1.SqlRestore($server1)
 

Restore the Tlog:

Restoring the tlog require restore the fullbackup with NoRecovery

[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SmoExtended”)
$restore1= New-Object (‘Microsoft.SqlServer.Management.Smo.Restore’)
$restore1.Database = “db2″
$restore1.Action = “Log”
$restore1.ReplaceDatabase = $true
$restore1.Devices.AddDevice(“c:\db1.log”, “File”)
$restore1.NoRecovery = $true
$restore1.set_NoRecovery(0)
$server1.KillAllProcesses(“db2″)
$restore1.SqlRestore($server1)
 
Ref:
https://social.technet.microsoft.com/wiki/contents/articles/900.how-to-sql-server-databases-backup-with-powershell.aspx
 
http://www.mssqltips.com/sqlservertip/1862/backup-sql-server-databases-with-a-windows-powershell-script/
About these ads
This entry was posted in Powershell and tagged . Bookmark the permalink.

One Response to Powershell Day 23

  1. ratna prasad says:

    Hi Vinay

    Can you please share with me basic SQL DBA adminstration issues on day to day basis.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s