Sql-server – Best Practice for Folder strucure

best practicesinstallationsql server

I don't like my data files in C:\Program Files… , especially with a new system that has a smallish SSD as C: which holds mainly the operating system.

So on a recent install I put my data files into D:\apps\MSSQL\DATA

Where do you put your Sql Server data files, if you are not going with the default?

I am mainly looking for good habits to make my sql server deployments easier to use.

Best Answer

Never on C:\ if it can be avoided, though with smallish SSDs and VMs on laptops it's hard to have anything but C:.

So where else? Depends on the config. I've done like you for simple workstation installs: data, logs, tempdb all on D:. But then you get into more advanced implementations where you have a SAN and many spindles to choose from - there I've had groups of data files on M:\ and N:\, log files on L:\, tempdb on T:\, etc. Making deployment easier isn't the only goal you should consider... it's also reducing the need for re-deployment and/or moving of data/log files later. :-)

Related Solutions

Sql-server – Backup failure in SQL Server 2005

Well, suppose you have a command to execute your backup like this

BACKUP DATABASE [naass] 
TO  DISK = N'C:\program files (x86)\Microsoft SQL Server\MSSQL.3\MSSQL\Backup\Naass.bak'' 
WITH NOFORMAT, INIT,  
NAME = N'naas-Complete Database Backup', 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10

this will fail if the process that executes this command doesn't have the permissions to write in the mentioned folder. And Windows 7 (for very good security reasons) keeps the C:\program files folders and its subfolders write locked also to an administrator account.

You could mess with the default permissions and give to your folder above write permissions for your account but of course this is not the recommended way.
Much better, simply change the script to work on a different directory on your disks.

BACKUP DATABASE [naass] 
TO  DISK = N'D:\BackupSQL\naass.bak' 
WITH NOFORMAT, INIT,  
NAME = N'naas-Complete Database Backup'
SKIP, NOREWIND, NOUNLOAD,  STATS = 10

Of course this new directory should have the correct permissions.

Sql-server – AdventureWorks2012: “The FILE SIZE property is incorrect” error while trying to attach sample database “Adventure Works for SQL Server 2012”

Try downloading from AdventureWorks Databases – 2012, 2008R2 and 2008.

This might be due to corrupt installs.

Best Answer

Related Solutions

Sql-server – Backup failure in SQL Server 2005

Sql-server – AdventureWorks2012: “The FILE SIZE property is incorrect” error while trying to attach sample database “Adventure Works for SQL Server 2012”

Related Question