Backup mssql database linux

Automate SQL Server Backup and Restore Tasks in Linux

Problem

You have several SQL Server tasks already running and working on Windows and now you want to repeat those SQL Server tasks in Linux. For example, you would like to kick off a restore of a few databases. But how? Here are some script ideas to get you started.

Solution

Linux shell scripts are easy and flexible, when you know how. This tip is a step by step example for you. In this case we will use a Linux ‘shell script’ to control along with sqlcmd and then automate it. The Linux sqlcmd is like the Windows version. See this tip if you are unfamiliar with sqlcmd.

Connect to SQL Server using a Linux script

Let’s build an example script carefully, step by step.

VIP: Go one step at a time. If a step does not work re-read it and try again.

Build your environment

1. You have a Linux system; these screen shots are from Ubuntu 16.04.
2. You have SQL Server installed. See this tip to install SQL Server on Linux.
3. Sqlcmd will need to be in your environment PATH for it to be accessible to these scripts.

Log into Linux and run this command to see if you have the SQL Server tools installed.

If nothing is listed then install the SQL Server utilities. Follow the tips in the SQL Server on Linux Tips category on installing SQL Server on Linux.

This command will tell you where sqlcmd exists.

The response will most likely be:

Step 1 — Build a simple connection script to your SQL Server instance.

Here is a simple script.

Tips: The #!/bin/bash is a convention in script writing. It shows where the shell you are using is located.

The other lines beginning with # are comment lines. Name your scripts at the top and save them first in an organized folder. Then add the code. I find that order of doing things clarifies your scripts purpose and keeps you on track.

You can use another user rather than sa, but we will be restoring some databases and so we will use sa.

Save the file. Then run that command by:

Or make it executable by the chmod command:

Success! We can see the version, date and instance name. We know sqlcmd is installed and working and we have a valid username and password.

Tips: Note that the

or tilde indicate the path to your login folder. So

/bin means your local bin folder.

Now proceed to step 2.

Step 2 — Let’s find the most recent backup we have in the backup area.

Let’s imagine you have production databases you wish to restore to test or development SQL Servers on Linux.

This Linux command will find the most recent backup in a backup area:

• The commands within the backticks (`) will cause the output to be set to the given variable name.
• The command of ls -t1 will list the backup files in one column in time order.
• The variable $BACKUP_NAME holds the path and the file name.
• The variable $FILE_NAME only holds the file name.

Even though we could sort on the backup file names it is best to let the operating system list the files in order. Because a file may be named with a date that is not the date the backup ran.

The pipe symbol (|) sends the output to the “head -n 1” command and that returns only the most recent file name.

I like to use capital letters for variables. Remember the back ticks execute the code.

Tip: Make sure you have no spaces around the equals sign when you set a variable.

The basename command gets only the file name not the path.

So now $FILE_NAME now holds the most recent backup file name of: Admin_backup_2018_1_3_21_04_09.BAK

Step 3 — Copy the backup to the standard location

The sudo command will ask for our password then copy the source file to the standard SQL Server backup location.

And if that location does not exist you will need to create it, but only once.

The -u “copy only when the SOURCE file is newer than the destination file or when the destination file is missing”.

Step 4 — Use the Linux variables and track what we do.

SQL Server keeps records in msdb, see this tip to track backups we have restored.

And now we can use Linux variables in a T-SQL script.

If it helps your work flow, first check to see if we have already loaded that backup. If a file has not been restored we restore it.

Well done. We have automated a recovery.

Step 5 — More automation

Let’s list the full script and think: How can we improve it?

Where can we automate? What about any place we have the database name?

If we replace the database name with another variable we can restore any, non-system, database.

The same script with more automation. The $1 variable below means the first argument supplied to the script.

Notice that the database file names in the database backup will need to be named Admin and the log file name Admin_Log for this script to work. If you have simple two file databases (i.e. database and transaction log) that were created with the default naming then it will work. Databases with more than two files will require extra scripting on your part.

This, more automated, script is run by:

And if we want to load many databases we can use several commands.

In each case the $1 variable holds the first parameter.

The variable $1 will hold the database name provided on the command line at the time you run the script.

Note: the databases are restored, but user logins may still need to be repaired.

Step 6 — Fix a user

You possibly will be restoring production databases to test and development servers. In that case automating the user security would be a good idea.

Let’s build a simple script to do that. The usage would be:

Here is the full script.

Step 7 — Automate with SQL Server Agent

I suggest that you use the SQL Server Agent to automate the backup task.

The SQL Server Agent is available on Unix.

Here are more tips on SQL Server on Linux.

Conclusion

Tip: Sometimes we spend a long time creating an automation tool. If you forget you created, debugged, tested and used it then you lose the efficiency gained by automation. A tip to help you remember is to create some system to remind you.

Reminder tip #1 — Each time you log in get a list of your automation tools.

Add this command to the bottom of your

Reminder Tip #2 — A Wiki page entry, a summary page or something like this register.

Automation Summary Register

/bin (When logged in as joe)

/bin (When logged in as smith)

/bin (When logged in as smith)

#	Location	Command	Parameters	Description
1	load_database.sh	Database_Name	Restores the most recent backup in /files/secured/Backups/Database_Name To the default instance on localhost.
2	Connect.sh	None	The very simplest way I can check if the SQL Server instance is running.
3	fix_account.sh	Database_Name user_name	Fixes orphans and adds executor role.

Next Steps

• Think about adding the call to the fix_account.sh script from the load_database.sh script.
• What commands have you built? Are they in a summary register or displayed when you log in?
• Check out more operating system commands available to you.
• Check out more SQL Server and Linux tips.

Related Articles

Popular Articles

About the author

Graham Okely is a contract SQL Server DBA and has been working with database systems since 1984 and has been specializing in SQL Server since 2007.

Article Last Updated: 2020-10-15

Comments For This Article

Wednesday, September 16, 2020 — 3:31:24 AM — Graham Okely	Back To Top (86485)
Hi Muneeb Check this link it lists limitations for Linux and SQL Server Agent jobs. https://cloudblogs.microsoft.com/sqlserver/2017/03/17/sql-server-on-linux-running-jobs-with-sql-server-agent/ Regards Graham

Tuesday, September 15, 2020 — 10:29:18 AM — Muneeb Ashraf	Back To Top (86480)
Hi Graham, Thanks for the reply. Can i use Linux commands like mkdir , tar in SQL agent job. Just learned that in Windowsi «CmdExec» does OS tasks but probably in Linux based SQL currently not available i think i may have to schedule my Linux commands and SQL agent job to run 1 by 1. Thanks again

Tuesday, September 15, 2020 — 9:05:51 AM — Graham Okely

Back To Top (86479)

Hi Muneeb Thanks for your question. I have not found a simple way of encrypting passwords when using crontab. I suggest that you use the SQL Server Agent to the backup task. And all regular SQL Server tasks. The SQL Server Agent is available on Unix. https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup-sql-agent?view=sql-server-ver15 And yes, you can create a stored procedure to do backups. This site has well tested maintenance system: https://ola.hallengren.com/ Regards Graham Okely

Tuesday, September 15, 2020 — 7:35:31 AM — Muneeb Ashraf

Back To Top (86473)

Hi, im using SQL server on linux and uptill now using backup with Cronjob but for audit requirements want to automate without mentioning clear text password in script. im aware of Environment variable usage but it still would be visiblle. My current bash script creates a folder with respective date, backs up databases , creates TAR and deletes folder. In Oracle we can run a shell script in Procedure but in SQL server linux im not sure we can do that. It would be great if you can guide Muneeb

Thanks for your reply. Note that in the article I say «Databases with more than two files will require extra scripting on your part.» I have shown the simplest example. If you have multiple files you will need to adjust the script.

I will consider your request about future articles.

Thursday, February 22, 2018 — 9:06:26 PM — Graham Okely	Back To Top (75283)

Thanks for sharing. it is very practical and useful. my only concern is, in this case study we assume db has only two db files (data * log) in Restore action. ALSO, can you please provide some Lab and article in create multi-node sql server cluster on Ubuntu 16.04.

Источник

Backup and restore SQL Server databases on Linux

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

You can take backups of databases from SQL Server 2017 on Linux with many different options. On a Linux server, you can use sqlcmd to connect to the SQL Server and take backups. From Windows, you can connect to SQL Server on Linux and take backups with the user interface. The backup functionality is the same across platforms. For example, you can backup databases locally, to remote drives, or to Microsoft Azure Blob storage service.

SQL Server on Linux only supports backing up to Azure Blob storage using block blobs. Using a storage key for backup and restore will result in a page blog being used, which isn’t supported. Use a Shared Access Signature instead. For information on block blogs versus page blogs, see Backup to block blob vs. page blob.

Backup a database

In the following example sqlcmd connects to the local SQL Server instance and takes a full backup of a user database called demodb .

When you run the command, SQL Server will prompt for a password. After you enter the password, the shell will return the results of the backup progress. For example:

Backup the transaction log

If your database is in the full recovery model, you can also make transaction log backups for more granular restore options. In the following example, sqlcmd connects to the local SQL Server instance and takes a transaction log backup.

Restore a database

In the following example sqlcmd connects to the local instance of SQL Server and restores the demodb database. Note that the NORECOVERY option is used to allow for additional restores of log file backups. If you do not plan to restore additional log files, remove the NORECOVERY option.

If you accidentally use NORECOVERY but do not have additional log file backups, run the command RESTORE DATABASE demodb with no additional parameters. This finishes the restore and leaves your database operational.

Restore the transaction log

The following command restores the previous transaction log backup.

Backup and Restore with SQL Server Management Studio (SSMS)

You can use SSMS from a Windows computer to connect to a Linux database and take a backup through the user-interface.

Use the latest version of SSMS to connect to SQL Server. To download and install the latest version, see Download SSMS. For more information on how to use SSMS, see Use SSMS to Manage SQL Server on Linux.

The following steps walk through taking a backup with SSMS.

Start SSMS and connect to your server in SQL Server 2017 on Linux.

In Object Explorer, right-click on your database, Click Tasks, and then click Back Up. .

In the Backup Up Database dialog, verify the parameters and options, and click OK.

SQL Server completes the database backup.

Restore with SQL Server Management Studio (SSMS)

The following steps walk you through restoring a database with SSMS.

In SSMS right-click Databases and click Restore Databases. .

Under Source click Device: and then click the ellipses (. ).

Locate your database backup file and click OK.

Under Restore plan, verify the backup file and settings. Click OK.

Источник

Thursday, February 22, 2018 — 10:51:14 AM — Reza	Back To Top (75278)