When it comes to moving a database from one server to another or from one file to another, the most common method used is the Database Attach or Detach process. However, it is important to stay updated with the latest practices and avoid using deprecated methods.

During a recent visit to an organization, I discovered that many developers were still using the outdated method of attaching databases. This prompted me to create this blog post to shed light on the new and correct method of attaching databases.

The deprecated method, sp_attach_db, is no longer recommended for attaching databases. Instead, we should use the following approach:

-- Detach Database
USE [master]
GO
EXEC MASTER.dbo.sp_detach_db @dbname = N'AdventureWorks2014_new'
GO

-- Correct Way to Attach Database
USE [master]
GO
CREATE DATABASE [AdventureWorks2014_new] ON
( FILENAME = 'E:\AdventureWorks2012_Data_new.mdf'),
( FILENAME = 'E:\AdventureWorks2012_log_new.ldf')
FOR ATTACH
GO

By following this method, you ensure that your database attachments are done using the recommended approach.

It is important to stay updated with the latest practices in SQL Server to ensure optimal performance and security. Using deprecated methods can lead to compatibility issues and potential vulnerabilities.

So, the question is, do you still use the old methods to attach databases? If yes, I strongly suggest that you start using the new method going forward.

For a more visual explanation, you can watch the SQL in Sixty Seconds video below:

Stay tuned for more SQL Server tips and best practices!