Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 6

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 6 Picture 1 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 1
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 6 Picture 2
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 2
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 6 Picture 3
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 3
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 6 Picture 4
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 4
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 6 Picture 5
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 5

The MAK

In Part 6, I will show you how to use PowerShell and scripts to back up the database.

Method 1

Let's assume we have a database ' MyDatabase ' on the ' HOME ' server. Now let's assume that we want to make a full backup of the database to the c: test directory.

Execute each cmdlet one by one. Refer to Figure 1.0

[System.Reflection.Assembly] :: LoadWithPartialName ("Microsoft.SqlServer.Smo") | out-null
[System.IO.Directory] :: CreateDirectory ("C: test") | out-null
$ srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" "HOME"
$ bck = new-object "Microsoft.SqlServer.Management.Smo.Backup"
$ bck.Action = 'Database'
$ fil = new-object "Microsoft.SqlServer.Management.Smo.BackupDeviceItem"
$ fil.DeviceType = 'File'
$ fil.Name = [System.IO.Path] :: Combine ("C: test", "MyDatabase" + ". bak")
$ bck.Devices.Add ($ fil)
$ bck.Database = "MyDatabase"
$ bck.SqlBackup ($ srv)
write-host "Backup of MyDatabase done"

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 6 Picture 6
Figure 1.0

This script will create a full backup of the MyDatabase database to the c: test directory as shown below. (Refer to Figure 1.1)

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 6 Picture 7
Figure 1.1

Method 2

Let's assume we have a database ' MyDatabase ' on the ' HOME ' server and want to perform a Transaction log backup of the databases into the c: test directory. Execute the cmdlet below, times the amount of each instruction (refer to Figure 1.2).

[System.Reflection.Assembly] :: LoadWithPartialName ("Microsoft.SqlServer.Smo") | out-null
[System.IO.Directory] :: CreateDirectory ("C: test") | out-null
$ srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" "HOME"
$ bck = new-object "Microsoft.SqlServer.Management.Smo.Backup"
$ bck.Action = 'Log'
$ fil = new-object "Microsoft.SqlServer.Management.Smo.BackupDeviceItem"
$ fil.DeviceType = 'File'
$ fil.Name = [System.IO.Path] :: Combine ("C: test", "MyDatabase" + ". trn")
$ bck.Devices.Add ($ fil)
$ bck.Database = "MyDatabase"
$ bck.SqlBackup ($ srv)
write-host "Log Backup of MyDatabase done"

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 6 Picture 8
Figure 1.2

This scenario will perform a Transaction log backup of the MyDatabase database to the c: test directory, as shown in the figure below (refer to Figure 1.3).

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 6 Picture 9
Figure 1.3

Method 3

Let's assume we have a database ' MyDatabase ' on the ' HOME ' server and want to make a Differential backup for the databases into the c: test directory. Follow the commands below, one by one (refer to Figure 1.4).

[System.Reflection.Assembly] :: LoadWithPartialName ("Microsoft.SqlServer.Smo") | out-null
[System.IO.Directory] :: CreateDirectory ("C: test") | out-null
$ srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" "HOME"
$ bck = new-object "Microsoft.SqlServer.Management.Smo.Backup"
$ bck.Incremental = 1
$ fil = new-object "Microsoft.SqlServer.Management.Smo.BackupDeviceItem"
$ fil.DeviceType = 'File'
$ fil.Name = [System.IO.Path] :: Combine ("C: test", "MyDatabase" + ". diff")
$ bck.Devices.Add ($ fil)
$ bck.Database = "MyDatabase"
$ bck.SqlBackup ($ srv)
write-host "Differential Backup of MyDatabase done"

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 6 Picture 10
Figure 1.4

This script will perform backup Differential of MyDatabase database to c: test directory as shown below (refer to Figure 1.5)

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 6 Picture 11
Figure 1.5

Method 4

Now let's connect methods 1, method 2 and method 3 to a PowerShell script that takes the Server name, Database name, Backup type and Folder folder as parameters so that it can be easily implemented. automatically. Create the Backup.ps1 script below in the C: PS folder (refer to Figure 1.6, 1.7).

param (
[string] $ ServerName,
[string] $ DatabaseName,
[string] $ Backuptype,
[string] $ BackupPath
)
[System.Reflection.Assembly] :: LoadWithPartialName ("Microsoft.SqlServer.Smo") | out-null
[System.IO.Directory] :: CreateDirectory ($ BackupPath) | out-null
$ srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" "$ servername"
$ bck = new-object "Microsoft.SqlServer.Management.Smo.Backup"

if ($ Backuptype -eq "FULL")
{
$ bck.Action = 'Database'
$ extenstion = ". BAK"
$ text1 = "Full Backup"
}

if ($ Backuptype -eq "TRAN")
{
$ bck.Action = 'Log'
$ extenstion = ". LOG"
$ text1 = "Transactional Log Backup"
}

if ($ Backuptype -eq "DIFF")
{
$ bck.Incremental = 1
$ extenstion = ". DIFF"
$ text1 = "Differential Backup"
}

$ fil = new-object "Microsoft.SqlServer.Management.Smo.BackupDeviceItem"
$ fil.DeviceType = 'File'
$ fil.Name = [System.IO.Path] :: Combine ($ BackupPath, $ DatabaseName + $ extenstion)
$ bck.Devices.Add ($ fil)
$ bck.Database = $ DatabaseName
$ bck.SqlBackup ($ srv)
write-host $ text1 of $ Databasename done

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 6 Picture 12
Figure 1.6

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 6 Picture 13
Figure 1.7

Now let's execute the above PowerShell script with the following parameters (refer to Figure 1.8).

./backup.ps1 HOME MyDatabase FULL C: test
./backup.ps1 HOME MyDatabase DIFF C: test
./backup.ps1 HOME MyDatabase TRAN C: test

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 6 Picture 14
Figure 1.8

Executing the script above gives the results below. (Refer to Figures 1.8 and 1.9)

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 6 Picture 15
Figure 1.9

Method 5

Let's assume that we need to add the Stamp with the file name when performing backup with the above script.
To do that, update the script with the code below (refer to Figure 2.0)

param (
[string] $ ServerName,
[string] $ DatabaseName,
[string] $ Backuptype,
[string] $ BackupPath
)
[System.Reflection.Assembly] :: LoadWithPartialName ("Microsoft.SqlServer.Smo") | out-null
[System.IO.Directory] :: CreateDirectory ($ BackupPath) | out-null
$ srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" "$ servername"
$ bck = new-object "Microsoft.SqlServer.Management.Smo.Backup"

if ($ Backuptype -eq "FULL")
{
$ bck.Action = 'Database'
$ extenstion = ". BAK"
$ text1 = "Full Backup"
}

if ($ Backuptype -eq "TRAN")
{
$ bck.Action = 'Log'
$ extenstion = ". LOG"
$ text1 = "Transactional Log Backup"
}

if ($ Backuptype -eq "DIFF")
{
$ bck.Incremental = 1
$ extenstion = ". DIFF"
$ text1 = "Differential Backup"
}

$ fil = new-object "Microsoft.SqlServer.Management.Smo.BackupDeviceItem"
$ fil.DeviceType = 'File'
$ fil.Name = [System.IO.Path] :: Combine ($ BackupPath, $ DatabaseName + "_" + [DateTime] :: Today.ToString ("yyyy_MM_dd") + $ extenstion)
$ bck.Devices.Add ($ fil)
$ bck.Database = $ DatabaseName
$ bck.SqlBackup ($ srv)
write-host $ text1 of $ Databasename done

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 6 Picture 16
Figure 2.0

Now let's execute the above PowerShell script with the following parameters (refer to Figure 2.1).

./backup.ps1 HOMESQLExpress Admin FULL C: test
./backup.ps1 HOMESQLExpress Admin DIFF C: test
./backup.ps1 HOMESQLExpress Admin TRAN C: test

Note : HOMESQLExpress is a named example of sql server on the HOME host;

Admin is the database name.

Executing the above scenario results in Figure 2.1 and 2.2.

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 6 Picture 17
Figure 2.1

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 6 Picture 18
Figure 2.2

Conclude

Part 6 of this series illustrated how to use PowerShell and PowerShell scripts to perform a complete backup - Full Backup, Transaction Log backup and Differential backup for the database with the file name already stamping outside.

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 6 Picture 19 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 7
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 6 Picture 20
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 8
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 6 Picture 21
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 9
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 6 Picture 22
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 10
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 6 Picture 23
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 11

4.5 ★ | 2 Vote

May be interested

  • Database management with TOADPhoto of Database management with TOAD
    database (database) is one of the important components of a software development project. although there are specialized software to build databases of well-known brands in the world such as oracle, sap, ibm and microsoft, but managers still do not
  • Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 1Photo of Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 1
    as you probably know, windows powershell is a new command-line utility and a scripting language that provides a command-line environment for computer interaction and administration surveys. in addition, it provides a scripting capability for fish
  • Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 2Photo of Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 2
    part i of this series shows how to set up and use simple powershell and smo commands. in part ii we will learn more about powershell as well as its features associated with smo. if you do & ati
  • Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 3Photo of Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 3
    part i and part ii of this series showed powershell and smo settings, simple wmi cmdlets. this part 3 will cover how to write code for the powershell cmdlet and execute them. script code is essential for automated operations and
  • Move the Tempdb and Master databases on SQL ServerPhoto of Move the Tempdb and Master databases on SQL Server
    when you want to move a typical user database to another drive to increase performance or to split logs, you can run sp_detach and sp_attach to perform the migration. however, for moving the master database and
  • Microsoft Windows Power Shell and SQL Server 2005 SMO - Part 4Photo of Microsoft Windows Power Shell and SQL Server 2005 SMO - Part 4
    part i and part ii of this series showed simple power shell settings, smo and wmi cmdlets. part iii instructs writing powershell and connecting to sql server. part 4 will show you how to use powershell code to iterate file content and connect to other servers.