I'll be the first to admit the documentation is not very clear on this. They state that you should be offloading your backups to the secondaries, but while most statements are made in a general sense, I think they really mean, specifically, log backups (and copy_only
backups, if you have some need for those).
You should occasionally run full backups on the primary IMHO. The copy_only
restriction is about full backups, not log backups, and only applies to the secondary AFAIK.
You will not be able to use shrinkfile against your current transaction logs because they are full of activity that has technically not been backed up. Once you take a full (non-copy_only
) backup on the primary, then let one log backup run, you should be able to shrink the log file manually. Currently your log backups are working because the database is set to full, but they are continuing to grow because (presumably) you've never run a proper full backup on the primary.
This should be a one-time operation, and you shouldn't shrink them too small; you need to set your primary to be backing up regularly, and you need to accommodate for the largest set of activity that will occur between full or log backups. I won't go on and on about how shrinking files only so they can grow again is a futile exercise and guarantees poor performance, but I could. :-)
A full backup in SQL Server 2008 does not break the log chain. It only resets the differential base-lsn.
You also can restore log backups after restoring from a copy only. The following script demos that:
CREATE DATABASE BakTst13;
GO
ALTER DATABASE BakTst13 SET RECOVERY FULL;
GO
USE BakTst13;
GO
CREATE TABLE dbo.tst(id INT IDENTITY(1,1));
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP DATABASE BakTst13
TO DISK = 'BakTst13_Full_1' WITH INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP LOG BakTst13
TO DISK = 'BakTst13_Log_1' WITH INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP DATABASE BakTst13
TO DISK = 'BakTst13_Full_2' WITH INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP DATABASE BakTst13
TO DISK = 'BakTst13_Full_C' WITH COPY_ONLY,INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP LOG BakTst13
TO DISK = 'BakTst13_Log_2' WITH INIT,FORMAT;
GO
USE tempdb;
GO
DROP DATABASE BakTst13;
GO
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Full_1' WITH NORECOVERY;
RESTORE LOG BakTst13 FROM DISK='BakTst13_Log_1' WITH NORECOVERY;
RESTORE LOG BakTst13 FROM DISK='BakTst13_Log_2' WITH RECOVERY;
GO
SELECT * FROM BakTst13.dbo.tst;
GO
DROP DATABASE BakTst13;
GO
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Full_C' WITH NORECOVERY;
RESTORE LOG BakTst13 FROM DISK='BakTst13_Log_2' WITH RECOVERY;
GO
SELECT * FROM BakTst13.dbo.tst;
GO
DROP DATABASE BakTst13;
It creates a database and a table and inserts 50 rows into that table. In between those inserts several backups are taken in this order:
- Full
- Log
- Full
- Full Copy_Only
- Log
Next the database is dropped and restored like this:
- 1st Full
- 1st Log
- 2nd Log
The following SELECT
demonstrates that the restore was successful.
This shows that neither a COP_ONLY
nor a normal Full Backup break the log chain.
Then the database is dropped again and restored like this:
- Copy_Only Full
- 2nd Log
Afterwards the SELECT
demonstrates success again.
This demonstrates that you can use a COPY_ONLY
full backup as the base of your Log Restore.
Differential tests
I created a DIFFERENTIAL
version too:
CREATE DATABASE BakTst13;
GO
ALTER DATABASE BakTst13 SET RECOVERY FULL;
GO
USE BakTst13;
GO
CREATE TABLE dbo.tst(id INT IDENTITY(1,1));
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP DATABASE BakTst13
TO DISK = 'BakTst13_Full_1' WITH INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP DATABASE BakTst13
TO DISK = 'BakTst13_Diff_1' WITH DIFFERENTIAL,INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP DATABASE BakTst13
TO DISK = 'BakTst13_Full_2' WITH INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP DATABASE BakTst13
TO DISK = 'BakTst13_Diff_2' WITH DIFFERENTIAL,INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP DATABASE BakTst13
TO DISK = 'BakTst13_Full_C' WITH COPY_ONLY,INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP DATABASE BakTst13
TO DISK = 'BakTst13_Diff_3' WITH DIFFERENTIAL,INIT,FORMAT;
GO
USE tempdb;
GO
DROP DATABASE BakTst13;
GO
RAISERROR('------> Starting restore F1, D1, D2',0,1)WITH NOWAIT;
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Full_1' WITH NORECOVERY;
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Diff_1' WITH NORECOVERY;
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Diff_2' WITH NORECOVERY;--<--Fails!
GO
DROP DATABASE BakTst13;
GO
RAISERROR('------> Starting restore FC, D3',0,1)WITH NOWAIT;
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Full_C' WITH NORECOVERY;
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Diff_3' WITH NORECOVERY;--<--Fails!
GO
DROP DATABASE BakTst13;
GO
RAISERROR('------> Starting restore F2, D2, D3',0,1)WITH NOWAIT;
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Full_2' WITH NORECOVERY;
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Diff_2' WITH NORECOVERY;
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Diff_3' WITH RECOVERY;
GO
SELECT * FROM BakTst13.dbo.tst;
GO
DROP DATABASE BakTst13;
This takes backups in this order:
- 1st Full
- 1st Differential
- 2nd Full
- 2nd Differential
- Copy_Only Diff
- 3rd Differential
It then tries this restore route:
- 1st Full
- 1st Differential
- 2nd Differential
Step 3 fails with this error:
Msg 3136, Level 16, State 1, Line 4
This differential backup cannot be restored because the database has not been restored to the correct earlier state.
This shows that a normal full backup breaks the differential chain.
Next the database is dropped and this restore flow is attempted:
- Copy_Only Full
- 3rd Differential
Step 2 fails with the same error as step 3 above. This shows that a copy only backup cannot be used as the base for a differential restore.
Then the database is dropped again and the following restore is executed:
- 2nd Full
- 2nd Differential
- 3rd Differential
The following select proves that this restore succeeded. This shows that a COPY_ONLY
Full backup does not interrupt the differential chain.
Best Answer
The restore from full/diff nightly is actually a good option, because it kills two birds with one stone: you get the Data Warehouse and you're also regularly testing your backups.
The typical method to populate a data warehouse from production is to use an ETL (Extract-Translate-Load) technology, like SQL Server Integration Services (SSIS). SSIS packages can be created in Visual Studio or simple ones can be created with the Import and Export Wizard. Now with 20 TB you obviously can't copy all the data every night - even a 10G connection probably couldn't keep up with that. But if you can copy most of the data over once and then incrementally load the changed/updated data every night, that should work. (The Generate Scripts wizard could help you replicate the table structures if necessary.)
Because you're running Enterprise edition on the source instance, you have several options for finding what's changed on a daily/nightly basis. Your best option would be Change Data Capture (aka CDC). The little brother of CDC is Change Tracking, which is less powerful but also a little easier to work with. If you're lucky enough that most or all of the tables have reliable
UpdateDate
columns, then that will work as well.There would be challenges in keeping the structure of your source and Data Warehouse tables in sync, but you should be able to mitigate that by making sure you push changes to the Data Warehouse databases when you push them to the production database. If the Data Warehouse is just a copy of the production stuff, then you should be able to just run the same release package/scripts.
I realize this may seem daunting, but once you've got it set up, it should mostly run itself.