URGENT - RESTORE Failure

• Peter Gadsby

  SSCrazy

  Points: 2718

  More actions

  March 4, 2005 at 4:28 am

  #109276

  Hi all,

  Is there anyway of finding out what the filename is of the full backup for an incremental backup? The reason I ask is as follows:-

  We are trying to restore our database but the restore has failed. Basically it thinks the incremental backup file is not associated with the full backup file.

  This is the command I used....

    RESTORE DATABASE uscgdm_staging

       FROM DISK = '\\172.16.1.40\backups\USC\DATABASE BACKUPS\INCREMENTAL\uscgdm_staging_BACKUP_2005_WK10_DAY0.BAK'

       WITH NORECOVERY

    RESTORE DATABASE uscgdm_staging

       FROM DISK = '\\172.16.1.40\backups\USC\DATABASE BACKUPS\INCREMENTAL\uscgdm_staging_BACKUP_2005_WK10_DAY5.BAK'

  We write the backups as follows:-

  <Database>_BACKUP_yyyy_WKno_DAYx

  x = 0 if full backup

  Any help much appreciated.

  Thanks

  Peter.Gadsby@customer-one.co.uk

   

  Peter Gadsby
  Business Intelligence Consultant
  www.hgconsult.co.uk

• Kishore-132325

  Hall of Fame

  Points: 3784

  More actions

  March 4, 2005 at 4:34 am

  #545428

  What is the error you are getting ?

  RESTORE DATABASE uscgdm_staging

       FROM DISK = '\\172.16.1.40\backups\USC\DATABASE BACKUPS\INCREMENTAL\uscgdm_staging_BACKUP_2005_WK10_DAY0.BAK'

       WITH NORECOVERY

  Is this your Full Backup File ?

  If yes, then just recover this one with norecovery and see if it works fine, rather than running all recovcery scripts in a single GO.

  --Kishore

• Peter Gadsby

  SSCrazy

  Points: 2718

  More actions

  March 4, 2005 at 4:38 am

  #545430

  Hi Kishore,

  I dont think this is our backup file because someone else has done some restores previously which has made the BACKUP_2005_WK10_DAY0.BAK invalid I think that is why the process is currently failing. I don't have the exact error message but it said something like it couldnt apply uscgdm_staging_BACKUP_2005_WK10_DAY5.BAK to the database.

  Is there a way to find out what full backup an incremtnal backup file iis?

   

  Thanks

  PEte

  Peter Gadsby
  Business Intelligence Consultant
  www.hgconsult.co.uk

• Kishore-132325

  Hall of Fame

  Points: 3784

  More actions

  March 4, 2005 at 5:23 am

  #545431

  There is no exact method of association of Full and T-Log backups.

  However you can try the following query in your msdb database.

  select backup_start_date, backup_finish_date,database_name,type,first_family_number, last_family_number

  from backupset where database_name like 'uscgdm_staging'

  --Here type means backup type:

  D = Database.

  I = Database Differential.

  L = Log.

  F = File or Filegroup.

  From this output, you will atleast know what kind of backup files they are.

  Once you get the output, let me know.

  --Kishore

   

• Peter Gadsby

  SSCrazy

  Points: 2718

  More actions

  March 4, 2005 at 5:30 am

  #545432

  Hi Kishore,

  Thanks for that... I know what backup type they are by the naming convention  (WK0 = full) WKx (x = 1 --> 7 ) differential.

  Can I use the CheckPointLSN  / DifferentialBaseLSN from

  RESTORE HEADERONLY

  FROM DISK = '\\172.16.1.40\backups\USC\DATABASE BACKUPS\INCREMENTAL\uscgdm_staging_BACKUP_2005_WK10_DAY5.BAK'

  RESTORE HEADERONLY

  FROM DISK = '\\172.16.1.40\backups\USC\DATABASE BACKUPS\INCREMENTAL\uscgdm_staging_BACKUP_2005_WK8_DAY0.BAK'

  To determine which backup the differential one is associated with?

  Thanks

  Pete

  Peter Gadsby
  Business Intelligence Consultant
  www.hgconsult.co.uk

• Kishore-132325

  Hall of Fame

  Points: 3784

  More actions

  March 4, 2005 at 6:14 am

  #545437

  Yes. YOu may check the FirstLsn and LastLsn of the backup files.

  If the LastLsn of the first file matches the FirstLsn of the last file, then the two files are in association.

  Hope this helps.

   

• Peter Gadsby

  SSCrazy

  Points: 2718

  More actions

  March 4, 2005 at 8:15 am

  #545484

  Thanks... That seemed to have worked.. I was right the full backup was not associated with the incremental one.

  Also I found that you can find the same information from the table MSDB..BACKUPSET

  I created a view against that table as follows:-

  CREATE VIEW INCREMENTAL_BACKUPS

  as

  SELECT

   BS.NAME   FULL_BACKUP_FILENAME,

   BS2.NAME   INCREMENTAL_BACKUP_FILENAME,

   BS.BACKUP_START_DATE  FULL_BACKUP_START_DATE,

   BS.BACKUP_FINISH_DATE  FULL_BACKUP_END_DATE,

   BS2.BACKUP_START_DATE  INCREMENTAL_BACKUP_START_DATE,

   BS2.BACKUP_FINISH_DATE  INCREMENTAL_BACKUP_END_DATE

  FROM MSDB..BACKUPSET BS,

       MSDB..BACKUPSET BS2

  WHERE BS.TYPE = 'D'

  AND BS.NAME IS NOT NULL

  AND BS2.DATABASE_BACKUP_LSN = BS.CHECKPOINT_LSN

  AND BS2.TYPE = 'I'

   

  And a function to help me check the backup files prior to restoring function....

  CREATE FUNCTION GET_FULL_BACKUP_NAME  (@INCREMENTAL_FILENAME  varchar(255))

  RETURNS varchar(255)

   AS 

  BEGIN

  DECLARE @FULL_BACKUP_NAME varchar(255)

  SET @FULL_BACKUP_NAME =

  (

  SELECT

   FULL_BACKUP_FILENAME 

  FROM

   MSDB..INCREMENTAL_BACKUPS

  WHERE

   INCREMENTAL_BACKUP_FILENAME = @INCREMENTAL_FILENAME

  )

  RETURN @FULL_BACKUP_NAME

   

  END

  You learn something new every day.....

   

  Regards

  Pete

   

   

   

  Peter Gadsby
  Business Intelligence Consultant
  www.hgconsult.co.uk