Accidental DBA: Unsure why I have a broken log chain?

First of all, thanks for having a look at this - We've got a large database in our production environment (1.26 TB), it's got a few hundred corrupt pages in it and has done for months, so the same corruption is in all of the backups available.

I got dragged into this late last week as it seems the scheduled jobs for reorganizing indexes have been failing for some time due to the corruption and we are now at the stage where the indexes on the largest and most commonly used tables range between 50% and 80% fragmentation which is seriously degrading application performance.

I've entertained a number of ideas on how I could remedy this situation (believe me, I'm more than open to alternatives) and from what I've read I think the following sounds like a good idea:

• Take a compressed backup of the corrupt database from production (SQL Server 2008R2), copy down to our (local) development server (SQL Server 2016) and run the repair (there isn't enough space on the production system to have two copies of the corrupt db attached concurrently).

• Restore two copies of this corrupt backup onto the development server where we do have enough space.

• Run the repair down on the development server with the allow_data_loss option for one of these dbs, rename to "DbNameHereRepaired" (most of the corrupt pages that I looked at with DBCC PAGE appeared to have had their data all set to 0x00 anyway).

-
Rename other copy to DbNameHereCorrupt, attempt to run the page level restore using the following code:

```
alter database DbNameHereCorrupt set single_user with rollback immediate

--set db to FULL recovery mode
alter database DbNameHereCorrupt set recovery full

--Declare paths for backups
declare @fullBackupPath nvarchar(max) = N'D:\Restore\DbNameHereCorrupt-FullBackup.bck'
declare @tranLogBackupPath nvarchar(max) = N'D:\Restore\DbNameHereCorrupt-LogBackup.bck'

--Take full backup to begin new TLogChain
backup database DbNameHereCorrupt to disk = @fu

Your command for full backup


--Take full backup to begin new TLogChain


backup database DbNameHereCorrupt to disk = @fullBackupPath with init, differential;

does not make full backup.


DIFFERENTIAL


Used only with BACKUP DATABASE, specifies that the database or file
backup should consist only of the portions of the database or file
changed since the last full backup.

As @dbamex mentioned full and restore commands use different file names.

StackExchange Database Administrators Q#224300, answer score: 3