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? TIASure...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
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
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment