Voice of the DBA

Tag: powershell

  • Powershell – Copy the Latest Backup

    I got an email recently where someone asked me how they can refresh a dev environment with Powershell. I guess I’d written something about this in 2009, though that would have been for testing as Red Gate had already banned me from development on SQLServerCentral by that time.

    I dug around and came up with a few partial scripts and cleaned them up for these posts. This post will look at getting the backup and a later one will examine the restore.

    Finding the Latest Backup

    I’ll assume that you make backups on a known path somewhere. My philosophy is that I want the machines to stand alone as much as possible. That means that I don’t want the source machine (the one making the backup) to be working on refreshing the backup elsewhere. I want a pull system.

    For a high level overview, this process looks like this:

    • Search the backup path for files matching a pattern.
    • Find the most recent one, based on date.
    • Copy that most recent file to another location.

    For the sake of consistency and easy, I want to copy the file with the same destination name every time. That will simplify my restore process, which I could easily then do in T-SQL.

    Let’s examine how to do this. I’ve got a folder with a few backups in it.

    backuplatest

    For my PoSh, I’ll start by setting a variable to the path.

    $backuppath = "D:\SQLServer\MSSQL11.MSSQLSERVER\MSSQL\Backup"

    Once I have this, I can now look for the files in this path. To do that, I’ll use Get-Children.

    get-childitem -path $backuppath

    This will return to me a list of the files. That’s what I want, but I want to limit the files to a pattern. In this case, I’m looking for .bak files, from the EncryptionPrimer database. All of these

    There’s nothing special about what I do that’s not contained in plenty of places. I don’t have this running on an environment currently as someone else manages that process, but here’s the process I’ve followed in the past:

    • Find the latest backup (whatever the date) in the source folder.
    • Copy this with a set name to the destination folder, overwriting previous backups with the same name.
    • Restore the known name to the development database, moving files as needed.

    I’ll go through each of these steps in my PoSh script.

    Find the Latest Backup

    This is fairly easy. I’ll use the Get-ChildItem method, which I found in a StackOverflow post. I’ll use a variable for the path I need, and then check the path.

    $backuppath = "D:\SQLServer\MSSQL11.MSSQLSERVER\MSSQL\Backup"

    get-childitem -path $backuppath

    That works well, but since I’m building a process for a specific backup type, I’ll add a filter.

    $backuppath = "D:\SQLServer\MSSQL11.MSSQLSERVER\MSSQL\Backup"

    get-childitem -path $backuppath -Filter "EncryptionPrimer*.bak"

     

    To find the latest backup, we’ll pipe the output through the Where-object filter, removing folders. Then we use sort-object to order things by creation date and select-object to get just the one file.

    $backuppath = "D:\SQLServer\MSSQL11.MSSQLSERVER\MSSQL\Backup"

    get-childitem -path $backuppath -Filter "EncryptionPrimer*.bak" |

        where-object { -not $_.PSIsContainer } |

        sort-object -Property $_.CreationTime |

        select-object -last 1

     

    The last part of the script is the copy-item command, which is again the recipient of piped output. We give a standard name, and path (another variable).

    $backuppath = "D:\SQLServer\MSSQL11.MSSQLSERVER\MSSQL\Backup"

    $destpath = "d:\SQLServer\Backup"

    get-childitem -path $backuppath -Filter "EncryptionPrimer*.bak" |

        where-object { -not $_.PSIsContainer } |

        sort-object -Property $_.CreationTime |

        select-object -last 1 | copy-item -Destination (join-path $destpath "EncryptionPrimer.BAK")

    Once this is done we can restore things. I learned how to do this from PoSh using this post: http://stuart-moore.com/day-11-31-days-sql-server-backup-restore-using-powershell-basic-restore/

    However, since I have a standard backup file name, I’d probably do this in T-SQL and set a job that I can just run anytime. It’s simpler and easier, and since most of the time I’d want to do this from SSMS, a job works well.

    Here’s the PoSh script.

    Import-Module "SQLPS" -DisableNameChecking

    $sqlsvr = New-Object -TypeName  Microsoft.SQLServer.Management.Smo.Server("JollyGreenGiant\SQL2012")

    $BackupFile = "D:\SQLServer\Backup\EncryptionPrimer.BAK"

    #

    #echo ""

    #echo "Databases"

    #echo "———"

    #foreach ( $db in $sqlsvr.Databases) { write-host $db.name }

    echo " "

    echo "Begin Restore"

    echo "============="

    $Restore = New-Object "Microsoft.SqlServer.Management.Smo.Restore"

    $Restore.NoRecovery = $false

    $Restore.ReplaceDatabase = $true

    $Restore.Action = "Database"

    $Restore.PercentCompleteNotification = 10

    $BackupDevice = New-Object ("Microsoft.SqlServer.Management.Smo.BackupDeviceItem") ($BackupFile, "File")

    $Restore.Devices.Add($BackupDevice)

    $RestoreDetails = $Restore.ReadBackupHeader($sqlsvr)

    $logicalFileNameList = $Restore.ReadFileList($sqlsvr)

    $Restore.Database = $RestoreDetails.Rows[0]["DatabaseName"]

    foreach($row in $logicalFileNameList) {

        $RestoreDBFile = new-object("Microsoft.SqlServer.Management.Smo.RelocateFile")

        $RestoreDBFile.LogicalFileName = $row["LogicalName"]

        $RestoreDBFile.PhysicalFileName = $row["PhysicalName"]

        $Restore.RelocateFiles.Add($RestoreDBFile)

            }

    $Restore.SqlRestore($sqlsvr)

    write-host ("Completed the Database Restore operation on server for Database " +  $RestoreDetails.Rows[0]["DatabaseName"] + " on server $server")

     

    That’s it. I ran this a few times, and it worked well. A handy script to get the last backup and have it ready in a dev/test environment.

  • SQL Server Disk Space Emergencies

    One of the things I’ll see happen often with SQL Server instances is that the system will run out of space on a drive. This could be for a variety of reasons, some of which can be prevented, and some cannot. You might have:

    • Don’t delete old backup files
    • Data growth fills the disk over time, usually years
    • tempdb rapid growth that uses all space
    • old import files not deleted over time

    There are other reasons, but I’ve often found that some process will cause an emergency and the SQL Server stops working, or stops backing up database, and administrators are in a panic to free space so the server can continue to function.

    Here’s what I suggest to smooth the way with a series of placeholders and a job.

    Create Placeholders

    First, create a folder on your SQL Server (or really every server) called Placeholder. I’d put it in the root to make it easy to find and standardize on it.

    placeholders3

    In the folder, place a series of files to save space. If you don’t know how to do this, I can show you an easy way. I have 4GB reserved here.

    placeholders4

    Now create a SQL Server Agent job. I might standardize this on every server I have with the same name and path.

    placeholders5

    The job has one step, which is designed to delete one file, each time it’s run.

    Note that I had a slight bug in what I shot above. I had the contig.exe utility in the folder and the first execution of the job deleted that file. Not a big deal in an emergency, because I can run the job again, but I’d make sure that only the place holder files are in this folder on machines.

    Here’s the job. It’s a PoSh type of step.

    placeholders6

    The actual PoSh code is here:

    $fileEntries = [IO.Directory]::GetFiles(“d:\placeholder”);
    $delete = 1;
    foreach($fileName in $fileEntries)
    {
    if ($delete -eq 1)
    {
    Remove-Item $fileName
    $delete = 0;
    }
    }

    When I run this, each time I run it, it’s just a single click or sp_start_job call.

    placeholders7

    After it runs, I have 1GB more free space. If I need more, run it again.

    placeholders8

    However, once you clear your low space condition, I’d be sure I put the placeholders back.

    For the next emergency.

  • Use -eq in Powershell

    I was writing a quick script to work with files and I only wanted to process one file for each execution of a loop. I could have done this multiple ways, but I threw this together:

    $fileEntries = [IO.Directory]::GetFiles(“d:\placeholder”);
    $delete = 1;
    foreach($fileName in $fileEntries)
    {
    if ($delete = 1)
    {
    # do something
    $delete = 0;
    }
    }

    When I ran it, it kept deleting everything in the folder. That was really annoying, and it took me a few minutes to spot the problem. I kept thinking my variable wasn’t getting set to a new value, but it was. The problem was it kept getting reset.

    I first changed to this, but that produced a PoSh error. That’s because I’m working in PoSh and not C.

    $fileEntries = [IO.Directory]::GetFiles(“d:\placeholder”);
    $delete = 1;
    foreach($fileName in $fileEntries)
    {
    if ($delete == 1)
    {
    # do something
    $delete = 0;
    }
    }

    Eventually I remembered that I need to compare things with -eq, so I ended up with this, which worked perfectly.

    $fileEntries = [IO.Directory]::GetFiles(“d:\placeholder”);
    $delete = 1;
    foreach($fileName in $fileEntries)
    {
    if ($delete -eq 1)
    {
         # do something
    $delete = 0;
    }
    }

  • Powershell Tips–Showwindow

    Looking for help is a pain in Powershell. I type something like

    help get-process

    execute this and I’m scrolling up and down. Things scroll off the screen and I have to muddle through, meanwhile, my code is at the bottom of the window.

    posh_help

    Even when things stop at the end of the page, like the old man pages in Unix, it’s a cumbersome way to work through something. However there’s a better way.

    help get-process –showwindow

    If I type this, all of a sudden I get a window of help.

    posh_help_b

    I can resize the window, move it, keep it handy on the section that’s relevant when I’m trying to write a piece of code.

    A very handy tip I got from one of the TechEd 2014 sessions.