Backup and Restore Automation using SQLCMD and SQL Server agent

Database administrators are often requested to refresh a database, mostly to create a live copy of the production database to the test or development environment. This is done so that the development or the test environment closely resembles the production environment; this way, we prevent many undesirable issues when running the application in the production environment.

Many developers, at times, are required to write and debug code on the production copy of the database, so it makes more sense to refresh the database on regular basis.

Let’s build the process to automate the database refresh activity using a simple backup-and-restore method and scheduling it using SQL Server Agent.

  • Restore the database to the same server with a different name


  • Restore the database to a different server using sqlcmd and Robocopy utility

  • Schedule the job


Let’s take a closer look at the process to see how this works.

Continue reading Backup and Restore Automation using SQLCMD and SQL Server agent


Posted in SQL Server Backup | Tagged , , | Leave a comment

Backup Linux/windows SQL Server databases using PowerShell and Windows task scheduler


This article is an in-depth guide on how PowerShell can be used to maintain and manage SQL backup on Linux and Windows machines.

Here’s an outline of what this article is all about:

  • Introduction
  • Technical specifications
  • How to load SQL Server modules on Windows machine
  • Security – Credential Management
  • The objectives of Backup and Restore
  • Library Linkage
  • How SQL Server 2017 backup feature is used on Linux
  • And more …


Tools to take SQL database backups, such as sqlcmd, SSMS, and PowerShell, are common to all platforms supported by SQL Server. In this post, I’m going to show how you can backup your SQL Server 2017 databases on a Linux machine using PowerShell.


  • RedHat Server 7.3 or CentOS 7.0 or higher
  • SQL Server 2017 or higher
  • SQL Server Management Studio (SSMS) 16.5 or higher

Flow Diagram

further reading

Backup Linux SQL Server databases using PowerShell



Posted in SQL Server Backup | Tagged , | Leave a comment

Data Recovery : Page level restore in SQL Server

In this article, we shall discuss the importance of understanding the internals of the page restoration for a database administrator. Most of the time, performing a page-level restore suffices for database availability. A good backup strategy is a key to recovery or restoration, and SQL Server provides us with an option to fix database pages at a granular level. Performing a page level restore in SQL Server is a clever option wherein the restoration of the entire VLDB database backup is not required.

Identifying the corruption and finding a solution is the key to successful restoration or recovery of the database. If the number of corrupted pages seems too large, and if the administrator feels that restoring the entire database from the most recent backup is a viable option, only in that case should the option be full restoration.

Page restore is a technique used to replace corrupted pages of information in a database with uncorrupted data from a backup. Page-level restore can be performed via SSMS or T-SQL. The intent of the restore is to fix one or more corrupted pages from the available backups. To identify corrupted pages, look for pages marked as “suspect” in the table, msdb syspect_pages.

In this series we will discuss the following topics:

  1. Introduction to page-level restore
  2. Different ways the data gets corrupted
  3. How to deal with corruption
  4. Restore a page from a full backup using SSMS and T-SQL
  5. And more…

Let’s explore the ways to corrupt and restore a page using a demo.

continue reading page level restore

Happy Learning!


Posted in Backup and Restore | Tagged , , , , , , | Leave a comment

Discuss smart database backup in SQL Server 2017

So far, we’ve discussed several phases of backup that starts with planning, creating, strategizing and implementing. In this article, we are going to see how database administrators can define the strategy to improve backup performance and efficiently manage backups in SQL Server 2017. The following are the topics of discussion:

  1. Discuss checkpoints
  2. Discuss the enhancements made in the Dynamic Management View (DMV) sys.dm_db_file_space_usagefor smart differential backups
  3. Discuss the enhancements made for the Dynamic Management function (DMF) sys.dm_db_log_stats  for smart transactional log backup
  4. Understand the functioning of smart differential backup and its internals
  5. Understand the Smart transaction log backup process and its internals
  6. T-SQL scripts
  7. And more…



A checkpoint is a background process that writes dirty pages to disk. A checkpoint performs a full scan of the pages in the buffer pool, lists all the dirty pages that are yet to be written to the disk, and finally writes those pages to the disk. In SQL instances that do not have many dirty pages in the buffer pool, this is a trivial operation. However, with the SQL instances that have OLTP databases, use more memory and/or involve sequential scanning of all pages, the performance of the system could be impacted.

With SQL Server 2012, indirect checkpoints were introduced. In this case, the dirty page manager manages the dirty page list and generally keeps tracks of the all the dirty pages modifiers of the database. By default, it runs every 60 seconds and tracks the dirty pages that need to be flushed.

For example, if an indirect checkpoint is set to 120 seconds then individual pages would be flushed around 120 seconds after that they were made dirty. This will impact I/O. I/O also depends on the number of dirty modifiers lists of the database; we may even see a significant spike in the set target interval and see small hits as the pages change. This eliminates overhead to tracing and flushing the dirty pages which result in significant improvement in backup performance. To review further, go to the reference section below to understand more about the checkpoint and backup performance improvement process in detail.

On a database where an automatic checkpoint mode is set, tracing of the entire unit of the buffer pool is necessary to evaluate for possible dirty pages. On the other hand, with an indirect checkpoint, only those pages of the buffer pool that are dirtied have to be dealt with.

For example, let’s consider a system with 2 TB of memory that contains ~250 million buffer units and has 100 dirty pages. Assume that it requires 5 CPU cycles to trace and identify the status of each buffer unit. To traverse 250 million BUFs, it would require 1.25 trillion CPU cycles—and to what? Simply to find 100 positive dirty pages in the automatic checkpoint mode! However, 100 dirty pages are hardly anything with the indirect checkpoint mode. The entire buffer pool scanning time is eliminated.

further reading smart database backup

Happy learning!


Posted in SQL Server Backup | Tagged , , , | Leave a comment

Mssql-cli Command-Line Query Tool

A recent announcement on the release of several SQL Server tools has raised expectations across various groups. Product requirements and business are almost always a trade-off, and striking the right balance in a product in terms of the toolset is a sign of a successful product. After testing the SQL Operations Studio, I feel that it’s a promising tool for many developers, administrators, and DevOps specialists. In my opinion, the mssql-cli tool adds another feature to SQL Server in order to make it a leading database product.

Microsoft announced mssql-cli, a SQL Server user-friendly, command line interactive tool hosted by the dbcli-org community on GitHub. It’s an interactive, cross-platform command line query tool. The public preview release of mssql-cli is available for testing. mssql-cli is based on Python and the command-line interface projects such as pgcli and mycli. Microsoft released this tool under the OSF (Open Source Foundation) BSD 3 license. We can find its source code on GitHub. Now, the tool and the code are available for public preview. The tool is officially supported on Windows, Linux, and MacOS, and is compatible with Python versions 2.7, 3.4, and higher.

mssql-cli improves the interactive CLI experience for T-SQL and includes support for SQL Server, MySQL, and PostgreSQL. The SQL Tools Service is the micro-service that forms the backend and based on .NET SDK Framework.

mssql-cli is a little ahead of sqlcmd in terms of functionality and enhancements. Some of these features are introduced below:


  1. T-SQL IntelliSense
    • This provides an array of keyword references that can be easily accessible during the development process.
    • The parameter or metadata population is contextual.
    • It also provides a quick reference.
  2. Syntax highlighting
    • This feature helps in identifying the troubleshooting of typo or syntax errors.
    • It helps to complete keywords and snippets automatically, thus, improving efficiency.
    • Autosuggestion and auto-completion of tasks where needed are available.
  3. Query history
    • It provides a way to store the history.
    • This tool is no different than many other editors—use the up/down arrow keys to select the desired SQL statement.
    • The advantage of this tool is to automatically suggest commands from the history.
  1. Configuration
    • A configuration file is used to configure the initial settings and parameters.
    • By default, the configuration file is stored to the following path:
      • Important file location paths on Linux
        • Configuration file – ~/.config/mssqlcli/config
        • Log file ~/.config/mssqlcli/config/mssqlcli.log
        • History file – ~/.config/mssqlcli/config/history
      • Important file location paths on Windows
        • Configuration file – %USERPROFILE%\AppData\Local\dbcli\mssqlcli\
        • Log file – %USERPROFILE%\AppData\Local\dbcli\mssqlcli\mssqlcli.log
        • History file – %USERPROFILE%\AppData\Local\dbcli\mssqlcli\history\
  2. Multi-line queries

Linebreaking within a query is very similar to what we have in SQL. To provide another example, in many languages, even shell languages, we can break up a single long command or a chain of commands into multiple lines. In PowerShell, we break the line with a backtick (`). In BAT/CMD, we use ^ to indicate that we have not yet done to issue commands to the shell.

  1. Mssql-cli modes

mssql-cli works in two very popular modes right out of the box: VI and EMACS. Those who are familiar with UNIX would instantly recognize these tools—text editors, to be precise. Most VI (and VIM—or VI Improved) users talk about “thinking in VI(M)”, which essentially means that the editor is so friendly and minimal that typing on it is almost the second nature to most VI users. If you’re one of them, you should feel right at home using this tool in the VI mode.

If you’re one of the EMACS users, you can enable or even customize this mode on mssql-cli. EMACS has a library of the LISP code that enhances EMACS and extends its capabilities.


Further reading mssql-cli

Happy Learing!



Posted in SQL tools | Tagged | Leave a comment

Automate SQL Server Database Restoration using bacpac with PowerShell or Batch techniques

Data is the key to your organization’s future, but if it’s outdated, irrelevant, or hidden then it’s no good. Maintenance and administration of databases take a lot of work. As database administrators, we often tend to automate most of these repetitive tasks. A database refresh is one of the most common tasks performed by most of the administrators as part of their daily routine.

Today, database refreshes are quite frequent because of Continuous Integration (CI) and Continuous Deployment (CD). In most of the cases, testing requires a separate but current production dataset to ensure the validity of the desired result.

In today’s world, we rely more on third-party tools to perform a Backup and Restore of databases. With many advanced tools and techniques, this is a pretty straightforward approach. Think of the real-world scenarios where customers rely on the native SQL Tools and techniques. Creating automated database refresh tasks regularly will have a huge impact on the quality of the release management cycles and would save a lot of time for the database administrators.

There are many ways to automate this, some of which are:

  • PowerShell
  • SqlPackage

In this article, we about the following:

  • Details of Sqlcmd
  • The use of the cross-platform tool, Sqlpackage
  • Automation using Windows batch scripting
  • And more…

Using sqlcmd provides flexible ways to execute T-SQL and SQL script files. As its available on Linux, Windows, and Mac, this command line utility plays a vital role in managing the database restore operations in a DevOps pipeline.

PowerShell script to automatically create a bacpac file and restore the database using the created bacpac, using SqlPackage.exe

This section deals with the preparation of a PowerShell script to automate database restoration using the SqlPackage tool which is part of the SQL Server Data Tools suite.

The first step is to prepare and set the environment variables. The SqlPackage tool is installed under C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin. The script uses sqlcmd and SqlPackage tool; make sure that the path variable is updated accordingly.

  1. The input parameter section lists the source, target SQL databases instance and folder for extracting the bacpac file
  2. Create the bacpac file using export action type
  3. Select the latest bacpac file for further database restoration action
  4. Drop the destination database using the sqlcmd command
  5. Restore the database using import action type.


Continue reading sqlpackage

Happy Learning!


Posted in SQL Server Backup | Tagged , , | Leave a comment

SQL Server: Database snapshots vs Database backups

In the article, we’ll walk through the concepts to understand database snapshots, and their benefits and limitations. This article will help you decide when to use a database snapshot, and when to use a backup. In some cases, the database relying on database backup and restore operation is not a viable option. Let’s dig into the concepts of database snapshots and see how it differs from a database backup.

Database snapshots are like a view of a database as it was at a certain point in time. It is a read-only copy of the data and the state of the pages, which are made possible using a pointer file called the sparse file. A snapshot contains the original version of pages, and changes in the source since the snapshot were created. However, remember that this is not a full copy of the database.

Snapshots are typically used for purposes of audits and reporting. Another use for snapshot backups is that multiple snapshots can be created for a database, and these can be taken at different points in time. This helps with period-over-period analyses.

It is important to understand that database snapshots are directly dependent on the source database. Therefore, snapshots can never substitute your regular backup and restore strategy. For instance, if an entire database is lost, it would mean its source files are inconsistent. If the source files are unavailable, snapshots cannot refer to them, and so, snapshot restoration would be impossible.

Snapshots can be created using the CREATE DATABASE command along with the AS SNAPSHOT option. A snapshot always starts with a near-zero size. This is because a snapshot store changes to the database since the snapshot was created. As changes in the database occur, the snapshot starts to grow, and may even see significant variation in size. Therefore, it is always recommended to keep an eye on them to avoid low-disk-space alerts.

The database snapshot feature is made available in all editions starting with SQL Server 2016 SP1.

Feature Enterprise Standard Web Express with Advanced Services Express
Database snapshot Yes Yes Yes Yes Yes

Points to note

  1. SQL Server Management Studio does not provide a graphical interface for creating snapshots; the only way to create them is using TSQL commands.
  2. The snapshot file name accepts arbitrary file extensions.
  3. SQL Server does not support backup operations over sparse files. In other words, sparse files cannot be backed up.
  4. A database snapshot appears to never change, because read operations on a database snapshot always access the original data pages, regardless of where it resides.
  5. After a page has been updated, a read operation on the snapshot still accesses the original page from the source database, and only the modified pages from the sparse file, also known as the side file.
  6. DBCC commands use an internal reference of database snapshots to validate the required transactional consistency of the database.
  7. When we start DBCC CHECKDB, a hidden database snapshot is created. There is no control, however, over these files, since these are created as alternate streams of the files.
  8. Alternate streams are not used since SQL Server 2014. And the database snapshot is created at the same location as the existing database.
  9. The database snapshots don’t reserve any space; the growth of the snapshot is directly proportional to the transaction rates that occur on the source database.
  10. The mechanism of reverting the database snapshot doesn’t work on an offline or a corrupted database. Also, reverting doesn’t work if any of the source files that were online when the database snapshot is created are offline during reversion.
  11. A database snapshot primarily depends on the side file for each of the data files in the source database. These side files are known as sparse files. Space allocations for these sparse files are made only for the modified portion of the data in the corresponding database file. It doesn’t include an allocation for the remaining portions of the source database—only the changes.
  12. The side page table stores indicator bits which represent data validity and include an in-memory bit map.
  13. During a read operation, a database snapshot always accesses the original data pages, regardless of where it resides. In the example below, the data read operation is performed on pages 1 through 10. However, if pages 3, 6 and 7 are the only ones that have changed, the pages 1, 2, 4, 5, 8, 9, 10 are read from the source database, and pages 3, 6, 7 are read from the sparse files.

Continue Reading database-snapshots-vs-database-backups


Posted in SQL Server Backup | Tagged , | Leave a comment

SQL Server database Backup and Restore strategies

One of the most important roles of a database administrator is to constantly protect the integrity of the databases and maintain the ability to recover quickly in case of a failure. In light of this, it’s critically important to have a backup-and-recovery strategy in place in order to be ready for an emergency.

A key responsibility of a database administrator is to ensure that a database is available whenever it’s needed, and prepare for various scenarios wherein the availability or the performance is impacted. Therefore, if a database, for whatever reason, gets corrupted, gets dropped, gets accidentally deleted, or goes into an unusable state, it is a database administrator’s responsibility to bring the database back up in a working state with little to no loss as per the defined service level agreements or government policies.

Database administrators must be prepared to deal with disaster recovery scenarios. One way of doing that is by testing SQL Server backup and restore strategies at regular intervals. This ensures seamless recovery of data. And seamless recovery means a quick recovery of systems with minimal or no data loss. Of course, a database administrator’s responsibility is also to safeguard data from the various data failures.

While designing the backup and restore plan, we need to consider the disaster recovery planning with reference to specific needs of business and the environment. For example, how do we recover from a case of multiple data failures across three prime locations in the environment? How long would it take to recover the data and how long would the system be down? What amount of data loss can the organization tolerate?

Another important point that database administrators have to concentrate on is the nature of the storage of data. This directly impacts the usefulness as well as the efficiency of the backup-and-restore process.

There are plenty of advanced techniques available such as Clustering, AlwaysOn, LogShipping and Mirroring that help ensure higher availability but still disaster recovery is all about having a well-defined and tested backup-and-restore process.

Points to consider defining good backup strategy including the:

  1. frequency at which data changes
  2. online transaction processing
  3. frequency of the schema changes
  4. frequency of change in the database configuration
  5. data loading patterns
  6. nature of the data itself

further reading  Backup and Restore Strategy

Happy Learning!

Please share your thoughts in the comment section…..


Posted in Backup and Restore | Tagged , , | Leave a comment

Overview of SQL Server Backup Types

SQL Server backups, in itself, is a vast subject; so vast, there are multiple books written about them. In this article, however, we are going to focus on the types of backups that are available to us, and understand how to pick what we need, and what aspects we base that decision on. This understanding would, in turn, help us decide our backup-and-restore strategy.

Following are the most common types of backups available in SQL Server:

  1. Full
  2. Differential
  3. Transaction log
  4. Tail Log backup

There are other backup types available as well:

  1. Copy-only backup
  2. File backups
  3. Partial backups.

Full backups

A full backup, as the name implies, backs up everything. It is the foundation of any kind of backup. This is a complete copy, which stores all the objects of the database: Tables, procedures, functions, views, indexes etc. Having a full backup, you will be able to easily restore a database in exactly the same form as it was at the time of the backup.

A full backup creates a complete backup of the database as well as part of the transaction log so the database can be recovered. This allows for the simplest form of database restoration since all of the contents are contained in one single backup.

A full backup must be done at least once before any of the other types of backups can be run—this is the foundation for every other kind of backup.

continue reading Backup Types


Happy Learning!



Posted in SQL | Tagged , , , , , , | Leave a comment