May 10, 2005 at 12:18 pm
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
You can lookup
sp_attach_db
and
sp_detach_db
in the books online
May 10, 2005 at 12:55 pm
THANK YOU!!!
It works great!
May 10, 2005 at 1:00 pm
HTH
May 11, 2005 at 2:24 am
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
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
Did you say that you DELETED the physical log file (LogFileName.ldf)?
-Dan
May 11, 2005 at 2:41 pm
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
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
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
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
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
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