最近,客户由于磁盘空间不够了,加了盘,需要对sql server2008的数据库文件进行迁移,客户使用了镜像方式,怕对镜像有影响,所以进行了充分的测试,最后实施很成功,具体过程如下:
这里mytest是镜像数据库来的
--对数据库文件进行检查备份
DBCC CHECKDB (master) WITH PHYSICAL_ONLY
DBCC CHECKDB (mytest) WITH PHYSICAL_ONLY
DBCC CHECKDB (BackupData) WITH PHYSICAL_ONLY
--备份数据库,客户用的第三方备份工具进行备份
--确定文件位置
SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files;
master C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf ONLINE
mastlog C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf ONLINE
tempdev C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\tempdb.mdf ONLINE
templog C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\templog.ldf ONLINE
modeldev C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\model.mdf ONLINE
modellog C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\modellog.ldf ONLINE
MSDBData C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MSDBData.mdf ONLINE
MSDBLog C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MSDBLog.ldf ONLINE
mytestdb M:\mytest\mytest.mdf ONLINE
mytestdb_log M:\mytest\mytest_1.LDF ONLINE
distribution C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\distribution.MDF ONLINE
distribution_log C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\distribution.LDF ONLINE
--登录数据库
C:\Users\Administrator>sqlcmd -S localhost -U sa -P ********
--移动生产数据库文件
将数据库关闭镜像,进行离线
ALTER DATABASE mytest SET PARTNER SUSPEND --PAUSE MIRROR --将镜像进行挂起操作,停止镜像
ALTER DATABASE mytest SET OFFLINE
1> alter database mytest modify file(name= mytestdb,filename='D:\mytest\mytest.mdf'); --目录根据情况调整
2> go
--移动生产数据库日志文件
1> alter database mytest modify file(name= mytestdb_log,filename='D:\mytest\mytest_1.ldf');同文件一样
2> go
--移动报表数据库
ALTER DATABASE BackupData SET OFFLINE
1> alter database BackupData modify file(name=BackupData,filename='D:\mytest\BackupData.mdf');
2> go
1> alter database BackupData modify file(name=BackupData_log,filename='D:\mytest\BackupData.ldf');
2> go
1> quit
--拷贝文件到相应的位置,将数据库online
ALTER DATABASE BackupData SET ONLINE
--CHECK DATABASE BackupData
DBCC CHECKDB (BackupData) WITH PHYSICAL_ONLY
ALTER DATABASE mytest SET ONLINE
--关闭服务
C:\Users\Administrator>Net stop MSSQLSERVER
SQL Server (MSSQLSERVER) 服务正在停止.
SQL Server (MSSQLSERVER) 服务已成功停止。
--拷贝文件到相应的位置
--开启服务
C:\Users\Administrator>Net start MSSQLSERVER
SQL Server (MSSQLSERVER) 服务正在启动
SQL Server (MSSQLSERVER) 服务已经启动成功。
DBCC CHECKDB (mytest) WITH PHYSICAL_ONLY
--开启镜像
ALTER DATABASE mytest SET PARTNER RESUME --RESUME MIRROR
最后成功实施,主库文件迁移,并不影响镜像库!
这里mytest是镜像数据库来的
--对数据库文件进行检查备份
DBCC CHECKDB (master) WITH PHYSICAL_ONLY
DBCC CHECKDB (mytest) WITH PHYSICAL_ONLY
DBCC CHECKDB (BackupData) WITH PHYSICAL_ONLY
--备份数据库,客户用的第三方备份工具进行备份
--确定文件位置
SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files;
master C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf ONLINE
mastlog C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf ONLINE
tempdev C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\tempdb.mdf ONLINE
templog C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\templog.ldf ONLINE
modeldev C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\model.mdf ONLINE
modellog C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\modellog.ldf ONLINE
MSDBData C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MSDBData.mdf ONLINE
MSDBLog C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MSDBLog.ldf ONLINE
mytestdb M:\mytest\mytest.mdf ONLINE
mytestdb_log M:\mytest\mytest_1.LDF ONLINE
distribution C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\distribution.MDF ONLINE
distribution_log C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\distribution.LDF ONLINE
--登录数据库
C:\Users\Administrator>sqlcmd -S localhost -U sa -P ********
--移动生产数据库文件
将数据库关闭镜像,进行离线
ALTER DATABASE mytest SET PARTNER SUSPEND --PAUSE MIRROR --将镜像进行挂起操作,停止镜像
ALTER DATABASE mytest SET OFFLINE
1> alter database mytest modify file(name= mytestdb,filename='D:\mytest\mytest.mdf'); --目录根据情况调整
2> go
--移动生产数据库日志文件
1> alter database mytest modify file(name= mytestdb_log,filename='D:\mytest\mytest_1.ldf');同文件一样
2> go
--移动报表数据库
ALTER DATABASE BackupData SET OFFLINE
1> alter database BackupData modify file(name=BackupData,filename='D:\mytest\BackupData.mdf');
2> go
1> alter database BackupData modify file(name=BackupData_log,filename='D:\mytest\BackupData.ldf');
2> go
1> quit
--拷贝文件到相应的位置,将数据库online
ALTER DATABASE BackupData SET ONLINE
--CHECK DATABASE BackupData
DBCC CHECKDB (BackupData) WITH PHYSICAL_ONLY
ALTER DATABASE mytest SET ONLINE
--关闭服务
C:\Users\Administrator>Net stop MSSQLSERVER
SQL Server (MSSQLSERVER) 服务正在停止.
SQL Server (MSSQLSERVER) 服务已成功停止。
--拷贝文件到相应的位置
--开启服务
C:\Users\Administrator>Net start MSSQLSERVER
SQL Server (MSSQLSERVER) 服务正在启动
SQL Server (MSSQLSERVER) 服务已经启动成功。
DBCC CHECKDB (mytest) WITH PHYSICAL_ONLY
--开启镜像
ALTER DATABASE mytest SET PARTNER RESUME --RESUME MIRROR
最后成功实施,主库文件迁移,并不影响镜像库!
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29371470/viewspace-1246903/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29371470/viewspace-1246903/