Stuart Moore

Musings of a Data professional

Stuart Moore

Category: sql server Page 2 of 3

Creating a Time Series database for SQL Server Restore testing

As a prelude to a post describing some new features of the dbatools Restore-DbaDatabase function, I thought I’d just got through how I create a time series database to give me something to test restoring to Point in Time with minimal overhead.

This sort of database can also be really handy for your own training if you want to play with point in time restores, or explore what can be done with standby restores.


I want to end up with:

  • A database – basic unit of restore
  • Single table – Let’s keep this small and simple
  • Table contains a series of rows written at a set time interval

So the table should end up like this:

StepID DateStamp
3 08/06/2017 09:46:45
4 08/06/2017 09:47:15

We should end up with a nice spread of times so we can easily say ‘Restore to this point in time’ and then be able to check we’ve actually gotten there

Read More

Track SQL Server Index Usage over time

As part of my talk at the Nottingham SQL Server User group last night, I offered some tips on how to track SQL Server index usage over time.

sys.dm_db_index_usage_stats is a very handy table for seeing how many seeks/scans and updates your indexes get. But there are a few issues with. Firstly it’s a DMV, so it will be flushed on a number of events:

  • SQL Server Instance Restart
  • Database Detach
  • Database Close
  • SQL Server 2012/2014 – Alter Index rebuild

For a good thorough check normally a full months worth of stats are needed, this normally covers most of the business processes. I may event be useful to compare stats across months so you also capture events such as Financial Year end.

A better break down of when the scans and seeks happened can be very useful. It’s all well and good knowing your index was scanned 5000 times and Seeked across 15000 times, but what processes were doing that? Was it OLTP workload during 9-5 or was it the overnight processing? This could make a big difference to any planned Index changes.

So here’s a technique I use. It’s pretty simple and allows you to tune it to your own needs,

Read More

Easier SQL Server Restores using DBATools

T-SQL Tuesday So for this month’s T-SQL Tuesday Kenneth asked us to talk about backups.

If you’ve been reading my blog for a while, you’ll be aware that I have a ‘thing’ about backups and restores (31 days of posts in a row on a topic shows a slight interest!), and I’ve been speaking at SQL user groups on the subject for years as well.


During my whole DBA career I’ve been aware that the absolute worst thing that can happen to me is having to admit to my employer that I can’t recover their precious data to the point they want (RPO), or do it in a time that they’re happy with (RTO).

To me, if either of those happens it’s a Resume Generating Event! And that’s an event I’ve spent years avoiding. And having those bases covered also stops other mistakes (what, I ran that DROP TABLE in prod?) from becoming RGEs as you’ve got a safety net.

As I’ve moved into management, I’ve also come to realise it’s even worse when you hear from someone who reports to you that they aren’t 100% sure about their backups, this could be 2 RGEs for the price of one!

So I’ve always been hugely keen on backups, and more importantly on ensuring that they’ve been tested and you’ve practiced your restores as much as possible.

SQL Server makes it very easy to perform backups, plenty of options to perform and unless you’ve a fairly large or specialised database setup then there’s fairly little setup required.

Restoring a database, now thats a whole other story. If you’re like most people, you’re taking a full backup each night and then transaction log backups hourly through the day. So at worst case you’re got 25 files to restore.

On a larger system you could have a weekend full backup a twice daily diff backup and 10 minute transaction log backups. Now, these things always fail just before the next full backup so you’re looking as 1 full backup, 13 differential backups and 71 transaction backups to process and restore! Now that’s going to take a while to script out and get going!

SSMS will help you out if you have access to the server that made the backups and it can see msdb, if that whole instance is now a smoking crater, all you’re going to have is a folder full of backup files.

So what to do about it?

Read More

Deleting Large amounts of data in SQL Server – Part 2

In the last part (Part 1) we looked at a reasonably generic script for deleting large amounts of data from SQL Server that helps when you issue a delete statement and SQL Server just sits there with your transaction logs filling up and refusing to clear out.

This time, we’re going to look at how to deal with that wonderful error message of:

Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK__address__personi__239E4DCF". The conflict occurred in database "dbgrow", table "dbo.address", column 'personid'.
The statement has been terminated.

Which is SQL Server’s way of letting you know that the data you’re trying to delete is referenced as part of a Foreign Key relationship, so can’t be deleted unless you delete the record on the other end of the relationship.

So how do you work around this?

Read More

SQL Server, getting the size of table partitions with T-SQL

Needed to quickly tell someone how large the partitions on a CRM 2011 auditbase table were (neither of us had access to the nice CRM GUI to answer it (ah they joys of not being trusted!)). So if anyone else needs some thing similar this is what I came up with:

sum(alloc.total_pages/128) AS TotalTableSizeInMB,
sum(alloc.used_pages/128) AS UsedSizeInMB,
sum(alloc.data_pages/128) AS DataSizeInMB
FROM sys.allocation_units AS alloc
INNER JOIN sys.partitions AS part ON AU.container_id = CASE WHEN alloc.type in(1,3) THEN part.hobt_id ELSE part.partition_id END
LEFT JOIN sys.indexes AS idx ON idx.object_id = part.object_id AND idx.index_id = part.index_id
where part.object_id = object_id('auditbase')
group by partition_number

If you compare the numbers with thos provided by CRM you’ll notice a differece:


And that’s just because Dynamics CRM doesn’t include the space the indexes take up.

Nottingham SQL Server User group begins

After many successful years running in Leicester we’re proud to announce that the PASS East Midlands chapter is moving to Nottingham for 2016.

Gavin had been running the group since it’s birth, but has now handed the reigns over to myself and Tobiasz. As we’re both based in Nottingham we have decided to move the SQL User group to Nottingham. With the response we had for Nottingham’s first SQL Relay event we wanted to build on that, and this also makes it more accessible with Nottingham’s connections to the rest of the East Midlands and Lincolnshire area.

We’re going be based at Capital One’s Trent House on Station Street, easy to get to from Derby, Leicester, Loughborough, Lincoln, Sheffield and all parts of Nottingham

We have 4 SQL Server User group dates lined up for next year:

As with all PASS SQL User groups there is no charge for attending, yep, that’s free training! We’ll even provide some food and drinks while you learn!

Myself and Tobiasz are speaking at the first event, and we’re currently lining up some great home and international speakers for the other sessions.

But we’d love to hear from you if there’s any particular topics you’d like covering, I’m happy to go out and find speakers covering areas of special interest to our members. Also let us know at what level you’d like sessions presented, if you want an introduction to a particular topic that’s great, as is wanting a level 500 brain warper from the likes of Bob Ward!

All details for future meetings will be posted via our Meetup page – PASS East Midlands User Group.

Please register with the meetup group to keep up to date. You’ll also need to register for the event to attend. This is because we need to provide Capital One with a list of names to be signed in at security, so if you haven’t registered then you won’t be allowed in I’m afraid.

Look forward to seeing some of you there. Please post any comments here or at PASS East Midlands User Group

Deleting Large amounts of data in SQL Server – Part 1

There comes a time when you’ll be asked to remove a large amount of data from one of your SQL Server databases. Perhaps an archive of order information is needs pruning, or removing those session records that aren’t needed anymore.

Yu’ve been off to have a look at, and it all looks simple enough. Looks like there’s a couple of million rows to delete, but you can identify them all easily. How easy is this going to be you think:

DELETE * FROM Table1 where DateAdded<getdate()-90;

And then it just sits there, and sits there, and sits there. Or you get a nice error message about breaking a key constraint. What to do about it?

Read More

Adding SQL Server jobs using PowerShell

PowerShellA sudden requirement for a new SQL Server agent job to be put on to every instance in our Enterprise was hardly greeted with joy. At least by those who hadn’t been playing with PowerShell.

