/*
USE Diablo;
GO
-- LITESPEED
WITH FileInfo
AS (SELECT fg.data_space_id,
fg.name AS FileGroupName,
fg.type AS FileGroupType,
fg.type_desc AS FileGroupTypeDesc,
m.name AS FileName,
FILEPATH,
SUBSTRING(FILEPATH, 1, LEN(FILEPATH) - CHARINDEX('\', REVERSE(FILEPATH), 0) + 1) AS FileDir
FROM sys.master_files m (NOLOCK)
LEFT OUTER JOIN sys.filegroups fg ON fg.data_space_id = m.data_space_id
CROSS APPLY (SELECT REPLACE (m.physical_name, ':\\', ':\') AS FILEPATH) b
WHERE m.database_id = DB_ID())
SELECT '@with = N''' + REPLACE('MOVE N''' + FileName + ''' TO N''' + FileDir + DB_NAME() + '_' + FileName + CASE WHEN FileGroupName = 'PRIMARY' THEN '.mdf'
WHEN FileGroupName IS NULL THEN '.ldf'
WHEN FileGroupType = 'FG' THEN '.ndf'
WHEN FileGroupType = 'FD' THEN ''
ELSE ''
END + '''', '''', '''''') + ''','
FROM FileInfo
ORDER BY FileName
*/
---- Kill all current connections
GO
USE MASTER
GO
DECLARE @cmdKill VARCHAR(50)
DECLARE killCursor CURSOR FOR
SELECT 'KILL ' + CONVERT(VARCHAR(5), p.SPID)
FROM master.dbo.sysprocesses AS p
WHERE p.dbid = DB_ID('Diablo')
OPEN killCursor
FETCH killCursor INTO @cmdKill
WHILE 0 = @@FETCH_STATUS
BEGIN
EXECUTE (@cmdKill)
FETCH killCursor INTO @cmdKill
END
CLOSE killCursor
DEALLOCATE killCursor
GO
EXEC master.dbo.xp_restore_database @database = N'Diablo' ,
@filename = N'H:\Backup\Diablo_Full_20110506_064302660.bak',
@filenumber = 1,
@jobp = 'BoUgFehNGE3+lGZVVXl+GSUGv1/VYKBE8GPE0SHCCuMdcVvvcJwscgBzOnTDZwXV',
@with = N'REPLACE', @with = N'KEEP_CDC',
@with = N'STATS = 10',
@with = N'MOVE N''Audit_Tax_1'' TO N''D:\Data\Data7\UAT1\Diablo_Audit_Tax_1.ndf''',
@with = N'MOVE N''Audit_Trans_1'' TO N''D:\Data\Data7\UAT1\Diablo_Audit_Trans_1.ndf''',
@with = N'MOVE N''Common_1'' TO N''D:\Data\Data7\UAT1\Diablo_Common_1.ndf''',
@with = N'MOVE N''Diablo'' TO N''D:\Data\Data7\UAT1\Diablo_Diablo.mdf''',
@with = N'MOVE N''Diablo_Default_1'' TO N''D:\Data\Data7\UAT1\Diablo_Diablo_Default_1.ndf''',
@with = N'MOVE N''Diablo_log'' TO N''D:\LOG\Log4\UAT1\Diablo_Diablo_log.ldf''',
@with = N'MOVE N''Propagation_1'' TO N''D:\Data\Data7\UAT1\Diablo_Propagation_1.ndf''',
@with = N'MOVE N''Propagation_2'' TO N''D:\Data\Data7\UAT1\Diablo_Propagation_2.ndf''',
@with = N'MOVE N''Tax_1'' TO N''D:\Data\Data7\UAT1\Diablo_Tax_1.ndf''',
@with = N'MOVE N''Tax_2'' TO N''D:\Data\Data7\UAT1\Diablo_Tax_2.ndf''',
@with = N'MOVE N''Trans_1'' TO N''D:\Data\Data7\UAT1\Diablo_Trans_1.ndf''',
@with = N'MOVE N''Trans_2'' TO N''D:\Data\Data7\UAT1\Diablo_Trans_2.ndf''',
@affinity = 0,
@logging = 0
GO
USE Diablo;
GO
EXEC sys.sp_cdc_drop_job @job_type = N'capture';
GO
EXEC sys.sp_cdc_drop_job @job_type = N'cleanup';
GO
EXEC sys.sp_cdc_add_job @job_type = 'capture'
,@maxtrans = 5000
GO
EXEC sys.sp_cdc_add_job
@job_type = 'cleanup'
,@retention = 14400
,@threshold = 5000