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
Jeanne CoteAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Tom CieslikIT EngineerCommented:
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
0
Jeanne CoteAuthor Commented:
As I mentioned I am really new to SQL what do you mean by "database transaction log backup"
Thanks
0
Tom CieslikIT EngineerCommented:
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
0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

ste5anSenior DeveloperCommented:
Do you made a backup before that? Then I would restore that..

And who ever told you that, keep him/her away from your databases and servers.
2

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeanne CoteAuthor Commented:
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
0
ste5anSenior DeveloperCommented:
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.
1
Jeanne CoteAuthor Commented:
How do I now if it's simple or not
Thanks
0
ste5anSenior DeveloperCommented:
SELECT name, recovery_model_desc  
FROM sys.databases; 

Open in new window

0
Jeanne CoteAuthor Commented:
Sorry I don't know where to write this
0
ste5anSenior DeveloperCommented:
In a new query window in SSMS.
0
Jeanne CoteAuthor Commented:
I have SQL Management Console 2000
0
Jeanne CoteAuthor Commented:
I am copying the mdb and ldf files from the backup this will take a few hours will try again then
0
ste5anSenior DeveloperCommented:
Then there. Just run it in a query window:

SQLMgtConsole_MSSQL2005.jpg
0
yo_beeDirector of Information TechnologyCommented:
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
0
Jeanne CoteAuthor Commented:
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
0
Jeanne CoteAuthor Commented:
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
5178.JPGWhat are my options?
0
Jeanne CoteAuthor Commented:
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
5173.JPGI 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
0
Jeanne CoteAuthor Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.