SQL Server

Administering SQL Server 2008 with PowerShell : Step-By-Step Examples (part 3) - Performing a Database Backup

7/20/2011 11:38:18 AM

Creating a Database Table

Creating a database and a table are common tasks that a DBA may undertake. You can use T-SQL with the Invoke-SqlCmd cmdlet to do this, but a demonstration on how to do this with the SQL Server PowerShell minishell using SMO is presented here to help you better understand the new functionality that is available:

cd databases
$my_db=New-Object Microsoft.SqlServer.Management.Smo.Database
$my_db.Parent=(Get-Item ..)
cd my_database
cd tables
$my_tbl=New-Object Microsoft.SqlServer.Management.Smo.Table
$my_tbl.Parent=(Get-Item ..)
$my_col=New-Object Microsoft.SqlServer.Management.Smo.Column
$my_col.DataType= ([Microsoft.SqlServer.Management.Smo.DataType]::Int)

In the preceding example, some new objects are created, some of their properties are set, and some methods are called. You can search for the particular SMO classes used in this example to gain further information.

In the future, there may be something like New-Database and New-Table cmdlets that help to create a database and table, which would likely reduce the preceding code to fewer than five lines.

Performing a Database Backup

Another example that may be useful is performing a database backup using SMO. Using the AdventureWorks2008R2 database, you can back up the database using just a few lines:

$server=New-Object Microsoft.SqlServer.Management.Smo.Server
$backup=new-object Microsoft.SqlServer.Management.Smo.Backup
$file=new-object Microsoft.SqlServer.Management.Smo.BackupDeviceItem

The preceding code could be copied into a .ps1 file (for this example, assume it’s copied to c:\temp\backup.ps1), and it could be changed to accept two arguments. The preceding code could be modified to the following code snippet so that it accepts parameters from the command line:

param([string]$device=$(throw Write-Host "Device required"), [string]
$database=$(throw Write-Host "Database required"))
Write-Host "backup of $database to $device starting..."
$server=New-Object Microsoft.SqlServer.Management.Smo.Server
$backup=new-object Microsoft.SqlServer.Management.Smo.Backup
$file=new-object Microsoft.SqlServer.Management.Smo.BackupDeviceItem
Write-Host "backup complete"
Get-Item $device

The changes in the preceding example introduce a new keyword, throw. Without it, error messages would be thrown to the console, but this might not help the end user to understand why it failed. For the purpose of printing feedback to the console, the Write-Host and Get-Item cmdlets were also added to provide a limited amount of feedback and to finally provide the details of the final backup file.

Then to invoke the script, as shown in the following example, you simply need to pass two parameters to the script: the names of the file to back up to and the database to actually back up.

PS> c:\temp\backup.ps1 $backup_to $db

As an example of using a conditional statement in the preceding script, perhaps a check could be done to see whether a backup has already been completed within the past seven days. To accomplish this, you would add this particular section of code just before the line Write-Host "backup of $database to $device starting...":

If((Test-Path $device) -and (Get-Item $device).LastWriteTime `
-gt (Get-Date).AddDays(-7)){
"Backup has been performed in last 7 days" Break

In the preceding code, a conditional statement is added to accomplish the check. The AddDays() method is passed a negative number which subtracts days from the current date..


When you use the param keyword, this section of code must be on the first noncommented line in all scripts. Otherwise, the PowerShell parser returns an error.

Again, using SMO isn’t necessarily for beginners, but the good thing is that scripts can easily be created and passed around. The preceding example shows the bare minimum required to do a database backup; several other options are available, and the preceding code would actually overwrite the backup each time it is run. There also isn’t any error checking of any sort, which isn’t the best way to develop scripts.

Along with the New-Database and New-Table cmdlets that may come in the future, maybe a Start-DbBackup will be another good cmdlet to have available.

Checking Server Settings

From the SSMS, by right-clicking on the SQL Server node and then starting a SQL Server PowerShell session, you can open a console directly in the root of the default SQL Server in the example.

From here, you can easily obtain information on the SQL Server. First, the location is set to the instance that is to be queried, and then an object representing the SQL Server is saved to a variable (this demonstrates the advanced features mentioned earlier where objects can be saved to variables). The properties of that variable can then be accessed as follows:

PS>Set-Location SQLSERVER:\SQL\<servername>\<instance_name>
PS>$sql_server=get-item .

Using the Get-Member cmdlet discussed earlier, you can easily discover other members of the object contained in $sql_server, but this is left as an exercise for you to perform on your own.


This example demonstrates an important feature of the SQL Server provider: context sensitivity. In the preceding example, the current location was in the root of the SQL Server provider or database, and the command Get-Item. was used. The dot in this command basically indicates that you want an object that represents the current location in the provider. If the dot were moved to a different location, this command would no longer work the same way.

Checking the Database Usage

Using the object retrieved in the $sql_server variable, you can create a quick report of database usage using the databases property of that object, as shown here:

PS SQLSERVER:\SQL\<servername>\<instancename>>
$sql_server.databases| Format-Table -autosize Name,@{
Label= "% Used"
}Name % Used
---- ------
AdventureWorks2008R2 0.02
AdventureWorksDW2008R2 0
AdventureWorksLT2008R2 0.02
bigpubs2008 0.18
Customer 0.74
master 0.24
model 0.39
msdb 0.02
my_database 0.38
tempdb 0.8

Using the Format-Table cmdlet, you can easily and quickly create all kinds of reports. Some capabilities we haven’t discussed yet were used to create this report:

  • Calculated properties— The values displayed by Format-Table can be calculated using scriptblocks. That allows the logic to be highly customized. These scriptblocks are laid out as follows:

    @{Label="some text value"
    Expression={the scriptblock to evaluate here}
  • Direct access to the .NET Framework— The following line is directly from the .NET Framework:


    .NET functionality is being used to round out the numbers to the second decimal point.

    PowerShell has special meaning for 1kb, 1mb, 1gb and 1tb, which all present the value of the counterpart in number of bytes—for example, 1kb=1024. The values can also be uppercase.

Other -----------------
- Administering SQL Server 2008 with PowerShell : Step-By-Step Examples (part 1) - General Tasks & Scheduling Scripts
- PowerShell in SQL Server 2008
- Protecting SQL Server Data : SCHEMA ARCHITECTURE STRATEGIES - Using Database Object Schemas
- Protecting SQL Server Data : SCHEMA ARCHITECTURE STRATEGIES - Protection via Normalization
- Troubleshooting and Optimizing SQL Server 2005 : Server Configuration Maintenance
- Troubleshooting and Optimizing SQL Server 2005 : Tuning the Database Structure
- Troubleshooting and Optimizing SQL Server 2005 : Data Analysis and Problem Diagnosis
- SQL Injection Attacks and Defense : Exploiting the Operating System - Consolidating Access
- SQL Injection Attacks and Defense : Executing Operating System Commands
- Administering SQL Server 2008 with PowerShell : PowerShell Scripting Basics (part 2)
- Administering SQL Server 2008 with PowerShell : PowerShell Scripting Basics (part 1)
- Administering SQL Server 2008 with PowerShell : Overview of PowerShell
- SQL Server 2008 Scheduling and Notification : Scripting Jobs and Alerts, Multiserver Job Management & Event Forwarding
- SQL Server 2008 Scheduling and Notification : Managing Alerts
- SQL Injection Attacks and Defense : Accessing the File System (part 2) - Writing Files
- SQL Injection Attacks and Defense : Accessing the File System (part 1) - Reading Files
- SQL Server 2008 Scheduling and Notification : Managing Jobs
- SQL Server 2008 Scheduling and Notification : Managing Operators
- SQL Server 2008 Scheduling and Notification : Configuring the SQL Server Agent
- SQL Server 2008 : Database Mail - Related Views and Procedures
Video tutorials
- How To Install Windows 8

- How To Install Windows Server 2012

- How To Install Windows Server 2012 On VirtualBox

- How To Disable Windows 8 Metro UI

- How To Install Windows Store Apps From Windows 8 Classic Desktop

- How To Disable Windows Update in Windows 8

- How To Disable Windows 8 Metro UI

- How To Add Widgets To Windows 8 Lock Screen

- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010
programming4us programming4us
- First look: Apple Watch

- 10 Amazing Tools You Should Be Using with Dropbox

- Sigma 24mm f/1.4 DG HSM Art

- Canon EF11-24mm f/4L USM

- Creative Sound Blaster Roar 2

- Alienware 17 - Dell's Alienware laptops

- Smartwatch : Wellograph

- Xiaomi Redmi 2
Popular tags
Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Indesign Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe After Effects Adobe Photoshop Adobe Fireworks Adobe Flash Catalyst Corel Painter X CorelDRAW X5 CorelDraw 10 QuarkXPress 8 windows Phone 7 windows Phone 8