May 13, 2015 at 2:12 pm
Comments posted to this topic are about the item Detach & Attach Procedure
Vimal LohaniSQL DBA | MCP (70-461,70-462)==============================The greatest barrier to success is the fear of failure ** Success is a journey not a destination**Think before you print, SAVE TREES, Protect Mother Nature
May 27, 2015 at 1:51 am
What's wrong with just using sp_detach_db and create database for attach?
If all you're doing here is detaching to move files then this is totally unnecessary anyway and is an old hangover from sql server 2000.
To move files simply offline the database and use
alter database ... Modify file ...
Please see my article at this link[/url] for more info
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 27, 2015 at 3:06 am
Sure. @perry Whittle,
But what, if you have more than 50 or 100 databases or each database has multiple ldf & log files.
Will you write the query.
The script is for those server. I created this script for my production & successfully detach & attach 66 databases each had 4 or 5 files.
Vimal LohaniSQL DBA | MCP (70-461,70-462)==============================The greatest barrier to success is the fear of failure ** Success is a journey not a destination**Think before you print, SAVE TREES, Protect Mother Nature
May 27, 2015 at 5:15 am
yes, you can script the alter database commands quite easily using the system catalog sys.master_files 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 27, 2015 at 7:22 am
Though we do this, we don't have many databases to make it worthwhile. Thanks for the script work anyway.
May 30, 2015 at 3:02 pm
I like to have my main data files and log files on different physical drives. Your script will put all of the files for a database in the same place.
Still is you have to move a whole bunch of databases and are forced (by short sighted managers) to put all the files for a database in the same spot then this can be a great script.
I agree with the other folks that a better way to move files is through ALTER DATABASE. I had never taken them off-line, or put them in single user mode first. I guess that those are both good ideas.
One other down side is that you have to enable the command shell. You will turn that off at the end of the script. That could cause something else that relies on the shell to break when you turn it off. I know it's not a good thing to have on so not having to use it would be a good thing. Another reason to use ALTER DATABASE.
Take some tips for improvement and re-post. Good work.
ATBCharles Kincaid
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply