How do I insert the old playback data into the new playback database?
If the old playback database gets corrupted and the user would like to restore the old data from the original playback database. The below SQL Statement can be issued.
All the playback data is still stored in the temporary folder of the Diagnostic Server machine, e.g.:
"<Quest Software>\Diagnostic Server\Agent\data\playback"
It will be moved to the playback database once the connection is re-established.
Note: this may take a long time, for example it may take 15 minutes to move 600 000 rows on a server.
declare @OldDB varchar(max)
declare @NewDB varchar(max)
set @OldDB = 'SpotlightPlaybackDatabase' -- Specify source DB name here
set @NewDB = 'SpotlightPlaybackDatabaseNew' -- Specify name destination DB name here
declare @sql varchar(max)
-- spotlight_playback_alarms
-- spotlight_playback_states
set @sql = ' SET IDENTITY_INSERT [' + @NewDB + ']..[spotlight_playback_states] ON '
set @sql = @sql + ' insert into [' + @NewDB + ']..[spotlight_playback_states] (pb_state_id,pb_domain_name,pb_timestamp,pb_me_name,pb_state,pb_severity,pb_clear_alarms)
select pb_state_id,pb_domain_name,pb_timestamp,pb_me_name,pb_state,pb_severity,pb_clear_alarms from [' + @OldDB + ']..[spotlight_playback_states] '
set @sql = @sql +' SET IDENTITY_INSERT [' + @NewDB + ']..[spotlight_playback_states] OFF '
exec (@sql)
-- spotlight_playback_data
set @sql = ' SET IDENTITY_INSERT [' + @NewDB + ']..[spotlight_playback_data] ON '
set @sql = @sql + ' insert into [' + @NewDB + ']..[spotlight_playback_data] (pb_id,pb_domain_name,pb_timestamp,pb_me_name,pb_package_name,pb_table_name,pb_data)
select pb_id,pb_domain_name,pb_timestamp,pb_me_name,pb_package_name,pb_table_name,pb_data from [' + @OldDB + ']..[spotlight_playback_data] '
set @sql = @sql +' SET IDENTITY_INSERT [' + @NewDB + ']..[spotlight_playback_data] OFF '
exec (@sql)
