I have an instance where a large group of tabs were accidentally deleted by another admin. This wasn't caught until a couple weeks later. I have found the deleted tabs in a back-up file and now want to restore them somehow. I have been toying with the SQL script below. If someone can help me, please do. If there is a better/easier way, please let me know that too.
There are 3 databases. I have a live database, a live TEST database (demo site), and the database containing the back-up from the live database. I am first attempting to restore the tabs on the test database.
The following code runs error-free (even in the SQL Server Profiler), but the expected updates are not made. Can some please help me with this one? Pretty please... :)
USE [RestoredDatabase];
DECLARE @Database NVARCHAR(50);
SET @Database = 'TestDatabase';
--SET @Database = 'LiveDatabase';
-- PROPERTIES FOR TABS CURSOR
DECLARE @TabId1 INT, @TabOrder1 INT, @PortalID1 INT, @TabName1 NVARCHAR(50), @IsVisible1 BIT, @ParentId1 INT, @Level1 INT,
@IconFile1 NVARCHAR(100), @DisableLink1 BIT, @Title1 NVARCHAR(200), @Description1 NVARCHAR(500),
@KeyWords1 NVARCHAR(500), @IsDeleted1 BIT, @Url1 NVARCHAR(255), @SkinSrc1 NVARCHAR(200),
@ContainerSrc1 NVARCHAR(200), @TabPath1 NVARCHAR(255), @StartDate1 DATETIME, @EndDate1 DATETIME,
@RefreshInterval1 INT, @PageHeadText1 NVARCHAR(500);
-- PROPERTIES FOR MODULES CURSOR
DECLARE @ModuleDefID2 INT, @ModuleTitle2 NVARCHAR(256), @AllTabs2 BIT, @IsDeleted2 BIT,
@InheritViewPermissions2 BIT, @Header2 NVARCHAR(MAX), @Footer2 NVARCHAR(MAX), @StartDate2 DATETIME,
@EndDate2 DATETIME, @PortalID2 INT;
-- PROPERTIES FOR TABMODULES CURSOR
DECLARE @TabID3 INT, @ModuleID3 INT, @PaneName3 NVARCHAR(50), @ModuleOrder3 INT, @CacheTime3 INT,
@Alignment3 NVARCHAR(10), @Color3 NVARCHAR(20), @Border3 NVARCHAR(1), @IconFile3 NVARCHAR(100),
@Visibility3 INT, @ContainerSrc3 NVARCHAR(200), @DisplayTitle3 BIT, @DisplayPrint3 BIT,
@DisplaySyndicate3 BIT;
-- PROPERTIES FOR TEXT/HTML MODULE CURSOR
DECLARE @ModuleID4 INT, @DesktopHtml4 NVARCHAR(MAX), @DesktopSummary4 NVARCHAR(MAX), @CreatedByUser4 INT,
@CreatedDate4 DATETIME;
DECLARE @TabIdentity INT, @ModuleIdentity INT;
SET @TabIdentity = 0;
SET @ModuleIdentity = 0;
DECLARE @Quotes NVARCHAR(5), @SafeQuotes NVARCHAR(5);
SET @Quotes = '''';
SET @SafeQuotes = '\''';
DECLARE @Sql NVARCHAR(4000);
SET @Sql = '';
-- LOAD THE TAB IDS INTO A LOCAL COLLECTION
CREATE TABLE #Tabs ([TabId] INT);
INSERT INTO #Tabs
SELECT [TabID] FROM [dbo].[Tabs] WHERE [TabName] LIKE 'Car Rental %' AND [IsDeleted] = 0;
-- LOAD THE MODULE IDS INTO A LOCAL COLLECTION
CREATE TABLE #Modules ([ModuleId] INT);
INSERT INTO #Modules
SELECT [ModuleID] FROM [dbo].[TabModules] WHERE [TabId] IN (SELECT tmpT.[TabId] FROM #Tabs tmpT);
--BEGIN TRANSACTION [t1] WITH MARK N'Importing SEO Car Landing Pages';
-- GET A CURSOR FOR THE TABS
DECLARE [cTabs] CURSOR FOR
SELECT [TabId],[TabOrder],[PortalID],[TabName],[IsVisible],[ParentId],[Level]
,[IconFile],[DisableLink],[Title],[Description],[KeyWords],[IsDeleted]
,[Url],[SkinSrc],[ContainerSrc],[TabPath],[StartDate],[EndDate]
,[RefreshInterval],[PageHeadText]
FROM [dbo].[Tabs]
WHERE [TabId] IN (SELECT tmpT.[TabId] FROM #Tabs tmpT)
ORDER BY [TabId];
OPEN [cTabs];
FETCH NEXT FROM [cTabs] INTO @TabId1, @TabOrder1, @PortalID1, @TabName1, @IsVisible1, @ParentId1, @Level1,
@IconFile1, @DisableLink1, @Title1, @Description1, @KeyWords1, @IsDeleted1, @Url1, @SkinSrc1,
@ContainerSrc1, @TabPath1, @StartDate1, @EndDate1, @RefreshInterval1, @PageHeadText1;
-- LOOP THROUGH TABS
WHILE @@FETCH_STATUS = 0
BEGIN
IF @Database = 'Dnn-Supplier-Demo'
BEGIN
SET @PortalId1 = 0;
SET @ParentId1 = 107;
END
-- ADD THE TAB TO THE DATABASE
SET @TabName1 = REPLACE(@TabName1, @Quotes, @SafeQuotes);
SET @IconFile1 = REPLACE(@IconFile1, @Quotes, @SafeQuotes);
SET @Title1 = REPLACE(@Title1, @Quotes, @SafeQuotes);
SET @Description1 = REPLACE(@Description1, @Quotes, @SafeQuotes);
SET @Keywords1 = REPLACE(@KeyWords1, @Quotes, @SafeQuotes);
SET @Url1 = REPLACE(@Url1, @Quotes, @SafeQuotes);
SET @SkinSrc1 = REPLACE(@SkinSrc1, @Quotes, @SafeQuotes);
SET @ContainerSrc1 = REPLACE(@ContainerSrc1, @Quotes, @SafeQuotes);
SET @TabPath1 = REPLACE(@TabPath1, @Quotes, @SafeQuotes);
SET @PageHeadText1 = REPLACE(@PageHeadText1, @Quotes, @SafeQuotes);
SET @Sql = 'INSERT INTO [' + @Database + '].[dbo].[Tabs] ([TabOrder],[PortalID],[TabName],[IsVisible],[ParentId]
,[Level],[IconFile],[DisableLink],[Title],[Description],[KeyWords],[IsDeleted]
,[Url],[SkinSrc],[ContainerSrc],[TabPath],[StartDate],[EndDate],[RefreshInterval]
,[PageHeadText]
)VALUES(' + CAST(@TabOrder1 AS NVARCHAR(50)) + ', ' + CAST(@PortalID1 AS NVARCHAR(50)) + ', ''' + @TabName1 + ''', ' + CAST(@IsVisible1 AS NVARCHAR(50)) +
', ' + CAST(@ParentId1 AS NVARCHAR(50)) + ', ' + CAST(@Level1 AS NVARCHAR(50)) + ', ''' + @IconFile1 + ''', ' + CAST(@DisableLink1 AS NVARCHAR(50)) +
', ''' + @Title1 + ''', ''' + @Description1 + ''', ''' + @Keywords1 + ''', ' + CAST(@IsDeleted1 AS NVARCHAR(50)) +
', ''' + @Url1 + ''', ''' + @SkinSrc1 + ''', ''' + @ContainerSrc1 + ''', ''' + @TabPath1 +
''', ''' + CAST(@StartDate1 AS NVARCHAR(50)) + ''', ''' + CAST(@EndDate1 AS NVARCHAR(50)) + ''', ' + CAST(@RefreshInterval1 AS NVARCHAR(50)) +
', ''' + @PageHeadText1 + ''')';
PRINT @Sql;
EXEC(@Sql);
SELECT @TabIdentity = SCOPE_IDENTITY();
-- * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
--
-- CHILD CUSOR FOR MODULES
--
-- * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
DECLARE [cModules] CURSOR FOR
SELECT [ModuleDefID],[ModuleTitle],[AllTabs],[IsDeleted]
,[InheritViewPermissions],[Header],[Footer],[StartDate],[EndDate],[PortalID]
FROM [Dnn_BackUp_Db].[dbo].[Modules]
WHERE [ModuleId] IN (SELECT [ModuleID] FROM [dbo].[TabModules] WHERE [TabId] = @TabId1);
OPEN [cModules];
FETCH NEXT FROM [cModules] INTO @ModuleDefID2, @ModuleTitle2, @AllTabs2, @IsDeleted2,
@InheritViewPermissions2, @Header2, @Footer2, @StartDate2, @EndDate2, @PortalID2;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @Database = 'Dnn-Supplier-Demo'
BEGIN
SET @PortalId2 = 0;
END
SET @ModuleTitle2 = REPLACE(@ModuleTitle2, @Quotes, @SafeQuotes);
SET @Header2 = REPLACE(@Header2, @Quotes, @SafeQuotes);
SET @Footer2 = REPLACE(@Footer2, @Quotes, @SafeQuotes);
SET @Sql = 'INSERT INTO [' + @Database + '].[dbo].[Modules] ([ModuleDefID],[ModuleTitle],
[AllTabs],[IsDeleted],[InheritViewPermissions],[Header],[Footer],[StartDate],
[EndDate],[PortalID]
)VALUES(
' + CAST(@ModuleDefID2 AS NVARCHAR(50)) + ', ''' + @ModuleTitle2 + ''', ' + CAST(@AllTabs2 AS NVARCHAR(50)) + ', ' + CAST(@IsDeleted2 AS NVARCHAR(50)) +
', ' + CAST(@InheritViewPermissions2 AS NVARCHAR(50)) + ', ''' + @Header2 + ''', ''' + @Footer2 +
''', ''' + CAST(@StartDate2 AS NVARCHAR(50)) + ''', ''' + CAST(@EndDate2 AS NVARCHAR(50)) + ''', ' + CAST(@PortalID2 AS NVARCHAR(50)) + ')';
PRINT @Sql;
EXEC(@Sql);
SELECT @ModuleIdentity = SCOPE_IDENTITY();
FETCH NEXT FROM [cModules] INTO @ModuleDefID2, @ModuleTitle2, @AllTabs2, @IsDeleted2,
@InheritViewPermissions2, @Header2, @Footer2, @StartDate2, @EndDate2, @PortalID2;
END
CLOSE [cModules];
DEALLOCATE [cModules];
-- * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
--
-- CHILD CURSOR FOR TABMODULES
--
-- * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
DECLARE [cTabModules] CURSOR FOR
SELECT [TabID],[ModuleID],[PaneName],[ModuleOrder],[CacheTime]
,[Alignment],[Color],[Border],[IconFile],[Visibility],[ContainerSrc]
,[DisplayTitle],[DisplayPrint],[DisplaySyndicate]
FROM [Dnn_BackUp_Db].[dbo].[TabModules]
WHERE [TabId] = @TabId1;
OPEN [cTabModules];
FETCH NEXT FROM [cTabModules] INTO @TabID3, @ModuleID3, @PaneName3, @ModuleOrder3, @CacheTime3,
@Alignment3, @Color3, @Border3, @IconFile3, @Visibility3, @ContainerSrc3, @DisplayTitle3,
@DisplayPrint3, @DisplaySyndicate3;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @PaneName3 = REPLACE(@PaneName3, @Quotes, @SafeQuotes);
SET @Alignment3 = REPLACE(@Alignment3, @Quotes, @SafeQuotes);
SET @Color3 = REPLACE(@Color3, @Quotes, @SafeQuotes);
SET @Border3 = REPLACE(@Border3, @Quotes, @SafeQuotes);
SET @IconFile3 = REPLACE(@IconFile3, @Quotes, @SafeQuotes);
SET @ContainerSrc3 = REPLACE(@ContainerSrc3, @Quotes, @SafeQuotes);
SET @Sql = 'INSERT INTO [' + @Database + '].[dbo].[TabModules]([TabID],[ModuleID],[PaneName],[ModuleOrder]
,[CacheTime],[Alignment],[Color],[Border],[IconFile],[Visibility],[ContainerSrc]
,[DisplayTitle],[DisplayPrint],[DisplaySyndicate]
)VALUES(
' + CAST(@TabIdentity AS NVARCHAR(50)) + ', ' + CAST(@ModuleIdentity AS NVARCHAR(50)) + ', ''' + @PaneName3 + ''', ' + CAST(@ModuleOrder3 AS NVARCHAR(50)) +
', ' + CAST(@CacheTime3 AS NVARCHAR(50)) + ', ''' + @Alignment3 + ''', ''' + @Color3 + ''', ''' + @Border3 +
''', ''' + @IconFile3 + ''', ' + CAST(@Visibility3 AS NVARCHAR(50)) + ', ''' + @ContainerSrc3 +
''', ' + CAST(@DisplayTitle3 AS NVARCHAR(50)) + ', ' + CAST(@DisplayPrint3 AS NVARCHAR(50)) + ', ' + CAST(@DisplaySyndicate3 AS NVARCHAR(50)) + ')';
PRINT @Sql;
EXEC(@Sql);
FETCH NEXT FROM [cTabModules] INTO @TabID3, @ModuleID3, @PaneName3, @ModuleOrder3, @CacheTime3,
@Alignment3, @Color3, @Border3, @IconFile3, @Visibility3, @ContainerSrc3, @DisplayTitle3,
@DisplayPrint3, @DisplaySyndicate3;
END
CLOSE [cTabModules];
DEALLOCATE [cTabModules];
-- * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
--
-- CHILD CURSOR FOR TEXT/HTML
--
-- * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
DECLARE [cTextHtml] CURSOR FOR
SELECT [ModuleID],[DesktopHtml],[DesktopSummary],[CreatedByUser],[CreatedDate]
FROM [Dnn_BackUp_Db].[dbo].[HtmlText]
WHERE [ModuleId] IN (SELECT [ModuleID] FROM [dbo].[TabModules] WHERE [TabId] = @TabId1);
OPEN [cTextHtml];
FETCH NEXT FROM [cTextHtml] INTO @ModuleID4, @DesktopHtml4, @DesktopSummary4, @CreatedByUser4, @CreatedDate4;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @DesktopHtml4 = REPLACE(@DesktopHtml4, @Quotes, @SafeQuotes);
SET @DesktopSummary4 = REPLACE(@DesktopSummary4, @Quotes, @SafeQuotes);
SET @Sql = 'INSERT INTO [' + @Database + '].[dbo].[HtmlText](
[ModuleID],[DesktopHtml],[DesktopSummary],[CreatedByUser],[CreatedDate]
)VALUES(
' + CAST(@ModuleIdentity AS NVARCHAR(50)) + ', ''' + @DesktopHtml4 + ''', ''' + @DesktopSummary4 + ''', ' + CAST(@CreatedByUser4 AS NVARCHAR(50)) +
', ''' + CAST(@CreatedDate4 AS NVARCHAR(50)) + ''')';
PRINT @Sql;
EXEC(@Sql);
FETCH NEXT FROM [cTextHtml] INTO @ModuleID4, @DesktopHtml4, @DesktopSummary4, @CreatedByUser4, @CreatedDate4;
END
CLOSE [cTextHtml];
DEALLOCATE [cTextHtml];
FETCH NEXT FROM [cTabs] INTO @TabId1, @TabOrder1, @PortalID1, @TabName1, @IsVisible1, @ParentId1, @Level1,
@IconFile1, @DisableLink1, @Title1, @Description1, @KeyWords1, @IsDeleted1, @Url1, @SkinSrc1,
@ContainerSrc1, @TabPath1, @StartDate1, @EndDate1, @RefreshInterval1, @PageHeadText1;
END
--COMMIT TRANSACTION [t1];
CLOSE [cTabs];
DEALLOCATE [cTabs];
DROP TABLE #Tabs;
DROP TABLE #Modules;