SQL SERVER – Attach or Detach Database – SQL in Sixty Seconds #068
- by Pinal Dave
When we have to move a database from one server to another server or when we have to move a database from one file to another file, we commonly use Database Attach or Detach process. I have been doing this for quite a while as well. Recently, when I was visiting an organization I found that in this organization lots of developers are still using an older version of the code to attach the database. I quickly pointed that out to them the new method to attach the database, however it was really interesting to find out that they really did not know that sp_attach_db is now a deprecated method to attach the database. This really made me to do today’s SQL in Sixty Seconds. I demonstrate in this SQL in Sixty Seconds how to attach or detach the database using a new method of attaching database.
The code which I have used in this code is over here:
-- Detach Database
USE [master]
GO
EXEC MASTER.dbo.sp_detach_db @dbname = N'AdventureWorks2014_new'
GO
-- Deprecated Way to Attach Database
USE [master]
GO
EXEC MASTER.dbo.sp_attach_db 'AdventureWorks2014_new',
'E:\AdventureWorks2012_Data_new.mdf',
'E:\AdventureWorks2012_log_new.ldf'
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
Here is the question back to you – Do you still use old methods to attach database? If yes, I suggest that you start using the new method onwards.
SQL in Sixty Seconds Video
I have attempted to explain the same subject in simple words over in following video.
Action Item
Here are the blog posts I have previously written on the subject of SA password. You can read it over here:
SQL SERVER – 2005 – T-SQL Script to Attach and Detach Database
SQL SERVER – Move Database Files MDF and LDF to Another Location
SQL SERVER – 2005 Take Off Line or Detach Database
SQL SERVER – Attach mdf file without ldf file in Database
SQL SERVER – Copy Database from Instance to Another Instance – Copy Paste in SQL Server
You can subscribe to my YouTube Channel for frequent updates.
Reference: Pinal Dave (http://blog.sqlauthority.com)Filed under: PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, SQLAuthority Book Review, SQLAuthority News, T SQL, Video
