SQL Server 2008 R2 is quickly approaching End Of Life (July 9,2019). Some are still looking for ways to migrate their databases to newer releases of SQL Server such as SQL Server 2017. This blog post details a simple method for migrating simple databases using detach and attach. By simple, I mean databases without complicated transnational replication technologies such as mirroring. I imagine this method would used for databases supporting simple applications.

Steps (High Level)

Detach Database
Copy Database files (.mdf and.ldf) from the source server to the target server
Attach the Database files to the new instance

Detach the Database

On the SQL Server 2008 R2 server, connect using SSMS right-click the database then select ‘Detach.’ Make sure to drop connections, and press OK.

Copy the Files

Once the databases are detached, the should no longer show in the menu. At that point, you are free to copy the database files to a new server.

Attach the Database

Attaching the database can be done via a script similar to the code the below. Make sure to change the values to match your file locations and database names.

USE master;
GO
CREATE DATABASE POC
ON (FILENAME = 'C:\Data\POC.mdf'),
(FILENAME = 'C:\Data\POC_Log.ldf')
FOR ATTACH;
GO

Once the database is attached, you will see it the menu. You can then perform queries against the database to validate that everything is as it should be.

One final validation is to check the database version. In my case, the source database was at version 100 on the SQL Server 2008 R2 server. The database stayed at version 100 once it was attached.

My test of this migration was with a very simple database. I got the idea from something I used to do with my team back in the day with older versions of the software. I know other methods exist, but using detach and attach bailed us out of several situations. I conducted this test to see if old tools would still work. In true Microsoft fashion, they do. Like a lot of Microsoft technologies, it takes a while before they are fully deprecated.

Links

sp_detach_db (Transact-SQL) - https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-detach-db-transact-sql?view=sql-server-2017>

Detach a Database - https://docs.microsoft.com/en-us/sql/relational-databases/databases/detach-a-database?view=sql-server-2017

Prepare for SQL Server 2008 end of support - https://www.microsoft.com/en-us/sql-server/sql-server-2008