Creating a SQL Server database in Azure Data Studio (MAC)

Question

I'm studying a course about database management based on the Transact-SQL language. The first problem I founded was in my OS because I'm using a MAC. I had to create a SQL container in Docker and create a connection using Azure Data Studio, where I have my server, but my problem (where I'm blocked) is this:

when I have to create the FILENAME for this database I don't know where is it the directory which I have to save the .mdf and .ldf files because I'm running my server from a 'virtual' location which is not in my computer system.

How can I solve this?

Because I know nothing about containers, is the first time using Docker or similar...

I already tried to do it in a virtual machine running windows but is too much slow so I have discarded that option at the moment and I have to use SQL Server because is the language used in the course, I don't have any other option.

"When I have to create the FILENAME for this database I don't know where is it the directory which I have to save the .mdf and .ldf files " then why do you need to specify the filename/path if you don't know where you want them? Why not just omit it and the instance will use the default path? — Thom A
– Thom A ♦, Commented Feb 12, 2023 at 11:44
You can run SELECT * FROM sys.database_files when connected to the system databases - such as master - to see where it stores the files for those databases but the minimal SQL needed to create a database is CREATE DATABASE Foo so you don't need to specify this — Martin Smith
– Martin Smith, Commented Feb 12, 2023 at 11:50
Hi @Larnu I know it but the people who teach me demands me to specify name, filename, size, max_size and filegrowth. The only way that I was doing this kind of exercises was in an old windows laptop that I had. In that computer I can see the files ubication through the interface but is not the case in MAC where I run SQL in Azure Data Studio, not in SQL Server and files aren't inside the PC's memory but in a virtual containt and I don't know how can I have access to this datafiles. — molik
– molik, Commented Feb 12, 2023 at 20:07
Again, who is "they"? If "they" are telling you to specify the filepath, then presumably they are telling you what that path is, so what path did they give you and why doesn't that path work? — Thom A
– Thom A ♦, Commented Feb 12, 2023 at 20:08

Aaron Bertrand · Accepted Answer · 2023-02-12 15:27:02Z

3

On a default Docker container (which you can check with EXEC master..sp_helpfile;), database files get created in:

/var/opt/mssql/data/

So you can say:

CREATE DATABASE floob 
ON 
(
  NAME     = N'floob_data', 
  FILENAME = N'/var/opt/mssql/data/floob.mdf'
)
LOG ON
(
  NAME     = N'floob_log',
  FILENAME = N'/var/opt/mssql/data/floob.ldf'
);

This is the bare minimum set of properties you need in order to dictate the location of the files; it will take other properties (like size/growth) from model.

But unless the course materials dictate that your CREATE DATABASE command absolutely must include FILENAME parameters, you can generally leave them out when building some skunkworks stuff on your own machine, as they will be placed in the default location that you don't really need to know (until that drive fills up).

In production that's generally not the best idea, though, because we often want to customize size, growth, separate data from log, etc.

answered Feb 12, 2023 at 15:27

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

molik Over a year ago

Thank you very much Aaron for your response. there is no way for opening this files on docker (I mean as whether it had an interface like windows directories), isn't you?

Aaron Bertrand Over a year ago

@molik why would you ever need to "open" an mdf or ldf file? SQL Server can see them, that's what's important.

Aaron Bertrand Over a year ago

@molik Though I do mention here how you can access the folders inside a Docker container from the command line: Attaching / restoring databases inside a container

Collectives™ on Stack Overflow

Creating a SQL Server database in Azure Data Studio (MAC)

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related