question

Rod avatar image
Rod asked

Creating a SQL Express database in my Documents folder

I'm trying to create a SQL Server 2008 Express database in the Documents folder on my development PC (a Windows 7 Ultimate box). What I do is, from SSMS, do I right-mouse click on the **Databases** folder, then I click on the Path button to try and locate it in my **Documents** folder under **C:\\Users\\Rod** (*i.e.*: **C:\\Users\\Rod\\Documents**), but all I can see is **C:\\Users\\Rod**. Why is that? Why can't I put the database into my Documents folder? And if the my Documents folder is indeed the wrong place to put it, where is the right place to put it?
sql-server-express
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sp_lock avatar image sp_lock commented ·
Possible UAC? Have you tried turning it off? Also, what service account is the SQL instance running?
0 Likes 0 ·
Oleg avatar image Oleg commented ·
@Jonlee Lockwood Looks like it is UAC, which restricts access to private folders and reverts the changes immediately after they are applied. I am glad to see SSMS filters the folders to include only those which are accessible :)
0 Likes 0 ·
Oleg avatar image
Oleg answered
This behaviour appears to be by design and it is related to the fact that the **Documents** folder is subjected to special permisions set. In other words, storing the files in the **Documents** folder will never happen. Here is an example. If you try to do it by the means of the script: use master; go create database [Test] on primary ( name = N'test', filename = N'C:\users\Rod\Documents\test.mdf' , size = 3072KB, filegrowth = 1024KB ) log on ( name = N'test_log', filename = N'C:\users\Rod\Documents\test_log.ldf', size = 1024kb , filegrowth = 10% ); go then you will get this:
Msg 5133, Level 16, State 1, Line 2 Directory lookup for the file "C:\users\Rod\Documents\test.mdf" failed with the operating system error 5(Access is denied.)
This explains why an attempt to browse to the **Documents** folder does not succeed. SSMS filters the dialog to only include those locations to which the account used to run SQL Server service has access. Windows 7 is very picky about permissions to private folders such as **Desktop** and **Documents**, which means that you will probably have to consider a different location to store the database and log files. I believe that even if you alter permissions to the Documents folder and grant the SQL Server account full access, the changes you made will be reverted by the OS immediately. Oleg
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Rod avatar image Rod commented ·
WOW, this is good to know. When I installed SQL 2008 Express, several months ago, I just let it take the defaults. Now things are different, as I want to specify some folder that is unique for each user. It seemed the Documents folder was the easiest choice, but clearly that won't work. It sounds like it would be a deployment nightmare. I guess I'll have to think of someplace different.
0 Likes 0 ·
Pavel Pawlowski avatar image
Pavel Pawlowski answered
As @Oleg wrote, you can create databases only where the Account udner which the SQL Server service is being executed has access. Genearaly you can create the databases everywhere, if you grant appropriate access rights to the account udnder which teh Service is running as Oleg also mentioned. I only add Information related to **Where is the right place**. So by general the right place is where you have configured SQL Server to store data and log files during installation. In the installation wizzard you were asked to provide those paths and if not a default were used in the SQL Server installation dierectory under Program files. For SQL Express this probably doesn't matter, but for larger installations and larger databases in is essential to point this paths to right places. Also on Standard and Enteprise versions of SQL server and big databases you will probably redistribute the database files accross several drives etc. This is a question of correct plannig of the database deployment. By general you should put the database files on the fastest possible drives to achieve best performance but also taking in mind the possible failure of the devices.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.