February 7, 2011 at 7:49 am
I have a decommissioned epicore system that is used as a reference and somehow the log files to the database are missing in the backups and the array they were on is faulty. I have the databases and they are showing up as suspect. I am not experienced with SQL and need help urgently.
Thanks for anyones help!!!
February 7, 2011 at 7:53 am
http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 7, 2011 at 8:12 am
Thank you. I will be testing these steps. Has anyone had success in these steps and were able to see their data in the databases?
February 7, 2011 at 8:42 am
Yes, I have seen it work (my test is in that blog post), I've also had feedback from people I've helped here that it worked.
It depends what's damaged and how. If the mdf is damaged, the chances go right down.
As for the data, it is repair_allow_data_loss, some may be lost.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 7, 2011 at 8:48 am
No, it is only that the log files on a partition were lost due to the RAID controller being faulty. The LDFs are also missing from the backups BUT the (MDF) SQL database is there and on my backups. I expect the database to NOT be corrupt. It was last accessed two months ago with no problems.
February 7, 2011 at 2:06 pm
ITUSER60 (2/7/2011)
The LDFs are also missing from the backups
Err, when you take a database backup it includes mdf and ldf. There's no way to lose a ldf from a SQL backup, it's part of the backup file.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 7, 2011 at 2:21 pm
I meant that it was from my BUE which backs up the file itself. However I did find that I had my BAK files secured somewhere else and the sizes were making suspicious that they included both. Thanks for the confirmation. What a relief and thanks for the guidance and info. I certainly know more now than before.
February 7, 2011 at 2:56 pm
ITUSER60 (2/7/2011)
I meant that it was from my BUE which backs up the file itself.
That is not the way to backup SQL Server and, if the 'backups' were taken while SQL was running, they could be useless
However I did find that I had my BAK files secured somewhere else and the sizes were making suspicious that they included both.
It's not totally correct to say a full backup includes both, but when restored it will restore a consistent database with data and log files/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 7, 2011 at 3:26 pm
I'd say that I plan on backing up with what has already been configured in the enterprise manager with the existing backups it already did last a month ago prior to me losing the array with the logs. I will not be using the actual databases from my backups. I think it is best to play it safe by the book and use the BAK files found on another partition. What do you think? It was a decommissioned system. No one barely even thinks about this system anymore. But don't want problems from them needing the reference data.
February 7, 2011 at 10:22 pm
ITUSER60 (2/7/2011)
I think it is best to play it safe by the book and use the BAK files found on another partition.
I think that's probably the best bet. Keep the mdf file around, just in case
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 8, 2011 at 3:57 am
hi,
you have a suspect database, right.
1. the prefered way is to restore it from backup you have taken,
2. if you have taken the ldf and mdf files backup while the database is running they do not help you in making your database up as the database files are taken backup while the database is shutdown in a proper way.
3. if you have detached a database from an instance and later if you have lost the ldf file, still you can make your database functional with the mdf file you are having as it was shutdown properly.
use sp_attach_single_file_db
example:
EXEC sp_attach_single_file_db @dbname = 'Avinash',
@physname = N'M:\Avinash.mdf';
if you dont have any of the above options working you can try with the last option which has worked for me and hope it will work for you as well.
you need to make your database from suspect mode to emergency mode and repair the database but there will be some potential data loss in this process, if you dont want any data loss, all the uncommited data will be lost. For the procedure check on my blog.
http://avinashily.blogspot.com/2011/02/how-to-recover-suspect-mode-database.html
February 8, 2011 at 4:07 am
avinashily (2/8/2011)
use sp_attach_single_file_dbexample:
EXEC sp_attach_single_file_db @dbname = 'Avinash',
@physname = N'M:\Avinash.mdf';
sp_attach_db and sp_attach_single_file_db are both deprecated, included for backward compatibility with SQL 2000 and will be removed from a future version. Neither should be used for new development.
The replacements are CREATE DATABASE ... FOR ATTACH and CREATE DATABASE ... FOR ATTACH_REBUILD_LOG. Do note that the ATTACH_REBUILD_LOG will only work if the database was cleanly shut down at the time the log was deleted. If it wasn't, the attach will fail.
p.s. Missing file results in the database state 'recovery_pending', not suspect.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 8, 2011 at 4:21 am
How do we know if a db was cleanly shutdown?
M&M
February 8, 2011 at 4:23 am
yeah glia,
you are right, we need to use create database statement to attach a file instead of sp_attach_db but i just gave this option as an option for now. any ways thanks for correcting me. i see all your posts really intresting.
February 8, 2011 at 4:38 am
when you use sp_detach_db your database will be shutdown cleanly by the sql server. or else you can use shutdown transact sql to shutdown the database.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply