Say you’re making a big jump and upgrading a database from SQL 2000 to SQL 2014. Of course it only makes sense that this will be a side-by-side upgrade and therefore you’ll need a plan to migrate your DB from the SQL2000 box to the SQL2014 box. SQL2014 won’t allow a restore or attach of a SQL2000 DB. SQL2014 only allows restore or attach of SQL2008 DBs and later. So we need a 2-hop migration plan – here’s a “you get the general idea” sketch of the plan:

1. disconnect DB connections – outage begins
2. backup the SQL 2000 DB
3. copy the backup to the SQL2008 server
4. hop 1- restore the DB to the SQL2008 instance
5. backup the SQL 2008 DB
6. copy the backup to the SQL2014 server
7. hop 2 – restore the DB to the SQL2014 instance
8. redirect DB connections & etc. – outage ends

The good news for our 2-hop plan is the intermediate SQL2008 instance need not be production caliber any way – it’s a just a  temporary stop-over on the way to update the DB so we can restore it to SQL2014. The bad news is, this 2-hop plan complicates the migration plan and extends the potential outage as we copy\paste database backups or data files over the network between the three servers.

But there is a way to simplify the plan and minimize the outage. Let’s try this:

1. disconnect DB connections – outage begins
2. detach the SQL 2000 DB
3. copy the database files to the SQL2014 server
4. hop 1- attach the DB to the SQL2008 instance
5. detach the SQL SQL2008 DB
6. hop 2 – attach the DB to the SQL2014 instance
7. redirect DB connections & etc. – outage ends

We’re using detach\attach here rather than backup\restore but that’s not too important – things would work out fine either way. The thing to notice is that we lost a step and – more importantly – gained back the time it takes to “6. copy the backup to the SQL2014 server”. We only need one copy operation – from the SQL2000 server direct to the target SQL2014 server. Depending on DB size, network speeds and copy methods, that might be a lot of time saved.

How can this work? Because SQL2008r2 (and SQL 2008 with -T1807) allows the database file path to be a UNC – i.e. in this case a file share to the DB files on the SQL2014 server.
So, the hop 1 (step 6 above) might look something like this:

USE master;

GO

CREATE DATABASE MyAdventureWorks

ON (FILENAME = '\\SQLServer2014\MySQLServer\AdventureWorks2012_Data.mdf'),

(FILENAME = '\\SQLServer2014\MySQLServer\AdventureWorks2012_Log.ldf')

FOR ATTACH;

GO

Read up on it here: Description of support for network database files in SQL Server.

This sketch is skimming over quite a few other considerations so be sure think through all the other migration do-s and don’t-s (some more ideas here if you like). And don’t forget to clean up that file share once the migration is complete. Happy (migration) trails to you! 😐