Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Stack Exchange Data Explorer - database import stored procedure.
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.