SQL7、2000数据迁移（基于NetApp F87）

davy-chen

假设NetApp的IP为：192.168.0.10
1、At Windows 2000 Server, SQL Server service logon account was changed to 域\administrator
2、master数据库的迁移
进入“企业管理器”，右键点击选择服务启动，选择启动参数
原始信息如下：
-dc:\program files\microsoft sql server\MSSQ\data\master.mdf
-ec:\program files\microsoft sql server\MSSQ\LOG\ERRORLOG
-lc:\program files\microsoft sql server\MSSQ\data\mastlog.ldf
更改为要求迁移的路径：
-dM:\data\master.mdf
-eM:\LOG\ERRORLOG
-lM:\data\mastlog.ldf
删除原始的启动参数；
重新启动SQL的服务。
3、Migrating User Databases

At Query Analyzer
DBCC CHECKDB

Output
DBCC results for 'master'.
DBCC results for 'sysobjects'.
There are 922 rows in 14 pages for object 'sysobjects'.
DBCC results for 'sysindexes'.
There are 85 rows in 3 pages for object 'sysindexes'.
DBCC results for 'syscolumns'.
There are 3226 rows in 41 pages for object 'syscolumns'.
DBCC results for 'systypes'.
There are 24 rows in 1 pages for object 'systypes'.
DBCC results for 'syscomments'.
There are 1318 rows in 499 pages for object 'syscomments'.
DBCC results for 'sysfiles1'.
There are 2 rows in 1 pages for object 'sysfiles1'.
DBCC results for 'syspermissions'.
There are 580 rows in 2 pages for object 'syspermissions'.
DBCC results for 'sysusers'.
There are 13 rows in 1 pages for object 'sysusers'.
DBCC results for 'sysdepends'.
There are 3660 rows in 18 pages for object 'sysdepends'.
DBCC results for 'sysreferences'.
There are 0 rows in 1 pages for object 'sysreferences'.
DBCC results for 'sysfulltextcatalogs'.
There are 0 rows in 1 pages for object 'sysfulltextcatalogs'.
DBCC results for 'sysdatabases'.
There are 7 rows in 1 pages for object 'sysdatabases'.
DBCC results for 'sysxlogins'.
There are 4 rows in 1 pages for object 'sysxlogins'.
DBCC results for 'sysdevices'.
There are 8 rows in 1 pages for object 'sysdevices'.
DBCC results for 'sysmessages'.
There are 2997 rows in 122 pages for object 'sysmessages'.
DBCC results for 'sysconfigures'.
There are 43 rows in 1 pages for object 'sysconfigures'.
DBCC results for 'sysservers'.
There are 1 rows in 1 pages for object 'sysservers'.
DBCC results for 'syslanguages'.
There are 24 rows in 2 pages for object 'syslanguages'.
DBCC results for 'syscharsets'.
There are 105 rows in 30 pages for object 'syscharsets'.
DBCC results for 'sysaltfiles'.
There are 2 rows in 1 pages for object 'sysaltfiles'.
DBCC results for 'sysfilegroups'.
There are 1 rows in 1 pages for object 'sysfilegroups'.
DBCC results for 'sysallocations'.
There are 1 rows in 1 pages for object 'sysallocations'.
DBCC results for 'spt_committab'.
There are 0 rows in 1 pages for object 'spt_committab'.
DBCC results for 'spt_monitor'.
There are 1 rows in 1 pages for object 'spt_monitor'.
DBCC results for 'spt_values'.
There are 675 rows in 5 pages for object 'spt_values'.
DBCC results for 'spt_fallback_db'.
There are 0 rows in 1 pages for object 'spt_fallback_db'.
DBCC results for 'spt_fallback_dev'.
There are 0 rows in 1 pages for object 'spt_fallback_dev'.
DBCC results for 'spt_provider_types'.
There are 23 rows in 1 pages for object 'spt_provider_types'.
DBCC results for 'spt_fallback_usg'.
There are 0 rows in 1 pages for object 'spt_fallback_usg'.
DBCC results for 'MSreplication_options'.
There are 2 rows in 1 pages for object 'MSreplication_options'.
DBCC results for 'spt_datatype_info_ext'.
There are 10 rows in 1 pages for object 'spt_datatype_info_ext'.
DBCC results for 'spt_datatype_info'.
There are 33 rows in 1 pages for object 'spt_datatype_info'.
DBCC results for 'spt_server_info'.
There are 29 rows in 1 pages for object 'spt_server_info'.
CHECKDB found 0 allocation errors and 0 consistency errors in database 'master'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