Just like everything else in SQL Server, we can automate SMO to achieve our ends. And once we’ve got it working on one SQL Sever instance, it’s simple to extend this to hundreds.

Read More

Migrating Databases From SQL Server 2000 to SQL Server 2012 using PowerShell

PowerShell IconI seem to have a spate of 3rd party applications finally moving from SQL Server 2000 to a version that’s a lot newer (SQL Server 2012). This means a lot of database migration, and as they want to keep the applications up while they test the new versions this means using backup and restore.

As Microsoft is only supporting restoring backups from the previous 3 versions (SQL Server 2005, 2008 and 2008R2) with SQL Server 2012 this the process actually has to go like this:

  1. Backup the SQL Sever 2000 database
  2. Restore the database onto an instance of SQL Sever 2005, 2008 or 2008R2
  3. “Upgrade” database by setting Compatibility Level to new Server version
  4. Backup new database
  5. Restore database onto SQL Server 2012 instance
  6. “Upgrade” database by setting Compatibility Level to new Server version

Which to me is a lot of manual handling for quite a lot of databases. And did I mention that steps 2 and 5 will probably also mean relocating data and log files (potentially multiples of each), and some full text indexes as well for good measure? All of which makes this an unappetising prospect to do manually.

So time for some PowerShell automation. First off we import our good friend the SQLPS module, and then define 2 simple functions:

Import-Module SQLPS -DisableNameChecking

set-location c:\

function DataBase-Restore
param([String]$SQLServerName, [String]$DataBaseName, [String]$BackupFilePath, [String]$RestorePath, [Boolean]$Upgrade=$FALSE)

    $sqlsvr_rest = New-Object -TypeName  Microsoft.SQLServer.Management.Smo.Server ($SQLServerName)
    $restore = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Restore
    $devicetype = [Microsoft.SqlServer.Management.Smo.DeviceType]::File

    $restoredevice = New-Object -TypeName Microsoft.SQLServer.Management.Smo.BackupDeviceItem($BackupFilePath,$devicetype)
    $restore.Database = $DataBaseName
    $restore.replacedatabase = $false


    if ($RestorePath.Length -gt 0){
        $restorefiles = $restore.ReadFileList($sqlsvr_rest)
        foreach($file in $restorefiles){
	        $rf = new-object -typename Microsoft.SqlServer.Management.Smo.RelocateFile
	        $rf.LogicalFileName = $file.logicalname
	        $rf.PhysicalFileName = $RestorePath+(split-path $file.PhysicalName -Leaf)
	        Remove-Variable rf

    if ($Upgrade -eq $TRUE){
        $sqlsvr_rest.Databases.Item($DataBaseName).CompatabilityVersion = $sqlsvr_rest.Databases.Item("Model").CompatabilityVersion

function Database-Backup
param([String]$SQLServerName, [String]$DataBaseName, [String]$BackupFilePath)
    $sqlsvr = New-Object -TypeName  Microsoft.SQLServer.Management.Smo.Server($SQLServerName)
    $backup = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Backup
    $backup.Action = [Microsoft.SQLServer.Management.SMO.BackupActionType]::Database
    $backup.Database = $DataBaseName
    $backupname = $BackupFilePath

    $devicetype = [Microsoft.SqlServer.Management.Smo.DeviceType]::File

    $backupdevice = New-Object -TypeName Microsoft.SQLServer.Management.Smo.BackupDeviceItem -ArgumentList $backupname,$devicetype


The eagle eyed amongst you will note Set-Location c:\ just after the import-module. That’s because the last thing SQLPS does is Set-Location sqlserver:\ to move to the SQLSERVER drive, which will make some of the later file copying much trickier, so we just make sure we’re back on a file drive.

First function Database-Restore takes the following parameters:

  • SQLServer Name – string of Server Name (and Instance Name if it’s a Named Instance)
  • DatabaseName – string of your Database Name
  • BackupFilePath – string of where the backup you want to restore is held
  • RestorePath – the path you want to move restored files to
  • Upgrade – optional boolean parameter to determine whether to upgrade the restored database to the server’s current compatibilitylevel

Nothing too unusual in the function, going through it quickly we perform the following operations:

  • Create a connection to SQL Server
  • Create a restore object, set it’s database, build a restore device and add our backup file to it. We set the option not to overwrite an existing database, just in case…..
  • If we’ve passed in a folder to move them to, then Read a list of files from the backup file, then loop through it creating a File Relocation object for every one of them, and adding them to our Restore object
  • Perform the actual restore (line 31)
  • Then if we’ve set Upgrade to TRUE we set the compatibility level equal to that of Model, and then call AlterDB() to write it back

The second function Database-Backup takes the following parameters:

  • SQLServer Name – string of Server Name (and Instance Name if it’s a Named Instance)
  • DatabaseName – string of your Database Name
  • BackupFilePath – string of where to put the backup.

So how are these going to help us migrate our databases? Simple, we can call them repeatedly and leave them running while we get on with something more interesting. If you’re lucky enough to have a shared backup drive that all your SQL Server Instances can read and write to then the upgrade is as easy as:

Database-Backup -SQLServerName "SQL2000\InstanceA" -DatabaseName "App1" -BackupFilePath "\\shared\backupdrive$\app1-2000.bak"
Database-Restore -SQLServerName "SQL2008" -DatabaseName "App1" -BackupFilePath "\\shared\backupdrive$\app1-2000.bak" -RestorePath "e:\dbs" -Upgrade $TRUE
Database-Backup -SQLServerName "SQL2008" -DatabaseName "App1" -BackupFilePath "\\shared\backupdrive$\app1-2005.bak"
Database-Restore -SQLServerName "SQL2012\InstanceB" -DatabaseName "App1" -BackupFilePath "\\shared\backupdrive$\app1-2005.bak" -RestorePath "f:\dbs" -Upgrade $TRUE

If not, then you’ll have to do some file moving yourself like so:

Database-Backup -SQLServerName "SQL2000\InstanceA" -DatabaseName "App1" -BackupFilePath "g:\backups\app1-2000.bak"
Copy-Item "\\SQL2000\g$\backups\app1-2000.bak" "\\SQL2008\f$\backups\app1-2000.bak"
Database-Restore -SQLServerName "SQL2008" -DatabaseName "App1" -BackupFilePath "f:\backups\app1-2000.bak" -RestorePath "e:\dbs" -Upgrade $TRUE
Database-Backup -SQLServerName "SQL2008" -DatabaseName "App1" -BackupFilePath "f:\backups\app1-2008.bak"
Copy-Item "\\SQL2008\f$\backups\app1-2005.bak" "\\SQL2012\f$\backups\app1-2005.bak"
Database-Restore -SQLServerName "SQL2012\InstanceB" -DatabaseName "App1" -BackupFilePath "f:\backups\app1-2005.bak" -RestorePath "f:\dbs" -Upgrade $TRUE

When placing the files remember that the script is running as the Windows account calling it, but the SQL Sever backups and restores will be running as the Database Engine Account.

Now I can happily migrate those databases to a happier place repeatedly without having to take time out from other larger projects. Win for me, and win for the people I’m working for.

Downloads for “Automating SQL Server Backups and Restores with PowerShell” presentation from Southampton SQL Sever User Group 07/08/2013

Hi, if you’ve just landed here you might be interested in the series of posts >31 days of SQL Server Backup and Restore with PowerShell where I’ll be providing more information about the concepts and scripts in this presentation

Scripts and Demos for PowerShell SQL Server Backup and Restore presentation given at Southampton SQL Server User Group 7th August 2013

Includes the presentation as a pdf, all the PowerShell scripts used during the presentation, the SQL scripts to build the demo databases, plus the backups of a couple of the databases to speed things up.

Readme.txt gives a quick overview of each script and the order to run through them in.

Any problems or Questions, please get in touch.

Page 2 of 3

Powered by WordPress & Theme by Anders Norén