Export/Import entire database using bcp

Question

Export/Import entire database using bcp

nash.sameer

Old Hand

Points: 320
More actions
September 14, 2010 at 12:10 am

#226815

Hello all,
I'm trying to find a way(utility/script) that would help me in export/import of entire database to/from MS SQL server 2005. We do have bcp utility for the same, however it exports individual tables instead of "entire database".Is there any script available that would help me doing these operations on "entire database" ?
Any help in this regard would be highly appreciated!
Thanks!

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

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

sqldba4u Hall of Fame Points: 3414 More actions · Answer 1

Is there any specific reason you want imp/exp an entire database? You can rahter use various option like backup/restore, attach/detach, copy db wizard etc. which are not complex and saves time too.

Lowell SSC Guru Points: 323518 More actions · Answer 2

i agree with Vivek; if the goal is to copy everything, there are easier ways to do it;

if the goal is to increase your experience with bcp, just as a proof of concept, then that's ok;

remember bcp will import/export your data, but you'd need to script out the table definitions, as well as any stored procs, views and functions you have..

let us know what your real goal is so we can offer suggestions; also how big/how much data are we talking about?

are you planning on bcp-ing just differences on a schedule or something?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

nash.sameer Old Hand Points: 320 More actions · Answer 3

Firstly, thank you for your prompt reply! Really appreciate it!

The thing is, I have written a utility in PERL to import/export database for ORACLE.

It uses the exp/imp executable of ORACLE to do the respective operations.Now with this

utility in place(for Oracle), I'm looking forward to make it generic enough to handle MS SQL database as well.So effectively, this utility will take care of import/export of databases for ORACLE as well as MS SQL. I'm aware of the fact that Microsoft has made these operations user friendly by providing wizards for imp/exp operation, however I'm looking for some exe like 'bcp' which I can invoke through my perlscript.If you have any better alternative approach to do the same please do suggest, I will be glad to know the same!

Lowell SSC Guru Points: 323518 More actions · Answer 4

nash this should help you get started then;

this example is finding every tables which have a column named "createdate" and also "changedate"

it then bcp's out the data from each of those tables to a file based on tablename+timestamp, like tbl1_20080819.txt

it exports only items that have changes based on those datetime fields.

change your criteria to your own....a list of specific tables, or everything like you said.

read it over, and post any questions back; let me know if this gets you started or not.

/*

--sample tables

--three tables, only two have both columns that are required:

create table tbl1(tbl1id int identity(1,1) primary key,tbltxt varchar(15),createdate datetime,changedate datetime)

create table tbl2(tbl1id int identity(1,1) primary key,tbltxt varchar(15),tbltxt2 varchar(15),

tbltxt3 varchar(15),createdate datetime,changedate datetime)

create table tbl3(tbl1id int identity(1,1) primary key,tbltxt varchar(15),createdate datetime)

--insert sample data

insert into tbl1(tbltxt,createdate,changedate) select 'blah',getdate() -1,getdate()

UNION select 'blah2',getdate() -1,getdate()

insert into tbl2(tbltxt,tbltxt2,tbltxt3,createdate,changedate) select 'blah3','m','w',getdate() -1,getdate()

UNION select 'blah4','m','w',getdate() -1,getdate()

insert into tbl3(tbltxt,createdate) select 'blah',getdate() -1 UNION select 'blah2',getdate() -1

*/

--my dates will be mdy in this example!

SET DATEFORMAT mdy

DECLARE

@table VARCHAR(128),

@filename VARCHAR(128),

@begindate DATETIME,

@enddate DATETIME,

@sql VARCHAR(500)

--dDateAdd(interval, number, date)

SET @begindate = DATEADD(d,-1 ,CONVERT(VARCHAR,GETDATE(),101)) --Aug 19 2008 12:00AM for example

SET @enddate = DATEADD(d, 0 ,CONVERT(VARCHAR,GETDATE(),101)) --Aug 20 2008 12:00AM for example

--only tables that have BOTH createdate and changedate

DECLARE c1 CURSOR FOR

SELECT

DISTINCT sysobjects.name

FROM sysobjects

INNER JOIN syscolumns ON sysobjects.id=syscolumns.id

INNER JOIN syscolumns dbl ON sysobjects.id=dbl.id

WHERE sysobjects.xtype = 'U'

AND syscolumns.name IN('createdate')

AND dbl.name IN('changedate')

--loop thru all the tables

OPEN c1

FETCH next FROM c1 INTO @table

WHILE @@fetch_status <> -1

BEGIN

--we need a dynamic name as well, i'm doing tablename+timestamp, like tbl1_20080819.txt

-- C:\tbl1_20080819.txt

SET @filename = 'C:\' + @table + '_' + CONVERT(VARCHAR,@begindate,112) + '.txt'

SET @sql = 'SELECT * FROM ' + @table + ' WHERE createdate between ''' + CONVERT(VARCHAR,@begindate,101)

+ ''' and ''' + CONVERT(VARCHAR,@enddate,101)

+ ''' or changedate between ''' + CONVERT(VARCHAR,@begindate,101)

+ ''' and ''' + CONVERT(VARCHAR,@enddate,101) + ''''

--put it all together

SET @sql = 'bcp "' + @sql + '" queryout "' + @filename + '" -T -c -Usa -PNotARealPassword -SD223\SQLEXPRESS'

--print it!

PRINT @sql

--run it?

EXEC xp_CmdShell @sql, NO_OUTPUT

FETCH next FROM c1 INTO @table

END

CLOSE c1

DEALLOCATE c1

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!