Home > PowerShell Scripting > Backup and restore SQL databases using powershell

Backup and restore SQL databases using powershell

I needed a powershell script to quickly backup or restore SQL database’s before doing maintenance work, but without having to remote into an SQL server. With this task in hand I wrote the below module. I’ve sealed this as a module to be imported on runtime so you can quickly run a backup before making any changes to infrastructure utilising sql, safe with the knowledge you have a backup, and more importantly restore if you make a mess of it!

This module should be used before using my xenapp 6 backup, reset and restore commands or Edgesight scripts. You can get a copy of it here.

To import the module, run the below command (assuming you have saved the module to c:\).

import-module c:\sql.backup.psm1

Module functions:

  • Backup-SQLdatabase
  • Restore-SQLdatabase

Backup-SQLdatabase

This module allows you to connect to an SQL server and use native SQL commands to perform the backup to a file from pretty much any windows machine with powershell installed.

This module supports trusted and untrusted connections and has built in error checking to catch typical problems and returns a job report as an object. The module will first try to connect and ensure you have the neccessary access rights, attempt to create the backup file, and assuming all has gone well then perform the task at hand:

Backup-SQLdatabase -SQLServer "sqlserver" -SQLDatabase "database" -Path "\\server\share$\backup.bak" -SQLTrustedConnection

(Private information ommitted)

Restore-SQLDatabase

This module allows you to connect to an SQL server and use native SQL commands to perform a restore from a file from pretty much any windows machine with powershell installed.

This module supports trusted and untrusted connections and has built in error checking to catch typical problems and returns a job report as an object. The module will first try to connect and ensure you have the neccessary access rights, attempt to locate the backup file, set the sql database to single user mode and assuming all has gone well then perform the task at hand.

restore-SQLdatabase -SQLServer "sqlserver" -SQLDatabase "database" -Path "\\server\share$\backup.bak" -SQLTrustedConnection

(Private information ommitted)

If for any reason, the restore doesn’t work correctly the function will take the corrective action of setting the database back to multi-user.

A full copy of the module to be viewed can be found after the jump below:

