SQL 2008 R2 database Move
We
recently moved our SAN to a new one on a Clustered SQL Server.
First SQL server service could not start and after fixing that SQL
Agent would not start for over a week and finally Microsoft's tech support
helped to find and fix the issue. I found out that I had Missed the last step called Follow-up:
After Moving All System Databases in
these instructions. And if it is a cluster environment then you need to perform this on the active node.
If
this is your first exposure to this process as it was for me, these instructions might be useful to you.
You
can find step by step database (including System DB) move on Microsoft's website http://msdn.microsoft.com/en-us/library/ms345408(v=sql.105).aspx
They are very clear and
extremely helpful and that is what used When we moved from old SAN to a new
one. I added my own notes and some scripts that would help simplify some of the
process such as
detaching and attaching databases, specially if you have lots of DBs to move.
Make a full back of each Database and save it on a
non-clustered drives/location.
1. Run and save the result to run at the end your
process. This generates the attach db statements. You need to to
this first as you will not have access to them once you detach the
databases. Then you can replace the drive and the path to point to the new location.
-- *********** First change the Query Results to text *************
select
Case
When fileid = 1 then ' EXEC sp_attach_db @dbname = N''' + [name]+
''','
+char(13) +
' @filename1 = N''' + filename + ''','
when fileid = 2 then ' @filename2 = N''' + filename + ''';'
+char(13)
+char(13)
end as [Name_path]
from sys.sysaltfiles
where dbid not in (1,2,3,4) -- All dbs except system dbs
2. Detach all databases except, System
DBs. You can run following statements to generate the detach syntax
SELECT 'EXEC MASTER.dbo.sp_detach_db @dbname = N'''
+ Name + ''''FROM sys.sysdatabases
WHERE dbid not in (1,2,3,4)
-- All dbs except system dbs
3. Change the System dbs file location
( except Master Db):
ALTER DATABASE Model MODIFY FILE ( NAME = modeldev, FILENAME =
'Drive Letter:\Path\model.mdf' )
ALTER DATABASE Model MODIFY FILE ( NAME = modellog , FILENAME =
'Drive Letter:\Path\modellog.ldf' )
ALTER DATABASE MSDB MODIFY
FILE ( NAME = MSDBData, FILENAME = 'Drive Letter:\Path\MSDBData.mdf' )
ALTER DATABASE MSDB MODIFY
FILE ( NAME = MSDBLog , FILENAME = 'Drive Letter:\Path\MSDBLog.ldf' )
ALTER DATABASE tempdb MODIFY
FILE ( NAME = tempdev , FILENAME = 'Drive Letter:\PathL\tempdb.mdf' )
ALTER DATABASE tempdb MODIFY
FILE ( NAME = templog , FILENAME = 'Drive Letter:\PathR\emplog.ldf' )
4. Move
all the .mdf and .ldf files to the new location
5. Detach Model, MSDB and Tempdb
SELECT 'EXEC MASTER.dbo.sp_detach_db @dbname = N'''
+ Name + ''''FROM sys.sysdatabases
WHERE dbid in (2,3,4) -- Only system dbs except MASTER DB
To move the master database, follow these steps. If this is clustered server then you need to perfrom these steps on all nodes
- From the Start menu, point to All Programs, point to Microsoft SQL Server, point to Configuration Tools, and then click SQL Server Configuration Manager.
- In the SQL Server Services node, right-click the instance of SQL Server (for example, SQL Server (MSSQLSERVER)) and choose Properties.
- In the SQL Server (instance_name) Properties dialog box, click the Advanced tab.
- Edit the Startup Parameters values to point to the planned location for the master database data and log files, and click OK. Moving the error log file is optional.
The parameter value for the data file must follow the -d parameter and the
value for the log file must follow the -l parameter. The following example shows the parameter
values for the default location of the master data and log files.
-dC:\Program Files\Microsoft SQL
Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf
-lC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\
DATA\mastlog.ldf
If the planned relocation for the master data and log files is E:\SQLData, the parameter values would be changed as follows:
-lC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\
DATA\mastlog.ldf
If the planned relocation for the master data and log files is E:\SQLData, the parameter values would be changed as follows:
-dE:\SQLData\master.mdf
-lE:\SQLData\mastlog.ldf
-lE:\SQLData\mastlog.ldf
6. Stop the instance of SQL Server
7. Move Model, MSDB, Tempdb and
Master data and log files to the new location
8. Restart the instance of SQL Server
If the msdb database is moved and
the instance of SQL Server is configured for
Database Mail, complete these additional
steps.
- Verify that Service Broker is enabled for the msdb database by running the following query.
SELECT is_broker_enabled
FROM sys.databases
WHERE name = N'msdb'
Important:
Follow-up: After Moving All System Databases
Pasted from <http://msdn.microsoft.com/en-us/library/ms345408(v=sql.105).aspx>
If you have moved all of the system databases to a new drive or
volume or to another server with a different drive letter, make the following
updates. If this is clustered server then you need to perfrom these steps on all nodes
Change the SQL Server Agent
log path. If you do not update this path, SQL Server Agent will fail to
start.
Change the database default location. Creating a new database may fail if the drive letter and path specified as the default location do not exist.
Change the SQL Server Agent Log
Path
From SQL Server Management Studio, in Object Explorer, expand SQL Server Agent. If this is clustered server then you need to perfrom these steps on all nodes
- Right-click Error Logs and click Configure.
- In the Configure
SQL Server Agent Error Logs dialog box, specify the new location of the
SQLAGENT.OUT file. The default location is C:\Program Files\Microsoft SQL
Server\MSSQL10_50.
\MSSQL\Log\.
Change the database default
location
- From SQL Server Management Studio, in Object Explorer, right-click the SQL Server server and click Properties.
- In the Server Properties dialog box, select Database Settings.
- Under Database Default Locations, browse to the new location for both the data and log files.
- Stop and start the SQL Server service to complete the change.
If
any reason you cannot modify the error log path
using Management Studio you modify the registry: Again if this is
clustered server then you need to perfrom these steps on all nodes
- Go to START --> Run and type Regedit
- HKEY_LOCAL_MACHINE -> SOFTWARE -> MICROSOFT -> Microsoft SQL Server ->
MSQL10_50.MSSQLSERVER -> SQLServerAgent and make sure it is
pointing to the new location.