SQLServer安装目录迁移

原创

myx666 2022-02-18 10:39:25 博主文章分类：DBA_SQLServer ©著作权

文章标签 sql sed 数据库 文章分类 SQL Server 数据库

©著作权归作者所有：来自51CTO博客作者myx666的原创作品，请联系作者获取转载授权，否则将追究法律责任

--1.查看所有数据库的recovery属性及当前的文件路径

--step1:check all the databses's properties and all their path

Recovery model, log reuse wait description, log file size, log usage size ，查看所有数据库的属性
and compatibility level for all databases on instance

SELECT db.[name] AS [Database Name], db.recovery_model_desc AS [Recovery Model],
db.log_reuse_wait_desc AS [Log Reuse Wait Description],
ls.cntr_value AS [Log Size (KB)], lu.cntr_value AS [Log Used (KB)],
CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT)AS DECIMAL(18,2)) * 100 AS
[Log Used %], db.[compatibility_level] AS [DB Compatibility Level],
db.page_verify_option_desc AS [Page Verify Option], db.is_auto_create_stats_on,
db.is_auto_update_stats_on, db.is_auto_update_stats_async_on, db.is_parameterization_forced,
db.snapshot_isolation_state_desc, db.is_read_committed_snapshot_on,
is_auto_shrink_on, is_auto_close_on
FROM sys.databases AS db WITH (NOLOCK)
INNER JOIN sys.dm_os_performance_counters AS lu WITH (NOLOCK)
ON db.name = lu.instance_name
INNER JOIN sys.dm_os_performance_counters AS ls WITH (NOLOCK)
ON db.name = ls.instance_name
WHERE lu.counter_name LIKE N'Log File(s) Used Size (KB)%'
AND ls.counter_name LIKE N'Log File(s) Size (KB)%'
AND ls.cntr_value > 0 OPTION (RECOMPILE);

Things to look at:
How many databases are on the instance?
What recovery models are they using?
What is the log reuse wait description?
How full are the transaction logs?
What compatibility level are they on?
File Names and Paths for databases in instance

SELECT DB_NAME([database_id])AS [Database Name],
[file_id], name, physical_name, type_desc, state_desc,
CONVERT( bigint, size/128.0) AS [Total Size in MB]
FROM sys.master_files WITH (NOLOCK)

Things to look at:
Are data files and log files on different drives?
Is everything on the C: drive?
Is TempDB on dedicated drives?
Are there multiple data files?

Recovery model, log reuse wait description, log file size, log usage size ，查看所有数据库的属性
and compatibility level for all databases on instance

Things to look at:
How many databases are on the instance?
What recovery models are they using?
What is the log reuse wait description?
How full are the transaction logs?
What compatibility level are they on?
File Names and Paths for databases in instance

SELECT DB_NAME([database_id])AS [Database Name],
[file_id], name, physical_name, type_desc, state_desc,
CONVERT( bigint, size/128.0) AS [Total Size in MB]
FROM sys.master_files WITH (NOLOCK)

Things to look at:
Are data files and log files on different drives?
Is everything on the C: drive?
Is TempDB on dedicated drives?
Are there multiple data files?

--step2: backup all the databases
backup database master to disk ='I:\migratebackups\master.bak'
backup database msdb to disk = 'I:\migratebackups\msdb.bak'
backup database model to disk ='I:\migratebackups\model.bak'
backup log model to disk ='I:\migratebackups\model_log.bak'
backup database partitiondbtest to disk = 'I:\migratebackups\partitiondbtest.bak'
backup log partitiondbtest to disk = 'I:\migratebackups\partitiondbtest_log.bak'

---step3, stop sql server service, copy or migrate all the files into another drive
---step4: modify files
---4.1system files , verify path again
select name, physical_name from sys.master_files where database_id = DB_ID(N'MSDB')
go
select name, physical_name from sys.master_files where database_id = DB_ID(N'Model')
go
select name, physical_name from sys.master_files where database_id = DB_ID(N'master')
go

select name, physical_name from sys.master_files where database_id = DB_ID(N'partitiondbtest')
go
----4.2Move Msdb and Model and user dabatases
--MSDB db file
use master;
go
Alter Database MSDB
Modify File (Name = MSDBData, FILENAME = 'I:\MSSQL15.SQL2019TEST1\MSSQL\DATA\MSDBData.mdf')
GO
--MSDB Log file
ALTER DATABASE MSDB
MODIFY FILE (NAME = MSDBLog, FILENAME = 'I:\MSSQL15.SQL2019TEST1\MSSQL\DATA\MSDBLog.ldf');
GO
--MODEL db file
use master;
go
Alter Database Model
Modify File (Name = Modeldev, FILENAME = 'I:\MSSQL15.SQL2019TEST1\MSSQL\DATA\Model.mdf')
GO
--MODEL log file
ALTER DATABASE Model
MODIFY FILE (NAME = Modellog, FILENAME = 'I:\MSSQL15.SQL2019TEST1\MSSQL\DATA\Modellog.ldf');
GO

--partitiondbtest db file
use master;
go
Alter Database partitiondbtest
Modify File (Name = partitiondbtest, FILENAME = 'I:\MSSQL15.SQL2019TEST1\MSSQL\DATA\partitiondbtest.mdf')
GO

use master;
go
Alter Database partitiondbtest
Modify File (Name = Sale2009data, FILENAME = 'I:\MSSQL15.SQL2019TEST1\MSSQL\DATA\Sale2009data.ndf')
GO

use master;
go
Alter Database partitiondbtest
Modify File (Name = Sale2010data, FILENAME = 'I:\MSSQL15.SQL2019TEST1\MSSQL\DATA\Sale2010data.ndf')
GO

use master;
go
Alter Database partitiondbtest
Modify File (Name = Sale2011data, FILENAME = 'I:\MSSQL15.SQL2019TEST1\MSSQL\DATA\Sale2011data.ndf')
GO

use master;
go
Alter Database partitiondbtest
Modify File (Name = Sale2012data, FILENAME = 'I:\MSSQL15.SQL2019TEST1\MSSQL\DATA\Sale2012data.ndf')
GO

use master;
go
Alter Database partitiondbtest
Modify File (Name = Sale2013data, FILENAME = 'I:\MSSQL15.SQL2019TEST1\MSSQL\DATA\Sale2013data.ndf')
GO

--partitiondbtest log file
ALTER DATABASE partitiondbtest
MODIFY FILE (NAME = partitiondbtest_log, FILENAME = 'I:\MSSQL15.SQL2019TEST1\MSSQL\DATA\partitiondbtest_log.ldf');
GO
--5.check the current dabase path

select name, physical_name from sys.master_files where database_id = DB_ID(N'MSDB')
go
select name, physical_name from sys.master_files where database_id = DB_ID(N'Model')
go
select name, physical_name from sys.master_files where database_id = DB_ID(N'master')
go

select name, physical_name from sys.master_files where database_id = DB_ID(N'partitiondbtest')
go

--6.change master location in SSCM
--I:\MSSQL15.SQL2019TEST1\MSSQL\DATA
--I:\MSSQL15.SQL2019TEST1\MSSQL\Log

--7.check whether need to change reistry or not