Probably the easiest way to move a physical SQL file from one location to another is using transact SQL to make the
ALTER DATABASE statements.
Below are six steps to successfully move SQL server database files, in the examples we are going to move the files for a database called testDB
First we need to open up SQL server management studio and create a new query, then we need to set the database context to the master database so that we can call the ALTER command against the database we want to move, to set the database context we use the USE DATABASE command as below
Use Master; GO
Next we need to find out what the logical names are of all the physical files that make up the database, we can do this by using a system view filtered for the database we want to move
SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID('testDB'); GO
Now we have the logical file names we need set the database to off line so that we are able to move the files without needing to detach the database, it goes without saying that doing this will end any client sessions to the database
ALTER DATABASE testDB SET OFFLINE GO
Now we need to physically move the files via the file system and cut and paste
Once the physical files have been moved we can update the database with the new location using modify file statements, this step will need to be repeated for each data and log file that make up the database.
ALTER DATABASE testDB MODIFY File (NAME = testDB, FILENAME = 'D:NewLocationtestDB.mdf') GO ALTER DATABASE testDB MODIFY File (NAME = testDB_Log, FILENAME = 'D:NewLocationtestDB_log.ldf') GO
Now we can set the database to be back on line.
ALTER DATABASE testDB SET ONLINE GO
Hopefully all has gone successfully and the database will now be back up and running from the new file location.