Recovery data from file mdf e log

from PC is uninstalled SQLSERVER2000 and related files mdf and log without to
execute backup database. With a tool of recovery file deleted *.mdf and
*.log, we have recoverd files. How to do you to recovery the database from
mdf e log file?
If you are lucky, you can attach them using sp_attach_db. This might not work, though as you didn't
detach the database first. If it doesn't work, open a case with MS Support and see if they can
assist.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"marisa" <marisa@.discussions.microsoft.com> wrote in message
news:E8CFE7A0-1C04-4324-A258-3EAB1BC2ADD5@.microsoft.com...
> from PC is uninstalled SQLSERVER2000 and related files mdf and log without to
> execute backup database. With a tool of recovery file deleted *.mdf and
> *.log, we have recoverd files. How to do you to recovery the database from
> mdf e log file?

Recovery data from file mdf e log

from PC is uninstalled SQLSERVER2000 and related files mdf and log without t
o
execute backup database. With a tool of recovery file deleted *.mdf and
*.log, we have recoverd files. How to do you to recovery the database from
mdf e log file?If you are lucky, you can attach them using sp_attach_db. This might not wor
k, though as you didn't
detach the database first. If it doesn't work, open a case with MS Support a
nd see if they can
assist.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"marisa" <marisa@.discussions.microsoft.com> wrote in message
news:E8CFE7A0-1C04-4324-A258-3EAB1BC2ADD5@.microsoft.com...
> from PC is uninstalled SQLSERVER2000 and related files mdf and log without
to
> execute backup database. With a tool of recovery file deleted *.mdf and
> *.log, we have recoverd files. How to do you to recovery the database from
> mdf e log file?

Recovery data from file mdf e log

from PC is uninstalled SQLSERVER2000 and related files mdf and log without to
execute backup database. With a tool of recovery file deleted *.mdf and
*.log, we have recoverd files. How to do you to recovery the database from
mdf e log file?If you are lucky, you can attach them using sp_attach_db. This might not work, though as you didn't
detach the database first. If it doesn't work, open a case with MS Support and see if they can
assist.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"marisa" <marisa@.discussions.microsoft.com> wrote in message
news:E8CFE7A0-1C04-4324-A258-3EAB1BC2ADD5@.microsoft.com...
> from PC is uninstalled SQLSERVER2000 and related files mdf and log without to
> execute backup database. With a tool of recovery file deleted *.mdf and
> *.log, we have recoverd files. How to do you to recovery the database from
> mdf e log file?

Recovering MDF file! Need Assistance!!

Somehow, our MS SQL 2000 Server database mdf file size is 0,
The database is in suspected state. and the backup file related to that database shown 0 size.
Its totaly weird as there are many databases in server group but just one database's mdf file and backup file size is 0.

Any idea how this can possibly happen and is there any way to recover it from this state

Thank you very muchTry running sp_resetstatus 'DBNAME' .
For a Suspect database the query "Select Status from master..Sysdatabases' would return 256.
The above sp should set it back to 0.

Also restoring the database file will turn off the suspect mode.|||reseting status did not help cause mdf files' physical size on the HD is zero bytes. the backup files size is as same as its

IS there a way to see a log file in sql to see what has happened?|||The only tool I have heard of that does this is Lumigent Log Explorer. It is fairly expensive though...

http://www.lumigent.com/products/le_sql/le_sql.htm|||My guess is that there is something wrong with your backup procedure (has it ever been tested) and that the zero length file has been restored.

Recovering DB with only the .MDF file

I saw the other thread that is related, but let me give you our circumstances. A drive failed and that drive had the SQL installation and the *.ldf files on it. The MDF files were unscathed.

The drive has been rebuilt and I have reinstalled SQL 2000 SP3A on there (that is what was there before). I have tried attaching the MDF through the GUI and letting it create the log for me. This failed.

I tried sp_attach_single_file_db, but this failed since the db was never dettached.

I tried creating the db again, dettaching it, copying the old (good) MDF file over the newly created one and then attaching (using multiple attach approaches). It knows that the log file and mdf are not matched and won't let me do the attach.

I also found DBCC REBUILD_LOG, but the article in SQL Mag supposes that your instance is still in working order and that there is an entry for the db in sysdatabases. Such is not true in my case.

Can you help? We are trying to get old backup tapes shipped to the site, looking up our Gold Partner contact info, etc., but in the meantime, I am trying to get the data restored even if we lose some transactions.

Thanks for any help!

Here is what worked for me. Thanks for SQL Server Mag for the core pieces of this:

-- LISTING 1: Undocumented DBCC Command REBUILD_LOG

/* Create a new db named the same as the inaccessible one (in this case "Ops") */

EXEC sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GO

BEGIN TRAN

UPDATE master..sysdatabases
SET status = status | 32768
WHERE name = 'Ops'

IF @.@.ROWCOUNT = 1
BEGIN
COMMIT TRAN
RAISERROR('emergency mode set', 0, 1)
END
ELSE
BEGIN
ROLLBACK
RAISERROR('unable to set emergency mode', 16, 1)
END

GO

EXEC sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE
GO

-- 3. Stop SQL Server
-- 4. Replace the newly created Ops.mdf file with the old good one
-- 5. Rename the newly created Ops_log.ldf file
-- 6. Start SQL Server

-- 7. Run the following DBCC command:
DBCC REBUILD_LOG('Ops','E:\Program Files\Microsoft SQL Server\MSSQL\Data\Ops_log.LDF')

-- You should get the message:
-- Warning: The log for database 'Ops' has been rebuilt. Transactional consistency has been lost.

-- DBCC CHECKDB should be run to validate physical consistency. Database options will have to be reset,

-- and extra log files may need to be deleted.
-- DBCC execution completed. If DBCC printed error messages, contact your system administrator.

-- 8. Run data consistancy check
/*Perform physical and logical integrity checks at this point.
Bcp data out if your integrity checks demonstrate that problems exist.
*/
DBCC CHECKDB (Ops)

ALTER DATABASE Ops SET MULTI_USER
GO

-- Set database options and recovery model as desired.
GO

-- Make a backup and start to use the DB again

|||

Note that in SQL Server 2005, all of this can be accomplished using the

CREATE DATABASE FOR ATTACH_REBUILD_LOG command.

All of the same caveats apply: if you attach a database without a log, you stand a good chance of losing data and/or ending up with a corrupt database. If you've got no choice, you've got no choice. That's what backups are for.

|||

dear Mr.BusmasterJones

I know it was a long time since you posted this but can you help me with the same problem but in SQL SERVER 2005

I had a lot of error messages when i TRIED the way you described...
there was an error when I tried the first part and error message was about adding ad hoc is not allowed
then calling the method was rejected too.

Please help me!!!!

I posted this for the same problem http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1976457&SiteID=1

THANKS IN ADVANCE

Recovering DB with only the .MDF file

I saw the other thread that is related, but let me give you our circumstances. A drive failed and that drive had the SQL installation and the *.ldf files on it. The MDF files were unscathed.

The drive has been rebuilt and I have reinstalled SQL 2000 SP3A on there (that is what was there before). I have tried attaching the MDF through the GUI and letting it create the log for me. This failed.

I tried sp_attach_single_file_db, but this failed since the db was never dettached.

I tried creating the db again, dettaching it, copying the old (good) MDF file over the newly created one and then attaching (using multiple attach approaches). It knows that the log file and mdf are not matched and won't let me do the attach.

I also found DBCC REBUILD_LOG, but the article in SQL Mag supposes that your instance is still in working order and that there is an entry for the db in sysdatabases. Such is not true in my case.

Can you help? We are trying to get old backup tapes shipped to the site, looking up our Gold Partner contact info, etc., but in the meantime, I am trying to get the data restored even if we lose some transactions.

Thanks for any help!

Here is what worked for me. Thanks for SQL Server Mag for the core pieces of this:

-- LISTING 1: Undocumented DBCC Command REBUILD_LOG

/* Create a new db named the same as the inaccessible one (in this case "Ops") */

EXEC sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GO

BEGIN TRAN

UPDATE master..sysdatabases
SET status = status | 32768
WHERE name = 'Ops'

IF @.@.ROWCOUNT = 1
BEGIN
COMMIT TRAN
RAISERROR('emergency mode set', 0, 1)
END
ELSE
BEGIN
ROLLBACK
RAISERROR('unable to set emergency mode', 16, 1)
END

GO

EXEC sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE
GO

-- 3. Stop SQL Server
-- 4. Replace the newly created Ops.mdf file with the old good one
-- 5. Rename the newly created Ops_log.ldf file
-- 6. Start SQL Server

-- 7. Run the following DBCC command:
DBCC REBUILD_LOG('Ops','E:\Program Files\Microsoft SQL Server\MSSQL\Data\Ops_log.LDF')

-- You should get the message:
-- Warning: The log for database 'Ops' has been rebuilt. Transactional consistency has been lost.

-- DBCC CHECKDB should be run to validate physical consistency. Database options will have to be reset,

-- and extra log files may need to be deleted.
-- DBCC execution completed. If DBCC printed error messages, contact your system administrator.

-- 8. Run data consistancy check
/*Perform physical and logical integrity checks at this point.
Bcp data out if your integrity checks demonstrate that problems exist.
*/
DBCC CHECKDB (Ops)

ALTER DATABASE Ops SET MULTI_USER
GO

-- Set database options and recovery model as desired.
GO

-- Make a backup and start to use the DB again

|||

Note that in SQL Server 2005, all of this can be accomplished using the

CREATE DATABASE FOR ATTACH_REBUILD_LOG command.

All of the same caveats apply: if you attach a database without a log, you stand a good chance of losing data and/or ending up with a corrupt database. If you've got no choice, you've got no choice. That's what backups are for.

|||

dear Mr.BusmasterJones

I know it was a long time since you posted this but can you help me with the same problem but in SQL SERVER 2005

I had a lot of error messages when i TRIED the way you described...
there was an error when I tried the first part and error message was about adding ad hoc is not allowed
then calling the method was rejected too.

Please help me!!!!

I posted this for the same problem http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1976457&SiteID=1

THANKS IN ADVANCE
sql