Crashed db recovery

Hello,

i've got MS Windows Server 2003 OS (RAID 10) and MS SQL 2005, however server crashed with "blue death" screen. After forced reset, SQL server marked my db as a SUSPECT. Tried DBCC CHECKDB with no luck, what can i do to bring it back to life, please? (have no backup).

Msg 926, Level 14, State 1, Line 1
Database 'IRIX' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:182; actual 32:7602287). It occurred during a read of page (1:182) in database ID 5 at offset 0x0000000016c000 in file 'D:\MSSQL\IRIX.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Msg 3313, Level 21, State 2, Line 1
During redoing of a logged operation in database 'IRIX', an error occurred at log record ID (844:9770:2). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.
Msg 3414, Level 21, State 1, Line 1
An error occurred during recovery, preventing the database 'IRIX' (database ID 5) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.

SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:182; actual 32:7602287). It occurred during a read of page (1:182) in database ID 5 at offset 0x0000000016c000 in file 'D:\MSSQL\IRIX.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
During redoing of a logged operation in database 'IRIX', an error occurred at log record ID (844:9770:2). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.
An error occurred during recovery, preventing the database 'IRIX' (database ID 5) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.
Cannot drop the table '#tmp_sp_db_vardecimal_storage_format', because it does not exist or you do not have permission. (Microsoft SQL Server, Error: 824)

you make use of sp_resetstatus sp....just execute it and you need to restart sql services i believe...if its not working you can try,

just bring the db into emergency mode and you can extract the data alone to another db....
alter database DBNAME set emergency

and export the datas using ssis

|||

Oh dear, it's so easy... i've reset the status, bring it to emergency and exported the data...ALL! Amazing, thank you for your kind help.

Crashed C drive, but DB on D drive

SQL 7.0
The C drive crashed, but the DB was safe(?) on drive D.
When a new drive C is installed is there any way to
retore or whatever the database?
Thanks,
DonDon wrote:

> SQL 7.0
> The C drive crashed, but the DB was safe(?) on drive D.
> When a new drive C is installed is there any way to
> retore or whatever the database?
You should be able to attach the databases after you install SQL.
Typically in this scenario if you were using SQL Logins and you don't
have them anymore, you probably lost the passwords. There are ways to
restore the master database which stores these passwords, however I have
never had much success with such a thing and is usually not necessary.
Attaching a database can be done through Enterprise Manager by right
clicking on the databases folder.
Aaron Weiker
http://blogs.sqladvice.com/aweiker
http://aaronweiker.com/|||Hi,
Step -1
Can you tell the location of system databases (Master,model,msdb,tempdb) MDF
and LDF locations. If it is in D drive you can easily make the
SQL server up with all the configurations and data same as old.
1. Install SQL server with same directory structure as old
2. Apply the service pack as old
3. Stop sql server
4. Copy all the MDF and LDF files to the same directory stucture as before
(Ensure that u keep the original some where safe)
5. Start SQL Server
Normally this will enable to have all the databases and Logins same as old.
Step 2
Incase if your system MDF and LDF were lost then, (the below steps will work
if you have MASTER database backup (master.bak))
1. Install SQL server with same directory structure as old
2. Apply service packs
3. Start SQL server in sigle user mode (-m)
4. Restore the master database
5. Restore MSDB database
6. Stop SQL server and copy the MDF and LDF to the directories as old
7. Start SQL server
Step 3
Incase if you dont have the MASTER database backup. Now all depends on luck.
SOme times your ATTACH may not work since you have not
detached the database using SP_DETACH_DB. If attach gives you issue then
probably you have restore the databases from Backup you have
and then create the logins manually and assign permissions.
Thanks
Hari
MCDBA
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:9d1701c4063f$47542660$a001280a@.phx.gbl...
> SQL 7.0
> The C drive crashed, but the DB was safe(?) on drive D.
> When a new drive C is installed is there any way to
> retore or whatever the database?
> Thanks,
> Don
>

Crash Recovery

