SQL Server FILESTREAM Database backup overview January 16, 2019 by Rajendra Gupta The SQL Server FILESTREAM feature in SQL Server allows storing the large documents files directly into the file systems. In my previous articles, we explored the concepts of FILESTREAM feature in SQL Server. We also learned the folder structures, metadata information and the process of garbage collection. In this article, we are going to learn about the backup and restores in SQL Server. Read more »
Forecast SQL backup size October 23, 2018 by Esat Erkec This article will cover the process of analyzing and predicting/forecasting the size of a SQL backup as a means to better handle/manage retention of backups. One of the main database management tenets is “Do not lose your data”. According to this; a database administrator incurs huge responsibilities to protect data. Under these circumstances, taking database backups and archiving SQL backup files is a key task for database administrators. In data protection strategy, taking database backups and archiving backup file processes play the leading role. Especially, backup planning is very significant for disaster recovery scenarios because backup file will be used for restore operation after any failure or data corruption. For this reason, every dba must generate recovery strategies for possible disaster scenarios and ensure that these scenarios can be solvable. At the same time, these backup files must be tested for data integrity; thus process provides to evaluate the recovery time and integrity of backup files. In this Backup and Restore (or Recovery) strategies for SQL Server database article you can find all details about backup and restore strategies. Read more »
SQL Server database backup tools September 18, 2018 by SQLShack This collection of content presents an overview of top SQL Server database backup tools, with product page links for each of the tools and screenshots if available. If you noticed a tool we missed, please let us know in the comments below. Read more »
SQL Server transactional replication: How to reinitialize a subscription using a SQL Server database backup September 13, 2018 by Prashanth Jayaram A workload management is considered as a critical aspect of SQL Server transactional replication. Replication is the oldest of the high availability technologies in SQL Server and it is available since the inception of SQL Server. As a very mature technology, SQL Server transactional replication is also very robust and, in most cases, very straightforward to set up and manage. Read more »
SQL interview questions on database backups, restores and recovery – Part I July 30, 2018 by Prashanth Jayaram So far, we’ve discussed a lot about database backup-and-restore process. The backup database command is an online database copy of the SQL Server database and restore database command gives an option to test the consistency and integrity of the backup file. As we all know, the backup database command bound with many database options. Indeed, it facilitates the execution of specific backup database command that meets the business requirement. Read more »
Database Backup and Restore process in SQL Server – series intro July 26, 2018 by Prashanth Jayaram It’s a time to turn the corner from “DBA” to “DBA specialist”. This in-depth guide explores the importance of database backup-and-restore features and skills you’ll need to build good backup-and-restore strategies using the available tools and techniques. Along the way, you’ll pick up some interesting insights and most of the concepts of database backup and restore procedures. Ready to dive-deep into Backup-and-Restore procedures? Let’s go! Read more »
SQL Interview questions on database backups, restores and recovery – Part IV July 25, 2018 by Prashanth Jayaram In this article, we’ll see the how the backup-and-restore meta-data tables store the information in the MSDB database. Also, discuss several T-SQL statements to derive most useful information with reference to data purge, database growth, backup report, restore history and more. Read more »
SQL interview questions on database backups, restores and recovery – Part III July 25, 2018 by Prashanth Jayaram So far, we’ve discussed a lot about database backup commands. In this article, we’ll discuss more on database restore or database recovery processes. When you initiate a restore process, it undergoes a series of internal stages to restore or recover the data to the specific point of time. Read more »
SQL interview questions on database backups, restores and recovery – Part II July 25, 2018 by Prashanth Jayaram In this article, we’ll walk through, some of the refined list of SQL Server backup-and-restore, (or recovery) interview Q&A. Also, it includes the high-level overview and links of the “stairway to backup-and-restore series” articles for detailed information. As you read through the lists of Q&A, you’ll learn most of the features and concepts of Backup-and-Restore operations. Read more »
Backup and Restore operations with SQL Server 2017 on Docker containers using Azure Data Studio July 19, 2018 by Prashanth Jayaram In this 18th article of the series, we will discuss the concepts of database backup-and-restore of SQL Server Docker containers using Azure Data Studio. Before proceeding, you need to have Docker engine installed and Azure Data Studio configured on your host machine. This article covers the following topics: Overview of Azure Data Studio (ADS) How to use Azure Data Studio integrated terminal Definition of Docker containers Step by step instructions to initiate backup-and-restore of SQL Server 2017 Docker containers using the Azure Data Studio interface And more… Read more »
Multiple methods for scheduling a SQL Server backup automatically July 17, 2018 by Robert Seles SQL Server backups are an essential part of every good disaster recovery strategy. That is the good. But setting up such backups to run effortlessly, is the goal. In this article, we’ll review the types of backups, recommended practices and three different methods for automatically setting up SQL Server backups on a schedule. Note: these solutions can also be used in combination with one another Read more »
Understanding Backup and Restore operations in SQL Server Docker Containers July 12, 2018 by Prashanth Jayaram In this 17th article of the series (see the full article index at bottom), we will discuss the concepts of database backup-and-restore operations on SQL Server Docker containers. This is certainly the era of containers era and it is buzz right now, so let us understand the importance of backup-and-restore the databases on the Docker containers. Read more »
In-Memory Optimized database backup and restore in SQL Server July 6, 2018 by Prashanth Jayaram A SQL Server backup and restore strategy is an essential process to safeguard and protect critical data. The vital role of any DBA is to minimize the risk of data loss and preserve data modifications at regular intervals of time. A well-planned and well-tested backup-and-restore strategy always help to protect the organization with no data loss, or at least a minimum, from the many varieties of system and database failure. As such, it is recommended to understand the importance and implication of the backup-and-restore strategy. Read more »
Database Filegroup(s) and Piecemeal restores in SQL Server June 22, 2018 by Prashanth Jayaram So far, we discussed many de-facto details about SQL Server database backup and restore. In this 15th article of the series, we are going to discuss, the file-group and piecemeal database backup and restore process. Read more »
SQL Server Database Backup and Restore reports June 14, 2018 by Prashanth Jayaram In the previous articles, we discussed several ways of taking backup and testing the restore process to ensure the integrity of the backup file. In this article, we’re going to discuss the importance of the database backup reporting. This kind of reporting will be designed to eliminate any unnecessary risk that may arise with the safety and security of information. This is the reason, the high-level report; Daily Health Check report will be generated and sent to the SME or to the DBA responsible for IT Infra management. Read more »
Tail-Log Backup and Restore in SQL Server May 31, 2018 by Prashanth Jayaram A tail-log backup is a special type of transaction log backup. In this type of backup, the log records that have not been backed up are captured. This prevents data loss and maintains the log chain sequence. Read more »
SQL Server Database Backup and Restore operations using the Cloud May 29, 2018 by Prashanth Jayaram This article discusses the concept of SQL Server backup and the various components required to use the Microsoft Azure Blob storage service as a target for the backup. As we all know, disk and tapes were the default destinations of choice until the evolution of the cloud platform. In addition to this, now we can expand the native functionality of SQL Server backup to cloud storage; the Windows Azure Blob storage. This functionality was added first in SQL Server 2012. In general, backup and restore functionality to and from the cloud are similar to using disk or tape, with very few differences. Read more »
Backup Linux SQL Server databases using PowerShell and Windows task scheduler May 22, 2018 by Prashanth Jayaram 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 … Read more »
How to perform a page level restore in SQL Server May 18, 2018 by Prashanth Jayaram In this article in our stairway series on SQL Server backup and restore, 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. Read more »
Smart database backups in SQL Server 2017 May 14, 2018 by Prashanth Jayaram 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: Discuss checkpoints Discuss the enhancements made in the Dynamic Management View (DMV) sys.dm_db_file_space_usage for smart differential backups Discuss the enhancements made for the Dynamic Management function (DMF) sys.dm_db_log_stats for smart transactional log backup Understand the functioning of smart differential backup and its internals Understand the Smart transaction log backup process and its internals T-SQL scripts And more… Read more »
