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.

    Like

  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.

    Like

  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?

    Like

  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.

    Like

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