I am trying to restore a crashed server. The server has an SQL2K
installation with a small database (say) DATA. The file DATA.MDF has gone
missing during the crash. The file DATA.LDF has been recovered. There is
also a backup file DATA.BAK. This backup is about 4 MB and I suspect it was
used to install the database in the first place, as it is several months
old. The log file LDF has been recently updated and is 47 Mb.
Is there any way to recover this database to a reasonable up to date version
using DATA.BAK and DATA.LDF. All suggestions are welcome?
BobHi,
Since the MDF file is not avaible you will not able be use the LDF.
But using DATA.BAK file you can recover the database back to the time in
whcih backup is created.
How to restore the BAK file.
From Query analyzer execute the below command
1. Copy the DATA.BAK to c:\backup folder
Restore database <dbname> from disk='c:\backup\data.bak'
You may need to use WITH Move option along with restore statement.
Thanks
Hari
MCDBA
"Bob Morris" <bmorris@.kenyaonline.com> wrote in message
news:#fVLgrcBEHA.3348@.TK2MSFTNGP11.phx.gbl...
> I am trying to restore a crashed server. The server has an SQL2K
> installation with a small database (say) DATA. The file DATA.MDF has gone
> missing during the crash. The file DATA.LDF has been recovered. There is
> also a backup file DATA.BAK. This backup is about 4 MB and I suspect it
was
> used to install the database in the first place, as it is several months
> old. The log file LDF has been recently updated and is 47 Mb.
> Is there any way to recover this database to a reasonable up to date
version
> using DATA.BAK and DATA.LDF. All suggestions are welcome?
> Bob
>
>|||Thanks,
But I need to know if there's any way to apply the LDF. The MDB file was
probably created on this machine from the BAK file. (i.e. the database was
created elsewhere and "restored" to this machine with the force restore
option). The LDF will reflect changes since that was done, and that's what I
want to try and do.
Regards
Bob
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:eAF4v9cBEHA.3568@.tk2msftngp13.phx.gbl...
> Hi,
> Since the MDF file is not avaible you will not able be use the LDF.
> But using DATA.BAK file you can recover the database back to the time in
> whcih backup is created.
> How to restore the BAK file.
> From Query analyzer execute the below command
> 1. Copy the DATA.BAK to c:\backup folder
> Restore database <dbname> from disk='c:\backup\data.bak'
> You may need to use WITH Move option along with restore statement.
> Thanks
> Hari
> MCDBA
>
>
>
> "Bob Morris" <bmorris@.kenyaonline.com> wrote in message
> news:#fVLgrcBEHA.3348@.TK2MSFTNGP11.phx.gbl...
gone
> was
> version
>|||Thanks, but what I need is to try and apply the LDF after I restore the
backup.
Regards
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:eAF4v9cBEHA.3568@.tk2msftngp13.phx.gbl...
> Hi,
> Since the MDF file is not avaible you will not able be use the LDF.
> But using DATA.BAK file you can recover the database back to the time in
> whcih backup is created.
> How to restore the BAK file.
> From Query analyzer execute the below command
> 1. Copy the DATA.BAK to c:\backup folder
> Restore database <dbname> from disk='c:\backup\data.bak'
> You may need to use WITH Move option along with restore statement.
> Thanks
> Hari
> MCDBA
>
>
>
> "Bob Morris" <bmorris@.kenyaonline.com> wrote in message
> news:#fVLgrcBEHA.3348@.TK2MSFTNGP11.phx.gbl...
gone
> was
> version
>|||You can't apply an LDF file to a restored database backup. If you're lucky,
you might be able to dig something out of the LDF file using some log reader
program. See my signature, the links.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Bob Morris" <bmorris@.kenyaonline.com> wrote in message
news:eiZLHtfBEHA.2768@.tk2msftngp13.phx.gbl...
> Thanks, but what I need is to try and apply the LDF after I restore the
> backup.
> Regards
> "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> news:eAF4v9cBEHA.3568@.tk2msftngp13.phx.gbl...
> gone
is
it
months
>