Post reply

Attaching a Single .mdf file

  • December 5, 2005 at 7:12 am

    #166706

    Hi all,

    I've got a .mdf file that was detached using the GUI.  Is it possible to reattach it without having the transaction logs?  I've tried CREATE DATABASE...FOR ATTACH and sp_attach_single_db but neither seem to love me.

    I'm using 2000 so I can't use the FOR ATTACH_REBUILD_LOGS (at least I don't think so).

    Any thoughts?

    Thanks,

     

    Iain

  • December 5, 2005 at 8:07 am

    #607710

    If the database was not shutdown cleanly before detach it is not possible to attach the file using sp_attach_db or sp_attach_single_file_db.

    Try this:

    Checking Files before Attaching

    You should note that you cannot re-attach more than 16 files for a single database. Before attaching the database, issue the following commands over the primary file-group data file to get a listing of files that make up the database structure:

    dbcc checkprimaryfile (N'E:\SQLServerData\MSSQL\Data\mydb_Data.MDF', 0)

    Is the file a primary file-group MDF file?

    dbcc checkprimaryfile (N'E:\SQLServerData\MSSQL\Data\mydb_Data.MDF', 2)

    Get me the database name, version and collation

    dbcc checkprimaryfile (N'E:\SQLServerData\MSSQL\Data\mydb_Data.MDF', 3)

    Get a list of all files associated with the database. (original name)

    Attaching the database

    The sp_attach_db command allows you to re-attach your database onto the SQL Server instance. For example:

    exec sp_attach_db 

    N'mydb' , 

    N'E:\SQLServerData\MSSQL\Data\new_aa_Data.MDF', N'E:\SQLServerData\MSSQL\Data\new_aa_Log.LDF'

    If this does not work, create a new database with the same name and same filenames and location. Stop SQL Server, replace the the MDF file, restart SQL Server, the database will be suspect and then use DBCC REBUILDLOG to recreate the log file for the database. The database will be online.

  • December 5, 2005 at 10:55 am

    #607749

    The above advice should work for you. Did you shutdown the server and actually detach the file or just copy it?

  • December 6, 2005 at 2:45 am

    #607887

    Thanks guys.  Can DBCC REBUILDLOG be used on SQL 2000?

  • December 6, 2005 at 12:08 pm

    #608020

    I used it few times (DBCC REBUILD_LOG) and it worked for me

Viewing 5 posts - 1 through 5 (of 5 total)

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