Database Restore - LSN Sequence Broken

• ishrarg 56334

  SSC Rookie

  Points: 46

  More actions

  April 26, 2016 at 8:01 am

  #326658

  Hello Everybody,

  This is first time I writing in this Forum. I use to write software in C#.net and I use sql server as backend.

  One of my software is distributed to around 500 businesses (All of them are non technical people). Its basically accounting software.

  Once user request back operation, stored procedure checks whether any file with name "FULL_[Databasename]_YEARMONTH" ie. "FULL_IGEnterp_2016April.bak" is exist or not.

  If it exist then stored procedure creates differential backup on this file.

  Otherwise it creates Full backup on this file (Creates new file of course).

  ALTER PROCEDURE [dbo].[op_BackupFull]

  @FullBackupLocation as varchar(550)

  ASBEGIN

  --declare @FullBackupLocation as varchar(200)

  --set @FullBackupLocation='H:'

  declare @FullPath as varchar(1050)

  declare @DBNm as varchar(max)

  SELECT @DBNm=db_name()

  declare @ShortDate as varchar(10)

  --set @ShortDate= replace(cast(cast(GETDATE() as date) as varchar(10)),'-','')

  set @ShortDate= cast(year(getdate()) as varchar) + cast(DATENAME(month,getdate()) as varchar)

  set @FullPath=@FullBackupLocation + '\FULL_'+@DBNm+'_'+@ShortDate;

  --print @fullpath

  DECLARE @File_Exists int

  execute master.dbo. xp_fileexist @FullPath , @File_Exists OUT

  --print @File_Exists

  if(@File_Exists =0)

  begin

  BACKUP DATABASE @DBNm

  TO DISK = @FullPath

  print 'Full Backup taken'

  end

  else

  begin

  BACKUP DATABASE @DBNm TO DISK = @FullPath WITH DIFFERENTIAL ;

  print 'Differencial Backup taken'

  end

  END

  Today it was time to restore backup file from one of my users. It has 29 backup sets/files/sequences inside one file and upto 17 everything is okay, but after 17th file I can't restore any of differential backup set because when I investigated I found that DatabaseBackupLSN is different from others above. Its unbelievable. I don't understand why this happened.

  Can you please look at attached snapshot in below URL and give me solution if you have experienced such thing.

  

  http://www.evernote.com/l/AQdQ1o5CtixCr6bjEpWVUtJo-0gglxN3NfM/"> http://www.evernote.com/l/AQdQ1o5CtixCr6bjEpWVUtJo-0gglxN3NfM/

  B'Regards

  Ishrar G