• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 7423
  • Last Modified:

Getting a database to restore - error 3241

i have an important database that was hosted from a server in a datacenter, we were having problems with the machine so sql on it was backup up and it was reformatted. When we got it back online it appears something is wrong with the sql backup and it now wont restore. The only backup we have that works is over 6 days old, is there anyway to get it to work?

I read somewhere that it may be to do with the transaction logs, and you can get it to work by restoring it to the MSDE 2000 rather than sql 2000 server. However i cant seem to manage to connect to the MSDE.

Any help fixing this would be appreciated.
0
CaptainGiblets
Asked:
CaptainGiblets
  • 10
  • 9
1 Solution
 
James MurrellProduct SpecialistCommented:
guessing its to do with service packs

http://support.microsoft.com/kb/297104
0
 
CaptainGibletsAuthor Commented:
it was backed up using sql server 2000 sp 4, and we restored it using sql server 2000 sp4  still no luck.
0
 
James MurrellProduct SpecialistCommented:
May be that backup is not taken correctly, try to run RESTORE VERIFYONLY to see whether it is consistent
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
CaptainGibletsAuthor Commented:
i cant seem to find the option to restore verifyonly, where abouts is it.
0
 
James MurrellProduct SpecialistCommented:
0
 
CaptainGibletsAuthor Commented:
iv etried restoring it using query analyzer and i get the message

Collation ID 49o is invalid.
0
 
James MurrellProduct SpecialistCommented:
mmm looks like backup is corrupt.....
0
 
CaptainGibletsAuthor Commented:
if i can access the old files using a file restorer, is there any way to get the data by finding the old sql database in the installation directory.
0
 
James MurrellProduct SpecialistCommented:
mmm that a question for experts in windows i think
0
 
CaptainGibletsAuthor Commented:
i know how to get the files back etc, its just opening them in sql once ive got them im not sure how 2 do.
0
 
James MurrellProduct SpecialistCommented:
just restore like mentioned earlier
0
 
CaptainGibletsAuthor Commented:
its an mdf file, not a bak file   its the whole database stored in the binn  folder that i managed 2 get hold of
0
 
James MurrellProduct SpecialistCommented:
you attach that file to sql

If you need to ignore the current LDF file and if your database is not detached properly and if you do not have the backup, follow the below steps to bring up your database. In the below step-7 is a undocumented DBCC command.

1. Create a new database with the same name and same MDF and LDF files 2. Stop sql server and rename the existing MDF to a new one and copy the original MDF to this location and delete the LDF files.
3. Start SQL Server
4. Now your database will be marked suspect 5. Update the sysdatabases to update to Emergency mode. This will not use LOG files in start up

Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status =  32768 where name = "BadDbName"
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO

6. Restart sql server. now the database will be in emergency mode

7. Now execute the undocumented DBCC to create a log file

DBCC REBUILD_LOG(dbname,'c:\dbname.ldf')  -- Undocumented step to create a new log file.

(replace the dbname and log file name based on ur requirement)

8. Execute sp_resetstatus <dbname>

9. Restart SQL server and see the database is online.
0
 
CaptainGibletsAuthor Commented:
sorry but how do i do this

4. Now your database will be marked suspect 5. Update the sysdatabases to update to Emergency mode. This will not use LOG files in start up

Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status =  32768 where name = "BadDbName"
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO
0
 
CaptainGibletsAuthor Commented:
i think i will need a hand doing all part 4 onward, sorry but im not really an expert in any of this stuff.
0
 
James MurrellProduct SpecialistCommented:
paste it into sql query anazlyer
0
 
CaptainGibletsAuthor Commented:
ive followed all them steps, and it said it did it ok, but when i go into the database its called    lom2_charddb (DBO Use Only)     and all the tables etc are there, but they are all empty.  however the database is still 120mb
0
 
CaptainGibletsAuthor Commented:
never mind, just rebooted and it came straight back up, thanks a lot for your help.
0
 
James MurrellProduct SpecialistCommented:
pheww glad all sorted
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 10
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now