Sql-server – How to copy a database using scripts only, no bak, no mdf, no ldf

schema-copysql serversql-server-2008-r2

Too often I face a terrible situation: I have a little time to take the backup of a customer db and download it for studying problems on it.

The problem is that the database contains many BLOBs (as sql server filestream).

Typically tabular data ("mdf") is 5% of the total data.

To study most issues I do not need BLOBs.

In a world with infinite bandwidth I could simply take a backup and download it, but this conflicts with "a little time" in the real world.

To get rid of BLOBs I also do:

create a copy of the DB at the customer server
set all blobs to NULL
run CHECKPOINT to force filestream data clearing from disk
take backup after some time (proportional to BLOBs total size)
download a small backup

BUT point (4) takes time.

So what I would like to achieve is:

a) create an empty database with a filestream filegroup on the customer's server

b) with a script, create the schema (at least tables, views, sp, sf) on the copied DB

c) with a script copy all the data but not the BLOBs

d) take backup and download

With my knowledge I can somehow manage (b) using the Tasks/Generate Scripts feature of Management Studio.

Then manually I should write queries like this to fill the tables:

INSERT INTO CopiedDB.dbo.Table1
SELECT * from MainDB.dbo.Table1

It is a very long procedure to be done manually even because the insert order must be sorted out manually (because of FK relationships).

Is there a way to create this script in a smart way avoiding the manual way?

Best Answer

Disable all your foreign keys before you start the transfer and then reenable them afterwards.

You might also want to think about your indexes, it's usually much quicker to disable the indexes before copying data and then rebuild them afterwards.

https://sqlundercover.com/2017/09/25/copying-data-from-one-table-to-another-to-disable-indexes-or-not-to-disable-indexes-thats-the-question/

Have a look at the below scriopt this is part of a much larger script so you might want to check that it does what you want but it should be ok. Just assign the relevant database names to the @SourceDatabase and @DestinationDatabase variables.

It'll deal with your indexes and constraints, disabling them before and reenabling them after the transfer. As I said, I've pulled this out of the much bigger process and tweaked it slightly so make sure you test it out.

SET NOCOUNT ON

DECLARE @SourceDatabase SYSNAME 
DECLARE @DestinationDatabase SYSNAME

SET @SourceDatabase = 'SourceDB'
SET @DestinationDatabase = 'DestinationDB'

IF OBJECT_ID('tempdb..#DisabledIndexes') IS NOT NULL
    DROP TABLE #DisabledIndexes

IF OBJECT_ID('tempdb..#DisabledFKs') IS NOT NULL
    DROP TABLE #DisabledFKs

IF OBJECT_ID('tempdb..#DisabledTriggers') IS NOT NULL
    DROP TABLE #DisabledTriggers

--Check for any objects that are currently disabled
RAISERROR ('Check For Currently Disabled Indexes', 0, 1) WITH NOWAIT

SELECT SCHEMA_NAME(tables.schema_id) + '.' + OBJECT_NAME(indexes.object_id) AS TableName, indexes.name AS IndexName
INTO #DisabledIndexes
FROM sys.indexes
JOIN sys.tables ON indexes.object_id = tables.object_id
WHERE is_disabled = 1

RAISERROR ('Check For Currently Disabled FKs', 0, 1) WITH NOWAIT
SELECT SCHEMA_NAME(schema_id) + '.' + OBJECT_NAME(parent_object_id) AS TableName, name AS FKName
INTO #DisabledFKs
FROM sys.foreign_keys
WHERE is_disabled = 1

RAISERROR ('Check For Currently Disabled Triggers', 0, 1) WITH NOWAIT
SELECT SCHEMA_NAME(tables.schema_id) + '.' + OBJECT_NAME(tables.object_id) AS TableName, triggers.name AS TriggerName
INTO #DisabledTriggers
FROM sys.triggers
JOIN sys.tables ON triggers.parent_id = tables.object_id
WHERE is_disabled = 1

--Switch Database To BulkLogged
RAISERROR ('Switch Database To BulkLogged', 0, 1) WITH NOWAIT

EXEC ('ALTER DATABASE [' + @DestinationDatabase + '] SET RECOVERY BULK_LOGGED')

--Disable all nonclustered indexes
RAISERROR ('Disable all nonclustered indexes', 0, 1) WITH NOWAIT

DECLARE DisableIndexes CURSOR STATIC FORWARD_ONLY
FOR
    SELECT 'ALTER INDEX ' + indexes.name + ' ON ' + schemas.name + '.' + tables.name + ' DISABLE' AS DisableIndexes
    FROM sys.tables
    JOIN sys.schemas ON tables.schema_id = schemas.schema_id
    JOIN sys.indexes ON indexes.object_id = tables.object_id
    WHERE tables.type = 'U'
    AND tables.name NOT LIKE 'sys%'
    AND tables.name NOT LIKE 'mspub%'
    AND tables.name NOT LIKE 'mspeer%'
    AND indexes.type_desc = 'NONCLUSTERED'

DECLARE @DisabledIndexCmd VARCHAR(MAX)

OPEN DisableIndexes

FETCH NEXT FROM DisableIndexes 
INTO @DisabledIndexCmd

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC(@DisabledIndexCmd)

    FETCH NEXT FROM DisableIndexes 
    INTO @DisabledIndexCmd
END

CLOSE DisableIndexes
DEALLOCATE DisableIndexes

--Disable Constraints and Triggers
RAISERROR ('Disable Constraints and Triggers', 0, 1) WITH NOWAIT

DECLARE DisableConstraints CURSOR STATIC FORWARD_ONLY
FOR
    SELECT 'ALTER TABLE ' + schemas.name + '.' + tables.name + ' DISABLE TRIGGER all' AS DisableTriggers,
    'ALTER TABLE ' + schemas.name + '.' + tables.name + ' NOCHECK CONSTRAINT all' AS DisableConstraints
    FROM sys.tables
    JOIN sys.schemas ON tables.schema_id = schemas.schema_id
    WHERE tables.type = 'U'
    AND tables.name NOT LIKE 'sys%'
    AND tables.name NOT LIKE 'mspub%'
    AND tables.name NOT LIKE 'mspeer%'


DECLARE @DisableConstraintsCmd VARCHAR(MAX)
DECLARE @DisableTriggersCmd VARCHAR(MAX)

OPEN DisableConstraints

FETCH NEXT FROM DisableConstraints 
INTO @DisableTriggersCmd, @DisableConstraintsCmd

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC(@DisableTriggersCmd)
    EXEC(@DisableConstraintsCmd)

    FETCH NEXT FROM DisableConstraints 
    INTO @DisableTriggersCmd, @DisableConstraintsCmd
END

CLOSE DisableConstraints
DEALLOCATE DisableConstraints

--begin data transfer
RAISERROR ('begin data transfer', 0, 1) WITH NOWAIT

DECLARE InsertCmds CURSOR STATIC FORWARD_ONLY
FOR
    WITH ColumnList (TableID, SchemaName, ColumnList)
    AS
    (SELECT tables.object_id, SCHEMA_NAME(tables.schema_id), 
                        STUFF((SELECT ',' + QUOTENAME(all_columns.name)
                        FROM sys.all_columns
                        WHERE tables.object_id = all_columns.object_id
                        AND system_type_ID NOT IN (34,35)
                        FOR XML PATH('')),1,1,'') AS txt
    FROM sys.tables)

    SELECT CASE WHEN MAX(CAST(is_identity AS INT)) = 1 
        THEN 'SET IDENTITY_INSERT ['+ @DestinationDatabase + '].[' + SchemaName + '].[' + OBJECT_NAME(TableID) + '] ON; INSERT INTO [' + @DestinationDatabase + '].[' + SchemaName + '].[' + OBJECT_NAME(TableID) + '](' + ColumnList + ') SELECT ' + ColumnList + ' FROM [' + @SourceDatabase + '].[' + SchemaName + '].[' + OBJECT_NAME(TableID) + ']; SET IDENTITY_INSERT ['+ @DestinationDatabase + '].[' + SchemaName + '].[' + OBJECT_NAME(TableID) + '] OFF;'
        ELSE 'INSERT INTO [' + @DestinationDatabase + '].[' + SchemaName + '].[' + OBJECT_NAME(TableID) + '] WITH (TABLOCK) (' + ColumnList + ') SELECT ' + ColumnList + ' FROM [' + @SourceDatabase + '].[' + SchemaName + '].[' + OBJECT_NAME(TableID) + ']'
        END
    FROM ColumnList
    JOIN sys.all_columns ON ColumnList.TableID = all_columns.object_id
    GROUP BY SchemaName,TableID,ColumnList

DECLARE @InsertCmd VARCHAR(MAX)

OPEN InsertCmds

FETCH NEXT FROM InsertCmds 
INTO @InsertCmd

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC(@InsertCmd)

    FETCH NEXT FROM InsertCmds 
    INTO @InsertCmd
END

CLOSE InsertCmds
DEALLOCATE InsertCmds


--Rebuild all nonclustered indexes
RAISERROR ('Rebuild all nonclustered indexes', 0, 1) WITH NOWAIT

DECLARE RebuildIndexes CURSOR STATIC FORWARD_ONLY
FOR
    SELECT 'ALTER INDEX ' + indexes.name + ' ON ' + schemas.name + '.' + tables.name + ' REBUILD' AS DisableIndexes
    FROM sys.tables
    JOIN sys.schemas ON tables.schema_id = schemas.schema_id
    JOIN sys.indexes ON indexes.object_id = tables.object_id
    WHERE tables.type = 'U'
    AND tables.name NOT LIKE 'sys%'
    AND tables.name NOT LIKE 'mspub%'
    AND tables.name NOT LIKE 'mspeer%'
    AND indexes.type_desc = 'NONCLUSTERED'

DECLARE @RebuildIndexCmd VARCHAR(MAX)

OPEN RebuildIndexes

FETCH NEXT FROM RebuildIndexes 
INTO @RebuildIndexCmd

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC(@RebuildIndexCmd)

    FETCH NEXT FROM RebuildIndexes 
    INTO @RebuildIndexCmd
END

CLOSE RebuildIndexes
DEALLOCATE RebuildIndexes


--Enable Constraints and Triggers
RAISERROR ('Enable Constraints and Triggers', 0, 1) WITH NOWAIT

DECLARE EnableConstraints CURSOR STATIC FORWARD_ONLY
FOR
    SELECT 'ALTER TABLE ' + schemas.name + '.' + tables.name + ' ENABLE TRIGGER all' AS DisableTriggers,
    'ALTER TABLE ' + schemas.name + '.' + tables.name + ' CHECK CONSTRAINT all' AS DisableConstraints
    FROM sys.tables
    JOIN sys.schemas ON tables.schema_id = schemas.schema_id
    WHERE tables.type = 'U'
    AND tables.name NOT LIKE 'sys%'
    AND tables.name NOT LIKE 'mspub%'
    AND tables.name NOT LIKE 'mspeer%'


DECLARE @EnableConstraintsCmd VARCHAR(MAX)
DECLARE @EnableTriggersCmd VARCHAR(MAX)

OPEN EnableConstraints

FETCH NEXT FROM EnableConstraints 
INTO @EnableTriggersCmd, @EnableConstraintsCmd

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC(@EnableTriggersCmd)
    EXEC(@EnableConstraintsCmd)

    FETCH NEXT FROM EnableConstraints 
    INTO @EnableTriggersCmd, @EnableConstraintsCmd
END

CLOSE EnableConstraints
DEALLOCATE EnableConstraints


--Switch Database To Full recovery model
RAISERROR ('Switch Database To Full recovery model', 0, 1) WITH NOWAIT

EXEC ('ALTER DATABASE [' + @DestinationDatabase + '] SET RECOVERY FULL')


--Disable any objects that were previously disabled
RAISERROR ('Disable any objects that were previously disabled', 0, 1) WITH NOWAIT

DECLARE DisableIndexes CURSOR STATIC FORWARD_ONLY
FOR
    SELECT 'ALTER INDEX ' + IndexName + ' ON ' + TableName + ' DISABLE' AS DisableIndexes
    FROM #DisabledIndexes

OPEN DisableIndexes

FETCH NEXT FROM DisableIndexes 
INTO @DisabledIndexCmd

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC(@DisabledIndexCmd)

    FETCH NEXT FROM DisableIndexes 
    INTO @DisabledIndexCmd
END

CLOSE DisableIndexes
DEALLOCATE DisableIndexes


--Disable Constraints and Triggers

DECLARE DisableConstraints CURSOR STATIC FORWARD_ONLY
FOR
    SELECT 'ALTER TABLE ' + TableName + ' NOCHECK CONSTRAINT ' + FKName AS DisableIndexes
    FROM #DisabledFKs
OPEN DisableConstraints

FETCH NEXT FROM DisableConstraints 
INTO @DisableConstraintsCmd

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC(@DisableConstraintsCmd)

    FETCH NEXT FROM DisableConstraints 
    INTO @DisableConstraintsCmd
END

CLOSE DisableConstraints
DEALLOCATE DisableConstraints


--Disable Triggers

DECLARE DisableConstraints CURSOR STATIC FORWARD_ONLY
FOR
    SELECT 'ALTER TABLE ' + TableName + ' DISABLE TRIGGER ' + TriggerName AS DisableIndexes
    FROM #DisabledTriggers
OPEN DisableConstraints

FETCH NEXT FROM DisableConstraints 
INTO @DisableConstraintsCmd

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC(@DisableConstraintsCmd)

    FETCH NEXT FROM DisableConstraints 
    INTO @DisableConstraintsCmd
END

CLOSE DisableConstraints
DEALLOCATE DisableConstraints

--Reseed Identity Columns
RAISERROR ('Reseeding Identity Columns', 0, 1) WITH NOWAIT

--drop table #ReseedCommands

DECLARE @Reseed VARCHAR(MAX) =
'SELECT ''DBCC CHECKIDENT ('''''' + schemas.name + ''.'' +  tables.name + '''''', RESEED, '' + CAST(IDENT_CURRENT(schemas.name + ''.'' + tables.name) AS VARCHAR) + '')''
FROM ' + @SourceDatabase + '.sys.all_columns
JOIN ' + @SourceDatabase + '.sys.tables ON all_columns.object_id = tables.object_id
JOIN ' + @SourceDatabase + '.sys.schemas ON tables.schema_id = schemas.schema_id
WHERE is_identity = 1'

DECLARE @ReseedCmd VARCHAR(8000)

CREATE TABLE #ReseedCommands
(command VARCHAR(8000))

SELECT @Reseed

INSERT INTO #ReseedCommands 
EXEC (@Reseed)

DECLARE ReseedCursor CURSOR STATIC FORWARD_ONLY
FOR SELECT command FROM #ReseedCommands

OPEN ReseedCursor

FETCH NEXT FROM ReseedCursor
INTO @ReseedCmd

WHILE @@FETCH_STATUS = 0
BEGIN 
    EXEC (@ReseedCmd)

    FETCH NEXT FROM ReseedCursor
    INTO @ReseedCmd
END

CLOSE ReseedCursor
DEALLOCATE ReseedCursor

Best Answer

Related Solutions

Sql-server – Copy encrypted database views

SQL Server – How to Copy Database and Clear Data Older Than N Days

Related Question