Copy all data from Database A. to Database B.

  • Hello Everyone...

    Could you please help me with a query that can copy all data (tables, functions, procedures, etc) from Database A. to Database B. in the same instance?

    Thanks in advance,

    Bill

  • Does database B already have data in it? If not, it would be easier to restore A over B.

  • No... Database B. is empty. I don't want to restore a backup of A. to B.

    I want a script to copy all data from A. to B.

    Maybe something like this...

    declare @linkedServer sysname='B';

    declare @SourceDbName sysname='A';

    declare @sql varchar(8000)

    declare @tableName sysname

    declare Cur_tab Cursor

    for

    select Name from sys.tables t where t.type='U';

    open cur_tab

    fetch next FROM CUR_TAB into @tablename;

    DECLARE @listStr VARCHAR(MAX)

    WHILE @@FETCH_STATUS =0

    begin

    SET @listStr=null;

    SELECT @listStr = COALESCE(@listStr+',' ,'') + Name

    FROM sys.columns where object_id=object_id(@tableName);

    SET @sql='SET IDENTITY_INSERT '+ @TABLENAME +' ON;INSERT INTO '+@TABLENAME+'('+@liststr+')SELECT '+@liststr+' FROM ['+

    convert(varchar,@linkedServer )+'].['+ convert(varchar,@SourceDbName )+'].[DBO].['+@tableName+']

    ;SET IDENTITY_INSERT '+ @TABLENAME +' OFF;'

    PRINT @sql

    EXEC(@SQL)

    fetch next FROM CUR_TAB into @tablename;

    end

    CLOSE CUR_TAB

    DEALLOCATE CUR_TAB

  • C@mel (12/27/2015)I don't want to restore a backup of A. to B.

    I want a script to copy all data from A. to B.

    WHY? To what end? What is the reason for doing it the most difficult way possible instead of the simplest?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (12/27/2015)


    C@mel (12/27/2015)I don't want to restore a backup of A. to B.

    I want a script to copy all data from A. to B.

    WHY? To what end? What is the reason for doing it the most difficult way possible instead of the simplest?

    Only reason I can think of for anyone wanting to do this is on the likes of Amazon's RDS where restoring a database from an external backup is not an option.

    😎

    "Quick" solution, in SSMS right click on the database, select Tasks->Generate Scripts... and include the data.

  • Eirikur Eiriksson (12/27/2015)


    Jeff Moden (12/27/2015)


    C@mel (12/27/2015)I don't want to restore a backup of A. to B.

    I want a script to copy all data from A. to B.

    WHY? To what end? What is the reason for doing it the most difficult way possible instead of the simplest?

    Only reason I can think of for anyone wanting to do this is on the likes of Amazon's RDS where restoring a database from an external backup is not an option.

    😎

    "Quick" solution, in SSMS right click on the database, select Tasks->Generate Scripts... and include the data.

    Gosh, no. Don't include the data unless there's very little. It creates one INSERT with a full list of column names and values per row. If you have a lot of data, it will take a month of Sundays to upload and run. Even RedGate datacompare makes the same kind of mess of things.

    I also agree that the SSMS script generator works a treat except for one thing... it doesn't always follow the correct dependency order. There can also be circular references between tables (not a good idea but it happens) that throw a monkey wrench into the script, however it's generated.

    It would be nice if the OP spoke up as to why the need, right about now.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 6 posts - 1 through 5 (of 5 total)

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