September 10, 2012

SQL SERVER - How to get Master Database File (.mdf) ?

Solution:
SELECT
   NAME
   ,TYPE_DESC
   ,STATE_DESC
   ,PHYSICAL_NAME 'LOCATION'
   ,SIZE
FROM MASTER.SYS.MASTER_FILES
WHERE NAME LIKE '% [ DATABASE NAME ] %'


You will get output like below
NAME        TYPE_DESC    STATE_DESC    LOCATION                                                                  SIZE
-----------------------------------------------------------------------------------------------------------------------
TEST        ROWS         ONLINE        c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TEST.mdf         280
TEST_log    LOG          ONLINE        c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TEST_log.LDF     128

2. Go through that resulted location, now you may noticed that, you are unable to copy or move.
     To Copy or Move a file, you need to "detach" required Database from sql server.

3. Copy the required .mdf & .ldf files from the location folder. and attach, file again if it is required.

1 comment:

  1. Thanks for sharing the nice blog with us.All the candidate want to know some more about this blog....
    Online Tutorial For SQL

    ReplyDelete

Recommended Post Slide Out For Blogger