Solved

Getting a database to restore - error 3241

Posted on 2008-06-12
19
7,394 Views
Last Modified: 2010-07-27
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
Comment
Question by:CaptainGiblets
  • 10
  • 9
19 Comments
 
LVL 31

Expert Comment

by:James Murrell
Comment Utility
guessing its to do with service packs

http://support.microsoft.com/kb/297104
0
 
LVL 6

Author Comment

by:CaptainGiblets
Comment Utility
it was backed up using sql server 2000 sp 4, and we restored it using sql server 2000 sp4  still no luck.
0
 
LVL 31

Expert Comment

by:James Murrell
Comment Utility
May be that backup is not taken correctly, try to run RESTORE VERIFYONLY to see whether it is consistent
0
 
LVL 6

Author Comment

by:CaptainGiblets
Comment Utility
i cant seem to find the option to restore verifyonly, where abouts is it.
0
 
LVL 31

Expert Comment

by:James Murrell
Comment Utility
0
 
LVL 6

Author Comment

by:CaptainGiblets
Comment Utility
iv etried restoring it using query analyzer and i get the message

Collation ID 49o is invalid.
0
 
LVL 31

Expert Comment

by:James Murrell
Comment Utility
mmm looks like backup is corrupt.....
0
 
LVL 6

Author Comment

by:CaptainGiblets
Comment Utility
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
 
LVL 31

Expert Comment

by:James Murrell
Comment Utility
mmm that a question for experts in windows i think
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 6

Author Comment

by:CaptainGiblets
Comment Utility
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
 
LVL 31

Expert Comment

by:James Murrell
Comment Utility
just restore like mentioned earlier
0
 
LVL 6

Author Comment

by:CaptainGiblets
Comment Utility
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
 
LVL 31

Accepted Solution

by:
James Murrell earned 500 total points
Comment Utility
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
 
LVL 6

Author Comment

by:CaptainGiblets
Comment Utility
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
 
LVL 6

Author Comment

by:CaptainGiblets
Comment Utility
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
 
LVL 31

Expert Comment

by:James Murrell
Comment Utility
paste it into sql query anazlyer
0
 
LVL 6

Author Comment

by:CaptainGiblets
Comment Utility
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
 
LVL 6

Author Comment

by:CaptainGiblets
Comment Utility
never mind, just rebooted and it came straight back up, thanks a lot for your help.
0
 
LVL 31

Expert Comment

by:James Murrell
Comment Utility
pheww glad all sorted
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now