asked on

Cannot reattach a database in SQL2000

Something happened, not quite sure what, when I tried to detach my database, which I was trying to do because the log file had become too big, so I was told that I needed to detach the database, remove the log file from the folder, and reattach for a new log file to be created. I tried that but then started getting error 1813.
I then took a copy of the database from the backup, tried to attach without an ldb file and still getting error 1813
I really need to fix this before Monday morning, any suggestions would be greatly appreciated taking into account that I am a newbie to SQL and have very little knowledge of that application
Thanks
Jeannie

Tom Cieslik

You can't attach database without log.
Thee is a few other options to reduce (purge) database log like database transaction log backup.

Without a log you can't attache database to SQL

Jeanne Cote

ASKER

As I mentioned I am really new to SQL what do you mean by "database transaction log backup"
Thanks

Tom Cieslik

Now you must use Database Emergency steps to re-create log
You cna find good step by step here

https://www.sqlskills.com/blogs/paul/checkdb-from-every-angle-emergency-mode-repair-the-very-very-last-resort/

After that you'll be able to bring database Online

ASKER CERTIFIED SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jeanne Cote

ASKER

Not quite sure why I would need the emergency mode, I do have a good copy of the database on the backup and a good copy of the ldf file as well, however as mentioned the log file has gotten too big, if there is a way I could just make the log file smaller I do believe that reattaching would work and everything would be back to normal.
Is there a way I could reduce the size of the log file before I reattach?
Thanks
Jeannie

ste5an

It depends on the revover mode. When it is simple, then just use DBCC SHRINKFILE on the log:

DBCC SHRINKFILE (YourLogfileLogicalName_Log, 1024);

Open in new window

When it's not simple, then make a log backup first. Set the recovery mode to simple, shrink the file and reset your original recovery mode.

And no, you cannot shrink the log of an unattached database.

Jeanne Cote

ASKER

How do I now if it's simple or not
Thanks

ste5an

SELECT name, recovery_model_desc  
FROM sys.databases;

Open in new window

Jeanne Cote

ASKER

Sorry I don't know where to write this

ste5an

In a new query window in SSMS.

Jeanne Cote

ASKER

I have SQL Management Console 2000

Jeanne Cote

ASKER

I am copying the mdb and ldf files from the backup this will take a few hours will try again then

ste5an

Then there. Just run it in a query window:

yo_bee

I think you should invest in a MSSQL Administration book ASAP and get up to speed quickly if you are going to be doing more MSSQL admin work.

This will cost you some money, but it maybe worth the investment.
https://www.microsoftpressstore.com/store/training-kit-exam-70-462-administering-microsoft-sql-9780735666078
Here is a free copy
https://ptgmedia.pearsoncmg.com/images/9780735666078/samplepages/9780735666078.pdf
Page 465 Backup and recovery chapter

Also you probably want to get off MSSQL 2000.

@ Ste5an
Is that a custom app you built.
It does not look like SMSS

Jeanne Cote

ASKER

thanks Ste5an, I will and we are moving away from MSSQL 2000 as the database is being recreated, however until it is ready and I stuck with this one one created by someone who obviously led me the wrong way as he is the one that told me to delete the log file to have it recreated

Jeanne Cote

ASKER

the recovery has been done, it cannot be done back into the original folder because of a size issue on the server, so I tried to reattach from the recovery location in order to reduce the size of the ldf file before copying back but I get the following error

What are my options?

Jeanne Cote

ASKER

Hi,
I had a smaller version of the ldf file on the server, so I put it in the original folder but I get the following error

I am at a loss as to what I can do to resolve this issue, I see that the error mentions the filename however the name is right as it is the same as the large size file I have on the backup

Jeanne Cote

ASKER

Restored from backup and shrank the file
It's now back to normal
Thank you for your help and will definitely look at the documentation you suggested
Have a nice Sunday