Solved

Getting a database to restore - error 3241

Posted on 2008-06-12
19
7,409 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 9
19 Comments
 
LVL 31

Expert Comment

by:James Murrell
ID: 21767364
guessing its to do with service packs

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

Author Comment

by:CaptainGiblets
ID: 21767488
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
ID: 21767520
May be that backup is not taken correctly, try to run RESTORE VERIFYONLY to see whether it is consistent
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 6

Author Comment

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

Expert Comment

by:James Murrell
ID: 21767567
0
 
LVL 6

Author Comment

by:CaptainGiblets
ID: 21767596
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
ID: 21767627
mmm looks like backup is corrupt.....
0
 
LVL 6

Author Comment

by:CaptainGiblets
ID: 21767668
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
ID: 21767689
mmm that a question for experts in windows i think
0
 
LVL 6

Author Comment

by:CaptainGiblets
ID: 21767727
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
ID: 21767780
just restore like mentioned earlier
0
 
LVL 6

Author Comment

by:CaptainGiblets
ID: 21768153
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
ID: 21768217
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
ID: 21768680
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
ID: 21768694
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
ID: 21768698
paste it into sql query anazlyer
0
 
LVL 6

Author Comment

by:CaptainGiblets
ID: 21768790
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
ID: 21768802
never mind, just rebooted and it came straight back up, thanks a lot for your help.
0
 
LVL 31

Expert Comment

by:James Murrell
ID: 21768860
pheww glad all sorted
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

688 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