<#
    Author: Andrew Morgan (@andyjmorgan on Twitter, http://www.andrewmorgan.ie)
    Allows backup of sql servers to disk and restoration
    Version: 1.0
    Any comments/ feedback welcome, ping me on twitter, drop me a comment on the blog or via e-mail (andrew at andrewmorgan dot ie)
#>

Function Backup-SQLdatabase{
    <#
        .SYNOPSIS
            Backs up an sql database to local or remote disk.

        .DESCRIPTION
            This script will perform a full backup of the specified database and save it to a local or network disk.

        .PARAMETER  SQLServer
            Specifies the sqlserver or instance name you wish to connect to and backup.

        .PARAMETER SQLDatabase
            Specifies the Database name you wish to connect to and backup.

        .PARAMETER Path
            Specifies the path to which you wish to backup the database, it can be the local disk on the sql server or network share.

        .PARAMETER TrustedConnection
            Specifies to the function that you wish to connect via your current credentials.

        .PARAMETER SQLusername
            Specifies the username you wish to use to connect to the SQL server. Use this when you dont wish to use a trusted connection.

         .PARAMETER SQLpassword
            Specifies the password you wish to use to connect to the SQL server. Use this when you dont wish to use a trusted connection.

        .EXAMPLE
            PS C:\> Backup-SQLdatabase -SQLServer "sqlserver" -SQLDatabase "database" -Path "\\server\share$\backup.bak" -SQLTrustedConnection

            This command backs up the database "database" on sql server "sqlserver" to the specified path using the local credentials.

        .EXAMPLE
            PS C:\> Backup-SQLdatabase -SQLServer "sqlserver" -SQLDatabase "database" -Path "\\server\share$\backup.bak" -SQLusername "user" -sqlpassword "password"

            This command backs up the database "database" on sql server "sqlserver" to the specified path using the account name user and password of password.

        .INPUTS
            Switch

        .OUTPUTS
            Results

        .NOTES
             http://www.andrewmorgan.ie for support information.

        .LINK
            http://www.andrewmorgan.ie for more information
            backup-sqldatabase
            restore-sqldatabase
    #>

    param(
        [Parameter(Mandatory=$true)]
        [string]$SQLServer,
        [Parameter(Mandatory=$true)]
        [string]$SQLDatabase,
        [parameter(Mandatory=$true)]
        [string]$Path,
        [switch]$TrustedConnection,
        [string]$SQLusername,
        [string]$SQLpassword
    )

    ## Performing error checking ##

    if ($path -notlike "*.bak"){
        write-warning "the file extension should be .bak, Closing."
        break}

    if ($TrustedConnection -eq $false){
        if ($SQLUsername -eq ""){
            write-warning "The SQL Username variable must be defined, Closing without fully loading module, please ensure you've defined this entry and try import this module again."
            break}

        if ($SQLPassword -eq ""){
            write-warning "The SQL Password variable must be defined, Closing without fully loading module, please ensure you've defined this entry and try import this module again."
            break}
    }

    new-item $path -itemtype File -ErrorAction SilentlyContinue | OUT-NULL
    if (!(test-path $path)){
        write-warning "Could not create the backup file, closing"
        break
    }

    $SQLConn = New-Object System.Data.SQLClient.SQLConnection

    #checks for a trusted SQL connection
    if ($TrustedConnection -eq $false){
        #Using an SQL account for login
        $SQLConn.ConnectionString = "Server=$SQLServer; user id=$SQLusername ; Password=$SQLpassword; Trusted_Connection=False"}
    Else {
        #Using a trusted connection
        $SQLConn.ConnectionString = "Server=$SQLServer; Trusted_Connection=True"
    }

## Trying a connection and capture a failure ##

    write-host "Attempting to connect to the Specified SQL server: " -nonewline -foregroundcolor yellow
    try {
        $SQLConn.Open()
        write-host "Success" -foregroundcolor Green
    }
    catch {
        write-host "Failed!" -foregroundcolor Red
        Write-warning "An exception was caught while attempting to open the SQL connection, please confirm the login details are correct and try again."
        Break
    }

    $SQLCmd = New-Object System.Data.SQLClient.SQLCommand
    $SQLcmd = $SQLconn.CreateCommand()
    $sqlcmd.commandtimeout=0
    $SQLcmd.CommandText="BACKUP DATABASE $sqldatabase TO DISK = '$path' WITH INIT"
    $starttime = Get-date
    try{
            $SQLcmd.Executenonquery() | out-null
            $backupsize=[math]::round((Get-ItemProperty $path).LENGTH / 1048576,0)
            $result="Success"
        }
    catch{
            $result="Failed"
            write-warning "An Exception was caught while backing up the database!"
            write-warning "$_"
        }
    finally{

            $timetaken=[math]::round(((get-date) - $starttime).totalseconds,0)
            $SQLconn.close()
            $report=new-object PSObject -Property @{
                SQLServer=$SQLserver;
                Database=$sqlDatabase;
                Result=$result;
                Timetaken="$timetaken Seconds";
                BackupSize="$backupsize MB";
                BackupFile=$path
            }
        }
        return $report

}

Function Restore-SQLdatabase{
    <#
        .SYNOPSIS
            Restore an sql database from local or remote disk.

        .DESCRIPTION
            This script will perform a full restore of the specified database from a local or network disk.

        .PARAMETER  SQLServer
            Specifies the sqlserver or instance name you wish to connect to and restore.

        .PARAMETER SQLDatabase
            Specifies the Database name you wish to connect to and restore.

        .PARAMETER Path
            Specifies the path to which you wish to restore the database from, it can be the local disk on the sql server or network share.

        .PARAMETER TrustedConnection
            Specifies to the function that you wish to connect via your current credentials.

        .PARAMETER SQLusername
            Specifies the username you wish to use to connect to the SQL server. Use this when you dont wish to use a trusted connection.

         .PARAMETER SQLpassword
            Specifies the password you wish to use to connect to the SQL server. Use this when you dont wish to use a trusted connection.

        .EXAMPLE
            PS C:\> restore-SQLdatabase -SQLServer "sqlserver" -SQLDatabase "database" -Path "\\server\share$\backup.bak" -SQLTrustedConnection

            This command restores the database "database" on sql server "sqlserver" from the specified path using the local credentials.

        .EXAMPLE
            PS C:\> restore-SQLdatabase -SQLServer "sqlserver" -SQLDatabase "database" -Path "\\server\share$\backup.bak" -SQLusername "user" -sqlpassword "password"

            This command restores the database "database" on sql server "sqlserver" from the specified path using the account name user and password of password.

        .INPUTS
            Switch

        .OUTPUTS
            Results

        .NOTES
             http://www.andrewmorgan.ie for support information.

        .LINK
            http://www.andrewmorgan.ie for more information
            backup-sqldatabase
            restore-sqldatabase
    #>
    param(
        [Parameter(Mandatory=$true)]
        [string]$SQLServer,
        [Parameter(Mandatory=$true)]
        [string]$SQLDatabase,
        [parameter(Mandatory=$true)]
        [string]$Path,
        [switch]$TrustedConnection,
        [string]$SQLusername,
        [string]$SQLpassword
    )

    ## Performing error checking ##

    if ($path -notlike "*.bak"){
        write-warning "the file extension should be .bak"
        break}

    if ($TrustedConnection -eq $false){
        if ($SQLUsername -eq ""){
            write-warning "The SQL Username variable must be defined, Closing without fully loading module, please ensure you've defined this entry and try import this module again."
            break}

        if ($SQLPassword -eq ""){
            write-warning "The SQL Password variable must be defined, Closing without fully loading module, please ensure you've defined this entry and try import this module again."
            break}
    }

    if (!(test-path $path)){
        write-warning "Could not find the backup file, closing"
        break
    }

    $SQLConn = New-Object System.Data.SQLClient.SQLConnection

    #checks for a trusted SQL connection
    if ($TrustedConnection -eq $false){
        #Using an SQL account for login
        $SQLConn.ConnectionString = "Server=$SQLServer; user id=$SQLusername ; Password=$SQLpassword; Trusted_Connection=False"}
    Else {
        #Using a trusted connection
        $SQLConn.ConnectionString = "Server=$SQLServer; Trusted_Connection=True"
    }

## Trying a connection and capture a failure ##

    write-host "Attempting to connect to the Specified SQL server: " -nonewline -foregroundcolor yellow
    try {
        $SQLConn.Open()
        write-host "Success" -foregroundcolor Green
    }
    catch {
        write-host "Failed!" -foregroundcolor Red
        Write-warning "An exception was caught while attempting to open the SQL connection, please confirm the login details are correct and try again."
        Break
    }

    $SQLCmd = New-Object System.Data.SQLClient.SQLCommand
    $SQLcmd = $SQLconn.CreateCommand()
    $sqlcmd.commandtimeout=0
    $SQLcmd.CommandText="ALTER DATABASE $SQLDatabase
                        SET SINGLE_USER WITH
                        ROLLBACK IMMEDIATE
                        RESTORE DATABASE $SQLDatabase
                        FROM DISK = '$path'
                        WITH REPLACE"
    $starttime = Get-date
    try{
            $SQLcmd.Executenonquery() | out-null
            $result="Success"
        }
    catch{
            write-warning "An Exception was caught while restoring the database!"
            write-warning "$_"
            write-warning "attempting to recover the database"
            $SQLcmd.CommandText="ALTER DATABASE $SQLDatabase SET MULTI_USER"
            $SQLcmd.Executenonquery() | out-null
            $result="Failed"
        }
    finally{
            $SQLconn.close()
            $timetaken=[math]::round(((get-date) - $starttime).totalseconds,0)
            $report=new-object PSObject -Property @{
                SQLServer=$SQLserver;
                Database=$sqlDatabase;
                Result=$result;
                Timetaken="$timetaken Seconds";
            }

    }
    Return $Report

}

About these ads
Categories: PowerShell Scripting
  1. December 14, 2011 at 4:52 pm

    A few suggestions:

    Whenever I use a mandatory parameter, I include a HelpMessage attribute as well.

    When using a switch, you don’t have to use the comparison operator.

    if ($TrustedConnection) { …}

    or if you want the opposite: if (-Not $TrustedConnection)

    I’d suggest turning on cmdletbinding for the functions so you can include support for -Whatif since these operations are critical. You could also then change all the messages from Write-Host (which is good) to Write-Verbose. Or consider using Write-Progress.

    To use online help, the URL needs to be in its own .LINK

    .LINK
    http://www.andrewmorgan.ie
    .LINK
    backup-sqldatabase
    restore-sqldatabase

    Now If I run help backup-sqldatabase -online, I’ll go to the web link.

    Finally, I’m not a big fan of the Return keyword. More from a philosophical viewpoint more than anything, I would prefer writing the output to the pipeline. Personally, I would use Write-Output $Report.

    • December 15, 2011 at 8:48 am

      Thanks very much for the tips Jeff! It’s greatly appreciated!

  2. May 17, 2012 at 12:49 am

    You should post it on GitHub allowing contributions. Also I’ve found a bug in the following piece of code:

    ALTER DATABASE $SQLDatabase
    260
    SET SINGLE_USER WITH
    261
    ROLLBACK IMMEDIATE
    262
    RESTORE DATABASE $SQLDatabase
    263
    FROM DISK = ‘$path’
    264
    WITH REPLACE”

    you should put $SQLDatabase in “[]”
    RESTORE DATABASE [$SQLDatabase]

    • May 18, 2012 at 7:01 am

      Hi Restuta,

      I’ll bear that in mind.

      With reference to the bug, was it generating an error?

      • May 18, 2012 at 2:51 pm

        Yes, just try to restore database to any with “-” in it’s name. “my-database” for instance.

      • May 18, 2012 at 4:59 pm

        Thanks for this, I’ll update the script

  3. Sara
    June 11, 2012 at 1:27 pm

    Thanks for script, but when I try to use it I get “An expression was expected after ‘(‘. At SQLConn.Open (<<<< )"

    Why is that?

    Thankful for quick answer.

    • June 11, 2012 at 1:41 pm

      Hi Sara,

      Sounds like you have the connection strings mixed up, did you modify the file after you downloaded it?

      Email me on andrew[at]andrewmorgan.ie and send me a copy of the file you are using, you probably dont want to publish it here :)

      A

  4. Dan
    September 28, 2012 at 4:53 pm

    Andrew – thanks for the great work here! It’ll be a massive time saver.

    I’m able to get a database backed up to a file, but I’m trying to duplicate rather than doing a restore over the original. I started with errors about the new database not existing. I’m able to script around that, but now I’m getting errors about the mdf and ldf being in use. Can you point me in the direction of some resources where I can look into modifying your work to cope with this?

    Thanks again for this – it’ll massively speed up the deployment of our test environment (a great thing to achieve a few weeks before my Performance Review!)

    • October 1, 2012 at 10:05 am

      Hi Dan,

      This script was the extent of my SQL knowledge I’m afraid :(

      A

  5. November 22, 2012 at 1:40 pm

    Hello, here is a simple way of backing up and restoring via poowershell
    http://pragmaticcoding.com/index.php/sharepoint-2010-simple-backuprestore-via-powershell/

  6. Bynez
    November 27, 2012 at 10:58 pm

    Your help documentation states that the parameter is named SQLTrustedConnection, but in the PS code it is actually -TrustedConnection (no SQL at the beginning)

    restore-SQLdatabase -SQLServer “sqlserver” -SQLDatabase “database” -Path “\\server\share$\backup.bak” -SQLTrustedConnection

  7. Bryan
    April 24, 2013 at 9:57 pm

    I get an error that says Access is denied for the backup device… (Operating System Error 5) Strange thing is that it creates the backup file, just doesn’t know how to write to it after that. Any ideas?

  1. No trackbacks yet.

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

Follow

Get every new post delivered to your Inbox.

Join 2,300 other followers

%d bloggers like this: