Solved

Getting a database to restore - error 3241

Posted on 2008-06-12
19
7,396 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
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
 
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
[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

863 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

25 Experts available now in Live!

Get 1:1 Help Now