Get file name of database to be moved:
SELECT name, physical_name AS current_file_location
FROM sys.master_files
WHERE physical_name like ‘%Name%’
— Plug name into FileName, and move the file:
ALTER DATABASE DatabaseName MODIFY FILE
(
Name = FileName,
Filename = ‘F:\New\Location\Name.mdf’
);
ALTER DATABASE DatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE DatabaseName SET OFFLINE;
— Backup the database — to protect yourself from data corruption during the file copy.
— COPY the file to the new location… (if you copy the file and there is data corruption while the file is being copied, you will still have the original file, but if you MOVE the file you could lose the file entirely – also remember to copy the ldf file if you are copying the mdf file)
— reset permissions on the file…
ALTER DATABASE DatabaseName SET ONLINE;
— Check the file for data corruption (this could have happened during the file move)
DBCC CHECKDB (DatabaseName)
— If there are any errors – consider deleting the file and re-copying from the original file – or repair the data corruption.
ALTER DATABASE DatabaseName SET MULTI_USER;
— ZIP the old file and keep it around in case the copied file became corrupt during the file copy, you can delete the old file later…
