Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 6
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 1
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 2
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 3
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 4
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"
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)
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"
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).
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"
Figure 1.4
This script will perform backup Differential of MyDatabase database to c: test directory as shown below (refer to Figure 1.5)
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
Figure 1.6
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
Figure 1.8
Executing the script above gives the results below. (Refer to Figures 1.8 and 1.9)
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
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.
Figure 2.1
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 7
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 8
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 9
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 10
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 11
You should read it
- Install Windows Server Backup on Windows Server 2008 R2
- Microsoft Forefront TMG - Backup and restore capabilities
- Backup SMS, MMS and call logs automatically on Android device
- Cause backup battery charger explodes, causes fire and prevention
- 11 best DVD and Blu-ray backup software
- 5 solutions to backup local Mac to replace Time Machine
- How to check whether spare batteries are allowed to be carried on board
- Instructions for backing up and storing data on your Android device securely
May be interested
- Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 10in part 10, i will show you how to use powershell scripts in conjunction with smo and parameters to create sql server scripts. creating sql server scripts is an important task for administrators and sql server database development professionals.
- 10 tips with PowerShell in Windows Server 2008 - Part 1in fact, there are a lot of windows server 2008 tasks that we can do a lot faster with powershell than the gui-based application or tool. in the following article, we will introduce you some basic and most frequently used operations with powershell ...
- Test SQL Server with Windows PowerShell - Part 5in this part 5, we will check whether we can connect to sql server and see if we can query some properties related to sql server.
- Test SQL Server with Windows PowerShell - Part 6part 6 will show you how to check all existing databases in the sql server instance and query the database properties.
- Instructions on how to use PowerShell in Windows Server 2012what is powershell? windows powershell is a command-line shell language interpreter and scripting language specifically designed for system administrators. built on the .net framework, windows powershell helps it professionals control and automate windows operating system administration tasks as well as applications running on windows server environments.
- Test SQL Server with Windows PowerShell - Part 1instead of using vbscript, bat files, or sql client implementations, ... we'll show you the power of windows powershell in checking sql servers status.
- Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 1as 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
- Test SQL Server with Windows PowerShell - Part 3in part 3, i will show you how to find some hardware and operating system information from the host machine.
- Next time, Microsoft will release PowerShell updates via Windows Update Windowsmicrosoft is making it easier to update powershell on windows 10 and windows server devices by releasing updates via microsoft update in the future.
- Test SQL Server with Windows PowerShell - Part 4in this next section, i will show you the information about network card and hard drive from the server.