Programming4us
         
 
 
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.Name="my_database"
$my_db.Parent=(Get-Item ..)
$my_db.Create()
cd my_database
cd tables
$my_tbl=New-Object Microsoft.SqlServer.Management.Smo.Table
$my_tbl.Name="my_table"
$my_tbl.Parent=(Get-Item ..)
$my_col=New-Object Microsoft.SqlServer.Management.Smo.Column
$my_col.Name="my_column"
$my_col.Parent=$my_tbl
$my_col.DataType= ([Microsoft.SqlServer.Management.Smo.DataType]::Int)
$my_tbl.Columns.Add($my_col)
$my_tbl.Create()

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
$file.Name="C:\backup\AW_DB.bak"
$backup.Devices.Add($file)
$backup.Database="AdventureWorks2008R2"
$backup.SqlBackup($server)

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
$file.Name=$device
$backup.Devices.Add($file)
$backup.Database=$database
$backup.SqlBackup($server)
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>$backup_to="C:\backup\AdventureWorks2008R2.bak"
PS>$db="AdventureWorks2008R2"
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..

Note

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 .
PS>$sql_server.Information.VersionString

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.

Note

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"
Expression={[math]::round((($_.spaceavailable/1kb)/$_.size),2)}
}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:

    "[math]::round(value,decimal)"

    .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
 
 
Most View
- jQuery 1.3 : Headline rotator (part1) - Setting up the page
- Security in Cloud Computing (part 1) - Data Security and Storage
- Exchange Server 2010 Administration Essentials : Validating the Exchange Server Licensing
- Windows Vista: Windows Reliability and Performance Monitor and Task Manager
- jQuery 1.3 : Working with numeric form data (part 3) - Parsing and formatting currency
- Windows Vista : User Account Control
- Windows Vista: Windows Firewall Settings - Advanced Configuration
- Exchange Server 2010 : Designing and Implementing AD RMS Integration (part 1) - Overview
- Programming Windows Phone 7 : Color Themes
- Windows 7 : Setting Up the Remote Computer as a Host (part 1) - Configuring Windows 7 or Vista to Act as a Remote Desktop Host
Top 10
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 3) - Configuring Recipient Filtering
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 2)
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 1)
- Implementing Edge Services for an Exchange Server 2007 Environment : Installing and Configuring the Edge Transport Server Components
- What's New in SharePoint 2013 (part 7) - BCS
- What's New in SharePoint 2013 (part 6) - SEARCH
- What's New in SharePoint 2013 (part 6) - WEB CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 5) - ENTERPRISE CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 4) - WORKFLOWS
- What's New in SharePoint 2013 (part 3) - REMOTE EVENTS