Running SQL Database Maintenance Tasks Using SQLCMD

Running SQL Database Maintenance Tasks Using SQLCMD
5 (100%) 1 vote

This article is about developing an advanced understanding of the Sqlcmd utility which lets you run T-SQL commands directly from the command prompt without needing SSMS (SQL Server Management Studio).

The article also highlights the importance of using Sqlcmd to perform some advanced-level database tasks that would otherwise require additional steps, e.g. getting connected to the database via a pre-installed database tool such as SSMS (SQL Server Management Studio) or SSDT (SQL Server Data Tools) followed by getting it ready to run SQL scripts against the desired database(s).

The Sqlcmd utility can be a great time saver for Database developers and DBAs since they can run the required SQL scripts right from the command line. Read More

Transferring Jobs and Schedules between Instances using T-SQL

Transferring Jobs and Schedules between Instances using T-SQL
Rate this post

Introduction

Quite often, there is a need to transfer Agent jobs to a different instance of MS SQL Server. Restoring a msdb database will not always be the best decision – there are cases in which you will need to transfer only the Agent jobs specifically, and these cases are not uncommon. Also, this wouldn’t work if the recipient MS SQL Server instance is of a newer version than the one you’re transferring the jobs from. So, how can you transfer the Agent jobs without restoring the msdb database?

We will look at an example of a T-SQL script which copies the Agent jobs from one instance of MS SQL Server to another. This solution was tested by transferring jobs from MS SQL Server 2012-2016 to MS SQL Server 2017.

Read More

Implementing Automated Database Backup and Restore with Default Means

Implementing Automated Database Backup and Restore with Default Means
4 (80%) 4 votes

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

Automatic Deletion of Forgotten Transactions in MS SQL Server

Automatic Deletion of Forgotten Transactions in MS SQL Server
4 (80%) 4 votes

Introduction

It is often the case when an MS SQL Server transaction is being forgotten by the initiator. The best example would be the following: a script is executed in SSMS which, via the ‘begin tran’ instruction, starts a transaction and an error occurs; however, ‘commit’ or ‘rollback’ do not go through and the execution initiator has left this query for a long time. As a result, more and more fluctuation appears when it comes to blocking the queries which request access to closed-off resources (tables and server resources such as RAM, CPU and the input-output system).

In this article, we’ll look at one of the ways you can automate the forgotten transaction deletion process.

Read More

SQL Server Database Replication

SQL Server Database Replication
3.4 (68%) 5 votes

Database replication is the technology to distribute data from the primary server to secondary servers. Replication works on Master-slave concept where Master database distributes data to one or multiple slave servers. Replication can be set up between multiple SQL Server instance on the same server, OR it can be set up between multiple database servers within same or geographically separated data centers. Read More

Overview of Data Compression in SQL Server

Overview of Data Compression in SQL Server
4.5 (90%) 4 votes

The database is a critical and vital part of any business or organization.  The growing trends predict that 82% of enterprises expect the number of databases to increase over the next 12 months. A major challenge of every DBA is to discover how to tackle massive data growth, and this is going to be a most important goal. How can you increase database performance, lower costs, and eliminate downtime to give your users the best experience possible? Is data compression is an option? Let’s get started and see how some of the existing features can be useful to handle such situations.

In this article, we are going to learn how the data compression solution can help us optimize the data management solution. In this guide, we’ll cover the following topics:

  • An overview of compression
  • Benefits of compression
  • An outline about data is compression techniques
  • Discussion of various types of data compression
  • Facts about data compression
  • Implementation considerations
  • and more…

Read More

Navigating SQL Server Error Logs

Navigating SQL Server Error Logs
3.9 (77.14%) 7 votes

Introduction

One of the key skills you need as a database administrator or an IT person is generally the ability to monitor systems very carefully. Lack of this key skill can lead to misdiagnosis when troubleshooting issues. SQL Server exposes a number of tools that can aid the DBA in troubleshooting problems that occur in production. The SQL Server Error Log and the SQL Server Agent Log are two of the most important facilities for troubleshooting SQL Server. In this article, we shall explore the ways we can manipulate the Server and Agent logs.

Read More

Move Datafiles in SQL Server – Part 2

Move Datafiles in SQL Server – Part 2
3.8 (76%) 10 votes

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

How to create Snapshot Replication

How to create Snapshot Replication
3.4 (67.5%) 8 votes

The concept of snapshot replication is simple. It generates and distributes the snapshot of schema and data of articles, appeared at a specific time and updates the changes on subscriptions. When we create snapshot replication, SQL Server creates a Snapshot Agent Job which generates a snapshot of publication database objects. Snapshot is stored on either network location or on the hard drive. You can define the interval to generate the snapshot using the SQL Job schedule. Read More