May 16, 2016 at 2:47 am
Dear All,
I wanted to find out a way of moving system databases onto different drives, I wanted to know if it is any different from moving user databases. I have the following script which does the user databases Move but I need to find out exactly what the difference are for System databases e.g. "Master database" requires any special procedure as the database server would not be able to start without it etc?
Thank you in advance!
May 16, 2016 at 2:58 am
Yes, the process is different from that for user databases. Search for "moving system databases sql server" and you'll soon find how to do it. I can't see the script that you mentioned, by the way.
John
May 16, 2016 at 3:29 am
Here is the script for the user databases:
1. stop SQL Server Service
2. Check the location of the tempdb files
Use master
GO
SELECT name AS [LogicalName],physical_name AS [Location],state_desc AS [Status]
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
GO
3.Change the location of tempDB Data and Log files
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'I:\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'I:\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\templog.ldf');
GO
4. Restart SQL Server Service
5. Check the new location of the tempDB files:
Use master
GO
SELECT name AS [LogicalName],physical_name AS [Location],state_desc AS [Status]
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
GO
6. Delete old tempdb.mdf and templog.ldf files from the original location
Thank you!
May 16, 2016 at 3:35 am
tt-615680 (5/16/2016)
Here is the script for the user databases:1. stop SQL Server Service
2. Check the location of the tempdb files
Use master
GO
SELECT name AS [LogicalName],physical_name AS [Location],state_desc AS [Status]
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
GO
3.Change the location of tempDB Data and Log files
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'I:\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'I:\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\templog.ldf');
GO
4. Restart SQL Server Service
Your order isn't correct (for one, how would you run an ALTER DATABASE after the service has been stopped), plus that's for TempDB not user databases.
Step 1 (stop SQL Service should be moved to between steps 3 and 4.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 17, 2016 at 7:40 am
You only mention TempDB, is this the only system database you wish to move?
To move tempdb correct sequence is
tt-615680 (5/16/2016)
1. Check the location of the tempdb filesUse master
GO
SELECT name AS [LogicalName],physical_name AS [Location],state_desc AS [Status]
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
GO
2.Change the location of tempDB Data and Log files
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'I:\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'I:\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\templog.ldf');
GO
3. stop SQL server service
4. Restart SQL Server Service
5. Check the new location of the tempDB files:
Use master
GO
SELECT name AS [LogicalName],physical_name AS [Location],state_desc AS [Status]
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
GO
6. Delete old tempdb.mdf and templog.ldf files from the original location
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply