SQL with Manoj

 
Here in this post we will see how to move user created Database to an another drive from the default drive.
 

–> The first 2 ways I’ve discussed in my other blog posts, please check the links below:

1. Detach & Attach task, video

2. Backup & Restore task, link, video
 

3. By using ALTER command, Syntax below:

ALTER DATABASE SET OFFLINE;
GO

— Manually move the file(s) to the new location.

ALTER DATABASE Database name
MODIFY FILE (
NAME = ‘Database name’,
FILENAME = ‘New Location’
)
GO

ALTER DATABASE Database name SET ONLINE;
GO

–> Script used in above video:

Step #1. Create a new Database in SSMS with name “ManDB”.

Step #2. Check the current location of database:

select * from ManDB.sys.database_files

--ManDB		D:\Program Files (x86)\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\ManDB.mdf
--ManDB_log	D:\Program Files (x86)\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\ManDB_log.ldf

Step #3. Take the database offline, so that we can move files from one drive to another:

ALTER DATABASE ManDB SET OFFLINE;
GO

Step #4. Move the file(s) to the new location manually.

Step #5. Change the system catalog settings:

ALTER DATABASE ManDB
MODIFY FILE (
	NAME = 'ManDB',
	FILENAME = 'E:\SQLDBs\ManDB.mdf' 
)
GO

ALTER DATABASE ManDB
MODIFY FILE (
	NAME = 'ManDB_log',
	FILENAME = 'E:\SQLDBs\ManDB_log.ldf' 
)
GO

Step #6. Take the database back online:

ALTER DATABASE ManDB SET ONLINE;
GO

Step #7. Check the new location:

select * from ManDB.sys.database_files

--ManDB		E:\SQLDBs\ManDB.mdf
--ManDB_log	E:\SQLDBs\ManDB_log.ldf