Select Page

SQL on Linux: Creating, Backing up & Restoring a Database

JP Chen | | September 18, 2019

Creating, backing up, and restoring a database are critical databases administration tasks that we must perform regardless of Operating System platform. Good news! If you had done these steps in Windows, the same steps apply in Linux. You just need to be aware of the file path structure.

Creating a Database and Verifying Its Data and Log Files

To create a database and verify its data and log files, perform the following steps:

1. Connect to your Ubuntu Server from your PuTTY remote terminal if not already connected.

2. Connect to your SQL Server:
sqlcmd -S localhost -U USERNAME -P ‘PASSWORD’

3. Create the database, DVSQLRocks:
create database DVSQLRocks
go

4. Specify the database to use:
use DVSQLRocks
go

you will see the message “Changed database context to ‘DVSQLRocks'”.

5. Create a table, major_conference, and insert two rows of data:
create table major_conference (id int, name nvarchar(50), start_date datetime)
go
insert into major_conference values (1, ‘SQL Saturday LA’, ‘2019-06-15’)
insert into major_conference values (2, ‘SQL PASS Summit’, ‘2019-11-05’)
insert into major_conference values (3, ‘AWS Re:Invent’, ‘2019-12-02’)
go


Figure 5-1: Create a database and table and insert data into a table

6. Find the location of the data and log files for the newly created databases:
select DB_NAME(database_id), physical_name from sys.master_files
where DB_NAME(database_id) = ‘DVSQLRocks’
go


Figure 5-2: Query the sys.master_files to find the location of the data and log files

Backing up a Database

To backup a database, perform the following steps:

1. Connect to your SQL Server if not already connected:
sqlcmd -S localhost -U USERNAME -P ‘PASSWORD’

2. Backup our sample database, DVSQLRocks:
backup database [DVSQLRocks] to disk = N’/var/opt/mssql/data/DVSQLRocks_20190615.bak’
go


Figure 5-3: Backup a database

If executed successfully, you will see the confirmation message as in Figure 5.3.

Restoring up a Database

To restore a database, perform the following steps:

1. Find the location of the backup file. In our example, it is /var/opt/mssql/data/DVSQLRocks_20190615.bak.

2. Connect to your SQL Server if not already connected:
sqlcmd -S localhost -U USERNAME -P ‘PASSWORD’

3. Change the database to master:
use master
go

Just as in SQL on Windows, you can’t restore a database if it is in use.

4. Restore the database. In this example, we will restore and replace the existing DVSQLRocks databases:
use master
go
restore database [DVSQLRocks] from disk = N’/var/opt/mssql/data/DVSQLRocks_20190615.bak’ with norecovery, replace


Figure 5-4: Restore a database

That’s it! For more info, please check out my latest white paper, SQL on Linux: Building Your Own SQL on Linux Lab.


Read This Next

SQL on Linux: Building Your Own SQL on Linux Lab

If you’re a SQL Server data professional interested in running in SQL on Linux and don’t know where to start, this white paper is for you. SQL Server expert, JP Chen details the essential steps to take including downloading and installing of the required software to build your own SQL on Linux lab on Windows PC.

Subscribe to Our Blog

Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.

12c Upgrade Bug with SQL Tuning Advisor

This blog post outlines steps to take on Oracle upgrade 11.2 to 12.1 if you’re having performance problems. Oracle offers a patch and work around to BUG 20540751.

Megan Elphingstone | March 22, 2017
code

Recover a Table from an RMAN Backup in an Oracle 12c

This blog post will is to show a table restore for one table in a container database

Megan Elphingstone | February 2, 2017
Oracle DBA Skills

8 Things Every Beginner Oracle DBA Should Know

A checklist of eight critical skills and areas an entry-level Oracle DBA should be familiar with to succeed on the job.

Patrick Gates | November 30, 2016

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS