Thursday, September 18, 2014

List of all backups from the last full backup

What if one of your database is corrupted and you need to restore all the backups one by one from your last full.

It will be a time consuming and confusing task to get all the backups in order to be restored so as to not break the log chain.

The following script will help you in getting the list of all backups since the last full backup for all the databases in your instance.

It assumes there was atleast one full backup in the last 30 days...if not, you have a bigger problem.


/*
  Author: Siva Ramasamy
 
  Date  : 09/18/2014

  Description: This procedure will return the list of backup files in order from the last fullbackup for the DBs for all the databases.

  Assumption:   This procedure assumes that there was atleast one full backup in the last 30 days. If not :-) you have a bigger problem.

*/
begin
declare @db_name varchar(100)
declare @backup_start_date datetime
declare @most_recent_diffbackup_date datetime
declare @sqlcmd varchar(1000)

--drop table #temp

select bps.database_name     DatabaseName,
    'Full'                BackupType,
    CAST(CAST(bps.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS Size,
    bps.backup_start_date BackupStartDate,
    bps.backup_finish_date BackupFinishDate,
    CAST (DATEDIFF(MM,BPS.backup_start_date,bps.backup_finish_date) AS VARCHAR(10)) + ' Mins' DurationOfBackup,
       bmf.physical_device_name BackupFileName,
       ROW_NUMBER() OVER(PARTITION BY bps.database_name ORDER BY bps.Backup_Start_Date Desc) BackupOrder
into   #temp
from msdb..backupset bps inner join
  msdb..backupmediafamily bmf on bps.media_set_id = bmf.media_set_id
where bps.type = 'D' --  use the Full Backups as the starting point
and   bps.Is_Copy_Only = '0'   -- no copy only backups since we need to load logs as well.
and bps.backup_start_date > DATEADD (DD,-30,getdate()) -- assuming there was atleast 1 good full backup in the last 30 days

delete from #temp where BackupType = 'Full' and  BackupOrder > 1
--select * from #temp

-- Add and Update the Flag for Verification
Alter table #temp add Processed char(1)
Update #temp set Processed = 'N'


While (select count(1) from #temp where backuptype = 'Full' and  Processed = 'N') > 0
begin


  select top (1) @db_name = databasename, @backup_start_date = BackupStartDate from #temp where processed = 'N'

  --print @db_name
 --print @backup_start_date
 insert into #temp (DatabaseName, BackupType, Size, BackupStartDate, BackupFinishDate, DurationOfBackup, BackupFileName,BackupOrder,Processed)
 select  bps.database_name     DatabaseName,
       'Diff'                BackupType,
      CAST(CAST(bps.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS Size,
      bps.backup_start_date BackupStartDate,
      bps.backup_finish_date BackupFinishDate,
      CAST (DATEDIFF(MM,BPS.backup_start_date,bps.backup_finish_date) AS VARCHAR(10)) + ' Mins' DurationOfBackup,
      bmf.physical_device_name BackupFileName,
   ROW_NUMBER() OVER(PARTITION BY bps.database_name ORDER BY bps.Backup_Start_Date Desc) BackupOrder,
   'P'
    from    msdb..backupset bps inner join
         msdb..backupmediafamily bmf on bps.media_set_id = bmf.media_set_id
    where   bps.type = 'I' --  Pull all the differential backups after the full backup
    and     bps.Is_Copy_Only = '0'   -- no copy only backups since we need to load logs as well.
    and     bps.backup_start_date > @backup_start_date
 and     bps.database_name  = @db_name

  delete from #temp where BackupType = 'Diff' and  BackupOrder > 1 and DatabaseName = @db_name


    --get all the log backups after most recent full or differential backup
    insert into #temp (DatabaseName, BackupType, Size, BackupStartDate, BackupFinishDate, DurationOfBackup, BackupFileName, BackupOrder,Processed)
 select  bps.database_name     DatabaseName,
       'Log'                BackupType,
   CAST(CAST(bps.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS Size,
   bps.backup_start_date BackupStartDate,
   bps.backup_finish_date BackupFinishDate,
      CAST (DATEDIFF(MM,BPS.backup_start_date,bps.backup_finish_date) AS VARCHAR(10)) + ' Mins' DurationOfBackup,
      bmf.physical_device_name BackupFileName,
   ROW_NUMBER() OVER(PARTITION BY bps.database_name ORDER BY bps.Backup_Start_Date) BackupOrder,
   'P'
 from    msdb..backupset bps inner join
   msdb..backupmediafamily bmf on bps.media_set_id = bmf.media_set_id
 where   bps.type = 'L' --  Pull all the Log Backups after the most recent Differential Backup
 and     bps.Is_Copy_Only = '0'   -- no copy only backups since we need to load logs as well.
 and     bps.backup_start_date > (select max(BackupStartDate) from #temp where DatabaseName = @db_name)
 and     bps.database_name  = @db_name
 order by backup_start_date

  Update #temp set Processed = 'P' where databasename = @db_name

end

select * from #temp order by databasename, backupstartdate

end



Monday, September 1, 2014

Trancactional Replication - Setup Issues and Solution.

Recently I encountered an issue with setting up Transactional Replication. There are many blog posts about setting up Transactional replication and providing step by step explanations. I followed the exact steps mentioned in them but have been unsuccessful in setting up transactional replication successfully.

After spending some time on it, I figured out that it is due to windows login being used to connect to the publisher and subscriber. The Windows login has sysadmin privilege on the instance and I was able to connect to the instance using that login successfully. But, for some reason, It did not like it to be tied up with replication. So, I changed it to SQL account and it started working..!!

I tried to re-produce this issue by completely deleting the replication and tried again windows credentials..this time it was successful.

But, if you have been having issues with transactional replication/windows login, try changing it to SQL Login.