Vaibhav Gaikwad
Microsoft .NET developer
Bla Code! Bla Code!

SQL Server: Moving DATA and LOG file to different location

Thursday, February 1, 2018 3:36 PM
The default location of the "data" and "log" files for SQL Server database is "C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA" for version 2016 unless it is set differently during the installation process.

The core issue in discussion here is the copy the database files from one drive to another in case of a scenario where you current drive is getting exhausted. In my case the C: drive was running of space and I wanted to move the files to F: drive

So start the SQL Management Studio and fire the following query to understand the current file locations for a specific DB.

SELECT name,physical_name FROM <database_name>.sys.database_files

Once you have verified the locations, just copy the .mdf and .ldf files from the location listed in the output of above query and move to the desired drive (location). At this instance you have the database DATA nd LOG files in 2 drives, and you want it at just the new location. So before you are ready to delete the files from the old location first you must change the registry of those file at Server level. You can edit the file locations using the following query command

ALTER DATABASE <database_name>
MODIFY FILE (NAME =
<database_name_file_name_for_mdf>, FILENAME = 'F:\DATA\my_db.mdf');

ALTER DATABASE
<database_name> MODIFY FILE (NAME = <database_name_file_name_for_ldf>_Log, FILENAME = 'F:\DATA\my_db.ldf');

Note: <value> are used as placeholders, do replace them with your respective values

The final part, is the take the DB offline delete the files in the old location and bring the DB online again so that it will start using the files from new location.







Feedback

# re: SQL Server: Moving DATA and LOG file to different location

Hi Admin,

I tried hosting me php website https://toptecheasy.com/

on cloud server, but it gave me "database not connected" error once.

But when I finally connected my database, then it started giving me error,


username and password error,

Could you please tell me what will be the user name and password for my database ?

And where can I find the same 2/4/2018 1:24 AM | Abhinav Sharma

# re: SQL Server: Moving DATA and LOG file to different location

What we need to get from the windows 10 recycle bin here is the website at http://recyclebinwindows10.com/ that we can use when we want something in windows 10. 2/9/2018 9:17 PM | jenny

# re: SQL Server: Moving DATA and LOG file to different location

Great point!

buy upc codes
2/23/2018 10:35 AM | Product Barcodes

# re: SQL Server: Moving DATA and LOG file to different location

I am almost certain about this data you shared on the grounds of cv specialists that it's extremely useful for everybody.
2/28/2018 3:37 AM | Alina Watson

# re: SQL Server: Moving DATA and LOG file to different location

That is exceptionally valuable performance has you share with us. As an expert writer at Online Assignment Help agency your writing skill really good and your post is too help full for everyone that is full of information. 2/28/2018 10:43 AM | Marianna Lewis

# re: SQL Server: Moving DATA and LOG file to different location

I have not idea about this SQL server something like Video Animation Inc i've tried this platform then they'll set my all SQL setup or files. 4/25/2018 10:45 AM | Betsy R. Thompson

# plusods.com

Buy Juul compatible pods at buy juul compatible pods. 4/28/2018 4:57 AM | Plus Pods

# compatible pods

Buy juul compatible pods at buy juul compatible pods 4/28/2018 4:59 AM | plus pods

# re: SQL Server: Moving DATA and LOG file to different location

Visit us to know the reviews of different assignment writing service provider.
allassignmenthelp reviews

5/2/2018 4:24 PM | james cook

# re: SQL Server: Moving DATA and LOG file to different location

[url=http://www.vinatrend.com/adidas-ultra-boost-parley]vinatrend.com/adidas-ultra-boost-parley[/url]
Unboxing Adidas Damen SL Loop-Runner c75348 Schwarz Schwarz-Weiß Shop 88% Rabatt. 5/25/2018 12:30 PM | pewwee pewwee

# re: SQL Server: Moving DATA and LOG file to different location

D'autant plus que vend des baskets de sport pour enfants, bébés, femmes et hommes, des maillots de football et du textile pour la pratique sportive c'est à dire à toute la famille. Les baskets de marque pas cher Adidas, Nike, Reebok, Le Coq Sportif ou Asics sont proposées sur la boutique en ligne .adidas tubular x pas cher
5/25/2018 12:35 PM | mimis

Post a comment