Unanswered: query unattached .mdf file for location of .ldf
I'm writing a script for a pending migration (2k enterprise to 2k5x64 enterprise). I've got about 325 or so databases, spanned across 3 instances that I need to migrate. My plan is to do a mass scripted detach of all DBs of the old boxes, copy the files (on a new domain) and then reattach all of the mdf and ldf files.
I've been writing a sql script that will scan a "staging" directory for each of the MDF and LDF files, then programmatically run sp_attachdb. The problem is that the mdf and ldf files weren't always a 1 to 1 naming convention. My predecessors stuck all sorts of things in the file names that make doing a simple scripted search pretty difficult. The disk paths are going to be different from server to server (simpler), so I need to move the files
Is there a way (using sp_attachdb or otherwise) that I could somehow scan each of the MDF files to get the path of the LDF? I can already do that with a .bak file using the RESTORE FILELISTONLY command, but I'm not sure how to do it like this. I'm figuring there has to be a way though, as SSMS seems to know where the LDF is if you try to attach an MDF.
Usually, Google does well, but I haven't been able to find anything as of yet. Any ideas?
hmm... that's an idea. I was originally going for as few steps as possible, but if I have to do that, I will...
The only open connectivity between the two domains is a single point to point firewall pinhole between a single management server on each domain (not any of the DB servers), so I'd have to export the lists as a CSV file, then manually import them. This is obviously a bit of a PITA, but if that's my only option, that's my only option.
Any reason this is not being done with backup/restore in the first place?
it's primarily a speed issue. This is a 24x7 system and I've got a limited maintenance window to move approximately 1.5 TB worth of data files across to the new environment, and having to contend with backups and restores is going to take too long. It's much faster for us to detach the databases, copy the files over, then reattach.
Also, I don't have enough disk space available on the old system to stage backups of all the databases. I would end up having to break the migration up in stages, which adds complexity, which increases the chance for mistakes.
I've actually already written a routine that would parse through each DB, get the filenames, and generate an insert statement for each database, giving the server, dbname, and mdf/ldf files. Querying just the master DB on each would be much faster, though.
I guess I'll have to just manually shuttle the generated script over to the new environment. It adds a couple small steps to the process, but it's not a huge deal, though.
I just figured there had to be some way, as Enterprise Manager seems to "magically" be able to tell you where the LDF file should be if you go through the Attach Database utility. This is one of the few things that I still have to manually do through the graphical UI to do (I sometimes move DBs between the instances), and I was just hoping that there was a way to script it out.