Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Moving Data and Log files

Author  Topic 

urmas80
Starting Member

20 Posts

Posted - 2014-07-30 : 04:37:17
Hi All,

I have an old SQL server 2008 R2, hosted on HyperV, which is going out of disc space. all Databases and logs are set on C:/ in the default drive and I have about 248 files in that folder.

What might be a best practice to change the architecture of the server from one drive to 4 drives? (C:system, D: data, E: Logs, F: Backups)?

Thank you in advance

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-30 : 12:43:09
We use mount points, like this:

F:\Backups (though NAS device is preferable)
F:\Data
F:\Log
F:\TempdbData
F:\TempdbLog

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2014-07-30 : 15:21:23
urmas80 that is what we use with subfolders similar to Tara's suggestion. However our virtual is not setup properly and I am in the process of getting them to change that.

djj
Go to Top of Page

andrew321
Starting Member

5 Posts

Posted - 2014-07-31 : 01:52:03
U can put 2 quantity on 1 raid and more than 2 or 4 on raid 10 means make C:/Windows, Backups, Batch Processing, Full Text Catalogs, Storage etc, D:/ for user defined databases and E:/ System Databases (TempDB), Transaction Logs etc according to your file size.
Go to Top of Page

urmas80
Starting Member

20 Posts

Posted - 2014-07-31 : 03:39:38
Guys,

You are taking this topic to a folder submission discussion.
My question is regarding a migration of the files to already submitted location. What is the best Practice to move the files to those folders from the default locations on C:\

*does it really matters if i set separate HD for each set of files?

Thank you in advance,

Uri
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-31 : 12:21:57
quote:
Originally posted by urmas80

Guys,

You are taking this topic to a folder submission discussion.




No those are not folders. They are mount points and point to their own disks. They are the same as using different drive letters. Starting with version 2005, SQL Server supports mount points.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2014-08-05 : 14:22:22
To move the files there are a couple of options:

1) Alter the database files (ALTER DATABASE ... MODIFY FILE (...) - setting the file names (both mdf and ldf) to the new locations. Once that is done, take the database offline, copy the files, bring database online.

2) Detach/Attach - detach the database, copy the files to the new location, attach database (note: reset owner of database after attaching)

3) Backup/Restore - backup the existing database, restore over existing database moving the files to the new locations.

When you create the new volumes, make sure they are formatted correctly with a 64K allocation unit. Whether or not you setup those as individual drives or mount points is up to you and your storage admin. There are advantages/disadvantages to each approach - and will depend on how those volumes are actually carved out of the storage for that host server.

Good luck...
Go to Top of Page

Shanky
Yak Posting Veteran

84 Posts

Posted - 2014-08-06 : 16:35:25
To move data and log files please only use TSQL command. Alter database modify file command. Check this Technet article for sample query http://technet.microsoft.com/en-us/magazine/gg452698.aspx.

Hope this helps

Regards
Shanky
http://social.technet.microsoft.com/wiki/contents/articles/24253.list-of-articles-by-shanky.aspx
Go to Top of Page
   

- Advertisement -