moving Database Files from One Drive Location to the other location

Dear Experts,
We are moving SQL 2012 Database Files (Data File and Log File) from One Drive Location to the other location.
We have more than 100 databases. Instead of moving single database at one time I am looking for script to move all at a time. Please advice.

we are using SharePoint 2013 and SQL 2012

Thanks
tabreedAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
The user dbs are easy to move.  The system dbs not so easy, especially master and model.

But, if you can afford for SQL to be done for long enough to physically copy all the db files to their new location(s), you can do all the dbs at once easily.
tabreedAuthor Commented:
Thanks.

 since our database is newly implemented we don't have large data files. therefore I have taken 8 hrs downtime.

The issue is I  have command to move single database, I am looking for the script which will move all the database (data & logs) at a time.

 we have different locations for different  databases & logs.

Please advice.

I am using attached commands
To-get-the-current-database-files-L.docx
Scott PletcherSenior DBACommented:
First, immediately before stopping SQL Server for the down time, you need to update all the catalog locations, like it was done in the script.  I prefer to generate the code to avoid unnecessary errors.  With any luck, all the data files and log files are going to the same place; if not, you'll need to adjust the code accordingly.  Copy the output of the query below to a new window, review it, and then run it.

NOTE: If you want to move the master db, you'll have to change the start up configuration parameters for SQL Server.  If you don't want to do that right now, uncomment the WHERE clause above.

DECLARE @new_data_file_path varchar(255)
DECLARE @new_log_file_path varchar(255)

SET @new_data_file_path = 'x:\new\path\to\data\files\'
SET @new_log_file_path = 'y:\new\path\to\log\files\'

SELECT
    'ALTER DATABASE [' + DB_NAME(mf.database_id) + '] ' +
    'MODIFY FILE ( NAME = [' + mf.name + '], FILENAME = ''' +
        CASE WHEN type_desc = 'LOG' THEN @new_log_file_path ELSE @new_data_file_path END +
        RIGHT(mf.physical_name, CHARINDEX('\', REVERSE(mf.physical_name)) - 1) + ''' )'
FROM sys.master_files mf
--WHERE DB_NAME(mf.database_id) <> 'master'
ORDER BY DB_NAME(mf.database_id), mf.file_id

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Need More Insight Into What’s Killing Your Network

Flow data analysis from SolarWinds NetFlow Traffic Analyzer (NTA), along with Network Performance Monitor (NPM), can give you deeper visibility into your network’s traffic.

tabreedAuthor Commented:
Thanks, How can add multiple data and logs file details in this script. please advice
Scott PletcherSenior DBACommented:
That code will generate an ALTER for every data and log file used in the instance.
tabreedAuthor Commented:
Hi ScottPletcher,

while change the new path I am getting below mentioned error. Please advice

Msg 5184, Level 16, State 2, Line 1
Cannot use file 'F:\Databases\IT\ITContent_IT.mdf' for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used. Either the disk resource containing the file is not present in the cluster group or the cluster resource of the Sql Server does not have a dependency on it.
Msg 942, Level 14, State 4, Line 1
Database 'ITContent_IT' cannot be opened because it is offline.
Paul MacDonaldDirector, Information SystemsCommented:
Sounds like "F:" is a local drive, not a shared cluster drive.
Scott PletcherSenior DBACommented:
Oh, it's a cluster.  Yes, all SQL databases have to go on shared drives.  If you don't know how to go into Cluster Manager to check which drives are available, maybe someone else there does and can show you?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.