Microsoft SQL server has two types of database, one is system database and another is user database. A system database is used by the SQL server for its own maintenance and management while a user database is used by the database administrator for storing user data. In this article, we will discuss about to “How to move SQL server user database within same instance”.
A database administrator can move data and log files of user database to a new location by specifying the new location in a filename clause of ALTER database statement. To move a SQL server user database follow the below steps:
- Set the database off-line through this command
ALTER DATABASE database_name SET OFFLINE;
- Move the files to a designation location
Run this command for each move file.
ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name, FILENAME = ' new_path\os_file_name' );
- Now set the database online through this command
ALTER DATABASE database_name SET ONLINE;
- Verify the changes
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'');
Note: The above method is applied to moving database within the same instance of SQL server database. To move database from one instance to another, you can try backup and restore method.
Example: Suppose we have a log file sqlserverdatabase and want to move in to a new location.
Use master;
Go
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'sqlserverdatabase')
AND type_desc = N'LOG';
Go
ALTER DATABASE sqlserverdatabase SET OFFLINE;
GO
ALTER DATABASE sqlserverdatabase MODIFY FILE ( NAME = sqlserverdatabase_log, FILENAME = 'C:\NewLoc\sqlserverdatabase_Log.ldf');
Go
ALTER DATABASE sqlserverdatabase SET ONLINE;
GO
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'sqlserverdatabase');
AND type_desc = N'LOG';
