Technical Article

Move Physical Files

,

The script is a good way to move physical files from one location to the other.

The Script will run the sc_helpfile or use the sys.database files view to get the physical and logical name of the database.

Change the database to single_user and set the database offline.

Move the files to the new location manually ...

Alter database and set new File locations, Set database back online and set database back to multi_user.

/***Script to move file to a new location ***/--Find Database Physical and logical name
--Write result down very important
sp_helpfile  ---Use this OR

use databasenName
select name,physical_name from sys.database_files
go

/*** Getting ready to go down to bussiness ****/--ponint to master db
use Master
go
-- Rollback all trans 
--Ensure all users are notify of the down time

alter database DatabaseName set single_user with rollback immediate
go
-- Set databae Offline
alter database DatabaseName set Offline
go
--- Cut and paste the files from the original location to the new location
-- Be sure to write the name and new path exactly 
alter database DatabaseName 
Modify file (Name = logicalName,Filename = 'DriveLetter:\newPath\DataFileName.mdf/ndf or ldf')--Create one per every file
go
---Set database backonline
alter database DatabaseName set Online
go
--Set Database back to multi_user
alter database DatabaseName set multi_user
go

Rate

3.29 (7)

Share

Share

Rate

3.29 (7)