April 21, 2009 at 5:16 am
I am using a piece of automation software called Taskcentre, this has it's own database. This morning we were having issues with the software and it turns out that an essntial table has been cleared of all data.
There are currently no backups for the database and all tasks that have been created over the past year are now gone unless i can find a way of restoring the database to 2 - 7 days ago.
I was wondering if anyone had any ideas.
David
April 21, 2009 at 5:26 am
No backup = no data. Simple as that. There is no way you're going to restore data lost several days ago if you have no backup.
Are you sure there's absolutely no backups anywhere? Check the MSDB database, there are tables in there that log backups (backupset and others). Check them to see if someone's perhaps taken an ad-hoc backup.
I know someone's going to suggest a log reader tool. Log readers only work on the active portion of the transaction log. If a database has no backups at all, the log will be automatically truncating on checkpoint, ie every couple minutes no matter what the recovery model.
The only way to get data lost that long ago back is to restore a backup that has it in, apply log backups if necessary and copy the lost tables back to the original database)
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
April 21, 2009 at 5:37 am
I have checked the MSDB database and it doesn't contain the database needed. I'm guessing there will not be any recovery tools that may work? Something like a system restore but for DB's? (i know i'm clutching at straws now)
April 21, 2009 at 5:44 am
any chance they do 'cold' server backups with sql services down, then you could restore the actual database files (to a DIFFERENT location)
---------------------------------------------------------------------
April 21, 2009 at 5:45 am
Looking again at the MSDB database, there was a full database backup created this morning. How do i found out where these backups are?
April 21, 2009 at 5:48 am
David O'Reilly (4/21/2009)
Looking again at the MSDB database, there was a full database backup created this morning. How do i found out where these backups are?
This morning's not going to help either. You need a backup taken before those tables were cleared.
Try this
select database_name, backup_finish_date, bmf.physical_device_name
from msdb..backupset bs inner join msdb..backupmediafamily bmf on bs.media_set_id = bmf.media_set_id
order by DB_ID(database_name), backup_finish_date
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
April 21, 2009 at 6:02 am
I think this backup was taken about an hour before the data was removed but i am going to try and restore this backup over a new database and find out, how does SQL know when to create these backups?
April 21, 2009 at 6:11 am
Looks as though this backup was taken after changes were made, will it only ever keep 1 backup?
April 21, 2009 at 6:31 am
David O'Reilly (4/21/2009)
how does SQL know when to create these backups?
SQL doesn't take automatic backups. A database is backed up when a BACKUP DATABASE statement is run, either manually or by a scheduled job.
Looks as though this backup was taken after changes were made, will it only ever keep 1 backup?
Depends how the backup statement was run. If it was specified to overwrite an existing backup, yes. If it was specified to write to a different file each time, no.
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
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply