Backup and Restore Database Tasks Using SQL Operations Studio

https://www.mssqltips.com/sqlservertip/5463/backup-and-restore-database-tasks-using-sql-operations-studio/?utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=20180424

Problem

In my previous tips SQL Operations Studio Installation And Overview and SQL Operations Studio - Query Editor and Source Control, we explored how to connect to SQL Server using SQL Operation Studio, an overview of the tool, running queries with query editor enhancements, multiple configuration options, etc.

In this tip, we will see how to perform commonly used tasks such as database backups and restores along with advanced configuration options.

Solution

As explored earlier, SQL Operation Studio is lightweight, cross-platform Open-Source tool for database administrators, developers, development, and operations.

Some of the important features of SQL Operations Studio are:

• Cross-platform database management tool for Windows, macOS, and Linux.
• Query Editor with advanced coding features like peek definition, auto suggestions, error diagnostics, formatting, etc.
• Query Results Viewer with various formats of result sets such as JSON\CSV\Excel.
• Nice graphical informative Query execution plan
• SQL Server Connection Management Server Groups in various color codes to differentiate the environment.
• Source control Git integration
• Integrated terminal bash, PowerShell

Tasks in SQL Operations Studio

Once we connect to the database instance, right-click on the instance name and click Manage.

This opens up the Tasks window, which has these options (highlighted in yellow square below):

• Backup
• New Query
• Restore
• Configure

We explored New Query and Configure in my previous tips, let's explore the Backup and Restore tasks.

SQL Server Database Backup Options Using SQL Operations Studio

As a starting point, as you probably already know about different backup types in SQL Server, such as these:

• Full Backup
• Differential backup
• Log Backup

To execute a backup using SQL Operations Studio, click on the Backup task. This opens up the below window to set up a backup.

We can see are these options:

• Backup name: the default format is DBName-BackupType-BackupRunDate
• Recovery model: this shows the recovery model of the database for which we want to take the backup.
• Backup type: This shows the available database backup options for the database. For example, in Simple recovery model, only Full and Differential backups are available, so it only shows these 2 backup options.
• Copy-only backup: If we want to take a copy-only backup, tick this option to execute a copy-only backup.
• Backup files: It shows the backup location as the default backup directory. If we want to change it, click the "-" icon below to remove this backup location and then the "+" icon to add the file into the desired location.

There are some advanced configuration options available, which are shown below:

• Compression: By default, compression is set to use the default server setting. If required, we can change it to compress the backup or not compress the backup.

• Encryption: If Certificates are available for encryption, this encryption checkbox will be available. In my case, I do not have a certificate or asymmetric key, so this option is disabled.

• Media: We can set the option to append backup to existing backup set or to a new media set.
• Transaction log: If the database recovery model is full, we can select an option such as truncate the transaction log or backup the tail of the log.

• Reliability: Under this option, we can select the options to validate the backup set such as:

• Perform checksum before writing to media
• Verify backup when finished
• Continue on error

• Expiration: We can set how long to retain the backup in days. By default, it is set to 0 that means the backups never expire.

Executing Database Backup Using SQL Operations Studio

In this demo, I will be taking database backup with the default options. To execute a backup, we have two options:

Backup Database Using Script Option

When we click on Script, it generates the backup script in a new query editor. In the Task History, it shows status as Backup Database scripting succeeded.

After it has been scripted, we can click on Run to execute the backup of the script generated.

Backup Database Using Backup Option

Click on Backup at the bottom, will execute the backup.

Once the backup is in progress, we can see it in the Task History.

We can see the Backup Database succeeded information is logged in the Task History once the backup has completed.

We can also get the script by right clicking on the Backup Database succeeded in Task History as shown below.

We can see the script that backup task used for taking the backup.

Restore a SQL Server Database Using SQL Operations Studio

In the demo, we will restore the database from the backup we just created. The Restore task is in the Tasks section.

Once we click on Restore, this opens up the restore database window.

Let us go through the restore database details. There are three tabs to configure for a database restore:

1. General
2. Files
3. Options

General Tab

This tab is useful for setting up the source, destination database, restore plan, etc.

• Source: We can restore the database from the database or from the backup file. If we want to use a backup file, we can select it from the drop-down menu.

If we select the option Backup file, another window opens up for providing the backup file:

Destination: In this tab, we have to select the destination database.  Enter the target database name and it shows the restore date as of the last available database backup. In my case, it shows the last backup was taken  "04 April 10:08:34".

It also shows the restore plan along with details of First LSN, Last LSN, Full LSN, CheckPoint, Start date, End date, etc.

Files Tab

In this tab, we can configure the database files location and can view the restore file location with the file name.

By default, the Relocate all files option is disabled. If we want to move the files from other than the default location, check this option and enter the new location. In this demo, I want to replace all files into the C:\mssqltips folder.

We can see the files location is pointing to the new location mentioned above.

Options Tab

In this tab, we can choose restore options, recovery state, connection state, etc.

Restore Options:

• If we want to overwrite the existing database, check Overwrite the existing database (WITH REPLACE)
• To preserve replication settings, check Preserve the replication settings (WITH KEEP_REPLICATION)
• If the access to the database needs to be restricted, check Restrict access to the restored database (WITH RESTRICTED_USER)

• Recovery State- we need to specify the state of the database after the restore.

• RESTORE WITH RECOVERY: If we want to perform recovery of the database and make database ONLINE.
• RESTORE WITH NORECOVERY: If we want to apply further restores on this database and do not want the database recovery to be performed, select this option.
• RESTORE WITH STANDBY: If the database needs to be used in standby mode, select this option.
  • We also need to specify standby file location as well.

Tail-Log backup:

• If we need to take a tail-log backup before the restore, select this option. We also need to specify the Tail Log Backup Filelocation for taking backup.

Server Connections:

• The last option is to close existing connections to the destination database. Sometimes we face issue when restoring the database because users are still connected to the database. To restore the database, the database should have no users connected. Therefore, by selecting this option, we can close all existing connections and start the database restoration.

Script or Restore:

• Now for running the database restore, we can either generate a Script and execute the script or directly run it after clicking on Restore.

We can see the restore database progress in the Task history.

If we need to cancel the restore, right click on the Restore Database in progress status and click Cancel.

The Restore Database succeeded status is logged in the task history.

Right click on the Restore Database succeeded message and click on Script.

This generates the script used in the database restore operation.

Next Steps

• Stay tuned for further tips on SQL Operations Studio.
• Read more about New SQL Operations Studio Installation and Overview.
• Read more about SQL Operations Studio - Query Editor and Source Control.
• Read more about SQL Server Backups.

SQL Operations Studio - Query Editor and Source Control

https://www.mssqltips.com/sqlservertip/5375/sql-operations-studio--query-editor-and-source-control/

Problem

In my previous tip, I gave an overview of SQL Operations Studio which was introduced by Microsoft to work on cross-platforms and it contains many exciting features. In this tip, we will explore additional features of SQL Operations Studio. 

Solution

Pre-requisites: Please read the previous tip to get information about installation, an overview of the menu items, connection dialog box etc.

SQL Operations Studio Overview

In this tip, I will be using the February Public Preview release of SQL Operations Studio.

You can find the detailed installation steps in the previous tip.

Once we launch SQL Operations Studio February Public Preview, we get the below welcome message.

Now let's see what it has to offer.

Connection dialog box - SQL Operations Studio

In the February release of SQL Operations Studio, if we connect to an instance, we do not need to enter the database name manually, instead it provides the available databases as a drop-down list.  The previous releases did not provide the database drop-down list.

We can also find the recent connections in the recent history tab. This enables us to quickly connect to the previous connection.

Once we connect to the instance, we can either right-click on the connection followed by new query or click on a new query from the Tasks.

A New Query window opens so we can write queries similar to SQL Server Management Studio.

We can see these options in the query editor window.

• Run: To execute a query
• Cancel: To cancel running query, if required.
• Disconnect: We can change the existing connection.
• Change Connection: Change database context from the drop-down list
• Explain: To show estimated execution plan.

Query Editor Overview in SQL Operations Studio

Snippets

SQL Operations Studio has an extensive library of snippets built in. A snippet is a code template that has the basics for a command. We can speed up writing queries with these T-SQL Code snippets.

As soon as you start typing in the query editor it starts giving a suggestion as shown below.

It shows details about the selected operation to right. For example, if we select sqlCreateDatabase, it shows what this does and the T-SQL code to create a new database.

Just hit the Tab key on the keyboard and it puts the code into a query editor.

As we can see, 'DatabaseName' is highlighted, so we just need to give the 'DatabaseName' and run it. This will create the database for us with the default settings quickly.

Go to Definition and Peek Definition

Developers and database administrators need to view object definitions such as column names, primary key, foreign keys, etc. while working with objects. One way is to expand the table from the drop-down in the object explorer or open a separate query window and get information about the object.

SQL Operations Studio makes it even easier to view the definition of an object. Select the object for which we want to view the definition and right click to get options.

Click on Go to definition or press F12 and a new query window will open with the definition of the object.

Here is the definition of the object:

If we do not want a separate window to open and need to view definition inside the same query editor, click on Peek Definition or Alt+F12.

We can then review and copy columns from the window. 

To close the peek window, just click on the X in the upper right side of the peek window.

It also saves a copy of the definition in the temporary directory.

We can open the directory and see the object definition in a .sql file.

Running Queries and Viewing Results

Now let's execute a query and view the output.

Running a query in SQL Operation Studio looks similar to SQL Server Management Studio. It gives results below the query in the results pane.

There are few highlighted options to look at:

• Explain: Once we click on Explain, it opens up the query execution plan. The format for the execution plans looks good with nice graphics.

If we move mouse over any of the operators, it gives details about the operator.

• Export Results: Along with the query results, we can see it provides options to export data in different formats like CSV, JSON and Excel format.

Save a JSON, export data in JSON format as shown below:

The final option is View as Chart. It represents the data in different chart formats.

We can choose from the below chart types and check out the following examples:

• Count
• Image
• Pie
• Bar
• Horizontal Bar
• Line
• Scatter
• Time series

Bar Chart Example

Pie Chart

If we move mouse over the chart, we can get the details as well.

We have the option to select the data direction (Vertical/horizontal) along with legend position.

We can also copy and save the chart image if required.

Create Insight opens up JSON window as shown below. We will talk more about it future tips.

Source Control in SQL Operations Studio

SQL Operations Studio comes with a Git source control manager. We need to install Git (version 2.0.0 or later) to use these features.

If Git is not installed on the system, we get the below message to download Git.

Also, in the Output Screen we can see that it looks for git in the location shown below.

Click on 'Download Git', it opens a web page to download the software.

Download the software and start the installation process.

Provide the installation folder.

Select the components, we will use the default setup options here.

Select the program shortcut for the start menu folder.

Choose the default editor used by Git.

Choose HTTPS transport for the backend.

Select the line ending conversions.

In the next screen, we can configure extra options (i.e. enable file system caching, enable Git credentials manager, etc.).

Start the installation process by clicking on Install.

Once setup is completed, we get the below notification.

Now launch SQL Operations Studio and click on Source Control.

Now select the folder to use for the Git repository and click on Initialize Repository.

Git integration is now complete and we can see Source Control as GIT.

We get multiple options by clicking on the ...  icon.

Next Steps

• Install SQL Operations Studio and explore the benefits.
• Read more about the February release of SQL Operations Studio/
• Read the release notes for SQL Operations Studio.