July 7, 2004 at 11:40 am
Hey everyone,
here's the situation.
I have a couple of servers.
I need to migrate all databases from one to the other.
I was able to do a bulk detach.
I can then copy the files over.
I was wondering if anyone has a script example of how to loop and issue a reattach statement for all databases.
Thanks in advance for your consideration.
M
July 7, 2004 at 11:52 am
I would think you would need the database names and the names of the files that are associated with each database to do that. You could have pulled that information from sysdatabases and sysfiles before detaching. I don't know about now. I suppose you could get the information from backup files if you have them, using "restore headeronly" and "restore filelistonly".
Maybe someone will have a better idea?
Steve
July 7, 2004 at 12:06 pm
In addition to hoo-t...
If .mdf and .ldf files named consistently you can try to write a script to execute xp_cmdshell with 'dir /B yourpath\*.*' and insert results into a temporary table and then write a select on the temp table to generate sp_attach_db batch for you. You'll have to parse file names to get DB names and find matching .ldfs.
Again, if you'd have all DBs attached on the old server or at least a backup of master DB before you've detached them then it would be just a select from sysdatabases joined with sysaltfiles
July 7, 2004 at 12:11 pm
You would have to build that script before you detach them.
If you have a script for the detach, then it becomes a matter of copying and modifying to be
sp_attach_db '%dbname%', '%drive%:\%path%\%filename%.mdf', '%drive%:\%path%\%filename%.ldf'
That is assuming the drive and paths are remaining the same as on the current server. The filenames can be queried from sysfiles table in each database. Otherwise you'll have the filename out of the filename field.
----------------
Jim P.
A little bit of this and a little byte of that can cause bloatware.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply