<html>

<head></head>

<body>
    <div class="item">
        <div class="item-header">



        </div>

        <div class="item-body">

            <p>Right-O Jeeves, I downloaded the Northwind backup for sql server, but how do I restore it with sqlcmd? The butler answered:</p>

            <p><code>RESTORE DATABASE Northwind FROM DISK='/tmp/Northwind.bak'</code></p>

            <p>But that produced</p>

            <pre>Msg 3201, Level 16, State 2, Server 61ea3d23c7fb, Line 1
Cannot open backup device '/tmp/Northwind.bak'. Operating system error 2(The system cannot find the file specified.).
Msg 3013, Level 16, State 1, Server 61ea3d23c7fb, Line 1
RESTORE DATABASE is terminating abnormally.
</pre>

            <p>Jeeves isn't in top form these days. Either that or he is still smarting over that incident of the purple socks. He left me scratching around and I figured out for myself that you need to move the file to the location where sql server's data is peristed. In my case, I started the docker container as :</p>

            <pre>docker run -d -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=*****'   -p 1433:1433 -v /var/opt/mssql:/var/opt/mssql -d microsoft/mssql-server-linux</pre>

            <p>This means that the Northwind.bak file has to be copied to '/var/opt/mssql', and then</p>

            <pre>Directory lookup for the file "D:\MSSQLDataFiles\UNTC\DB_All\DB\NORTHWND.MDF" failed with the operating system error 2(The system cannot find the file specified.).
Msg 3156, Level 16, State 3, Server 61ea3d23c7fb, Line 1
File 'Northwind' cannot be restored to 'D:\MSSQLDataFiles\UNTC\DB_All\DB\NORTHWND.MDF'. Use WITH MOVE to identify a valid location for the file.
Msg 5133, Level 16, State 1, Server 61ea3d23c7fb, Line 1
Directory lookup for the file "D:\MSSQLDataFiles\UNTC\DB_All\DB\NORTHWND_log.ldf" failed with the operating system error 2(The system cannot find the file specified.).
Msg 3156, Level 16, State 3, Server 61ea3d23c7fb, Line 1
File 'Northwind_log' cannot be restored to 'D:\MSSQLDataFiles\UNTC\DB_All\DB\NORTHWND_log.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Server 61ea3d23c7fb, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Server 61ea3d23c7fb, Line 1
RESTORE DATABASE is terminating abnormally.
</pre>

            <p>This is paydirt! According to <a href="https://msdn.microsoft.com/en-us/library/mt710790.aspx">MSDN</a>, you need to make a close note of these paths, and change your restore command in sqlcmd appropriatelty. Something like:</p>

            <p><code>RESTORE DATABASE Northwind FROM DISK='/var/opt/mssql/Northwind.bak' WITH MOVE 'Northwind' TO 'D:\MSSQLDataFiles\UNTC\DB_All\DB\NORTHWND.MDF', MOVE 'Northwind_log' TO 'D:\MSSQLDataFiles\UNTC\DB_All\DB\NORTHWND.LDF'</code></p>

            <p>What, still the same error!! Then I found a stackoverflow answer that lead me down the right track. You have to use the actual file system paths on linux rather than the virtual paths that are shown in the error.</p>

            <p><code>RESTORE DATABASE Northwind FROM DISK='/var/opt/mssql/Northwind.bak' WITH MOVE 'Northwind' TO '/var/opt/mssql/data/NORTHWND.MDF', MOVE 'Northwind_log' TO '/var/opt/mssql/data/NORTHWND_log.ldf' Go </code></p>

            <p>What-ho! What-ho! What-ho!</p>


        </div>
    </div>


</body>

</html>

Restoring a database on ms sql server for linux (docker)

<html>

<head></head>

<body>
    <div class="item">
        <div class="item-header">



        </div>

        <div class="item-body">

            <h3>Ancient history.</h3>

            <p>These bundles are known by many different names like XAMPP, WAMPP, LAMPP etc. They all have a few things in common, such as&nbsp;&nbsp;PHP, MySql and Apache being bundle installer togther with a bunch of other stuff like Wordpress, Joomla and phpmyadmin. Sometimes Apache makes way for nginx. These bundles&nbsp;were very popular a decade ago and sadly even today they are used far more often than they should be. These were and still are primarily used by developers on their desktops. The reason for their popularity was the fact that one does not need to seperately install any of the servers but instead they are all installed with reasonable defaults.</p>

            <p>All these bundles are obsolete now rather they became obsolete as far back&nbsp;march 2012.</p>

            <h3>PHP Development server.</h3>

            <p>When php 5.4 was released in 2012, it completely eleminated the need to setup apache for development enviorenments. This is thanks to the inclusion of a PHP built in webserver. Since then all that was needed to test your scripts locally was to enter the terminal, move into the folder with the scripts and just type</p>

            <p><code>php -S localhost:8000</code></p>

            <p>And the built in webserver would immidiately start to deliver your files. You can view them in the browser just as you would with apache.</p>

            <h3>What about mysql?</h3>

            <p>Exactly! What about mysql? First of all do you really need mysql? Don't be put off by the lite in sqlite. It's a fantastic database and it's perfectly capable of powering almost all the websites out there. Let's admit it almost all the websites out there including this one get's very little traffic. A vast majority of the page views on almost any website doesn't result in a database write operation. Sqlite may suck at write operations but it handles read operations really&nbsp;well. So whenever possible switch to sqlite and do away with all that hassle.</p>

            <p>On the other hand if you really do need to use mysql, it's fairly straightforward to install the server from the downloads available for your operating system on the mysql website. Alternatively you could install mariadb or percona server.</p>

            <p>Using this approach helps to avoid having too many black boxes in your system. Often while working with mentees i've had to ask where are your errors logs? Where is the php.ini file why are you not using a debugger and an IDE and often they didn't have an answer due to the fact they had used a bundle. And these bundles make it very hard to customize each installation to meet your requirements + updating individual paakges are a nightmare.</p>


        </div>
    </div>


</body>

</html>

Monthly Archives for 2019 05

Restoring a database on ms sql server for linux (docker)

Why you shouldn't use PHP + MySql + Apache bundles