Attaching Multiple SQL Server Databases with DBCC CHECKPRIMARYFILE

  • Comments posted to this topic are about the item Attaching Multiple SQL Server Databases with DBCC CHECKPRIMARYFILE

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • You should clear the @TSQLFilename variable on each loop, or it will just keep growing and will not work beyond the first loop.

  • I don't see the need for this undocumented command for this case (attaching a file). In reality it would cause more problems that it solves.

    The command for attaching a database is very easy, even if it are multiple files:

    CREATE DATABASE [MyDB] ON 
    ( FILENAME = N'g:\MyDB_01.mdf'),
    ( FILENAME = N'e:\MyDB_LOG.ldf'),
    ( FILENAME = N'g:\MyDB_FG_DEFAULT_01.ndf'),
    ( FILENAME = N'g:\MyDB_FG_MyDB_OLD_DATA_01.ndf'),
    ( FILENAME = N'g:\MyDB_FG_INPUT_01.ndf'),
    ( FILENAME = N'g:\MyDB_FG_INPUT_2023_01.ndf'),
    ( FILENAME = N'g:\MyDB_FG_MyDB_Data_2023_01.ndf'),
    ( FILENAME = N'g:\MyDB_FG_INPUT_2024_01.ndf'),
    ( FILENAME = N'g:\MyDB_FG_MyDB_Data_2024_01.ndf'),
    ( FILENAME = N'g:\MyDB_FG_INPUT_2025_1.ndf'),
    ( FILENAME = N'g:\MyDB_FG_MyDB_Data_2025_1.ndf'),
    ( FILENAME = N'f:\MyDB_FG_MyDB_Data_2025_2.ndf'),
    ( FILENAME = N'f:\MyDB_FG_INPUT_2025_2.ndf'),
    ( FILENAME = N'f:\MyDB_FG_MyDB_Data_2024_02.ndf'),
    ( FILENAME = N'f:\MyDB_FG_INPUT_2024_02.ndf'),
    ( FILENAME = N'f:\MyDB_FG_MyDB_Data_2023_02.ndf'),
    ( FILENAME = N'f:\MyDB_FG_INPUT_2023_02.ndf'),
    ( FILENAME = N'f:\MyDB_FG_INPUT_02.ndf'),
    ( FILENAME = N'f:\MyDB_FG_MyDB_OLD_DATA_02.ndf'),
    ( FILENAME = N'f:\MyDB_FG_DEFAULT_02.ndf'),
    ( FILENAME = N'f:\MyDB_02.ndf')
    FOR ATTACH;
    GO

    So I just need a list of the files that belongs to a database (they should either be named properly or be stored in a single folder (or folders on multiple drives). And then I could either use almost every script language (batch files, PowerShell, bash, SQL running a DIR with exec sys.xp_cmd) or even Excel to create the FILENAME lines for this command.

    The only reason, where I could really need DBCC CHECKPRIMARYFILE() is when I get a bunch of files / folders badly named database files to find out which one belongs to which database. For this very specific case it may be really useful.👍

    God is real, unless declared integer.

  • Thanks for the tip.  I did not know there was a way to test a random file without reading the header directly.

    If I had to do this myself, I think I would use PowerShell to handle most of the grunt work.  A version that reads the header directly - without sql - might be more useful.  (It's already undocumented, so why not.)  If TDE was enabled, then all bets are off.  If a SQL Server with the right keys is not handy....

    I think this is the stuff nightmares are made of.  Please attach this collection of files recovered from burnt disks from our 20 sql servers.  Sorry, we could not recover the names.  Sorry, we only found out yesterday that backup never worked.  Welcome to the team.

    RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply