February 4, 2010 at 5:06 am
i need to ask about sql 2005 backup and restore. My question is our database crashed 12:41 it was user level mistake. i taken backup 12:51 can i make point in time recovery or rollback database before 10 mints state
February 4, 2010 at 5:37 am
What is your recovery model set to?
If it is set to full then you can perform a point in Time recovery.
]
If it is set to Bulked-logged then you can still recover to a point in time but this mode will not capture bulked imports and other operations.
If you are set to simple recovery the transaction log is truncated at checkpoint and a point in time recovery is not possible.
When did the failure occur?
When was the last transaction log performed?
Are you missing any backup that if you were attempt to perform the restore than the backup chain is broken and you will not be able to perform a PIT Recover.
These conditions will determine what action to take.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 4, 2010 at 5:54 am
You need a backup that was taken before whatever happened. You cannot take a backup after a crash to undo it.
Do you have a backup from before 12:41? Do you have transaction log backups (assuming full recovery)
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 5, 2010 at 3:58 am
Thanks for the detail reply.
i have 20 databases in single instance all databases in full recovery model.below is our backup policy which we are using legato sql backup,
MSSQL: ==> all DB's1 Full in 2 weeks - rest incr daily tonight
As i said database is in full recovery option but when database was crashed that time we were having fullbackup and all incremental backup chain which we taken last night.Last backup and crashed logbackup gap almost 10 hours.
in simple way,
Crashed database =FULL recovery mode
Database =SQL server 2005 ENT with sp2
Database crashed time=12:41 PM
Taken full backup database using sql server not legato=12:51PM
My question is if we want to go back at 12:30PM database stat what would be the procedure.
Regards,
Hayat
February 5, 2010 at 4:11 am
The backup that you took at 12:51 cannot be used to restore to 12:30. There's no time machine in SQL.
You need to take the latest full backup from before 12:30, restore that and then restore all transaction logs stopping at 12:30 (use the STOPAT option in the RESTORE LOG command)
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 5, 2010 at 4:13 am
Thanks Gill your detail reply.
i have increm backup which i taken sunday 3:00AM there is gap as i explained database has crashed sunday 12:41.Database was open for users after crashed but some object was not found on application level.
then user told me we are facing such problem then i taken full database backup at 12:51.in this satuation can we go back database state before 12:41 or not as we dont have any trasaction log backup before crased we have only last incr mental backup tonight.database is in full recovery mode option enabled.
if yes what would be the procedure to go back before 15 mints database state.
Regards,
February 5, 2010 at 4:22 am
zhayatali (2/5/2010)
then user told me we are facing such problem then i taken full database backup at 12:51.in this satuation can we go back database state before 12:41 or not as we dont have any trasaction log backup before crased we have only last incr mental backup tonight.database is in full recovery mode option enabled.if yes what would be the procedure to go back before 15 mints database state.
For the third time! You CANNOT use a backup taken after an event to rollback to before that event. SQL Server does not have a built in time machine.
If you want to restore to 12:30, you need a full backup taken before 12:30 and transaction log backups that cover the interval between the time the full backup was taken and the time you want to restore to.
If you do not have the log backups, then you are out of luck, you cannot restore to the 12:30 time and you will need to make other plans for how to fix whatever it was that the users broke
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 5, 2010 at 4:33 am
Assuming you do not have transaction log backup after 3 AM, at which you are saying you have incremental backup (it must be differential I guess)...then you can restore your database only till 3 AM...
The general sequence of restores would be...
Use below backups before DB crash...
Last Full Backup
Last Differential Backup
All Transaction log backups in sequence after last diff backup(till 12:30 PM)..ofcourse if you have one..
Suggestion:
Backup your transaction log regurarily...to minimise the impact in future
February 5, 2010 at 8:49 am
First, you need to learn some terminology so we can help you. There is no "incremental" backup in SQL Server. There are backups that function as an incremental might work in backup software, but SQL Server has
- full backup
- differential backup
- log backup
Please let us know which backups you have from Saturday afternoon until Sunday's crash. The types and times.
Also, which log backups have you taken since the last full backup PRIOR to the crash? Please list those and we can see if there is a way to recover.
However as Gail mentioned, there is no time machine. A full backup at 12:51 has zero relevance here.
February 5, 2010 at 3:13 pm
Thank for reply.
As i understand we need last fullbackup and all TLOGS for recover point to time in recovery well What is best backup plan which i need to impliment for sql server it save zero data loss and minimal down time in production environement can you send me the best backup plan in sql server 2005.Kinldy if you have such document refer to me
Regards
February 5, 2010 at 3:26 pm
Thank you so much for detail reply.
Can u send me any backup plan sample which give me zero dataloss and recovery time reduce.
February 5, 2010 at 3:40 pm
There is no single way to get this done. What you ought to read is more on what types of HA are available and what options you have.
You can read more about the options in BOL: http://msdn.microsoft.com/en-us/library/ms190202%28SQL.90%29.aspx
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply