Copy HUGE SQL SERVER DATABASE tables to a New Empty Database
29-03-2024Before using the script below, an empty database is created and then it scans all the tables in the other database and transfers them with their data. This way you can quickly generate a test database from a very large database.
Note: Don't forget to create the target database from source database script!
USE [TTBISDBTEST] DECLARE @tableName NVARCHAR(128), @schemaName NVARCHAR(128), @sqlCmd NVARCHAR(MAX), @columnList NVARCHAR(MAX), @hasIdentity BIT, @databaseName NVARCHAR(255) = 'TTBISDB', @qualifiedTableName NVARCHAR(255), @destQualifiedTableName NVARCHAR(255); -- Foreign key kısıtlamalarını devre dışı bırak SET @sqlCmd = 'EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"'; EXEC sp_executesql @sqlCmd; DECLARE tableCursor CURSOR FOR SELECT t.name, s.name FROM [TTBISDB].sys.tables AS t JOIN [TTBISDB].sys.schemas AS s ON t.schema_id = s.schema_id WHERE t.type = 'U' and t.name !='sysdiagrams' and s.name!='sm_log'; OPEN tableCursor; FETCH NEXT FROM tableCursor INTO @tableName, @schemaName; WHILE @@FETCH_STATUS = 0 BEGIN PRINT 'Şu anki işlem tablosu: ' + @schemaName + '.' + @tableName; SET @qualifiedTableName = QUOTENAME(@databaseName) + '.' + QUOTENAME(@schemaName) + '.' + QUOTENAME(@tableName); SET @destQualifiedTableName = QUOTENAME(@schemaName) + '.' + QUOTENAME(@tableName); SELECT @columnList = STRING_AGG(QUOTENAME(c.COLUMN_NAME), ', ') FROM [TTBISDB].INFORMATION_SCHEMA.COLUMNS AS c WHERE c.TABLE_NAME = @tableName AND c.TABLE_SCHEMA = @schemaName; SELECT @hasIdentity = CASE WHEN EXISTS ( SELECT * FROM [TTBISDB].sys.columns AS col WHERE col.object_id = OBJECT_ID(@qualifiedTableName, 'U') AND col.is_identity = 1 ) THEN 1 ELSE 0 END; IF @hasIdentity = 1 BEGIN SET @sqlCmd = 'SET IDENTITY_INSERT ' + @destQualifiedTableName + ' ON;' + 'DELETE FROM '+ @destQualifiedTableName+';'+ 'INSERT INTO ' + @destQualifiedTableName + ' (' + @columnList + ') SELECT ' + @columnList + ' FROM ' + @qualifiedTableName + ';' + 'SET IDENTITY_INSERT ' + @destQualifiedTableName + ' OFF;'; EXEC sp_executesql @sqlCmd; END ELSE BEGIN SET @sqlCmd = 'DELETE FROM '+ @destQualifiedTableName+';'+ 'INSERT INTO ' + @destQualifiedTableName + ' (' + @columnList + ') SELECT ' + @columnList + ' FROM ' + @qualifiedTableName + ';'; EXEC sp_executesql @sqlCmd; END FETCH NEXT FROM tableCursor INTO @tableName, @schemaName; END; CLOSE tableCursor; DEALLOCATE tableCursor; -- Foreign key kısıtlamalarını tekrar etkinleştir SET @sqlCmd = 'EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"'; EXEC sp_executesql @sqlCmd;