At Query Analyzer
EXEC sp_detach_db 'YGCWDB'

Output

DBCC results for 'master'.
DBCC results for 'sysobjects'.
There are 922 rows in 14 pages for object 'sysobjects'.
DBCC results for 'sysindexes'.
There are 85 rows in 3 pages for object 'sysindexes'.
DBCC results for 'syscolumns'.
There are 3226 rows in 41 pages for object 'syscolumns'.
DBCC results for 'systypes'.
There are 24 rows in 1 pages for object 'systypes'.
DBCC results for 'syscomments'.
There are 1318 rows in 499 pages for object 'syscomments'.
DBCC results for 'sysfiles1'.
There are 2 rows in 1 pages for object 'sysfiles1'.
DBCC results for 'syspermissions'.
There are 580 rows in 2 pages for object 'syspermissions'.
DBCC results for 'sysusers'.
There are 13 rows in 1 pages for object 'sysusers'.
DBCC results for 'sysdepends'.
There are 3660 rows in 18 pages for object 'sysdepends'.
DBCC results for 'sysreferences'.
There are 0 rows in 1 pages for object 'sysreferences'.
DBCC results for 'sysfulltextcatalogs'.
There are 0 rows in 1 pages for object 'sysfulltextcatalogs'.
DBCC results for 'sysdatabases'.
There are 7 rows in 1 pages for object 'sysdatabases'.
DBCC results for 'sysxlogins'.
There are 4 rows in 1 pages for object 'sysxlogins'.
DBCC results for 'sysdevices'.
There are 8 rows in 1 pages for object 'sysdevices'.
DBCC results for 'sysmessages'.
There are 2997 rows in 122 pages for object 'sysmessages'.
DBCC results for 'sysconfigures'.
There are 43 rows in 1 pages for object 'sysconfigures'.
DBCC results for 'sysservers'.
There are 1 rows in 1 pages for object 'sysservers'.
DBCC results for 'syslanguages'.
There are 24 rows in 2 pages for object 'syslanguages'.
DBCC results for 'syscharsets'.
There are 105 rows in 30 pages for object 'syscharsets'.
DBCC results for 'sysaltfiles'.
There are 2 rows in 1 pages for object 'sysaltfiles'.
DBCC results for 'sysfilegroups'.
There are 1 rows in 1 pages for object 'sysfilegroups'.
DBCC results for 'sysallocations'.
There are 1 rows in 1 pages for object 'sysallocations'.
DBCC results for 'spt_committab'.
There are 0 rows in 1 pages for object 'spt_committab'.
DBCC results for 'spt_monitor'.
There are 1 rows in 1 pages for object 'spt_monitor'.
DBCC results for 'spt_values'.
There are 675 rows in 5 pages for object 'spt_values'.
DBCC results for 'spt_fallback_db'.
There are 0 rows in 1 pages for object 'spt_fallback_db'.
DBCC results for 'spt_fallback_dev'.
There are 0 rows in 1 pages for object 'spt_fallback_dev'.
DBCC results for 'spt_provider_types'.
There are 23 rows in 1 pages for object 'spt_provider_types'.
DBCC results for 'spt_fallback_usg'.
There are 0 rows in 1 pages for object 'spt_fallback_usg'.
DBCC results for 'MSreplication_options'.
There are 2 rows in 1 pages for object 'MSreplication_options'.
DBCC results for 'spt_datatype_info_ext'.
There are 10 rows in 1 pages for object 'spt_datatype_info_ext'.
DBCC results for 'spt_datatype_info'.
There are 33 rows in 1 pages for object 'spt_datatype_info'.
DBCC results for 'spt_server_info'.
There are 29 rows in 1 pages for object 'spt_server_info'.
CHECKDB found 0 allocation errors and 0 consistency errors in database 'master'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

At Query Analyzer
sp_detach_db 'pubs'

Output
Changed language setting to us_english.
Successfully detached database 'pubs'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

