SQL Server Performance Forum – Threads Archive
Moving & Attaching dbs on indexing folder struture
We are in a change management phase to move data and log files in an indexing architecture.ie say if the database names is Accounts the its mdf and ldf should exist in E
ataACCAccounts,if the name is Administration it should exist in EataADMAdministration and so on Our present scnerio is simply E
ataAccounts for account_data.mdf,account_log.ldf;E
ataAdminsitration for administration_data.mdf,administration_log.ldf and so onI have written a small script which genetates the sp_attachdb script for all the databases on the server.
SELECT ‘sp_attach_db @dbname=N”’+Name + ”’,
@filename1=N”’+filename + ”’
G’+’O
‘
from master.dbo.sysdatabases
where dbid>6
order by name Now this scripts genetates only the sp_attachdb statements with the present path.
I am looking for some advance logic in script which will help to have new db path ie the new path should be like ‘E
ataACCAccounts’.Something like indexing folders.
If any one in this forum have any kind of idea please give me.
Hi,<br />try this :<br /><br />SELECT ‘sp_attach_db @dbname=N”’+Name + ”’,<br />@filename1=N”e:acc’+name +’.mdf’+ ”’ <br />G’+’O<br />'<br />from master.dbo.sysdatabases<br />where dbid>6<br />order by name<br /><br />i have replaced filename with name and concatenate .mdf with it .<br /><br />[<img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />]<br />Regards<br /><br /><br /><br />Hemantgiri S. Goswami<br />[email protected]<br />"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri Goswami<br />
I cant do its as this becomes a time consuming process for more than 400 dbs.
and there are three level directory(folder) structure ;first level a-z for each a there is a-z and again inside this a there is a-z.I hope you can now get my model
If you need the structure to be like that then you have to design the same using the CREATE database code, otherwise SQL will install by default under DATA folder. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
I know this fact.But now we are in change management phase.DBs already exist on a server.We need to migrate it to new structure.The main problem now comming is in attaching.
Can you suggest me some logic using vb script /TSQL script to do this activity.Changing the path of more than 400 dbs mannualy is error prone and time consuming.I have to keep the downtime minimum.
Build few stored procedures to detach the databases, copy the data and log files to concerned locations and re-attach with new locations. You can ISQL to accomplish this 3 stage process, but before that I suggest you to test in the development environment. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
I already have tested the detached ,and the login scripts,the problem comming with only the attach script as the folder structure in new server is different from present server
Can you post the code used. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
For detach I have use a simple code which script up all the dbs present on server
Select ‘sp_detach_db ”’+Name+”’
G’+’O
‘
from master.dbo.sysdatabases
where dbid > 6
order by name The result produce can be finally use to detach databases
for logins I m using the utility script provide by Microsoft i.e sp_help_revlogin
Have you tried as per Hemant’s suggestion
SELECT ‘sp_attach_db @dbname=N”’+Name + ”’,
@filename1=N”e:acc’+name +’.mdf’+ ”’
G’+’O
‘
from master.dbo.sysdatabases
where dbid>6
order by name
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Yes but this is applicable only if the name start with a.I cant keep the datafile in ‘a’ folder whose name start with b,c.d,e and so on
I have many databases whose name start from a-z or 0-9 so I need a global script I guess I am not able to make the architecture clear
Then you may need to have 26 +10 scripts for the databases starting with each digit & alphabet, there is no easy way round as the condition will be more complex if you need fit in one script. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
]]>