How to restore Point In Time in this situation ?
in [Server Configuration]
|
From: Patrick on 16 Mar 2010 05:36 Hi, We are new to SQL Server 2008. We make daily FULL backup of production database at night and transaction log backup every hour by maintenance plan. We also grant privilege to run FULL Database Backup by using the following SQL: BACKUP DATABASE [Finance_Live] TO DISK = N'D:\FINBackup\FLIVE.bak' WITH INIT We have tested the script above for a number of times between 4:00pm and 4:45pm. When we attempt to restore the backup (FLIVE.BAK) to a new database (For testing), we find that we have to use the FLIVE.BAK instead of the FULL Backup last night + All today's transaction log backup. When we attempt to restore the database Point In Time to 4:25pm, SQL Server chooses the FLIVE.BAK File (at 4:23pm) and transaction Log. Sine that FLIVE.BAK at 4:25pm is overwritten by the later backup (The latest one is 4:47pm), we get the following error message: TITLE: Microsoft SQL Server Management Studio------------------------------ Restore failed for Server 'SERVER1'. (Microsoft.SqlServer.SmoExtended) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.2531.0+((Katmai_PCU_Main).090329-1015+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476 ------------------------------ADDITIONAL INFORMATION: System.Data.SqlClient.SqlError: The specified STOPAT time is too early. Allor part of the database is already rolled forward beyond that point.(Microsoft.SqlServer.Smo) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.2531.0+((Katmai_PCU_Main).090329-1015+)&LinkId=20476 ------------------------------BUTTONS: OK------------------------------ We would like to know is there any way to restore Point In Time to 4:25pm ? We did have full backup last night + all transaction log today. Thanks again.
From: Uri Dimant on 16 Mar 2010 06:19 http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp "Patrick" <Patrick(a)discussions.microsoft.com> wrote in message news:%23DIT0xOxKHA.948(a)TK2MSFTNGP05.phx.gbl... > Hi, > > We are new to SQL Server 2008. > > We make daily FULL backup of production database at night and transaction > log backup every hour by maintenance plan. > > We also grant privilege to run FULL Database Backup by using the following > SQL: > BACKUP DATABASE [Finance_Live] TO DISK = N'D:\FINBackup\FLIVE.bak' WITH > INIT > > We have tested the script above for a number of times between 4:00pm and > 4:45pm. > > When we attempt to restore the backup (FLIVE.BAK) to a new database (For > testing), we find that we have to use the FLIVE.BAK instead of the FULL > Backup last night + All today's transaction log backup. > > When we attempt to restore the database Point In Time to 4:25pm, SQL > Server chooses the FLIVE.BAK File (at 4:23pm) and transaction Log. Sine > that FLIVE.BAK at 4:25pm is overwritten by the later backup (The latest > one is 4:47pm), we get the following error message: > > TITLE: Microsoft SQL Server Management > Studio------------------------------ Restore failed for Server 'SERVER1'. > (Microsoft.SqlServer.SmoExtended) For help, click: > http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.2531.0+((Katmai_PCU_Main).090329-1015+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476 - > -----------------------------ADDITIONAL INFORMATION: > System.Data.SqlClient.SqlError: The specified STOPAT time is too early. > Allor part of the database is already rolled forward beyond that > point.(Microsoft.SqlServer.Smo) For help, click: > http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.2531.0+((Katmai_PCU_Main).090329-1015+)&LinkId=20476 - > -----------------------------BUTTONS: OK------------------------------ > > > We would like to know is there any way to restore Point In Time to 4:25pm > ? We did have full backup last night + all transaction log today. > > Thanks again.
From: jgurgul on 16 Mar 2010 07:06 Hi, You may also want to look at WITH COPY_ONLY: http://msdn.microsoft.com/en-us/library/ms191495.aspx Jon "Patrick" wrote: > Hi, > > We are new to SQL Server 2008. > > We make daily FULL backup of production database at night and > transaction log backup every hour by maintenance plan. > > We also grant privilege to run FULL Database Backup by using the > following SQL: > BACKUP DATABASE [Finance_Live] TO DISK = N'D:\FINBackup\FLIVE.bak' WITH INIT > > We have tested the script above for a number of times between 4:00pm and > 4:45pm. > > When we attempt to restore the backup (FLIVE.BAK) to a new database (For > testing), we find that we have to use the FLIVE.BAK instead of the FULL > Backup last night + All today's transaction log backup. > > When we attempt to restore the database Point In Time to 4:25pm, SQL > Server chooses the FLIVE.BAK File (at 4:23pm) and transaction Log. Sine > that FLIVE.BAK at 4:25pm is overwritten by the later backup (The latest > one is 4:47pm), we get the following error message: > > TITLE: Microsoft SQL Server Management > Studio------------------------------ Restore failed for Server > 'SERVER1'. (Microsoft.SqlServer.SmoExtended) For help, click: > http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.2531.0+((Katmai_PCU_Main).090329-1015+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476 > ------------------------------ADDITIONAL INFORMATION: > System.Data.SqlClient.SqlError: The specified STOPAT time is too early. > Allor part of the database is already rolled forward beyond that > point.(Microsoft.SqlServer.Smo) For help, click: > http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.2531.0+((Katmai_PCU_Main).090329-1015+)&LinkId=20476 > ------------------------------BUTTONS: OK------------------------------ > > > We would like to know is there any way to restore Point In Time to > 4:25pm ? We did have full backup last night + all transaction log today. > > Thanks again. > . >
From: Tibor Karaszi on 16 Mar 2010 07:11 Whenever the GUI acts up on you, do it yourself. I have read the post several times, but I can't make heads or tails of what backups you produced and what are still available (those not available has been overwritten using INIT, perhaps). If you can post such a description, it would be much easier for us to help. Something like: 13:00 Full 13:30 Log .... 14:40 Log 14:45 Full (no longer available - overwritten) 14:50 Log With above information, we will be able to tell you what restore options you have. Note that the GUI will only base its restore suggestion on backup history - it will not verify that the stuff is still there.... -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "Patrick" <Patrick(a)discussions.microsoft.com> wrote in message news:#DIT0xOxKHA.948(a)TK2MSFTNGP05.phx.gbl... > Hi, > > We are new to SQL Server 2008. > > We make daily FULL backup of production database at night and transaction > log backup every hour by maintenance plan. > > We also grant privilege to run FULL Database Backup by using the following > SQL: > BACKUP DATABASE [Finance_Live] TO DISK = N'D:\FINBackup\FLIVE.bak' WITH > INIT > > We have tested the script above for a number of times between 4:00pm and > 4:45pm. > > When we attempt to restore the backup (FLIVE.BAK) to a new database (For > testing), we find that we have to use the FLIVE.BAK instead of the FULL > Backup last night + All today's transaction log backup. > > When we attempt to restore the database Point In Time to 4:25pm, SQL > Server chooses the FLIVE.BAK File (at 4:23pm) and transaction Log. Sine > that FLIVE.BAK at 4:25pm is overwritten by the later backup (The latest > one is 4:47pm), we get the following error message: > > TITLE: Microsoft SQL Server Management > Studio------------------------------ Restore failed for Server 'SERVER1'. > (Microsoft.SqlServer.SmoExtended) For help, click: > http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.2531.0+((Katmai_PCU_Main).090329-1015+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476 - > -----------------------------ADDITIONAL INFORMATION: > System.Data.SqlClient.SqlError: The specified STOPAT time is too early. > Allor part of the database is already rolled forward beyond that > point.(Microsoft.SqlServer.Smo) For help, click: > http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.2531.0+((Katmai_PCU_Main).090329-1015+)&LinkId=20476 - > -----------------------------BUTTONS: OK------------------------------ > > > We would like to know is there any way to restore Point In Time to 4:25pm > ? We did have full backup last night + all transaction log today. > > Thanks again.
From: Patrick on 17 Mar 2010 06:22
Dear all, Many thanks for your advice. 02:00 Full (Maintenance Plan) 07:00 Log ...... 14:00 Log 15:00 Log 16:00 Log 16:02 Full (Manual / No longer available - overwritten) 16:15 Full (Manual / No longer available - overwritten) 16:23 Full (Manual / No longer available - overwritten) 16:45 Full (Manual) 17:00 Log Is there any way to restore database point in time to 16:25 by using 0:2:00 Full + Transaction Logs ? Thanks again Patrick On 16/03/2010 10:11 PM, Tibor Karaszi wrote: > Whenever the GUI acts up on you, do it yourself. I have read the post > several times, but I can't make heads or tails of what backups you > produced and what are still available (those not available has been > overwritten using INIT, perhaps). If you can post such a description, it > would be much easier for us to help. Something like: > > 13:00 Full > 13:30 Log > ... > 14:40 Log > 14:45 Full (no longer available - overwritten) > 14:50 Log > > With above information, we will be able to tell you what restore options > you have. Note that the GUI will only base its restore suggestion on > backup history - it will not verify that the stuff is still there.... > |