Post reply

Attaching/Detaching Database

  • May 10, 2005 at 12:18 pm

    #88874

    Hello SQLers,

    Does anyone have advice on attaching/detaching a database WITHOUT the use of Enterprise Manager? Is there a way through SQL syntax (so I can use C++ or VB) to do it?

  • May 10, 2005 at 12:31 pm

    #557859

    You can lookup

    sp_attach_db

    and

    sp_detach_db

    in the books online

  • May 10, 2005 at 12:55 pm

    #557873

    THANK YOU!!!

    It works great!

  • May 10, 2005 at 1:00 pm

    #557875

    HTH

  • May 11, 2005 at 2:24 am

    #557971

    A better way would be to use SQL-DMO. It can be used for most functions available via EM. I have a backup/restore utility written in VB which can also drop/detach/reorganise/reindex and a whole host of other functions just using SQL-DMO.

     

    Steve.

  • May 11, 2005 at 10:39 am

    #558154

    Hi All

    i faced a strange behaviour for attach /detaach and reattach .

     

    the database mdf files are under data folder and ldf goes to logs folder , the detach happens successfull i deleted the physical log file and ran the attach from EM it shows the mdf file cirectly and rd cross on ldf file path , so i gave there logs folder path , and clicked ok . it attaches the database successfully but when i notice the physical  path of trn log  file its in data folder and not in logs folder.

    i tried 2-3 times more but its happening same and with every other db on this server . since this server is not configured by me so i dont know the cause of this behaviour.

     

    pls help with your views

     

    Regards

    Deepa

     

     

     

  • May 11, 2005 at 11:41 am

    #558184

    Did you say that you DELETED the physical log file (LogFileName.ldf)?


    -Dan

  • May 11, 2005 at 2:41 pm

    #558220

    Hi

     

    No actually i renamed the ldf file so that attach recreates the trn file and i can  save some space in disk as the log was  around 5 GB.

     

    Regards

    Deepa

  • May 11, 2005 at 3:54 pm

    #558231

    1. Try DBCC Shrinkfile to reduce the size of the log.  The method you are using is DANGEROUS!

    2. The default location for the log files is apparently set to the same as the data files.  In Enterprise Manager, right click on the server, select Properties, select the Database Settings tab.  The default file locations are at the bottom of the popup.

    Steve

  • May 11, 2005 at 4:53 pm

    #558239

    Thanks Steve

     

    will work on dbcc next time , but right now i am just finding out why log files are going to data and not into log folder whihc i specify

     

    i checked the database settings at bottom of pop u have written and bothe fields are blank

     

    Regards

    Deepa

  • May 11, 2005 at 11:05 pm

    #558262

    Hi Deepa

    you cannot just delete the trn log files and invent some at the time of attach. When you attach a database files without trn log files it will by default create new trn files in the same where the data files are. You can move those trn log files later, though.

    But the good practice would be to attach the data and log files as they are and then trucate the log files using dbcc if you really want to reclaim some space...

    Hope that helps...

  • May 12, 2005 at 12:31 pm

    #558532

    Hi 

     

    I  have used attach detach many a times on other servers and never ever faced such situation where the filelog file is going into data folder( and not in the one where it was eralier before  detaching)

    so this is stabge behaviour.

     

    i am trying to find cause of it

    secondly once primary trn log file is attached how can i move it to other location ?

    Please help

     

    Regards

    Deepa

  • May 12, 2005 at 2:39 pm

    #558559

    To move the log to a different location, detach the database, copy the file to your destination and attach the file stating the new location.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply