Tag: database backup

Accelerated Database Recovery in SQL Server 2019

Kenneth Igiri Leave a comment
Total: 16 Average: 3.7

An Overview of Traditional Recovery

As with all relational database systems, SQL Server guarantees the durability of data by implementing crash recovery. Durability in the acronym ACID which refers to the characteristics of transactions in relational databases means that we can be assured that if the database fails suddenly, our data is safe.

SQL Server implements this capability using the transaction log. Changes made by all Data Manipulation Operations in SQL Server are captured in the transaction log before being applied to data files (through the checkpoint process) in case it’s needed to roll back or roll forward.

Read More

Basics of SQL Server Task Automation

Haroon Ashraf Leave a comment
Total: 9 Average: 4.6

This is an introductory article about automation in SQL server primarily focused on the basic concepts. We will discuss some standard practices and a few examples to help beginners get started with SQL server automation.

This article also highlights the importance of automating SQL server tasks to save time and effort required to do these tasks manually.

Additionally, we will look at cases in which it is not a good idea to automate SQL server tasks despite the fact that automation saves time and effort. Read More

Automating SQL Database Maintenance Tasks using SQLCMD

Haroon Ashraf Leave a comment
Total: 4 Average: 4.3

This article is about automating SQL database maintenance tasks through SQLCMD utility which lets you run T-SQL commands directly from the command prompt without using SSMS (SQL Server Management Studio).

Typically, automating database tasks requires SSMS (SQL Server Management Studio) for scheduling jobs that run these tasks, but in this article, an alternative approach is used to automate database tasks without having to use the much-needed SSMS.

The SQLCMD utility can be a real time saver for database developers and DBAs since they can immediately run the necessary SQL scripts from the command line, and automating database maintenance tasks with the SQLCMD utility is a plus.

Read More

Implementing Automated Database Backup and Restore with Default Means

Evgeniy Gribkov Leave a comment
Total: 8 Average: 3.9

Introduction

You can find a lot of guides on how to backup and restore databases. In this one, we’ll show how this can be done using the default MS SQL Server means.

This example will cover a number of approaches – from checking the database’s integrity before backing it up to restoring the database from a previously created backup copy.

Read More

Monitoring Backups Across Instances

Kenneth Igiri Leave a comment
Total: 11 Average: 3.9

Introduction

In the last two or three months, I have been asked twice for a solution native to SQL Server that consolidates a backup report for several SQL Server instances across an enterprise. This question came from friends that did not necessarily want to spend money buying a tool but were more inclined to leverage the capabilities of SQL Server. I have thought about two possible ways to achieve this:

  1. Using Linked Servers, catalog views, SQL Agent Jobs and Database Mail
  2. Using Central Management Server

In this article, I will demonstrate the first and hope we shall have a second part of the article sometime later. Read More

Move Datafiles in SQL Server – Part 2

Kenneth Igiri Leave a comment
Total: 12 Average: 3.9

Introduction

In the first part of the two-part series, we explored migrating databases by first updating the master database system catalogs which contain records of the physical location of data files. In the current article, we shall look at two other methods of migrating databases in SQL Server which essentially have the same effect through the approach is different. Read More

Backup and Restore FILESTREAM-Enabled Database

Nisarg Upadhyay Leave a comment
Total: 8 Average: 4.3

In my previous articles, I explained how to create and configure the FILESTREAM feature in SQL server instance. Moreover, I demonstrated how to create a table that has a FILESTREAM column and hot to insert and delete the data from it.

In this article, I am going to explain how to backup and restore the FILESTREAM-enabled dataase. Moreover, I am going to demonstrate how to restore FILESTREAM filegroup without making database offline. Read More

Fixing Data Loss Using Log Shipping with Delayed Recovery

Kenneth Igiri Leave a comment
Total: 8 Average: 3.8

Introduction

Transaction Log Shipping is a very well-known technology used in SQL Server to maintain a copy of the live database in the Disaster Recovery Site. The technology depends on three key jobs: the Backup Job, the Copy Job, and the Restore Job. While the Backup job runs on the Primary Server, the Copy and Restore jobs run on the Secondary Server. Essentially the process involves periodic transaction log backups to a share from which the Copy Job moves same to the Secondary Server; subsequently, the Restore Job applies the log backups to the secondary server. Before all this starts, the Secondary Database must be initialized with a full backup from the Primary server restored with NORECOVERY option.

Read More

Configuring Azure SQL Long-term Backup Retention

Esat Erkeç Leave a comment
Total: 5 Average: 3.4

Backup is the most important case for database administration because it provides business continuity solutions for high availability and disaster recovery. For this reason, Azure SQL database (PaaS: platform as a service) can make a backup automatically and restore it at a point in time. However, the retention period of backup files changes according to the service tiers.

Read More