ALTER DATABASE database_name SET OFFLINE;

ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name, FILENAME = 'new_path\os_file_name' );

-- Replace MyTestDatabase with your database name
SELECT name AS LogicalName, physical_name FROM sys.master_files WHERE database_id = DB_ID('MyTestDatabase');

quote:

---

Originally posted by James K

Couple of things that you want to do to avoid the situation where you took the database offline and then can't bring it back.

1. Take a full backup of the database and keep it safely somewhere.
2. Create a small test database, and go through this process to make sure you can move the files and bring it back online successfully.

Run this command - it tells you where the physical files are located currently. When you specify the new names, you should specify in the same format. It is the physical files that you are moving, not the logical names.

-- Replace MyTestDatabase with your database name
SELECT name AS LogicalName, physical_name FROM sys.master_files WHERE database_id = DB_ID('MyTestDatabase');

Connection strings etc. would remain the same. Logically the database remains the same. It is the physical files that are moving, so anything to do with physical files would be affected.

Again, I cannot reiterate how important it is that a) you take a full backup of the database and b) practise doing this on a test database.

---

LogicalName	physical_name
Test_DB	c:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\Test_DB.mdf
Test_DB_log	c:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\Test_DB_log.ldf

-- 1. create a test database
USE master 
GO

CREATE DATABASE MyTestDatabase;
GO

-- 2. see where the files are now.
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'MyTestDatabase');
GO
--MyTestDatabase	D:\Sql_Data\MyTestDatabase.mdf	ONLINE
--MyTestDatabase_log	L:\Sql_Log\MyTestDatabase_log.LDF	ONLINE

-- 3. set the database offline.
ALTER DATABASE MyTestDatabase SET OFFLINE;
GO
-- 4. now go to windows exporer and move the file manually to the new location.
-- in my case I am moving it from D:\Sql_Data\MyTestDatabase.mdf to 
-- D:\Temp\MyTestDatabase.mdf

-- 5. Tell SQL Server that the physical file has moved.
ALTER DATABASE MyTestDatabase MODIFY FILE ( NAME = MyTestDatabase, FILENAME = 'D:\Temp\MyTestDatabase.mdf' );
GO

-- 6. See where SQL Server will look for the database files when it comes online
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'MyTestDatabase');
GO

-- 7. Set the database back online.
ALTER DATABASE MyTestDatabase SET ONLINE;
GO

-- 8. See where the files are now.
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'MyTestDatabase');
GO

--MyTestDatabase	D:\Temp\MyTestDatabase.mdf	ONLINE
--MyTestDatabase_log	L:\Sql_Log\MyTestDatabase_log.LDF	ONLINE

-- 9. Cleanup. You DONT WANT TO DO THIS WITH YOUR REAL DATABASE ;)
DROP DATABASE MyTestDatabase
GO

quote:

---

Originally posted by asp__developer

On the step I am getting error that database is offline so can't do anything ? why ?

5. Tell SQL Server that the physical file has moved.
ALTER DATABASE MyTestDatabase MODIFY FILE ( NAME = MyTestDatabase, FILENAME = 'D:\Temp\MyTestDatabase.mdf' );
GO

---

ALTER DATABASE Move_DB MODIFY FILE ( NAME = Move_DB, FILENAME = 'C:\Move_DB.mdf' );
GO

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'Move_DB');

Move_DB	C:\Move_DB.ldf	ONLINE
Move_DB_log	C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\Move_DB_log.ldf	ONLINE

-- 1. create a test database
USE master 
GO

CREATE DATABASE MyTestDatabase;
GO

-- 2. see where the files are now.
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'MyTestDatabase');
GO
--MyTestDatabase	D:\Sql_Data\MyTestDatabase.mdf	ONLINE
--MyTestDatabase_log	L:\Sql_Log\MyTestDatabase_log.LDF	ONLINE

-- 3. set the database offline.
ALTER DATABASE MyTestDatabase SET OFFLINE;
GO
-- 4. now go to windows exporer and move the file manually to the new location.
-- in my case I am moving it from D:\Sql_Data\MyTestDatabase.mdf to 
-- D:\Temp\MyTestDatabase.mdf

-- 5. Tell SQL Server that the physical file has moved.
ALTER DATABASE MyTestDatabase MODIFY FILE ( NAME = MyTestDatabase, FILENAME = 'D:\Temp\MyTestDatabase.mdf' );
GO

-- 5b. now go to windows exporer and move the LOG file manually to the new location.
-- in my case I am moving it from L:\Sql_Log\MyTestDatabase_log.LDF to 
-- D:\Temp\MyTestDatabase_log.LDF

-- 5c. Tell SQL Server that the physical file has moved.
ALTER DATABASE MyTestDatabase MODIFY FILE ( NAME = MyTestDatabase_log, FILENAME = 'D:\Temp\MyTestDatabase_log.LDF' );
GO

-- 6. See where SQL Server will look for the database files when it comes online
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'MyTestDatabase');
GO

-- 7. Set the database back online.
ALTER DATABASE MyTestDatabase SET ONLINE;
GO

-- 8. See where the files are now.
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'MyTestDatabase');
GO

--MyTestDatabase	D:\Temp\MyTestDatabase.mdf	ONLINE
--MyTestDatabase_log	D:\Temp\MyTestDatabase_log.LDF'	ONLINE

-- 9. Cleanup. You DONT WANT TO DO THIS WITH YOUR REAL DATABASE ;)
DROP DATABASE MyTestDatabase
GO