Mssql linux restore backup

Restore a SQL Server database in a Linux Docker container

Applies to: SQL Server (all supported versions) — Linux

This tutorial demonstrates how to move and restore a SQL Server backup file into a SQL Server 2017 Linux container image running on Docker.

This tutorial demonstrates how to move and restore a SQL Server backup file into a SQL Server 2019 Linux container image running on Docker.

• Pull and run the latest SQL Server Linux container image.
• Copy the Wide World Importers database file into the container.
• Restore the database in the container.
• Run Transact-SQL statements to view and modify the database.
• Backup the modified database.

Prerequisites

• Docker Engine 1.8+ on any supported Linux distribution or Docker for Mac/Windows. For more information, see Install Docker.
• Minimum of 2 GB of disk space
• Minimum of 2 GB of RAM
• System requirements for SQL Server on Linux.

Pull and run the container image

Open a bash terminal on Linux/Mac or an elevated PowerShell session on Windows.

Pull the SQL Server 2017 Linux container image from Docker Hub.

Throughout this tutorial, docker command examples are given for both the bash shell (Linux/Mac) and PowerShell (Windows).

To run the container image with Docker, you can use the following command:

Host volume mapping for Docker on Windows does not currently support mapping the complete /var/opt/mssql directory. However, you can map a subdirectory, such as /var/opt/mssql/data to your host machine.

Host volume mapping for Docker on Mac with the SQL Server on Linux image is not supported at this time. Use data volume containers instead. This restriction is specific to the /var/opt/mssql directory. Reading from a mounted directory works fine. For example, you can mount a host directory using -v on Mac and restore a backup from a .bak file that resides on the host.

This command creates a SQL Server 2017 container with the Developer edition (default). SQL Server port 1433 is exposed on the host as port 1401. The optional -v sql1data:/var/opt/mssql parameter creates a data volume container named sql1ddata. This is used to persist the data created by SQL Server.

This example uses a data volume container within Docker. If you instead chose to map a host directory, note that there are limitations for this approach on Docker for Mac and Windows. For more information, see Configure SQL Server container images on Docker.

To view your Docker containers, use the docker ps command.

If the STATUS column shows a status of Up, then SQL Server is running in the container and listening on the port specified in the PORTS column. If the STATUS column for your SQL Server container shows Exited, see the Troubleshooting section of the configuration guide.

Open a bash terminal on Linux/Mac or an elevated PowerShell session on Windows.

Pull the SQL Server 2019 Linux container image from Docker Hub.

Throughout this tutorial, docker command examples are given for both the bash shell (Linux/Mac) and PowerShell (Windows).

To run the container image with Docker, you can use the following command:

Host volume mapping for Docker on Windows does not currently support mapping the complete /var/opt/mssql directory. However, you can map a subdirectory, such as /var/opt/mssql/data to your host machine.

Host volume mapping for Docker on Mac with the SQL Server on Linux image is not supported at this time. Use data volume containers instead. This restriction is specific to the /var/opt/mssql directory. Reading from a mounted directory works fine. For example, you can mount a host directory using -v on Mac and restore a backup from a .bak file that resides on the host.

This command creates a SQL Server 2019 container with the Developer edition (default). SQL Server port 1433 is exposed on the host as port 1401. The optional -v sql1data:/var/opt/mssql parameter creates a data volume container named sql1ddata. This is used to persist the data created by SQL Server.

To view your Docker containers, use the docker ps command.

If the STATUS column shows a status of Up, then SQL Server is running in the container and listening on the port specified in the PORTS column. If the STATUS column for your SQL Server container shows Exited, see the Troubleshooting section of the configuration guide.

Change the SA password

The SA account is a system administrator on the SQL Server instance that’s created during setup. After you create your SQL Server container, the MSSQL_SA_PASSWORD environment variable you specified is discoverable by running echo $MSSQL_SA_PASSWORD in the container. For security purposes, change your SA password:

Choose a strong password to use for the SA user.

Use docker exec to run the sqlcmd utility to change the password through a Transact-SQL statement. Replace and with your own password values:

Copy a backup file into the container

This tutorial uses the Wide World Importers sample database. Use the following steps to download and copy the Wide World Importers database backup file into your SQL Server container.

First, use docker exec to create a backup folder. The following command creates a /var/opt/mssql/backup directory inside the SQL Server container.

Next, download the WideWorldImporters-Full.bak file to your host machine. The following commands navigate to the home/user directory and downloads the backup file as wwi.bak.

Use docker cp to copy the backup file into the container in the /var/opt/mssql/backup directory.

Restore the database

The backup file is now located inside the container. Before restoring the backup, it is important to know the logical file names and file types inside the backup. The following Transact-SQL commands inspect the backup and perform the restore using sqlcmd in the container.

This tutorial uses sqlcmd inside the container, because the container comes with this tool pre-installed. However, you can also run Transact-SQL statements with other client tools outside of the container, such as Visual Studio Code or SQL Server Management Studio. To connect, use the host port that was mapped to port 1433 in the container. In this example, that is localhost,1401 on the host machine and Host_IP_Address,1401 remotely.

Run sqlcmd inside the container to list out logical file names and paths inside the backup. This is done with the RESTORE FILELISTONLY Transact-SQL statement.

You should see output similar to the following:

Call the RESTORE DATABASE command to restore the database inside the container. Specify new paths for each of the files in the previous step.

You should see output similar to the following:

Verify the restored database

Run the following query to display a list of database names in your container:

You should see WideWorldImporters in the list of databases.

Make a change

The following steps make a change in the database.

Run a query to view the top 10 items in the Warehouse.StockItems table.

You should see a list of item identifiers and names:

Update the description of the first item with the following UPDATE statement:

You should see output similar to the following text:

Create a new backup

After you’ve restored your database into a container, you might also want to regularly create database backups inside the running container. The steps follow a similar pattern to the previous steps but in reverse.

Use the BACKUP DATABASE Transact-SQL command to create a database backup in the container. This tutorial creates a new backup file, wwi_2.bak, in the previously created /var/opt/mssql/backup directory.

You should see output similar to the following:

Next, copy the backup file out of the container and onto your host machine.

Use the persisted data

In addition to taking database backups for protecting your data, you can also use data volume containers. The beginning of this tutorial created the sql1 container with the -v sql1data:/var/opt/mssql parameter. The sql1data data volume container persists the /var/opt/mssql data even after the container is removed. The following steps completely remove the sql1 container and then create a new container, sql2, with the persisted data.

Stop the sql1 container.

Remove the container. This does not delete the previously created sql1data data volume container and the persisted data in it.

Create a new container, sql2, and reuse the sql1data data volume container.

The Wide World Importers database is now in the new container. Run a query to verify the previous change you made.

The SA password is not the password you specified for the sql2 container, MSSQL_SA_PASSWORD= . All of the SQL Server data was restored from sql1, including the changed password from earlier in the tutorial. In effect, some options like this are ignored due to restoring the data in /var/opt/mssql. For this reason, the password is as shown here.

Stop the sql1 container.

Remove the container. This does not delete the previously created sql1data data volume container and the persisted data in it.

Create a new container, sql2, and reuse the sql1data data volume container.

The Wide World Importers database is now in the new container. Run a query to verify the previous change you made.

The SA password is not the password you specified for the sql2 container, MSSQL_SA_PASSWORD= . All of the SQL Server data was restored from sql1, including the changed password from earlier in the tutorial. In effect, some options like this are ignored due to restoring the data in /var/opt/mssql. For this reason, the password is as shown here.

Next steps

In this tutorial, you learned how to back up a database on Windows and move it to a Linux server running SQL Server 2017. You learned how to:

In this tutorial, you learned how to back up a database on Windows and move it to a Linux server running SQL Server 2019. You learned how to:

• Create SQL Server Linux container images.
• Copy SQL Server database backups into a container.
• Run Transact-SQL statements inside the container with sqlcmd.
• Create and extract backup files from a container.
• Use data volume containers in Docker to persist SQL Server data.

Next, review other Docker configuration and troubleshooting scenarios:

Источник

Restore a SQL Server database on a Linux based instance from a backup on a Windows instance

Problem

I explained how to install SQL Server vNext on Linux in my last couple of tips. Now let’s dig into administrative tasks. In this tip, I will show you how to restore a database on a Linux based SQL Server instance from a backup taken on Windows based SQL Server.

Solution

Restoring databases are basic admin tasks which DBAs do day to day. Recently Microsoft launched SQL Server for Linux based servers, so I will take a backup of a Windows based SQL Server database and will restore it on a Linux based SQL Server instance. The method to do this is quite similar to what we do in a Windows environment except for few steps which we will cover in this tip.

Restore database on a Linux based SQL Server

Step 1: The below picture shows the details of the source and destination servers. I have a SQL Server 2014 instance hosted on Windows Server 2012 R2 and a SQL Server vNext instance hosted on Red Hat Linux 7.2. I have a database named «Manvendra» on the Windows based SQL Server and I will take a backup of this database and restore it on the Linux based SQL Server.

Let’s verify the version of SQL Server on the source server along with the details of table in the Manvendra database. We will compare this output to the one we captured on the Linux server post restore.

Step 2: Create a backup of the database «Manvendra» on the Windows server either by executing the below command or using SQL Server Managemen Studio (SSMS).

We can see the full backup of database Manvendra has completed successfully in the above picture. Before going ahead, we need to check the data and log file names of this database because we need to use the MOVE option along with the RESTORE command as there is a different directory structure in Windows vs. Linux. Run the below command to get the logical and physical names of each database file.

I executed this command on the source server only, but you can run this command on your destination server as well after moving the backup file. If you are running the above command on the Linux server make sure to change to the location of the backup file.

Step 3: The next step is to copy this full backup file from the Windows server to the Linux server. There are many ways to transfer files between these servers, but I used WinSCP a free application to copy files to a Linux server. You can install WinSCP on your Windows machine. Once you open the application, it will ask you to enter the destination server details to establish a connection. Once the connection is established you will get a similar window like the below picture. The left side shows your local Windows machine and the right side shows the remote Linux machine.

To copy a file from the source to the destination server just drag and drop the file from the left side to the right side. You can see the full backup file Manvendra_Full.bak is now on both sides. The location of the file is highlighted on both sides. I have copied it to /home/Manvendra/. We can verify the file exists by running ls -lrt command on the Linux server. Next, connect to a Linux server using PuTTY as I did in the below screenshot then run the below command to display the files in location /home/manvendra.

Step 4: We can see Manvendra_Full.bak file has been copied to the target Linux server where we want to restore it on SQL Server. I will create a directory named backup under /var/opt/mssql and copy the backup file to folder /var/opt/mssql/backup as shown below.

We can see our backup file has been copied to /var/opt/mssql/backup.

Step 5: The next step is to connect to the SQL Server instance and then restore this backup file. We will use sqlcmd to make a database connection on the Linux server. Run the below command to make a connection.

Once you get connected to SQL Server, run the below RESTORE command to restore this database on the Linux server. As both servers have different directory structures we need to use the MOVE option with the RESTORE command. I am restoring all database files to location /var/opt/mssql/data. The name of the logical files and the physical files can be identified from the screenshot in Step 2 where we ran a RESTORE FILELISTONLY command.

Once you execute this command, it will restore the database, run a series of updates and let you know when the restore has completed as shown below.

Step 6: Now we will validate that the database restore was successful. We can check the sysdatabases system catalog view to get the database details. Run the below command to check for an entry for this database in sysdatabases.

We can see database «Manvendra» is a now a database on this server.

We will validate the rows of the table as we captured in Step 1. We can see the data does exist for this table.

We can also validate using SSMS on the Windows server. I have connected to both the source and destination servers and compared the objects and databases. We can see that both servers have the same database name and table name.

Next Steps

• Take the time to learn more about SQL Server vNext.
• Stay tuned for new tips based on SQL Server on Linux.
• Explore more knowledge on SQL Server Database Administration Tips.
• Read these other SQL Server on Linux Tips.

Источник