SQL Server 2000 and Restore-DbaDatabase

dbatools likes to support as many users as we possibly. It’s written for DBAs who may have no choice but to run older versions of software or don’t want to have to constantly upgrade everything even if they could.

This means we support PowerShell from 3.0 upwards, and we try to go back as far as we can with SQL Server. Due to some major changes between SQL Server 7 and SQL Server 2000 (I was there, and it was a big change), we can only go back to SQL Server 2000.

The change from SQL Server 2000 to SQL Server 2005 was another massive jump in terms of the Storage Engine and other components. The one that causes dbatools’ Restore-DbaDatabase the most issues was a change to the way headers were read from backup files. It appears in SQL 2000 some extra headers were added to the write, but not to the read action.

This means that despite the information being in a SQL Server 2000 backup, you can’t read it with a SQL Server 2000 instance.

Also, the SQL Server backup history in msdb have a major rewrite at the same time. Working that into Get-DbaDbBackupHistory would make that function even harder to maintain.

Fear not, we have a solution. Get-DbaBackupInformation can read the information from the SQL Server 2000 backup headers, as long as you use a more modern version of SQL Server to read the files. It doesn’t matter which edition or version you use, so feel free to spin up whichever copy of SQL Sever Express -gt 2005 and follow along:

    $backupHistory = Get-DbaDbBackupHistory -SqlInstance server\sql2008 -Path \\server2\files$\sql2000\db1 -Recurse
    $backupHistory | Restore-DbaDatabase -SQLInstance sql2000 -DatabaseName db2 -ReplaceDbNameInFile

If you think this is going to be a regular occurence, then to speed things up you might want to have a look at some of the strategies I outlined in Day 16’s post Recreating Backup History to have this information to hand without scanning lots of files


As much as we want to support as many versions transparently as we can, there are some limits. But we still to support those who are left on SQL Server 2000 if we can.

All posts in this series can be found at 31 Days of dbatools Backup and Restores