September 23, 2015 at 3:18 am
I have enabled CDC on my productive database with
USE MyDB
GO
EXEC sys.sp_cdc_enable_db
GO
and I have enables cdc on tables within the database using
USE MyDB
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'MyTable',
@role_name = N'MyRole',
@filegroup_name = N'MyDB_CT',
@supports_net_changes = 1
GO
as per https://msdn.microsoft.com/en-us/library/cc627369(v=sql.105).aspx
Data changes are then stored in a system table called cdc.dbo_MyTable_CT
So far so good.
I backup the database and restore it
backup database MyDB
to Disk = 'C\Backup\MyDB.bak'
restore database MyDB1
from disk = 'C\Backup\MyDB.bak'
:w00t: No table called cdc.dbo_MyTable_CT in system tables!
Have I missed a step? and is there a way to get these tables into the backup.
I am now building a ssis job to move the contents of the cdc.dbo_MyTable_CT to an archive table, but i am worried that if I had to restore the database to a point in time I may not be able to do it for these tables.
Thanks in advance for your help
Ian
September 23, 2015 at 3:21 am
Check that you restored the correct database. If there are multiple backups in that file, your restore statement would have restored the oldest of the backups.
RESTORE HEADERONLY FROM DISK = 'C\Backup\MyDB.bak'
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 23, 2015 at 3:41 am
Thank you Gail
there is just one file in MyDB.BAK.
I've just noticed I'm moving between a productive 2008R2 database and a 2014 Test database and I am trying to recreate this without changing versions.
Ian
September 23, 2015 at 4:10 am
All on the one server
create database MyDB
create table MyTable (ColID int primary key, Col1 int)
USE MyDB
GO
EXEC sys.sp_cdc_enable_db
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'MyTable',
@role_name = N'Public',
@supports_net_changes = 1
GO
backup database MyDB
to disk = 'D:\BackupFiles\MyDB.BAK'
/*
488 Seiten wurden für die MyDB-Datenbank, Datei 'MyDB' für Datei 1, verarbeitet.
3 Seiten wurden für die MyDB-Datenbank, Datei 'MyDB_log' für Datei 1, verarbeitet.
BACKUP DATABASE hat erfolgreich 491 Seiten in 0.109 Sekunden verarbeitet (35.156 MB/s).
*/
Restore database MyDB1
FROM DISK = N'D:\BackupFiles\MyDB.BAK'
WITH FILE = 1
, MOVE N'MyDB' TO N'D:\MSSQL\Data\MyDB1.mdf'
, MOVE N'MyDB_log' TO N'D:\MSSQL\Logs\MyDB1Log.mdf'
, NOUNLOAD
, REPLACE
, STATS = 10
/*
100 Prozent verarbeitet.
488 Seiten wurden für die MyDB1-Datenbank, Datei 'MyDB' für Datei 1, verarbeitet.
3 Seiten wurden für die MyDB1-Datenbank, Datei 'MyDB_log' für Datei 1, verarbeitet.
Die DBCC-Ausführung wurde abgeschlossen. Falls DBCC Fehlermeldungen ausgegeben hat, wenden Sie sich an den Systemadministrator.
RESTORE DATABASE hat erfolgreich 491 Seiten in 0.047 Sekunden verarbeitet (81.532 MB/s).
*/
select Name,Schema_id from MyDB.sys.tables
Name Schema_id
systranschemas1
change_tables5
ddl_history5
lsn_time_mapping5
captured_columns5
index_columns5
MyTable1
dbo_MyTable_CT5
select Name,Schema_id from MyDB1.sys.tables
NameSchema_id
MyTable1
I have also tried creating a MyDB2 database and then enabling CDC then running the restore, but the results are the same. the CDC sys tables are not in the backup.
September 23, 2015 at 4:43 am
Problem Solved 🙂
When MyDB is restored from its own backup the CDC tables are there.
When the MyDB backup is used to restore a test database the CDC tables are not there.
use master
go
alter database MyDB
set single_user with rollback immediate
go
Restore database MyDB
FROM DISK = N'D:\BackupFiles\MyDB.BAK'
select Name,Schema_id from MyDB.sys.tables
NameSchema_id
systranschemas1
change_tables5
ddl_history5
lsn_time_mapping5
captured_columns5
index_columns5
MyTable1
dbo_MyTable_CT5
I'm pleased to say I can live with that 🙂
DOH!:blush: until I lose the hard drive
alter database MyDB
set single_user with rollback immediate
go
drop database MyDB
Restore database MyDB
FROM DISK = N'D:\BackupFiles\MyDB.BAK'
select Name,Schema_id from MyDB.sys.tables
NameSchema_id
systranschemas1
change_tables5
ddl_history5
lsn_time_mapping5
captured_columns5
index_columns5
MyTable1
dbo_MyTable_CT5
That works too. It would apear to be something to do with the changing name of the database
September 23, 2015 at 4:47 am
Ah!
https://msdn.microsoft.com/en-us/library/ms186858.aspx
<change_data_capture_WITH_option>::=
| KEEP_CDC
https://msdn.microsoft.com/en-us/library/ms178615.aspx
KEEP_CDC
Supported by: RESTORE
KEEP_CDC should be used to prevent change data capture settings from being removed when a database backup or log backup is restored on another server and the database is recovered. Specifying this option when restoring a backup with the NORECOVERY option is not permitted.
Restoring the database with KEEP_CDC will not create the change data capture jobs. To extract changes from the log after restoring the database, recreate the capture process job and the cleanup job for the restored database. For information, see sys.sp_cdc_add_job (Transact-SQL).
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 23, 2015 at 5:05 am
Blimey! Wouldn't KEEP be the more obvious default choice (and NOKEEP the optional one)?
September 23, 2015 at 5:54 am
Thank you Gail.
That could be solving another issue I have.
When MyTable was used in the restored database the log_reuse_wait_desc always showed 'Replication' and cdc had to be set up to clear the blockage.
September 23, 2015 at 6:10 am
Ian_McCann (9/23/2015)
When MyTable was used in the restored database the log_reuse_wait_desc always showed 'Replication' and cdc had to be set up to clear the blockage.
Yup. CDC uses the replication log reader to track the changes. As BoL mentioned though, you'll still need to create the CDC jobs after moving the DB.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply