I am doing something similar. We need to get data from one server to the other with minimal impact to the developers deployment process and minimal bandwidth. What we opted for was a local bulk copy into another database then compress and backup / restore to the other server. Overall it was fastest compared to other methods.

Here is what I have so far. This is the part that runs on the source server. You could modify it to however you see fit (maybe restore as a different name then do a name swap at the database level). I don't have the other half completed yet.

Good luck

DECLARE

-- configurable variables

@sourceDatabase SYSNAME = '' -- source database

,@dataPath VARCHAR(255) = '' -- path to data file

,@logPath VARCHAR(255) = '' -- path to log file

,@tableListFile VARCHAR(255) = '' -- text file containing the list of tables to copy

,@destinationDataSize INT = 500 -- destination database size in MB

,@destinationLogSize INT = 50 -- destination log file size in MB

-- non-configurable variables

,@destinationDatabase SYSNAME

,@TSQL VARCHAR(MAX)

-- table specific variables

,@tableName SYSNAME

,@columnList VARCHAR(MAX)

-- initializations

SELECT

@destinationDatabase = 'Copy_' + @sourceDatabase

,@dataPath = CASE WHEN RIGHT(@dataPath, 1) <> '\' THEN @dataPath + '\' ELSE @dataPath END

,@logPath = CASE WHEN RIGHT(@logPath, 1) <> '\' THEN @logPath + '\' ELSE @logPath END

IF OBJECT_ID('tempdb..##t_TableList') IS NOT NULL

DROP TABLE ##t_TableList

CREATE TABLE ##t_TableList (

TableNameSYSNAME NOT NULL

)

IF OBJECT_ID('tempdb..##t_ColumnList') IS NOT NULL

DROP TABLE ##t_ColumnList

CREATE TABLE ##t_ColumnList (

ColumnListVARCHAR(MAX) NOT NULL

)

SET NOCOUNT ON

BEGIN TRY

-- Cleanup

SELECT @TSQL = '' +

'IF EXISTS ( SELECT name FROM master.sys.databases WHERE name = ''' + @destinationDatabase + ''' ) ' +

'BEGIN ' +

'ALTER DATABASE [' + @destinationDatabase + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE ' +

'USE [master] DROP DATABASE [' + @destinationDatabase + '] ' +

'END'

PRINT(@TSQL)

EXEC(@TSQL)

-- Create Database

SELECT @TSQL = '' +

'CREATE DATABASE [' + @destinationDatabase + '] ' +

'ON PRIMARY ' +

'( NAME = N''' + @destinationDatabase + '_data'', FILENAME = N''' + @dataPath + @destinationDatabase + '_data.mdf'' , SIZE = ' + CONVERT(VARCHAR, @destinationDataSize) + 'MB , FILEGROWTH = 512000KB ) ' +

'LOG ON ' +

'( NAME = N''' + @destinationDatabase + '_log'', FILENAME = N''' + @logPath + @destinationDatabase + '_log.ldf'' , SIZE = ' + CONVERT(VARCHAR, @destinationLogSize) + 'MB , FILEGROWTH = 10%)'

PRINT(@TSQL)

EXEC(@TSQL)

-- Get Table List from File

SELECT @TSQL = '' +

'BULK INSERT ##t_TableList FROM ''' + @tableListFile + ''' with (FirstRow = 1)'

EXEC(@TSQL)

-- DEBUGSELECT * FROM ##t_TableList

-- Process Each Table

WHILE EXISTS ( SELECT 1 FROM ##t_TableList )

BEGIN

SELECT TOP 1 @tableName = tableName, @columnList = '' FROM ##t_TableList

DELETE FROM ##t_TableList WHERE tableName = @tableName

PRINT(@tableName)

-- Fetch Column List

BEGIN

SELECT @TSQL = 'USE [' + @sourceDatabase + ']

DECLARE @columnList VARCHAR(MAX) = ''''

SELECT @columnList = @columnList + CASE WHEN @columnList = '''' THEN ''['' + [name] + '']'' ELSE '',['' + [name] + '']'' END FROM sys.columns c WHERE c.is_computed = 0 AND OBJECT_SCHEMA_NAME([object_id]) = ''dbo'' AND OBJECT_NAME([object_id]) = ''' + @tableName + ''' ORDER BY column_id

SELECT @columnList'

INSERT INTO ##t_ColumnList (ColumnList)

EXEC(@TSQL)

SELECT @columnList = ColumnList FROM ##t_ColumnList

DELETE FROM ##t_ColumnList

--PRINT(@columnList)

END

-- Generate Destination Schema

-- **note** Must create dummy join so identity columns are not propagated to destination database

SELECT @TSQL = 'SELECT TOP 0 ' + @columnList + ' INTO [' + @destinationDatabase + '].[dbo].[' + @tableName + '] FROM [' + @sourceDatabase + '].[dbo].[' + @tableName + '] LEFT JOIN ( SELECT TOP 1 ''Dummy'' AS DummyColumn FROM master.sys.databases ) b ON b.[DummyColumn] = ''Dummy'''

PRINT(@TSQL)

EXEC(@TSQL)

-- Insert Data

SELECT @TSQL = 'INSERT INTO [' + @destinationDatabase + '].[dbo].[' + @tableName + '] WITH (TABLOCK) (' + @columnList + ') SELECT ' + @columnList + ' FROM [' + @sourceDatabase + '].[dbo].[' + @tableName + '] WITH (NOLOCK)'

PRINT(@TSQL)

EXEC(@TSQL)

-- Enable Table Compression

SELECT @TSQL = 'USE [' + @destinationDatabase + '] ALTER TABLE [dbo].[' + @tableName + '] REBUILD WITH (DATA_COMPRESSION=PAGE)'

PRINT(@TSQL)

EXEC(@TSQL)

END

END TRY

BEGIN CATCH

DECLARE @ErrorMessage NVARCHAR(4000);

DECLARE @ErrorSeverity INT;

DECLARE @ErrorState INT;

SELECT

@ErrorMessage = ERROR_MESSAGE(),

@ErrorSeverity = ERROR_SEVERITY(),

@ErrorState = ERROR_STATE();

IF @@TRANCOUNT > 0

ROLLBACK TRAN

ELSE

-- Use RAISERROR inside the CATCH block to return error

-- information about the original error that caused

-- execution to jump to the CATCH block.

RAISERROR (@ErrorMessage, -- Message text.

@ErrorSeverity, -- Severity.

@ErrorState -- State.

);

END CATCH

-- Cleanup

SELECT @TSQL = 'IF EXISTS ( SELECT name FROM master.sys.databases WHERE name = ''' + @destinationDatabase + ''' ) USE [' + @destinationDatabase + '] ALTER DATABASE [' + @destinationDatabase + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE USE [master] DROP DATABASE [' + @destinationDatabase + ']'

PRINT(@TSQL)

EXEC(@TSQL)

IF OBJECT_ID('tempdb..##t_TableList') IS NOT NULL

DROP TABLE ##t_TableList

IF OBJECT_ID('tempdb..##t_ColumnList') IS NOT NULL

DROP TABLE ##t_ColumnList