Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2004
    Location
    michigan
    Posts
    17

    Unanswered: MS SQL 2000 full export (not backup)

    Hi Everybody,

    I am kind of new to MS SQL server databases. I like to take a full export at database level. When I use DTS wizard, it did allow me to take one table at a given time. I have 1000's of table in my database. Manually doing so is not possible. Should i call the 'bcp' command line utility 1000 times to collect the table data to 1000 different flatfiles or is there any provision to take export of all the 1000 tables in one single command/tool.

    Many thanks.

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    You should be able to use DTS to export the entire database at one time. Just right click on the database (in EM), click on all tasks and select export data.

    Specify your source server/database, your target server/database and then select either copy tables and views or copy objects. Copy objects is more useful when you are trying to recreate the database structure. Copy data just moves over any tables.

    Regards,

    hmscott

  3. #3
    Join Date
    Feb 2004
    Location
    michigan
    Posts
    17
    Thanks for your reply scott. my export is just a part of the work, because, i have to drop the entire instance and recreate it including 'master' database due to collation name change. So, i cannot export it to another database under this instance or to a different instance (due to space restriction). Hence my initial interest is to go with a flat (text) file. Is it still possible ?

    Thanks

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Or....

    Code:
    If exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[isp_bcp_out_database]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    drop procedure [dbo].[isp_bcp_out_database]
    GO
    
    CREATE PROC isp_bcp_out_database
    	  @dbName sysname
    	, @fp varchar(255)
    	, @User varchar(255)
    	, @Pwd varchar(255)
    AS
    /* 
    	EXEC isp_bcp_out_database
    		  'Northwind'
    		, 'd:\Data\Northwind\'
    		, 'sa'
    		, ''
    
    */
    
    SET NOCOUNT ON
    
    DECLARE bcpout CURSOR FOR
    	SELECT  -- 'EXEC Master..xp_cmdshell ' +
    --		  '"D:\MSSQL7\Binn\bcp.exe ' + db_Name() + '.[' + TABLE_SCHEMA + '].[' + TABLE_NAME+'] '
    		  'bcp ' + db_Name() + '.[' + TABLE_SCHEMA + '].[' + TABLE_NAME+'] '
    		+ 'out ' + @fp + '\DATA\'+TABLE_SCHEMA +'_'+ REPLACE(TABLE_NAME,' ','_') + '.dat  '
    		+ '-S'+@@SERVERNAME+' -U'+@User+' -P'+@Pwd+' '
    		+ '-f'+@fp+'FORMAT\'+TABLE_SCHEMA +'_'+REPLACE(TABLE_NAME,' ','_')+'.fmt ' 
    		+ ' > ' + @fp + 'DATA\'+TABLE_SCHEMA +'_'+ REPLACE(TABLE_NAME,' ','_') + '.log'
    		-- + ', no_output' AS CMD
    	  FROM    INFORMATION_SCHEMA.Tables
    	 WHERE    TABLE_TYPE = 'BASE TABLE'
          ORDER BY TABLE_SCHEMA, TABLE_NAME
    
    DECLARE @CMD varchar(8000)
    
    --create table a (id int identity(1,1), Add_Dt datetime DEFAULT GetDate(), s varchar(1000))
    -- DROP TABLE a
    OPEN bcpout
    
    FETCH NEXT FROM bcpout INTO @CMD
    
    WHILE @@FETCH_STATUS = 0
    	BEGIN
    		SELECT @CMD
    		SELECT @CMD = 'ECHO ' + @CMD + ' > ' + @fp + '\bcpout.bat'
    		EXEC master..xp_cmdshell @CMD 
    		SELECT @CMD = @fp + '\bcpout.bat'
    		SELECT @CMD
    		insert a (s)
    		exec master..xp_cmdshell @cmd
    
    
    		FETCH NEXT FROM bcpout INTO @CMD
    	END
    
    CLOSE bcpout
    DEALLOCATE bcpout
    
    	select id, ouputtmp = s from a
    
    SET NOCOUNT OFF
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Dec 2002
    Posts
    1,245
    Yurk. That's the second post I've seen today from someone needing to change the collation on their server.

    I think for this you will sincerely want a second database server available as the target for your export. A developer license is cheap ($50?). Put it on a handy box and then away you go.

    Short of that, you might be able to work out some combination of backup, re-install, restore (to different DB name), script off db, change collation specified in script, run script to create new DB and then use DTS to copy the data from the restored DB to the new DB.

    Am I making sense? It IS getting close to 5:00 here. At 5:00, the value of my responses declines by at least 75%.

    Regards,

    hmscott

  6. #6
    Join Date
    Dec 2002
    Posts
    1,245
    Or just do what Brett (BCP King) Kaiser suggests.

    Brett, is it any coincidence that your initials BK might stand for 'BCP King'?

    regards,

    hmscott

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Got a chuckle out of that one...

    You can point that code at ANY database....

    I think you'll need a DELETE in there for table a...I originally had it as a temp table (still want to delete a temp, so that's no excuse...just poor coding habit)

    Oh and the table is to log the events for each bcp...

    Let us know if it works out....just make sure you got plent of drive space...

    I wouldn't do this over a network either....

    just copy the files when they're done...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Feb 2004
    Location
    michigan
    Posts
    17
    Many Thanks for both of you (Scott and Brett).
    I am browsing Brett's script. I am sure that, it will be a big help to get the export work done successfully. Hope I will get my next 15 instances converted from Latin1_General_CI_AS to SQL_Latin1_General_CI_AS ASAP. Thank you very much.

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    15 Instances! On 1 box?

    Wow

    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •