SQL SERVER – Attach or Detach Database – SQL in Sixty Seconds #068

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:

You can subscribe to my YouTube Channel for frequent updates.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

5 thoughts on “SQL SERVER – Attach or Detach Database – SQL in Sixty Seconds #068

  1. Hi Pinal,
    Can you please let me know how to solve following task.
    I have a XML as follows

    14
    XYZ
    32

    12
    Marketing

    Hyderabad
    040—

    i need to get the above data into Excel as
    Employee
    Totalcount 0
    Basic Details
    ID 14
    Name XYZ etc i.e.. all the elements, properties of elements and attributes(child nodes) into one column and their corresponding values into one column.

  2. Hi Pinal ,
    Sorry , the XML structure not getting properly if i paste that in the comment area. Please let me know how to read XML data into Excel such a way that all the all the elements, properties of elements and attributes(child nodes) into one column and their corresponding values into one column.

  3. Pinal, Is there a practical difference here other than using a deprecated stored procedure that may go away in a future release? I understand that it’s just better to use what is intended by the vendor, but want to know if there is a different result here when using the old vs new method. Is the resulting database different in any way?

  4. You can do Attach/Detach with right click on the database to Attach/Detach too. These options are under the right click menu Tasks.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s