Script to move all database data

  • Hey All,

    At the company I work for, each employee has their own copy of our database. This is so they can insert / delete data and not affect other users.

    When they want to recreate their database to the original state, we need to go through a process to drop all of the objects in their local database, recreate all these objects by copying what is in our primary database, and then copy all of the data from the primary database into their own.

    The first two steps I have done already, but Im running into some trouble with how to define a generic script that moves all of the data from one database to another. Keeping in mind that these database have FK contrainsts and unique columns.

    This is the script I have so far:

    DECLARE

    @sql VARCHAR(500),

    @tableName VARCHAR(128),

    @foreignKeyName VARCHAR(128),

    @schema varchar(128)

    -- A list of all foreign keys and table names

    DECLARE foreignKeyCursor CURSOR

    FOR SELECT

    tbl.name AS tbl_name,

    sch.name AS sch_name

    FROM

    sys.tables tbl

    INNER JOIN sys.schemas sch

    ON tbl.schema_id = sch.schema_id

    OPEN foreignKeyCursor

    FETCH NEXT FROM foreignKeyCursor

    INTO @tableName, @schema

    WHILE ( @@FETCH_STATUS = 0 )

    BEGIN

    SET @sql = 'ALTER TABLE ['

    + @schema + '].['

    + @tableName + '] NOCHECK CONSTRAINT ALL'

    EXECUTE(@sql)

    FETCH NEXT FROM foreignKeyCursor

    INTO @tableName, @schema

    END

    CLOSE foreignKeyCursor

    DEALLOCATE foreignKeyCursor

    -- Move all of the data from project1 into the local database

    DECLARE@testIdnumeric(18,0)

    DECLARE foreignKeyCursor CURSOR

    FOR SELECT

    tbl.name AS tbl_name,

    sch.name AS sch_name

    FROM

    sys.tables tbl

    INNER JOIN sys.schemas sch

    ON tbl.schema_id = sch.schema_id

    OPEN foreignKeyCursor

    FETCH NEXT FROM foreignKeyCursor

    INTO @tableName, @schema

    WHILE ( @@FETCH_STATUS = 0 )

    BEGIN

    if ( SELECT IDENT_CURRENT('[' + @schema + '].[' + @tableName + ']')) is not NULL

    begin

    SET @sql = 'set IDENTITY_INSERT [' + @schema + '].[' + @tableName + '] ON;'

    + 'delete from [' + @schema + '].[' + @tableName + ']' + '; '

    + 'insert into [' + @schema + '].[' + @tableName + '] ('

    + 'select * from [project1].[' + @schema + '].[' + @tableName + ']; '

    + 'set IDENTITY_INSERT [' + @schema + '].[' + @tableName + '] off;'

    end

    else

    begin

    SET @sql = 'delete from [' + @schema + '].[' + @tableName + ']' + '; '

    + 'insert into [' + @schema + '].[' + @tableName + '] '

    + 'select * from [project1].[' + @schema + '].[' + @tableName + ']; '

    end

    print @sql

    EXECUTE(@sql)

    FETCH NEXT FROM foreignKeyCursor

    INTO @tableName, @schema

    END

    CLOSE foreignKeyCursor

    DEALLOCATE foreignKeyCursor

    -- Finally turn the constraints back on

    DECLARE foreignKeyCursor CURSOR

    FOR SELECT

    tbl.name AS tbl_name,

    sch.name AS sch_name

    FROM

    sys.tables tbl

    INNER JOIN sys.schemas sch

    ON tbl.schema_id = sch.schema_id

    OPEN foreignKeyCursor

    FETCH NEXT FROM foreignKeyCursor

    INTO @tableName, @schema

    WHILE ( @@FETCH_STATUS = 0 )

    BEGIN

    SET @sql = 'ALTER TABLE ['

    + @schema + '].['

    + @tableName + '] CHECK CONSTRAINT ALL'

    EXECUTE(@sql)

    FETCH NEXT FROM foreignKeyCursor

    INTO @tableName, @schema

    END

    CLOSE foreignKeyCursor

    DEALLOCATE foreignKeyCursor

    This is doing 3 things, disable all of the constraints. Move all of the data. Enable all of the constraints. The issue I'm running into now is the portion of the code which turns IDENTITY INSERT ON doesn't work because it doesn't specify the columns.

    So, does anyone have any advice on how to do this? Is there an easier way I'm not thinking of? It seems as though something as simple as, I have two identical database and want to copy the data from one to the other, should be easy through a script.

    Thanks,

    -Dave

  • Try seaching for a script with the sp_MSforEachTable. It is an undocumented stored proc that will loop through all the tables in the database. If you can't find anything, I might have some examples but it will take me a while to find them.

  • I'd really recommend you look at one of the compare tools out there. I work for Red Gate, but ApexSQL and other companies have similar tools.

    It's not that this is a hard problem, but it has a lot of moving parts to it. Places you can make a mistake, and places where you will need to test/validate it. your time should be worth more than a few hundred dollars to do this, especially if you have to iterate through versions of this.

    The compare tools are well tested and work great. I don't think a lot of third party tools necessarily fit many situations, but compare tools do for sure.

  • I've thought about that same thing, and we're currently use ApexSQL DataDiff for doing just that.

    The issue with this is that now it's a manual process that a DBA needs to run. In the end, I'd like to have a script I provide to the other employees they can use to recreate their local databases without requiring a DBA.

    It'd be fantastic if one of these tools had a feature which let me accomplish what I'm looking to do, but I haven't seen anything thus far.

    -Dave

  • Why no just use BACKUP and RESTORE to make a copy of the database?

    It will create an exact copy of all database objects and files.

  • BACKUP and RESTORE would be would perfectly, something along the lines:

    use master

    backup database project1 to DISK='C:\DatabaseBackup.bak'

    restore database project1_dcarrigg from DISK='C:\DatabaseBackup.bak'

    The problem (as you might have already noticed) is that each local local database is running on the same database server, and is named differently. This causes the RESTORE to print the error: "The backup set holds a backup of a database other than the existing 'project1_dcarrigg' database."

    Is it possible that there's a way to restore from a backup like this, where the database isn't named the same as what is in the backup?

    -Dave

  • ApexDiff should allow you do generate a script you can give to people. SQLCompare does.

    The restore should allow you to restore to a different name and path with the MOVE option.

  • You could create an SSIS package that copies database objects then create a job that runs the package. The users can either run the job or you could create an executable that they can double click.

  • Fantastic. I think using the BACKUP and RESTORE method is going to work, otherwise I'll look into creating an SSIS package. You guys have been a big help.

    Thanks,

    -Dave

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply