❔ Restore database in Docker windows container
Hi.
I am going to rewrite a solution built in classic asp in .NET and thought I should dockerise the solution as I will need both the old and new systems to run in parallel. Due to the fact that ASP requires windows servers to run I am limited to windows containers.
After a lot of searching I found a 3'rd party image of sql server dev-edition for windows and made it running.
My question is: How can I write a docker command that restores a backup of the database to develop against?
56 Replies
Unknown User•16mo ago
Message Not Public
Sign In & Join Server To View
Thanks for the answer.
I tried using chatGPT which gave me this answer, but it doesn't work.
RUN sqlcmd -S localhost -U SA -P $env:SA_PASSWORD -Q "RESTORE DATABASE [$env:RESTORE_DB] FROM DISK = 'C:\SQLData\YourBackupFile.bak' WITH MOVE 'YourLogicalName' TO 'C:\SQLData$env:RESTORE_DB.mdf', MOVE 'YourLogicalName_Log' TO 'C:\SQLData$env:RESTORE_DB.ldf', REPLACE;"
But I will search some more in the docs 🙂
As it is now I can start the container and manually restore the database. I am just hoping that I can do it all automatically
Unknown User•16mo ago
Message Not Public
Sign In & Join Server To View
what should a "3rd party sql server dev edition" should be? the original one is free to use
and "doesnt work" is 0 info
if you just want to get your stuff backin non automated use ssms gui
When I try to use the official one I get an error message when trying to download the image from Microsoft. What I have understood from searching is that the windows image is not supported and only the linux one is.
which linux do you have?
whatever
I can't use linux as I have to host classic ASP. Therefore I must run it all in windows containers right now
you say you are on linux but you have a powershell command
and what error do you get with the command
Unexpected argument. and returned a non-zero code: 1
you have powershell on that container?
Unknown User•16mo ago
Message Not Public
Sign In & Join Server To View
Sqlcmd: 'DATABASE [XXXX] FROM DISK = C:\SQLData\XXX.bak WITH MOVE XXX TO C:\SQLData$env:RESTORE_DB.mdf REPLACE': Unexpected argument. Enter '-?' for help.
The command 'powershell -Command $ErrorActionPreference = 'Stop'; $ProgressPreference = 'SilentlyContinue'; sqlcmd -S localhost -U SA -P $env:SA_PASSWORD -Q "RESTORE DATABASE [$env:RESTORE_DB] FROM DISK = 'C:\SQLData\backupfile.bak' WITH MOVE 'XXX' TO 'C:\SQLData$env:RESTORE_DB.mdf', REPLACE;"' returned a non-zero code: 1
ok. It might be that I have no powershell in that image
Unknown User•16mo ago
Message Not Public
Sign In & Join Server To View
or pwsh
Unknown User•16mo ago
Message Not Public
Sign In & Join Server To View
sqlcmd -S localhost -U SA -P $env:SA_PASSWORD this is impossible that it works as its powershell syntax that you run in cmd
it cant workI thought about that as well. Maybe I have to start the container before I restore the database?
is your sql server online?
you are currently hardcore trying random stuff...
1. turn your sql server on
2. check that your login is valid
3. are your files where they should be
I am trying to start a development environment. I am not connecting to the real server.
whatever it is where you want to connect to
in addition to that if you dont need a ful install and just the db you could use localdb
which is meant for developers
I'm not used to work in docker. Therefore I'm kind of winging it right now to learn
how big is your developement db at max
in total
Quite small. 200Mb
the bak-file
which linux do you have?
ubuntu?
I'm on windows, so it is wsl for docker containers. But I think it is Ubuntu yes
you are on windows
then why do you not just install it regurarly or use localdb?
I wanted it to be general so colleagues could easilly work on it as well
but your pc wont run all the time
then you are at the same spot again
That is why I wanted the database in the container instead of my localdb
if you turn oyur pc off then the container is also off
Yes. But if I could run a sql server in a container and restore a demodb inside it. All of them could get one through the docker-compose. That was my thought at least
or you throw the mdf into a shared file system
and launch it through localdb
localdb is just a click in VS
its the engine to be able to open mdf files
ok. Thanks. I will look into that option.
Is it as simple in Rider?
uhhh
seems like
its only supposed to handle mdf files tho
like there is no sql agent
or management or always on
Thanks.
developer edition is meant to replicate your production db for testing ( with no real data .. else you get license problems )
as its a ful enterprise version
sql express is awful ( for companies who dont know what to do with their money )
and localdb is for developement purposes
you edit your mdf file
and give it your dbas so they can hook it into the developer edition and the testers can test then on a production equivalent edition
or you rent in your company one Wndows server with 1 core and get happy
1 core is more than enough
there you would let your dbas make it in always on developer edition so you have at any time a sql server no matter what
localdb is never bad to have though... you can just open mdf files which is nice to have ( max 10gb )
Yes. It might be that it is the best solution. But right now access to our databases are restricted and can't be accessed from anywhere else.
But I think we will open up a development server
you have a dba team? then let them do it... 2 1-core servers in always on cluster
so you push the management to them
less work for you
and less responsibility
it is a very small company where I recently joined. I have one colleague who is in contact with the server-host. All knowledge they have is probably 20 years old and related to classic asp
you are not allowed to store real user data into a dev system tho.. or "protected user data"
imagine it like microsoft could come any day and look at the data and you have to allow them to do so
if you are a small company
get these two 1 core servers
they dont need an sql server license as developer is free
and throw all developement instances on it
mssql can easily manage multiple instances on single core when only a few people work at it
Thanks 🙂
more cores is a waste of money
you will have to pay windows server license but i think there was a cheap license for small companies
mybe increase at some point to 2 cores... but i doubt that you wil lreach that
point
always on is easy to setup...which makes it the best per default
the way it works is also nice but the ease of use makes it bueno
you will need an Active Directory Availability Group listener and your 2 servers in a cluster
then you type that in in ssms and let it do the rest
ah and a gmsa account
to make it easier with host account
Thanks for your help. It is much appreciated. I haven't been handling db-related stuff before. Only focused on coding 🙂
Yes, I see 🙂
and i would tell you not to use ORM where the code is the truth source
but any other coder will tell you ORM is the best
😆
is there any particular reason to use mssql?
in the end you will result in paying licenses for the enterprise edition in production
at some point
mybe its not the best choice at all 👀
It is used in the old solution. My goal is to be more flexible in the future, but right now focus is on making the two systems running together
postgres is much more flexible than mssql , but some sort of more complexe
but its free
you will have to pay enterprisedb support at some point but thats a fraction of the enterprise license cost
I'm not sure how well the old system can handle that change right now. And as they did in the 90's they have no version control and use the rename the file versioning
bruh
mkay
keep it in mind
postgres has compared to mssql some advantages
one is
you can embed it entirely
with mysticmind postgresembed
microsoft discontinued a ful embedded mssql server in 2006
I will. I am also open to migrate some tables to Nosql if I find it suitable
also try phind.com as AI helper as he makes less nonsense answers and gives you the sauces frome where it got it
Thanks
Was this issue resolved? If so, run
/close - otherwise I will mark this as stale and this post will be archived until there is new activity.