Stack Exchange Data Explorer - database import stored procedure.

sp_Refresh_Database.sql

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_Refresh_Database] @DBName nvarchar(100)
AS
BEGIN
	SET NOCOUNT ON;

    Declare @TempDBName varchar(100) = @DBName + '_Temp';
    Declare @ServerName varchar(100),
            @DataPath VARCHAR(200),
            @LogPath VARCHAR(200),
            @ServerRegion VARCHAR(2) = (Select SubString(@@SERVERNAME, 0, 3));
    If (@ServerRegion = 'NY')
    Begin
        Set @ServerName = Case @DBName When 'StackOverflow' Then 'SQL-STACKOVERFLOW_AG' Else 'SQL-SENETWORK_AG' End;
        Set @DataPath = 'C:\Data\';
        Set @LogPath = 'C:\Data\';
    End
    If (@ServerRegion = 'CO')
    Begin
        Set @ServerName = Case @DBName When 'StackOverflow' Then 'CO-SQL01' Else 'CO-SQL03' End;
        Set @DataPath = 'D:\Data\';
        Set @LogPath = 'D:\Data\';
    End

    If Exists(Select * From sys.databases Where name = @TempDBName)
    Begin
        Exec('Alter Database [' + @TempDBName +'] Set SINGLE_USER WITH ROLLBACK IMMEDIATE');
        Exec('Drop Database [' + @TempDBName + ']');
    End

    Declare @DateAppend varchar(100) = Replace(Replace((Select Convert(varchar, GETUTCDATE(), 120)),' ','_'),':','-');
    Declare @BaseFileName varchar(200) = @DBName + '_' + @DateAppend;
    Declare @LogFileName varchar(200) = @DBName + '_log';

    Exec('Create Database [' + @TempDBName + ']
          ON  PRIMARY (NAME = N''' + @DBName + ''', FILENAME = N''' + @DataPath + @BaseFileName + '.mdf'' , SIZE = 4096KB , FILEGROWTH = 102400KB)
          LOG ON (NAME = N''' + @LogFileName + ''', FILENAME = N''' + @LogPath + @BaseFileName + '_log.ldf'' , SIZE = 1024KB , FILEGROWTH = 10%)');

    Declare @TablesToImport Table (TableName varchar(100));
    Insert Into @TablesToImport Values 
    ('Users'),
    ('PostTypes'),
    ('PostsWithDeleted'),
    ('PostHistoryTypes'),
    ('PostHistory'),
    ('VoteTypes'),
    ('Votes'),
    ('Badges'),
    ('Comments'),
    ('TagSynonyms'),
    ('PostFeedback'),
    ('SuggestedEdits'),
    ('SuggestedEditVotes'),
    ('PostLinks'),
    ('FlagTypes'),
    ('PendingFlags'),
    ('CloseReasonTypes'),
    ('CloseAsOffTopicReasonTypes'),
    ('ReviewTasks'),
    ('ReviewTaskTypes'),
    ('ReviewTaskStates'),
    ('ReviewTaskResults'),
    ('ReviewTaskResultTypes'),
    ('ReviewRejectionReasons');

    Declare @TableName varchar(100);
    Declare Tables_Cursor Cursor For Select TableName From @TablesToImport;
    Open Tables_Cursor;
    Fetch Next From Tables_Cursor Into @TableName;

    Declare @timer datetime;

    While @@Fetch_Status = 0
    Begin
        Print 'Importing ' + @TableName;
        Set @timer = GETUTCDATE();

        Exec('Select * Into [' + @TempDBName + '].[dbo].[' + @TableName + '] From [' + @ServerName + '].[' + @DBName + '].[dbo].[vExport' + @TableName + '] with (nolock)');
        Exec('Create Unique Clustered Index UIX_' + @TableName + '_Id On [' + @TempDBName + '].[dbo].[' + @TableName + '](Id)');
        
        Print 'Import of ' + @TableName + ' complete: ' + convert(varchar(20),DateDiff(ms, @timer, GETUTCDATE())) + ' ms';

        Fetch Next From Tables_Cursor Into @TableName;
    End

    Deallocate Tables_Cursor;

    --Special Case Tags
    Set @TableName = 'Tags';
    Print 'Importing ' + @TableName;
    Set @timer = GETUTCDATE();
    Exec('Select Id, Name as TagName, Count, ExcerptPostId, WikiPostId Into [' + @TempDBName + '].[dbo].[' + @TableName + '] From [' + @ServerName + '].[' + @DBName + '].[dbo].[' + @TableName + '] with (nolock)');
    Exec('Create Unique Clustered Index IX_' + @TableName + '_Id On [' + @TempDBName + '].[dbo].[' + @TableName + '](Id)');
    Print 'Import of ' + @TableName + ' complete: ' + convert(varchar(20),DateDiff(ms, @timer, GETUTCDATE())) + ' ms';

    --Special Case PostTags
    Set @TableName = 'PostTags';
    Print 'Importing ' + @TableName;
    Set @timer = GETUTCDATE();
    Exec('Select * Into [' + @TempDBName + '].[dbo].[' + @TableName + '] From [' + @ServerName + '].[' + @DBName + '].[dbo].[' + @TableName + '] with (nolock)');
    Exec('Create Unique Clustered Index IX_' + @TableName + '_PostId_TagId On [' + @TempDBName + '].[dbo].[' + @TableName + '](PostId,TagId)');
    Print 'Import of ' + @TableName + ' complete: ' + convert(varchar(20),DateDiff(ms, @timer, GETUTCDATE())) + ' ms';

    --Index Creation
    Print 'Creating Indexes';
    Set @timer = GETUTCDATE();
    Exec('Create Unique Index IX_PostTags_TagId_PostId On [' + @TempDBName + '].[dbo].[PostTags](TagId,PostId)');
    Exec('Create Unique Index IX_Tags_TagName On [' + @TempDBName + '].[dbo].[Tags](TagName)');
    Exec('Create Index IX_Users_EmailHash On [' + @TempDBName + '].[dbo].[Users](EmailHash)');
    Exec('Create Index IX_PostsWithDeleted_OwnerUserId_CommunityOwnedDate_Includes On [' + @TempDBName + '].[dbo].[PostsWithDeleted](OwnerUserId,CommunityOwnedDate) Include (Id, ParentId)');
    Exec('Create Index IX_PostsWithDeleted_ParentId On [' + @TempDBName + '].[dbo].[PostsWithDeleted](ParentId)');
    Exec('Create Index IX_PostsWithDeleted_DeletionDate On [' + @TempDBName + '].[dbo].[PostsWithDeleted](DeletionDate)');
    Exec('Create NonClustered Index IX_PostHistory_PostId_PostHistoryTypeId_UserId_Includes On [' + @TempDBName + '].[dbo].[PostHistory](PostId, PostHistoryTypeId, UserId) Include (CreationDate)');
    Print 'Indexes Created: ' + convert(varchar(20),DateDiff(ms, @timer, GETUTCDATE())) + ' ms';
    Declare @dbSql nvarchar(100) = '[' + @TempDBName + '].[dbo].sp_executesql';
    Declare @viewSql nvarchar(200) = 'Create View [Posts] As Select * From [PostsWithDeleted] Where DeletionDate Is Null';
    Exec @dbSql @viewSql;
    Print 'Views Created: ' + convert(varchar(20),DateDiff(ms, @timer, GETUTCDATE())) + ' ms';

    Exec('Use [' + @TempDBName + ']; DBCC SHRINKFILE (''' + @LogFileName + ''', 64);');

    If Exists(Select * From sys.databases Where name = @DBName)
    Begin
        Exec('Alter Database [' + @DBName +'] Set SINGLE_USER WITH ROLLBACK IMMEDIATE');
        Exec('Drop Database [' + @DBName + ']');
    End

    Exec('Use [' + @TempDBName + ']; 
Create User [STACKEXCHANGE\svc_sede] For Login [STACKEXCHANGE\svc_sede];
Alter Role [db_datareader] Add Member [STACKEXCHANGE\svc_sede];
Grant ShowPlan To [STACKEXCHANGE\svc_sede];');

    Exec('Alter Database [' + @TempDBName + '] Modify Name = [' + @DBName + ']');
END