At Query Analyzer
dbcc traceon (1807)
GO
sp_attach_db 'pubs','\\192.168.0.10\home\sql70\Data\pubs.mdf','\\192.168.0.10\home\sql70\Data\pubs_log.ldf'
dbcc traceoff (1807)


Output
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Successfully attached database 'pubs'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

At Query Analyzer
EXEC sp_detach_db 'Northwind'

Output
Successfully detached database 'Northwind'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.


At Query Analyzer
dbcc traceon (1807)
GO
sp_attach_db 'Northwind','\\192.168.0.10\home\sql70\Data\northwnd.mdf','\\192.168.0.10\home\sql70\Data\northwnd.ldf'
dbcc traceoff (1807)
Output
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Successfully attached database 'Northwind'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

At Query Analyzer
EXEC sp_detach_db 'YGCWDB'

Output
Successfully detached database 'YGCWDB'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.


At Query Analyzer
dbcc traceon (1807)
GO
sp_attach_db 'YGCWDB','\\192.168.0.10\home\sql70\Cwsj\YGCWDBLog_7.dat','\\192.168.0.10\home\sql70\Cwsj\YGCWDBData_7.dat'
dbcc traceoff (1807)

Output
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Successfully attached database 'YGCWDB'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

4、Restarted SQL Server Service

5、Database Check

At Query Analyzer
DBCC CHECKDB

Output
Changed language setting to us_english.
DBCC results for 'master'.
DBCC results for 'sysobjects'.
There are 922 rows in 14 pages for object 'sysobjects'.
DBCC results for 'sysindexes'.
There are 85 rows in 3 pages for object 'sysindexes'.
DBCC results for 'syscolumns'.
There are 3226 rows in 41 pages for object 'syscolumns'.
DBCC results for 'systypes'.
There are 24 rows in 1 pages for object 'systypes'.
DBCC results for 'syscomments'.
There are 1318 rows in 499 pages for object 'syscomments'.
DBCC results for 'sysfiles1'.
There are 2 rows in 1 pages for object 'sysfiles1'.
DBCC results for 'syspermissions'.
There are 580 rows in 2 pages for object 'syspermissions'.
DBCC results for 'sysusers'.
There are 13 rows in 1 pages for object 'sysusers'.
DBCC results for 'sysdepends'.
There are 3660 rows in 18 pages for object 'sysdepends'.
DBCC results for 'sysreferences'.
There are 0 rows in 1 pages for object 'sysreferences'.
DBCC results for 'sysfulltextcatalogs'.
There are 0 rows in 1 pages for object 'sysfulltextcatalogs'.
DBCC results for 'sysdatabases'.
There are 7 rows in 1 pages for object 'sysdatabases'.
DBCC results for 'sysxlogins'.
There are 4 rows in 1 pages for object 'sysxlogins'.
DBCC results for 'sysdevices'.
There are 8 rows in 1 pages for object 'sysdevices'.
DBCC results for 'sysmessages'.
There are 2997 rows in 122 pages for object 'sysmessages'.
DBCC results for 'sysconfigures'.
There are 43 rows in 1 pages for object 'sysconfigures'.
DBCC results for 'sysservers'.
There are 1 rows in 1 pages for object 'sysservers'.
DBCC results for 'syslanguages'.
There are 24 rows in 2 pages for object 'syslanguages'.
DBCC results for 'syscharsets'.
There are 105 rows in 30 pages for object 'syscharsets'.
DBCC results for 'sysaltfiles'.
There are 2 rows in 1 pages for object 'sysaltfiles'.
DBCC results for 'sysfilegroups'.
There are 1 rows in 1 pages for object 'sysfilegroups'.
DBCC results for 'sysallocations'.
There are 1 rows in 1 pages for object 'sysallocations'.
DBCC results for 'spt_committab'.
There are 0 rows in 1 pages for object 'spt_committab'.
DBCC results for 'spt_monitor'.
There are 1 rows in 1 pages for object 'spt_monitor'.
DBCC results for 'spt_values'.
There are 675 rows in 5 pages for object 'spt_values'.
DBCC results for 'spt_fallback_db'.
There are 0 rows in 1 pages for object 'spt_fallback_db'.
DBCC results for 'spt_fallback_dev'.
There are 0 rows in 1 pages for object 'spt_fallback_dev'.
DBCC results for 'spt_provider_types'.
There are 23 rows in 1 pages for object 'spt_provider_types'.
DBCC results for 'spt_fallback_usg'.
There are 0 rows in 1 pages for object 'spt_fallback_usg'.
DBCC results for 'MSreplication_options'.
There are 2 rows in 1 pages for object 'MSreplication_options'.
DBCC results for 'spt_datatype_info_ext'.
There are 10 rows in 1 pages for object 'spt_datatype_info_ext'.
DBCC results for 'spt_datatype_info'.
There are 33 rows in 1 pages for object 'spt_datatype_info'.
DBCC results for 'spt_server_info'.
There are 29 rows in 1 pages for object 'spt_server_info'.
CHECKDB found 0 allocation errors and 0 consistency errors in database 'master'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.


6、Check for Error Log (ERRORLOG)

Output

2003-05-05 15:36:11.09 kernel   Microsoft SQL Server  7.00 - 7.00.623 (Intel X86)
        Nov 27 1998 22:20:07
        Copyright (c) 1988-1998 Microsoft Corporation
        Standard Edition on Windows NT 4.0 (Build 1381: Service Pack 6)

2003-05-05 15:36:11.09 kernel   Copyright (C) 1988-1997 Microsoft Corporation.
2003-05-05 15:36:11.09 kernel   All rights reserved.
2003-05-05 15:36:11.09 kernel   Logging SQL Server messages in file 'C:\MSSQL7\log\ERRORLOG'.
2003-05-05 15:36:11.09 kernel   initconfig: Number of user connections limited to 32767.
2003-05-05 15:36:11.09 kernel   SQL Server is starting at priority class 'normal'(1 CPU detected).
2003-05-05 15:36:11.09 kernel   User Mode Scheduler configured for thread processing
2003-05-05 15:36:12.10 server   Directory Size: 10799
2003-05-05 15:36:12.10 spid1    Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks
2003-05-05 15:36:12.10 kernel   Attempting to initialize Distributed Transaction Coordinator.
2003-05-05 15:36:12.10 spid1    Failed to obtain TransactionDispenserInterface: XACT_E_TMNOTAVAILABLE
2003-05-05 15:36:12.12 spid1    Starting up database 'master'.
2003-05-05 15:36:12.12 spid1    Opening file C:\MSSQL7\data\master.mdf.
2003-05-05 15:36:12.12 spid1    Opening file C:\MSSQL7\data\mastlog.ldf.
2003-05-05 15:36:12.12 spid1    Loading SQL Server's  Unicode collation.
2003-05-05 15:36:12.12 spid1    Loading SQL Server's  non-Unicode sort order and character set.
2003-05-05 15:36:12.12 spid1    5 transactions rolled forward in database 'master' (1).
2003-05-05 15:36:12.12 spid1    0 transactions rolled back in database 'master' (1).
2003-05-05 15:36:12.15 spid1    Starting up database 'model'.
2003-05-05 15:36:12.15 spid1    Opening file m:\DATA\model.mdf.
2003-05-05 15:36:12.17 spid1    Opening file m:\data\modellog.ldf.
2003-05-05 15:36:12.17 spid1    Clearing tempdb database.
2003-05-05 15:36:12.17 spid1    Creating file m:\DATA\TEMPDB.MDF.
2003-05-05 15:36:12.18 spid1    Closing file m:\DATA\TEMPDB.MDF.
2003-05-05 15:36:12.18 spid1    Creating file m:\DATA\TEMPLOG.LDF.
2003-05-05 15:36:12.18 spid1    Closing file m:\DATA\TEMPLOG.LDF.
2003-05-05 15:36:12.18 spid1    Opening file m:\DATA\TEMPDB.MDF.
2003-05-05 15:36:12.18 spid1    Opening file C:\DATA\TEMPLOG.LDF.
2003-05-05 15:36:12.21 spid1    Closing fileM:\DATA\TEMPDB.MDF.
2003-05-05 15:36:12.21 spid1    Closing file C:\DATA\TEMPLOG.LDF.
2003-05-05 15:36:12.21 spid1    Starting up database 'tempdb'.
2003-05-05 15:36:12.21 spid1    Opening file C:\DATA\TEMPDB.MDF.
2003-05-05 15:36:12.21 spid1    Opening file m:\DATA\TEMPLOG.LDF.
2003-05-05 15:36:12.25 spid1    Server name is 'hp'.
2003-05-05 15:36:12.25 kernel   Using 'SQLEVN70.DLL' version '7.00.623'.
2003-05-05 15:36:12.25 kernel   Using 'OPENDS60.DLL' version '7.00.00.0623'.
2003-05-05 15:36:12.25 ods      Using 'SSNMPN70.DLL' version '7.0.623' to listen on '\\.\pipe\sql\query'.
2003-05-05 15:36:12.25 ods      Using 'SSMSSO70.DLL' version '7.0.623' to listen on '1433'.
2003-05-05 15:36:12.25 ods      Using 'SSMSRP70.DLL' version '7.0.623' to listen on 'HPLH300'.
2003-05-05 15:36:12.28 spid6    Starting up database 'msdb'.
2003-05-05 15:36:12.28 spid6    Opening file m:\DATA\msdbdata.mdf.
2003-05-05 15:36:12.28 spid7    Starting up database 'pubs'.
2003-05-05 15:36:12.28 spid7    Opening file \\192.168.0.10\home\sql70\Data\pubs.mdf.
2003-05-05 15:36:12.28 spid8    Starting up database 'Northwind'.
2003-05-05 15:36:12.28 spid8    Opening file \\192.168.0.10\home\sql70\Data\northwnd.mdf.
2003-05-05 15:36:12.28 spid9    Starting up database 'YGCWDB'.
2003-05-05 15:36:12.28 spid9    Opening file \\192.168.0.10\home\sql70\Cwsj\YGCWDBData_7.dat.
2003-05-05 15:36:12.29 spid6    Opening file m:\DATA\msdblog.ldf.
2003-05-05 15:36:12.29 spid7    Opening file \\192.168.0.10\home\sql70\Data\pubs_log.ldf.
2003-05-05 15:36:12.29 spid8    Opening file \\192.168.0.10\home\sql70\Data\northwnd.ldf.
2003-05-05 15:36:12.29 spid9    Opening file \\192.96.36.19\home\sql70\Cwsj\YGCWDBLog_7.dat.
2003-05-05 15:36:12.32 spid1    Recovery complete.
2003-05-05 15:36:12.32 spid1    SQL Server's Unicode collation is:
2003-05-05 15:36:12.32 spid1            '中文' (ID = 2052).
2003-05-05 15:36:12.32 spid1            comparison style = 196609.
2003-05-05 15:36:12.32 spid1    SQL Server's non-Unicode sort order is:
2003-05-05 15:36:12.32 spid1            'nls_cp936' (ID = 199).
2003-05-05 15:36:12.32 spid1    SQL Server's non-Unicode character set is:
2003-05-05 15:36:12.32 spid1            'cp936' (ID = 14).
2003-05-05 15:36:26.14 spid7    DBCC CHECKDB (master) executed by TAGD\Administrator found 0 errors and repaired 0 errors.

Michael_Zhao

佩服！
8月份我们要帮助用户做SQL的数据迁移，如果能帮忙，允许我们向你咨询，不胜感激。

用户系统背景：1台SQL Server 7，1台SQL 2000，要求合并，并将数据移植到一台新的SQL 2000中。

谢谢！我的地址：michael_zhao@allsourcestek.com

yddll

exp/imp不可以吗？

davy-chen

[quote]原帖由 "yddll"]exp/imp不可以吗？[/quote 发表：
  
是数据的迁移，不是备份！而且实在数据库是open的状态下，将数据库的数据文件迁到NAS上！花时间少！另外，您这样的导回去，还的迁移！

bird_man

俺没有看懂 ，可以解释一下不？

wolfgroup

SQL SERVER的库文件改变存储位置，主要是master麻烦，以前也用过楼主的方法测试过，可是SQL SERVER的服务根本起不来呀？

davy-chen

我就是这样做的,而且成功了！可能您后台的用户连接的关闭掉吧！
在运行的数据库中，我们将所有的数据库的数据文件迁移到NetApp的F87上去。

yddll

用文件组的形式将库分布到M：\不好吗？

davy-chen

[quote]原帖由 "yddll"]用文件组的形式将库分布到M：\不好吗？[/quote 发表：
   
我没有试过，不知行不行？

yddll

你在迁移的过程中客户端还是要断开的，所以不如重装
mdf拷过去再attach一下好了