Category Archives: SQL DBA

Log Shipping Complete Tutorial Part 2 Step by Step Log shipping


To setup a log-shipping you must have sysadmin rights on the server.

Minimum Requirements

  1. SQL Server 2005 or later
  2. Standard, Workgroup or Enterprise editions must be installed on all server instances involved in log shipping.
  3. The servers involved in log shipping should have the same case sensitivity settings.
  4. The database must use the full recovery or bulk-logged recovery model
  5. A shared folder for copying T-Log backup files
  6. SQL Server Agent Service must be configured properly

In addition, you should use the same version of SQL Server on both ends. It is possible to Log Ship from SQL 2005 to SQL 2008, but you can not do it the opposite way. Also, since Log Shipping will be primarly used for failover if you have the same versions on each end and there is a need to failover you at least know you are running the same version of SQL Server.

Steps to Configure Log-Shipping:

1. Make sure your database is in full or bulk-logged recovery model. You can change the database recovery model using the below query. You can check the database recovery model by querying sys.databases

SELECT name, recovery_model_desc FROM sys.databases WHERE name = 'DBName'

USE [master]

2. On the primary server, right click on the database in SSMS and select Properties. Then select the Transaction Log Shipping Page. Check the “Enable this as primary database in a log shipping configuration” check box.

setting up log shipping for sql server

3. The next step is to configure and schedule a transaction log backup. Click on Backup Settings… to do this.

right click on the database in ssms

If you are creating backups on a network share enter the network path or for the local machine you can specify the local folder path. The backup compression feature was introduced in SQL Server 2008 Enterprise edition. While configuring log shipping, we can control the backup compression behavior of log backups by specifying the compression option. When this step is completed it will create the backup job on the Primary Server.

transaction log backup settings in ssms

4. In this step we will configure the secondary instance and database. Click on the Add… button to configure the Secondary Server instance and database. You can add multiple servers if you want to setup one to many server log-shipping.

add a secondary server

When you click the Add… button it will take you to the below screen where you have to configure the Secondary Server and database. Click on the Connect… button to connect to the secondary server. Once you connect to the secondary server you can access the three tabs as shown below.

Initialize Secondary Database tab

In this step you can specify how to create the data on the secondary server. You have three options: create a backup and restore it, use an existing backup and restore or do nothing because you have manually restored the database and have put it into the correct state to receive additional backups.

intialize secondary database

Copy Files Tab

In this tab you have to specify the path of the Destination Shared Folder where the Log Shipping Copy job will copy the T-Log backup files. This step will create the Copy job on the secondary server.

specify where the log shipping copy job will copy the t-log backup files

Restore Transaction Log Tab

Here you have to specify the database restoring state information and restore schedule. This will create the restore job on the secondary server.

create the restore on the secondary server

5. In this step we will configure Log Shipping Monitoring which will notify us in case of any failure. Please note Log Shipping monitoring configuration is optional.

log shipping monitoring will notify us in case of any faulures

Click on Settings… button which will take you to the “Log Shipping Monitor Settings” screen. Click on Connect …button to setup a monitor server. Monitoring can be done from the source server, target server or a separate SQL Server instance. We can configure alerts on source / destination server if respective jobs fail. Lastly we can also configure how long job history records are retained in the MSDB database. Please note that you cannot add a monitor instance once log shipping is configured.

monitoring can be done from the source server, target server or a separate SQL Server instance.

6. Click on the OK button to finish the Log Shipping configuration and it will show you the below screen.

Happy practicing ……

Log Shipping Complete Tutorial Part 1

What is SQL Server log shipping?

SQL Server log shipping is a technique which involves two or more SQL Server instances and copying of a transaction log file from one SQL Server instance to another. The process of transferring the transaction log files and restoring is automated across the SQL Servers. As the process result there are two copies of the data on two separate locations

A log shipping session involves the following steps:

  • Backing up the transaction log file on the primary SQL Server instance
  • Copying the transaction log backup file across the network to one or more secondary SQL Server instances
  • Restoring the transaction log backup file on the secondary SQL Server instances

Operating modes

There are two available modes and they are related to the state in which the secondary, log shipped, SQL Server database will be:

  • Standby mode – the database is available for querying and users can access it, but in read-only mode
    • The database is not available only while the restore process is running
      • Users can be forced to disconnect when the restore job commence
      • The restore job can be delayed until all users disconnect themselves
  • Restore mode – the database is not accessible

Advantages and disadvantages of using SQL Server log shipping

SQL Server log shipping is primarily used as a disaster recovery solution. Using SQL Server log shipping has multiple benefits: it’s reliable and tested in details, it’s relatively easy to set up and maintain, there is a possibility for failover between SQL Servers, data can be copied on more than one location etc.

Log shipping can be combined with other disaster recovery options such as AlwaysOn Availability Groups, database mirroring, and database replication. Also, SQL Server log shipping has low cost in human and server resources

The main disadvantages in the SQL Server log shipping technique are: need to manage all the databases separately, there isn’t possibility for an automatic failover, and secondary database isn’t fully readable while the restore process is running.

SQL Server 2008 Editions

The editions of SQL Server 2008 are as follows:

  • Enterprise: this is the high-end edition with the full feature set. It supports systems up to 8 CPU and 2 TB RAM. The maximum size of the database is 524 PB.
  • Standard it has a bit less features than Enterprise, but it is a good choice when advanced functions (such as data compression, compressed backups, indexed views, etc.) are not required for the business. It supports systems up to 4 CPU and 64 GB RAM.
  • Workgroup: it is suitable for remote offices of a larger company. It supports systems up to 2 CPU and 4 GB RAM.
  • Web: it is designed for web applications. It supports 4 CPU without memory limitations.
  • Developer: similar to Enterprise, but licensed to only one user for development, testing and demo. It can be easily upgraded to Enterprise without reinstallation.
  • Express: free entry level database. It can utilize only 1 CPU and 1 GB memory, the maximum size of the database is 10 GB.
  • Compact: free embedded database for mobile application development. The maximum size of the database is 4 GB.

Another point of interest is that SQL Server Analysis Services and SQL Server Integration Services are included only in Enterprise and Standard Edition. SQL Server Reporting Services is also very limited in the free editions.

SQL SERVER – Get Database Backup History

-- Get Backup History for required database
CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB'AS bkSize,
CAST(DATEDIFF(second, s.backup_start_date,
s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,
CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,
CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,
CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END AS BackupType,
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id =m.media_set_id
WHERE s.database_name = DB_NAME() -- Remove this line for all the database
ORDER BY backup_start_date DESC, backup_finish_date

Setting Up Alerts for All SQL Server Agent Jobs

Some times clients ask to enable alerts for the sql agent jobs, it will be difficult when we have more jobs, because we need to goto each and every job and enable the notifications.

Here is the script for enabling for the jobs,

Steps :

1. Create Mail Profile

2. Enable the mail profile for agent (SQL Server Agent Properties –> Alert System –>select Enable Mail profile & Select profile)

3. Create Operator with Mail ID. (Ex : DBA)

4. Run below script for enabling the alerts for all the jobs.

We need to decide when we want email to be trigger (i.e  after Complete, Failed, Success) below table will give the details.

Value Description
0 Never
1 On Success
2 On Failure
3 Always

In the below script i assume that , Alert is sent on 2 (On Failure) and alert will be sent to DBA (Operator).

use msdb

DECLARE @operator varchar(50)
SET @operator = ‘DBA’

SELECT ‘EXEC msdb.dbo.sp_update_job @job_ID = ”’ + convert(varchar(50),job_id) + ”’ ,@notify_level_email = 2, @notify_email_operator_name = ”’ + @operator + ”’ ;’ FROM sysjobs


Execute the script which is generated by above script.





Windows Disk Space Alert From SQL SERVER JOB

In SQL server when the auto growth option is set to true and when we have defined the autogorwth in MB, some times we may face the issue like windows disk space is full, during that time SQL Server cannot increase the auto growth size of the mdf or ldf files.

As Best practice to have the alert for the drives free space. We can create a job for the below script. schedule it for every 15 or 30 minutes based on your application usage / load.

Types of SQL Server Backup -1 : FULL Backup

The most common types of SQL Server backups are complete or full backups, also known as database backups.  These backups create a complete backup of your 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 backup.

A full backup can be completed either using T-SQL or by using SSMS

Create a full backup of the AdventureWorks database to one disk file


BACKUP DATABASE AdventureWorks TO DISK = 'C:\AdventureWorks.BAK'

SQL Server Management Studio

  • Right click on the database name
  • Select Tasks > Backup
  • Select “Full” as the backup type
  • Select “Disk” as the destination
  • Click on “Add…” to add a backup file and type “C:\AdventureWorks.BAK” an click “OK”
  • Click “OK” again to create the backup

SQL Server – Hide system objects in Object Explorer – SQL Server Management Studio

By default, SQL Server system objects are listed in Object Explorer in Management Studio. These system objects include system database, system tables/views/procedures and so on.

SQL Server Management Studio provides an option to hide these objects from Object Explorer to prevent *accidental* use.

To enable this option follow below steps.

Step1: Go to Tools > Options


Step2: Navigate to Environment > Startup


Step3: Check Hide system objects in Object Explorer and click OK:


Step4: An confirmation message will be displayed saying that changes will take effect after Management Studio is restarted, click on OK to continue:



Once you restart SQL Server Management Studio, you will notice that system objects are no longer listed in Object Explorer:

What is hidden by this setting:

1. System databases – This is not hidden for SQL Server 2012
2. System Stored Procedures
3. System Tables
4. System Views


How to Find SQL Server Database Size

if convert(varchar(20),SERVERPROPERTY(‘productversion’)) like ‘8%’

SELECT [name], fileid, filename, [size]/128 AS ‘Total Size in MB’,
[size]/128.0 – CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128.0 AS ‘Available Space In MB’,
CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128.0 AS ‘Used Space In MB’,
(100-((([size]/128.0 – CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128)/([size]/128))*100)) AS ‘percentage Used’
FROM sysfiles
SELECT [name], file_id, physical_name, [size]/128 AS ‘Total Size in MB’,
[size]/128.0 – CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128.0 AS ‘Available Space In MB’,
CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128.0 AS ‘Used Space In MB’,
(100-((([size]/128.0 – CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128)/([size]/128))*100)) AS ‘percentage Used’
FROM sys.database_files