none
Restoring or attaching the *.mdf file

    Question

  • Hai,

    I am trying to attach the *.mdf file then i am getting the error 1813.
    The problem occurs when our development database transcation running out of memory disk, then I dettach database and at the time of dettaching database some of users are connected. I kill the session of all users.
    After I deleted the log file. Now when i am trying to attach the database then I am getting the error no 1813.

    Anybody urgent reply

    Monday, November 21, 2005 10:18 AM

Answers

  • -----------------------------------------------------------------------------------------------------------------------------------------------------
    1) move the existing .MDF file to a new location (to backup)
    2) Start SQL Server Enterprise manager, and create a new (dummy) database where the .MDF file is the exact same name and size of your old/existing .MDF. The ldf file can remain at 1mb in size.

    3) Stop the SQL Server service and copy only the .MDF to the location of the new/dummy .MDF file.

    4) Start SQL Server. At this point, the database should come up as suspect. You will then want to place the database into emergency bypass mode and rebuild the Transaction log. This can be accomplished by performing the following steps:

    a) Change the database context to Master and allow updates to system tables. Note you will be performing this within Query Anayzler:
    Use Master
    go
    sp_configure 'allow updates',1
    reconfigure with override
    go
    b) Set the database in emergency bypass mode:
    Select dbid, name, status from sysdatabases where name = '<database name>'
    -- Note the value of status and write it down for future reference
    begin tran
    update sysdatabases set status = 32768 where name = '<database name>'
    commit tran
    c) Stop and restart your SQL Server. At this point, the database will come up in emergency mode. At this point, you will be able to browse, but not update any data within the database.

    d) We will now want to rebuild the log/ldf file. To do this, you will want to run the following:

    DBCC rebuild_log('<database name>','')
    If you do not receive any errors, we will want to reset the status of the database by running the following commmand and restarting SQL Server:
    use master
    exec sp_dboption 'database name','single user',true
    go
    begin tran
    update sysdatabases set status = 0 where name = '<database name>'
    commit tran
    e) set database option to not allow updates to the system tables:

    sp_configure 'allow updates', 0
    reconfigure with override
    go
    f) stop and restart SQL Server
    When the server restarts, you will then want to launch Query Analyzer and run the following to validate the database and check the overall integrity:

    use master
    go
    sp_dboption '<database name>','Single user',true
    You will then want to run the following:
    DBCC checkdb('<database name>')

     

     

    Tuesday, December 06, 2005 12:41 PM

All replies

  • -----------------------------------------------------------------------------------------------------------------------------------------------------
    1) move the existing .MDF file to a new location (to backup)
    2) Start SQL Server Enterprise manager, and create a new (dummy) database where the .MDF file is the exact same name and size of your old/existing .MDF. The ldf file can remain at 1mb in size.

    3) Stop the SQL Server service and copy only the .MDF to the location of the new/dummy .MDF file.

    4) Start SQL Server. At this point, the database should come up as suspect. You will then want to place the database into emergency bypass mode and rebuild the Transaction log. This can be accomplished by performing the following steps:

    a) Change the database context to Master and allow updates to system tables. Note you will be performing this within Query Anayzler:
    Use Master
    go
    sp_configure 'allow updates',1
    reconfigure with override
    go
    b) Set the database in emergency bypass mode:
    Select dbid, name, status from sysdatabases where name = '<database name>'
    -- Note the value of status and write it down for future reference
    begin tran
    update sysdatabases set status = 32768 where name = '<database name>'
    commit tran
    c) Stop and restart your SQL Server. At this point, the database will come up in emergency mode. At this point, you will be able to browse, but not update any data within the database.

    d) We will now want to rebuild the log/ldf file. To do this, you will want to run the following:

    DBCC rebuild_log('<database name>','')
    If you do not receive any errors, we will want to reset the status of the database by running the following commmand and restarting SQL Server:
    use master
    exec sp_dboption 'database name','single user',true
    go
    begin tran
    update sysdatabases set status = 0 where name = '<database name>'
    commit tran
    e) set database option to not allow updates to the system tables:

    sp_configure 'allow updates', 0
    reconfigure with override
    go
    f) stop and restart SQL Server
    When the server restarts, you will then want to launch Query Analyzer and run the following to validate the database and check the overall integrity:

    use master
    go
    sp_dboption '<database name>','Single user',true
    You will then want to run the following:
    DBCC checkdb('<database name>')

     

     

    Tuesday, December 06, 2005 12:41 PM
  • Thank you so much.  You just saved me alot of headaches.  My transaction log spun out of control, it was up to 28 GB.  I detached the db and tried to reatach and I got this problem.  Normally it would just automatically create a log file I thought. 

    Thanks again!
    Tuesday, December 20, 2005 8:46 PM
  • Thank You!!!
    Tuesday, November 28, 2006 11:51 AM
  • Hello,

    Please note the process is not applicable to SQL Server 2005 and up (for example statement update sysdatabases set status = 32768 where name = '<database name>' is not valid for SQL Server 2005). This is true even if you are running your database in SQL Server 2000 compatibility mode.


    Arthur
    Thursday, August 26, 2010 5:56 PM