Database Recovery Issue

We had a hard drive crash on one of our servers. There are no backups of the
database available but we were able to recover the MDF and LDf for the only
database that was on the server. The server had to be rebuilt and SQL
reinstalled. The question I have, is there a way to recover this database
using the MDF and LDF files. I tried using the sp_attach_db command but
without much success (don't know if I used the wrong parameters or what). Any
suggestions would be greatly appreciated.There are several possibilities depending on the state of the database and
files involved. Your best bet is to contact PSS
(http://support.microsoft.com) who will be able to help you get up and
running again (and fix your backup process, of course :-)
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"MACason" <MACason@.discussions.microsoft.com> wrote in message
news:987BCD70-4AD9-42A6-AF66-CBB37EECB647@.microsoft.com...
> We had a hard drive crash on one of our servers. There are no backups of
the
> database available but we were able to recover the MDF and LDf for the
only
> database that was on the server. The server had to be rebuilt and SQL
> reinstalled. The question I have, is there a way to recover this database
> using the MDF and LDF files. I tried using the sp_attach_db command but
> without much success (don't know if I used the wrong parameters or what).
Any
> suggestions would be greatly appreciated.

Database Recovery Issue

We had a hard drive crash on one of our servers. There are no backups of the
database available but we were able to recover the MDF and LDf for the only
database that was on the server. The server had to be rebuilt and SQL
reinstalled. The question I have, is there a way to recover this database
using the MDF and LDF files. I tried using the sp_attach_db command but
without much success (don't know if I used the wrong parameters or what). Any
suggestions would be greatly appreciated.
There are several possibilities depending on the state of the database and
files involved. Your best bet is to contact PSS
(http://support.microsoft.com) who will be able to help you get up and
running again (and fix your backup process, of course :-)
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"MACason" <MACason@.discussions.microsoft.com> wrote in message
news:987BCD70-4AD9-42A6-AF66-CBB37EECB647@.microsoft.com...
> We had a hard drive crash on one of our servers. There are no backups of
the
> database available but we were able to recover the MDF and LDf for the
only
> database that was on the server. The server had to be rebuilt and SQL
> reinstalled. The question I have, is there a way to recover this database
> using the MDF and LDF files. I tried using the sp_attach_db command but
> without much success (don't know if I used the wrong parameters or what).
Any
> suggestions would be greatly appreciated.

Database Recovery Issue

We had a hard drive crash on one of our servers. There are no backups of the
database available but we were able to recover the MDF and LDf for the only
database that was on the server. The server had to be rebuilt and SQL
reinstalled. The question I have, is there a way to recover this database
using the MDF and LDF files. I tried using the sp_attach_db command but
without much success (don't know if I used the wrong parameters or what). An
y
suggestions would be greatly appreciated.There are several possibilities depending on the state of the database and
files involved. Your best bet is to contact PSS
(http://support.microsoft.com) who will be able to help you get up and
running again (and fix your backup process, of course :-)
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"MACason" <MACason@.discussions.microsoft.com> wrote in message
news:987BCD70-4AD9-42A6-AF66-CBB37EECB647@.microsoft.com...
> We had a hard drive crash on one of our servers. There are no backups of
the
> database available but we were able to recover the MDF and LDf for the
only
> database that was on the server. The server had to be rebuilt and SQL
> reinstalled. The question I have, is there a way to recover this database
> using the MDF and LDF files. I tried using the sp_attach_db command but
> without much success (don't know if I used the wrong parameters or what).
Any
> suggestions would be greatly appreciated.

database recovery if no backup available

Hi,

If we delete a Database accidentally (no backup exists), Is it possible to recover. How?

Thanks

ven--

If you have dropped the database and with no backup it is not possible to recover, thats why BACKUP is an important arm on the basis availability.|||

Hi Venp,

As Satya said it very crucial to take backup daily basis other wise you will lose your important data, but some third party tolls like Stellar can recovery the files , how ever i only tried with demo version but not for the SQL Database Files, this is FYI only.

Hemantgiri S. Goswami

|||

You can try some third-party file recovery tools to attempt to un-delete the files, and then attach them to the instance.

You might get lucky. Otherwise, there's not much to be done. We can't fix a file that isn't there without a backup.

DAtabase Recovery from emergency mode

We had a RAID breakdown that brought around 20 of our
databases each around 100 GB in Emergency mode.
What is the best way to recover the database.
We have tried the method of renaming the database files
and log files and running the following set of commands:-
sp_configure 'allow updates', 1
go
reconfigure with override
go
use master
go
Update sysdatabases set status = 32768 where name = 'TEST'
go
sp_configure 'allow updates', 0
go
reconfigure with override
But when we try to re gendrate the logs it fails with the
DBNETLIB error.
How shoule we procede to recover the databasesrecreate the RAID and attach the databases. There is also a way to reset the database state in the configuration table.
rgn|||The best way is from good backups. If your Raid crashed that hard you can
not be sure of the integrity of the files and restoring from backups is the
proper way to recover them.
--
Andrew J. Kelly
SQL Server MVP
"YOGI.GUPTA@.MAIL.SPRINT.COM" <anonymous@.discussions.microsoft.com> wrote in
message news:019001c3ca6e$92327720$a401280a@.phx.gbl...
> We had a RAID breakdown that brought around 20 of our
> databases each around 100 GB in Emergency mode.
> What is the best way to recover the database.
> We have tried the method of renaming the database files
> and log files and running the following set of commands:-
> sp_configure 'allow updates', 1
> go
> reconfigure with override
> go
> use master
> go
> Update sysdatabases set status = 32768 where name = 'TEST'
> go
> sp_configure 'allow updates', 0
> go
> reconfigure with override
> But when we try to re gendrate the logs it fails with the
> DBNETLIB error.
> How shoule we procede to recover the databases
>

Database Recovery

i have a database which restored with a null database. how can i recover my previous data? i have no backup.

It is not clear what you mean by 'null database'.

IF you have restored a database from a 'empty' database (either by replacing the files or using a backup), and IF you do not have another backup with data -you are out of luck and need to quickly type up and send out your rsum.

There is no way to recover data that doesn't exist (except for re-entering it by hand), and without backups, in your situation, it appears that the data no longer exist.

|||

Arnie Rowland wrote:

It is not clear what you mean by 'null database'.

IF you have restored a database from a 'empty' database (either by replacing the files or using a backup), and IF you do not have another backup with data -you are out of luck and need to quickly type up and send out your rsum.

There is no way to recover data that doesn't exist (except for re-entering it by hand), and without backups, in your situation, it appears that the data no longer exist.

i restore it with a wrong backup file. really there is no way?|||

The restore will have overwritten the data files so you won't be able to get anything from the current database.

If you have a disk backup you might be able to attach the .mdf otherwise if you don't have a backup then I'm afraid you've lost it.

|||Unfortunately, DBA skillsets STILL do not include magic! (Although at times it may seek like we do practice the 'black arts' ... Mostly, just sticking pins in PM dolls ... )

Database Recovery

My database server crashed. I was able to recover the physical database
files, which a reattached to a new server with sp_attached_db. Now I got a
problem with recreating my login accounts. I got an error message that
<login_Account> al ready had a role in my database.
What is te proper way to recreate login accounts?
You can use sp_change_users_login to remap:
http://msdn.microsoft.com/library/en...ca-cz_8qzy.asp
-oj
"Arne" <Arne@.discussions.microsoft.com> wrote in message
news:15AD2C9D-48A8-4158-9834-594C9787CBB5@.microsoft.com...
> My database server crashed. I was able to recover the physical database
> files, which a reattached to a new server with sp_attached_db. Now I got a
> problem with recreating my login accounts. I got an error message that
> <login_Account> al ready had a role in my database.
> What is te proper way to recreate login accounts?

Database Recovery

My database server crashed. I was able to recover the physical database
files, which a reattached to a new server with sp_attached_db. Now I got a
problem with recreating my login accounts. I got an error message that
<login_Account> al ready had a role in my database.
What is te proper way to recreate login accounts?You can use sp_change_users_login to remap:
http://msdn.microsoft.com/library/e..._ca-cz_8qzy.asp
-oj
"Arne" <Arne@.discussions.microsoft.com> wrote in message
news:15AD2C9D-48A8-4158-9834-594C9787CBB5@.microsoft.com...
> My database server crashed. I was able to recover the physical database
> files, which a reattached to a new server with sp_attached_db. Now I got a
> problem with recreating my login accounts. I got an error message that
> <login_Account> al ready had a role in my database.
> What is te proper way to recreate login accounts?

Database Recovery

My database server crashed. I was able to recover the physical database
files, which a reattached to a new server with sp_attached_db. Now I got a
problem with recreating my login accounts. I got an error message that
<login_Account> al ready had a role in my database.
What is te proper way to recreate login accounts?You can use sp_change_users_login to remap:
http://msdn.microsoft.com/library/en-us/tsqlref/ts_sp_ca-cz_8qzy.asp
-oj
"Arne" <Arne@.discussions.microsoft.com> wrote in message
news:15AD2C9D-48A8-4158-9834-594C9787CBB5@.microsoft.com...
> My database server crashed. I was able to recover the physical database
> files, which a reattached to a new server with sp_attached_db. Now I got a
> problem with recreating my login accounts. I got an error message that
> <login_Account> al ready had a role in my database.
> What is te proper way to recreate login accounts?

Database Recovery

We are trying to recover a database that has been
corrupted. We have a full backup with recovery from the
night before the problem, and one right after the problem
occurred. We did not do a Transaction Log backup. Will
this work:
1. Restore the second backup.
2. Do a Transaction Log backup - this should be all the
transactions since our 1st good backup (but it seems as
though the t-log is truncated during recovery because we
are getting an error when we try to apply it).
3. Restore the first backup.
4. Apply the Transaction log.
If the t-log is indeed truncated (why would it even allow
that?) do we have any way to recover those transactions?I don't think that will work. The correct course of action would have been
:-
Midnight - Full Backup
Hourly - Log Backups
13:30 User corrupts data.
13:33 Take Log Backup
Restore Full Database backup
Restore each hourly Log Backup in order
Restore from 13:33 Log Backup with the STOPAT clause set to 13:29
--
HTH
Ryan Waight, MCDBA, MCSE
"Ed" <anonymous@.discussions.microsoft.com> wrote in message
news:002901c3b29d$90932d80$a101280a@.phx.gbl...
> We are trying to recover a database that has been
> corrupted. We have a full backup with recovery from the
> night before the problem, and one right after the problem
> occurred. We did not do a Transaction Log backup. Will
> this work:
> 1. Restore the second backup.
> 2. Do a Transaction Log backup - this should be all the
> transactions since our 1st good backup (but it seems as
> though the t-log is truncated during recovery because we
> are getting an error when we try to apply it).
> 3. Restore the first backup.
> 4. Apply the Transaction log.
> If the t-log is indeed truncated (why would it even allow
> that?) do we have any way to recover those transactions?|||Should also mention that when we try the scenario below -
we get a 4305 error when we try to apply the transaction
log - I am assuming that is because the log is truncated -
or is there something else happening?
>--Original Message--
>We are trying to recover a database that has been
>corrupted. We have a full backup with recovery from the
>night before the problem, and one right after the
problem
>occurred. We did not do a Transaction Log backup. Will
>this work:
>1. Restore the second backup.
>2. Do a Transaction Log backup - this should be all the
>transactions since our 1st good backup (but it seems as
>though the t-log is truncated during recovery because we
>are getting an error when we try to apply it).
>3. Restore the first backup.
>4. Apply the Transaction log.
>If the t-log is indeed truncated (why would it even
allow
>that?) do we have any way to recover those transactions?
>.
>|||If you never did a log backup, you will only have the db backup to work with.
If the database is in simple recovery mode, the log is truncated regularly.
And even if the database is in full recovery mode, the log is truncated at 70% fill until you do the
first database backup.
So, chances that the log records are available to produce a usable log backup are slim to nothing.
If you like to try that, you would have to do the log backup before doing any type of restore.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Ed" <anonymous@.discussions.microsoft.com> wrote in message
news:002901c3b29d$90932d80$a101280a@.phx.gbl...
> We are trying to recover a database that has been
> corrupted. We have a full backup with recovery from the
> night before the problem, and one right after the problem
> occurred. We did not do a Transaction Log backup. Will
> this work:
> 1. Restore the second backup.
> 2. Do a Transaction Log backup - this should be all the
> transactions since our 1st good backup (but it seems as
> though the t-log is truncated during recovery because we
> are getting an error when we try to apply it).
> 3. Restore the first backup.
> 4. Apply the Transaction log.
> If the t-log is indeed truncated (why would it even allow
> that?) do we have any way to recover those transactions?

Database Partial Backup

Are there any way to backup just some tables of the database instead of a full backup? Or recover just some things of a backup?

Another thing, how can I recover a database without overwriting the settings of the existent database (no changes in filenames)?

You can perform full or differential backups on the entire database, a single filegroup, or a single file. So, if you want to plan to backup only certain tables, simply create those tables/indexes on a specific file or filegroup and perform a backup on just that file/filegroup.

You can restore at the database, file, filegroup levels...in SQL 2005 you can also restore specific database pages instead of the entire database, file, or filegroup.

When you perform a restore, you have a pretty robust set of options for specifying file locations, replacement, etc. Additionally, if after the restore, some database options are reset, simply run an 'alter database' statement to reset them to the state you'd like them in. Generally speaking, most options set on a database using the 'alter database' command are reset to the state they were in at the time of the corresponding backup statement.

Take a look at the 'BACKUP DATABASE' and 'RESTORE DATABASE' documentation in SQL Books Online for more detailed information.

HTH