Nisarg Upadhyay
Backup file is copying

Manage SQL Databases in CentOS: Backup and Restore SQL Database

September 12, 2022 by

In this article, we will learn how we can backup and restore the SQL Database in CentOS. This article is the second article on the topic Manage SQL Server on CentOS. In my previous article, Install SQL Server on CentOS, we learned how we could install and create a SQL Database in CentOS.

  1. Backup and restore SQL database between windows 10 and CentOS Linux using SQL Server management studio
  2. Copy data of SQL server instance on windows 10 to the SQL instance on CentOS using SQL Server Data Tools
  3. Copy data of SQL server instance on windows 10 to the SQL instance on CentOS using SQL Server Import and export wizard

In this article, we will learn the backup and restore method to copy SQL Server data from Windows to CentOS.

I have created a virtual machine on a workstation and installed CentOS 8.0. I have downloaded and configured the stackoverflow2010 database on the SQL Server instance of Windows 10.

We are using WinSCP software to copy the backup file from windows to Linux. It’s a free SFTP client that is used to copy data between local and remote computers. To copy the data, it uses the following file transfer protocols.

  1. Secure file transfer protocol (SFTP)
  2. File transfer protocol (FTP)
  3. Secure Copy Protocol (SCP)
  4. Amazon S3: It’s a cloud storage service. You can copy between Amazon S3 and local computer
  5. WebDAV: Web distribution Authoring and versioning. It is an extension of the HTTP protocol that is used for remote file editing and modification.

You can download WinSCP from here. Installation is simple and interactive.

Review database schema

Before copying the SQL database from source and destination, it is advisable to review the database files, schema structure, and data. Run the following set of queries.

Run the following query to view the list of files and filegroups

Output

Filegroup

Run the following query to view the list of tables and data.

Query Output

list of tables and rows

Let us generate a copy-only backup of the stackoverflow2010 database. The backup is generated in the D:\SQLBackups\StackOverflow folder.

Once the backup is generated, let us copy it to CentOS. I have created a directory named SQLServer in the root directory of Linux. The directory path is /SQLServer. I have created another directory named SQLBackup in /SQLServer directory. We are copying the backup in the/SQLServer/SQLBackup directory. Create the directories by running the following command.

[root@localhost /]# mkdir /SQLServer

[root@localhost /]# mkdir /SQLServer/SQLBackup

The directory has read, write and execute permission to the user named root. To view the permissions, you can run the ls -l command.

[root@localhost /]# ls -l /SQLServer/

Command output

View permission on directory

Now, let us copy the backup files. Launch WinSCP. First, we must configure a session to copy files between servers. On the login screen, you can provide the login details. Enter appropriate configuration values to connect to the CentOS file share. Enter the following values of configuration parameters.

  1. File protocol: Specify the appropriate file transfer protocol. In our case, we are using SFTP protocol, so we selected SFTP from the drop-down list.
  2. Hostname and Port: Specify the hostname/IP Address and the port of the computer that you want to connect. We are connecting to my workstation, and IPAddress is 192.168.1.2. So, I have specified 192.168.1.2, and the port number is 22.
  3. Username: Specify the username of the remote machine. I am connecting using root user, so I have entered root as a username.
  4. Password: Specify the appropriate password to connect to the remote machine. I have entered the password of the root user.

Click on Login. Following is the screenshot of the Login screen.

Login to WinSCP

We can drag and drop the backup files between the D:\SQLBackup\Stackoverflow folder to /SQLServer/SQLBackup directory. See the following illustration.

Use WinSCP to manage sql database

The upload process begins.

Backup file is copying

Once the backup file is copied, let us check that backup file is copied or not. Also, we must check the permissions on the backup file. Run the following command.

[root@localhost /]# ls -l /SQLServer/SQLBackup/

Output

View permission on backup file

As you can see in the above image, the backup file is copied, and permissions are also applied properly. Let us restore the database in SQL Server on CentOS. To do that, run the following restore command.

Once the SQL database is restored, let us run a few queries to verify that database is restored successfully.

Run the following query to view the list of files and filegroups

Query Output

Filegroup in Linux

As you can see, filegroups are created. Now, Run the below query to view the list of tables and data.

Query Output

Tables and rows in Linux

As you can see, the table and data are restored successfully.

Summary

This article explains how to copy the data of the database from Windows 10 to CentOS. I have explained a backup and restore database method. Suppose you are planning to migrate the operating system of SQL Server from Windows Server to Linux. In that case, this article helps you set up a development environment which helps to set up a development environment. In the next article, we will learn how to copy the SQL Database from Windows 10 to CentOS using the SQL Server Import-export wizard.

Nisarg Upadhyay
Backup and restore, Linux, SQL Server on Linux

About Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration. He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com

253 Views