Results 1 to 15 of 15
  1. #1
    Join Date
    Aug 2005
    Posts
    75

    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?

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Why not prior to the mass detachment query each of the databases' sysfiles (or sys.database_files for SQL 2005) table to see not only where the log files are, but any .NDF files?

  3. #3
    Join Date
    Aug 2005
    Posts
    75
    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.

    and no, there aren't any NDF files.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Any reason this is not being done with backup/restore in the first place?

  5. #5
    Join Date
    Aug 2005
    Posts
    75
    Quote Originally Posted by MCrowley
    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.

  6. #6
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    What about replication ?

  7. #7
    Join Date
    Aug 2005
    Posts
    75
    because of the nature of the way the two environments are put together, direct SQL Server to SQL Server communication will not be possible. There's no way around that.

    I'm assuming by how much the topic seems to be straying that there's not a way that anyone knows to accomplish my original question, correct?

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    There is no way that I know of to query an unattached file, no.

    Well, you could open it in a hex editor, and...no. It will be easier to get the list of files prior to the their being detached.

  9. #9
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Never mind last post
    Last edited by PMASchmed; 04-29-08 at 17:22.

  10. #10
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Never mind this post.

  11. #11
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Actually, you can get the info you need from master.

    select * from master..sysaltfiles

  12. #12
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Not if the database has been detached.

  13. #13
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by MCrowley
    Not if the database has been detached.
    Yep, I kinda thought so.

  14. #14
    Join Date
    Aug 2005
    Posts
    75
    that's great. I didn't know that table existed.

    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.

    thanks.

  15. #15
    Join Date
    Aug 2005
    Posts
    75
    Quote Originally Posted by MCrowley
    Not if the database has been detached.
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •