:::: MENU ::::

Posts Categorized / dbDigger

  • Jan 22 / 2018
  • 0
Generate restore script for last full backup of multiple databases
Backup and Restore, dbDigger, T-SQL Scripts

Generate restore script for last full backup of multiple databases

In a previous post i shared various commands and scripts used during databases migration process. Restore script there is not able to generate scripts for multiple databases in a single execution. Following script will generate restore script of multiple databases for their last full backup.

Following are the aspects to consider while using the script

  • Modify the select statement of cursor to get your required databases selected.
  • Modify the WITH parameters in script like REPLACE, KEEP_CDC, RECOVERY and STATS as per your requirement.
  • Any DB without FULL backup will not generate error but will be skipped in result.
  • Once result is retrieved you can review and change the file paths for files before starting the restore on destination server.
  • Setting the result to text will help to select all the scripts in one go.

Set SSMS result toText format

Following is the script to generate restore statements of multiple databases for their last full backups.


  • Sep 19 / 2017
  • 0
dbDigger, High Availability, Replication, SQL Server tools, SQL Server Training

SQL Server Replication refresher

During phase of training material preparation, a brief overview of replication architecture is with me. In coming lines i am going to put it in this post as a good starting point for replication learners.


Replication is a way of distributing data automatically from a source database to one or more recipient databases. It is built to sync the selected objects of a database not whole DB.  It encompasses set of objects in a database whose data is required to keep sync at subscriber databases. Replication architecture is not part of the SQL Server database engine, but a separate feature or application. It works through agents, triggers and scheduled jobs specific for this purpose. Replication can have uses in high availability systems or in a distributed system and can have different toplogies.

There are 3 main types of replication

  • Transactional Replication (one or two way)
  • Merge Replication
  • Snapshot Replication

Transactional Replication

This type of SQL Server replication is used for near real time or short delayed synchronization from publisher and subscriber. Initially a snapshot process synchronizes the subscribers with the publisher. All committed transactions on the publisher are then propagated to the subscribers in sequence. It may be one way transactional replication or two way.

In one way replication, modifications from publisher are synced on subscriber but no modification from subscriber is propagated back to publisher. While in case of peer to peer or two way replication, changes are propagated both ways and data remains sync on both sides for selected articles.

There are two options while propagating changes back from subscriber to publisher.

  • Immediate updating
  • Queued updating

Both methods can be used at the same time to maximize the availability and efficiency.

Immediate updating

In case of immediate updating changes made at the subscriber are synced to the publisher using a two-phase commit process managed by Microsoft Distributed Transaction Coordinator (MS DTC). Changes made at the subscriber must be made to both systems or are rolled back. This method is suitable for reliable, high-speed connection between the Publisher and Subscriber.

Queued updating

This method is suitable for less reliable connections. Writes updates at the subscriber to a queue. Publisher reads the queue and applies changes. This flow might result in conflicts due to latency.


Peer to Peer replication

Peer to peer replication was introduced in SQL Server 2005 and only supported in Enterprise edition of SQL Server version. It resembles in some features features with two way transactional replication. Main difference is that peer to peer replication involves more than just single publisher and associated subscriber nodes. It can span on multiple nodes and each will be kept in Sync. Also peer to peer replication is better while propagating the DDL of articles and locking mechanism.

Merge Replication

Merge replication is relatively complex type of replication. It does not use transactions but a set of defined rules used to resolve conflicts when data updated at multiple sites is synchronized. It tracks changes through triggers and metadata tables provides the means to implement part of a high-availability system. This also serves well the original purpose of serving mobile and disconnected users. It is designed for cases where the publishers are not in constant communication with the subscribers. After the initial snapshot synchronization, subsequent changes are tracked locally and the databases are merged when in contact using series of rules to resolve conflicts.


Snapshot Replication

Data changes are not tracked for snapshot replication. Each time a snapshot is applied, it completely overwrites the existing data. Snapshot replication process provides the initial synchronization for transactional and merge publications. In several cases, this initial synchronization is all that is necessary. Data is synced at defined intervals as opposed to constant transactional sync of other types.


Distribution Setup

Distribution is important setup in replication architecture. It has following components and terms.

Distribution server

Distribution server is a role where distribution setup (agent, jobs, DB) resides. Distribution server may be same as publisher or a dedicated server. For big and busy replication setups separate distributor server is recommended. We can mark a server as distributor before going to setup replication publisher and subscriber.

Distribution database

Distribution is system database for replication setup and is created automatically on server with distributor role. We can customize the DB name, files name, location and file size however. For transactional replication, the distribution database tracks changes made to publications. For merge replication it stores only synchronization history.

Distribution agent

Distribution agent is setup as a separate module and works in form of scheduled job. Distribution agent is a process that moves transactions and snapshots held in the distribution database to subscribers. Works in form of scheduled job under replication jobs category.


Replication components

There are some other terms and components that are always involved in replication setup.


Publisher is a server that makes data available for other servers, i.e. it is the server that makes data available for replication.


Publication can be a selection of tables, indexed view and stored procedures. Each publication can be replicated as a snapshot publication, a transactional publication or a merge publication. It is important to choose the publication articles on a valid base like size, relation of tables. There may be multiple publications in a single publisher.


An object included in a publication is called an article. Articles are combined on some basis to form a publication. Article can be an entire table, select rows from a table, specified columns from a table indexed view or a stored procedure. An article is a unit in publication.


Subscriber is the server that hosts the DB where data from publisher is being synced. There may be multiple subscribers against a single publisher. Subscriber may be on same server as that of publisher. Also there may be multiple subscriber databases on a single subscriber server.


Subscription relates to publication and goes to subscriber database. There may be multiple subscriptions against a single publisher and each will go to separate subscriber database. There are two types of subscriptions.

Pull Subscription

With pull subscription the subscriber asks the publisher for periodic updates. With pull subscription each subscriber can specify the best time for the update. Pull subscription is generally considered the better form of replication when a large number of subscribers are involved, or where the load on the LAN needs to be distributed.

Push Subscription

With push subscription the publisher pushes any changes made to the data out to the subscribers. Typically push subscription is used where changes need to be replicated to the subscribers shortly after the change occurs, i.e. where the replication of changes is required in near real time.

Replication Agents

As we have discussed several components of replication in above sections some agents were discussed as well. It looks suitable to list the agents so that they may give a clear picture of setup

  • Snapshot agent
  • Log Reader Agent
  • Distribution Agent
  • Merge Agent
  • Que Reader Agent

Replication effect on log file

It is important to know the relation and effects of replication on database log file. If replication falls in latency then log begins to accumulate in log file unless it is send to distributor. This may ultimately fill the log file and slow down the system in some cases. Situation is same when database is with any recovery model FULL or even SIMPLE.

Replication topologies

Topology is how the replication setup is designed and components are arranged. Following are different disgrams to give view of different toplogies.

SQL Server Replication Topology Central Distributor


SQL Server Replication Topology Central Publisher with Remote Distributor


SQL Server Replication Topology Central Publisher


SQL Server Replication Topology Central Subscriber


SQL Server Replication Topology Publishing Subscriber

  • Aug 21 / 2017
  • 0
Columnstore Indexes, dbDigger

Introduction to SQL Server Columnstore Indexes

Columnstore indexes are introduced in SQL Server 2012. These are used for large tables where primarily READ operations are performed. Suitable environment is DWH fact tables. Storing data in this structure may help to get 10 X read performance and 10 X compression as compared to traditional row structured storage of data.

In this technology data is physically stored, retrieved and managed in a columnar data format. There are two types of columnstore indexes

  • Clustered columnstore indexes (CCI)
  • Non clustered columnstore indexes

Clustered columnstore indexes (CCI)

are used for large tables in DWH where considerable size difference may be acheived by compression. And READ operations are intensive.

Non clustered columnstore indexes

are used for environment where OLTP load is also involved with analysis queries.

Creating columnstore index on existing table

While creating a clustered columnstore index (CCI) on a table there may be some issue due to data type or column type. For example we may not create CCI on a table with data type nvarchar(max) in it. Or with a computed column (persisted or non persisted) in that table.

Combining columnstore and rowstore indexes

Starting from SQL Server 2016 we can have non clustered columnstore indexes on a table with rowstore table. And we can also have non clustered rowstore indexes on a columnstore table.

Get columnstore indexes in a DB

We can get general information about columnstore indexes in a DB with following script

Columnstore indexes on partitioned tables

Columnstore indexes can be implemented with partitioned tables by keeping these partition aligned.

  • Aug 09 / 2017
  • 0
dbDigger, Monitoring and Analysis, System Administration, Windows Server

Get disk report by using PsInfo utility

In a previous post we have discussed PsInfo utility is part of PsTools set.Today we will utilize Psinfo to get disk information on any machine. Any of PsTools utility can be used by simply unzipping on disk. I have unzipped all PsTools files in D drive and PsInfo is also among those.

Get disk report by using PsInfo utilityNo we can utilize the PsInfo to get disk information for local or any other connected machine.

  • Open cmd and go to unzipped PsTools folder
  • Execute psinfo \\MachineNameHere -D disk

It would generate general information about drives like label, type, format, Size and free size.

Run PsInfo in cmd


  • May 10 / 2017
  • 0

SQL Server Management Studio (SSMS) 2017 available now

SQL Server Management Studio (SSMS) 2017 is now available for download now. Click here to get it. Download page has further information about new features and compatibility.

Following are some highlights

This release features improved compatibility with previous versions of SQL Server, a stand-alone web installer, and toast notifications within SSMS when new releases become available.

SSMS 17.x can be installed side-by-side with SSMS 16.X or SQL Server 2014 SSMS and earlier.

Support SQL Server 2008 and onward. There is no explicit block for SQL Server 2000 or SQL Server 2005, but some features may not work properly.

Supports Windows 10, Windows 8, Windows 8.1, Windows 7 (SP1), Windows Server 2016, Windows Server 2012 (64-bit), Windows Server 2012 R2 (64-bit), Windows Server 2008 R2 (64-bit)

Click here to get full list of changes

Click here for list of known issues and work arounds

  • Mar 09 / 2017
  • 0
Database Migrations, dbDigger, T-SQL Scripts

Code snippets for database migration task

Following are some code snippets that i used during a database migration task from one server to another. Backup restore method is used in this migration task

DB list on source server

Get the database list on source server that are required to migrate

Note DB properties

Note the important database properties on source server so that we may verify these on destination server after migration is complete.

Size of DBs

Get size of all databases that are to be migrated. It would help to estimate the disk capacity on destination and also the time estimate for backup and restore process.

Get which DBs are in Avaiability Group

Set DBs to READ ONLY and Restricted mode on source server

Before backing up the databases we will do following operations for each DB. As here i have databases in AG so i also included command to set DB out of AG so that i may put it to READ ONLY mode next.

Backup Databases

We will take backup of all databases through following script. Go through the option used for backup and also put a valid path. Backup file name will be generated automatically.


Get restore command for each DB one by one

Put DB name in first line of script one by one and get the restore commands for last full backup of that specific database


Set databases to READ WRITE mode on destination server


Fix and verify the orphan users


Update the DB properties

Following scripts will generate commands to fix the specific database property.



Consult us to explore the Databases. Contact us