Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2003
    Posts
    8

    Unanswered: Restore a sql 2k database to 7

    Hi,

    I need to restore a db from 2K to 7. I know I can't do a restore from backup, objects transfer is also out of the question because of the compatibility issue. Beside upgrading my server to 2k is there another way for me to accomplish this task? TIA

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Sure...bcp out all the data, script sql 2k with 7.0 only options, build the structure in 7.0, bcp all the data in...

    good luck

    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
    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
  •