Script to Check How Much percentage complete database backup and Restore

Ever wondered how long it would take to complete a backup or restore that is currently in progress. Use the following command to find that. It gives you a rough estimate of the amount of time required to complete it.

select percent_complete from sys.dm_exec_requests where session_id=Session_id

or

select * from sys.sysprocesses where cmd like '%backup%'

select name,log_reuse_wait, log_reuse_wait_desc from sys.databases

or 

SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete) AS [Percent Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time], CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min], CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min], CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours], CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2, CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END) FROM sys.dm_exec_sql_text(sql_handle))) FROM sys.dm_exec_requests r WHERE command IN ('RESTORE log','BACKUP DATABASE')

or

SELECT command,

            s.text,

            start_time,

            percent_complete,

            CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '

                  + CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '

                  + CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,

            CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '

                  + CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '

                  + CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,

            dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time

FROM sys.dm_exec_requests r

CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s

WHERE r.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG')

SQL Server Get status of Backup
SQL Server Get status of Restore
SQL Server Get Backup completion
SQL Server Get Restore completion. 

Database Recovery Models

The feature "database recovery model" was included by Microsoft since SQL Server 2000. It helps us to manage the transaction log. Database Recovery model is part of the recovery options that SQL Server offers. The other option besides of recovery model is PAGE_VERIFY (CHECKSUM or TORN_PAGE_DETECTION), this option detect corrupt pages of the database because I/O problems. PAGE_VERIFY is not explain in this article.

SQL Server 2000, 2005 and 2008 offers three recovery models:

• Simple Recovery Model
• Bulk-logged Recovery Model
• Full Recovery Model

The recovery model is defined by each database and it can be changed according to business necessities. The following information show us details of each recovery model.

Simple Recovery Model.

- Help to minimize the overhead over the transaction log, because transaction log is not backed up.

- Reduces the space used for transaction log on disk, because the transactions are not remaining on the transaction log, they are truncated.

- This recovery model logs minimal amount of information on the transaction log, logging only the information necessary to ensure the database consistency after a restore from a backup or after system crash.

- SQL Server truncates the log when a checkpoint occurs.Checkpoint truncates the log removing committed transactions, it is performed automatically by SQL Server at regular intervals. The intervals can be configured with Recovery Interval option using sp_configure. Is important to know that exists factors that can delay the log truncation.

- No log backup is supported. It means that if a disaster happens, a restore to point in time can not be performed and the database must be restore until the last full backup or differential backup. Therefore, the databases under this recovery model must be backed up frequently to avoid loss of data as much as possible.

- Simple recovery model is advisable to use for user databases, development and testing scenarios. Is not recommendable to use in Production systems. For production systems is recommendable Full Recovery Model.

Bulk-logged Recovery Model.

- Bulk-logged recovery model provide better protection of data than simple recovery model.

- Bulk-logged recovery model works in similar way than full recovery model. The only difference is the way to handle bulk operations. If there are not bulk operations running, bulk-logged recovery model works similar than full recovery model.

- This recovery model offers minimal logging for Bulk operations as BCP, SELECT INTO, BULK INSERT, CREATE INDEX, reducing the log space consumption.

- Microsoft recommends use bulk-logged recovery model for short periods of time. A best practice is switch the recovery model from full recovery model to bulk-logged recovery model before perform bulk operations and restore it to full recovery model when those operations are completed.

- This model is recommended for user database where bulk operations are performed when no users using the database.

- For log backup that contains bulk operations, the database can be recovered just to end of the log backup and the point in time (STOPAT) recovery is not allowed. If the log backup does not have bulk log operations logged, then the point in time recovery is allowed.

Full Recovery Model.

- This model is recommended for production databases, critical environments and in environment where you cannot afford to lose data.

- Support the recovery to point in time, assuming the log backups are complete up to the point in time desired.

- This recovery model offers fully logs for all the transaction on the database. Even logs any bulk operation.

- Transaction log, logs all the transactions of the database and the transactions are retain until a log backup is performed. Log backup is supported.

How to check the recovery model

To know the current recovery model the DBA can check it using TSQL or Graphical tool (SSMS).

Using TSQL:

SELECT DATABASEPROPERTYEX('SID', 'RECOVERY')

Or

select recovery_model, recovery_model_desc from sys.databases wheredatabase_name = 'SID'

How to switch the recovery model

The recovery model can be modified on TSQL and with Graphical tool (SSMS).

Using TSQL:

--Switch to simple ALTER DATABASE <SID> SET RECOVERY SIMPLE GO --Switch to Bulk logged ALTER DATABASE <SID> SET RECOVERY BULK_LOGGED GO --Switch to full ALTER DATABASE <SID> SET RECOVERY FULL GO

To switch the recovery model is necessary to have permissions to execute ALTER statement.

Important Considerations when switching recovery model

The recovery model should be defined depending the business necessities. But, is important to know the impact that has switching between one recover model to another.

Simple -> Full / Bulk logged

This switch is recommendable when is a productive database. For simple recovery model, the transaction log is truncated in every checkpoint, but, in full/bulk logged the transaction log is not truncate until a log backup is performed. When switch to bulk logged every transaction is fully logged except bulk transaction as SELECT INTO, but in full recovery model all the transaction are fully logged.

Bulk logged -> Full

When bulk task are finished is necessary switch to full recovery model then is recommendable take a log backup.

Full -> Bulk Logged

This switch is recommendable when a bulk task is going to be performed in order to minimize the log space used. A log backup should be performed before switch to Bulk logged and is recommendable no users connected when bulk logged recovery model is defined.

Full/Bulk logged -> Simple

This switch is not recommendable for productive databases. The log will be truncated in every checkpoint and the log backup is not supported. A log backup should be performed before switch to simple. You switch to simple before a full backup to decrease the file size of the backup.

Conclusion

The person in charge of the database should be necessary to understand the recovery models in SQL Server also the impact that has to use each of them; this article shows important information to understand that.

SQL SERVER – Checkpoint

Checkpoint is the process that writes all dirty pages to disk for the current database. Checkpoint process help to minimize time during a later recovery by creating a point where all dirty pages have been written to disk.

• Checkpoints can be performed concurrently on the databases
• If a checkpoint fails or is interrupted and a recover is required, the database engine can not recover from the "failure" checkpoint, is necessary to recover from the last successful checkpoint.
• The database engine performs any modification to database pages in memory (for performance reasons) it cause dirty pages, those dirty pages are not written to disk on each modification performed, those are written to disk just when a checkpoint occur.
• When checkpoints occur?
  • Before a backup, the database engine performs a checkpoint, in order that all the changes to database pages (dirty pages) are contained in the backup.
  • Stopping the server using any of the following methods, they it cause a checkpoint.
    • Using Shutdown statement,
    • Stopping SQL Server through SQL Server configuration, SSMS, net stop mssqlserver and ControlPanel-> Services -> SQL Server Service.
    • When the "SHUTDOWN WITH NOWAIT" is used, it does not execute checkpoint on the database.
  • When the recovery internal server configuration is accomplished. This is when the active portion of logs exceeds the size that the server could recover in amount of time defined on the server configuration (recovery internal).
  • When the transaction log is 70% full and the database is in truncation mode.
    • The database is in truncation mode, when is in simple recovery model and after a backup statement has been executed.

• The time required to perform a checkpoint depends directly of the amount of dirty pages that the checkpoint must write.

[Questions & Answers]

Q: What is Dirty Page?.

A: Dirty pages are the data pages that exists on the buffer cache and have been modified, but not yet written to disk.

Q: What is the checkpoint syntax?

A:

Checkpoint [duration_time]

Duration_time:

• Time desired to perform the checkpoint process.
• Is specified in seconds.
• Must be greater than 0.
• When is omitted, the database engine adjust automatically the duration time to minimize the performance impact.
• Depending of the duration defined, is the amount of resources that SQL Servers assign to checkpoint process. Ex. If the duration_time is defined in 10 seconds but the checkpoints normally is going to take 20 seconds, SQL Server assigns more resources with performance impact in order to accomplish the 10 seconds defined. In the other hand, if the checkpoint is going to take 5 seconds, SQL Server assigns fewer resources than would be assigned by default. Checkpoint process usually takes more/less time than the specified.

How long is running SQL Server

To know how much time my SQL Server is running, we can mention 3 ways:

1. Check on sys.sysprocesses the spid = 1, it is an internal process that is created when the SQL Server instance is started.

2. Check the creation date of the tempdb database. It can be checked in sys.databases on the column create_date.

3. Look into the ERRORLOG. The first line is logged when the SQL Server is started.

For test purpose, you can check the steps described below before and after restart the SQL Server service.

-- 0. -- Current date-time select getdate() ----------------------- 2009-06-03 04:15:27.513 (1 row(s) affected) /* 1. Using sys.sysprocesses Here we can see how much time the SQL Server using the login_time column and sys.sysprocesses */ select login_time as SQLStart, datediff(mi, login_time, getdate()) Running_in_Minutes, datediff(hh, login_time, getdate()) Running_in_Hours, datediff(dd, login_time, getdate()) Running_in_Days from sys.sysprocesses where spid = 1 Result SQLStart Running_in_Minutes Running_in_Hours Running_in_Days ----------------------- ------------------ ---------------- --------------- 2009-06-03 04:13:11.793 2 0 0 (1 row(s) affected) /* 2. Using sys.databases Here we can see the column create_date for the TEMPDB database. */ select create_date as SQLStart, datediff(mi, create_date, getdate()) Running_in_Minutes, datediff(hh, create_date, getdate()) Running_in_Hours, datediff(dd, create_date, getdate()) Running_in_Days from sys.databases where name = 'tempdb' Result. SQLStart Running_in_Minutes Running_in_Hours Running_in_Days ----------------------- ------------------ ---------------- --------------- 2009-06-03 04:13:11.763 2 0 0 (1 row(s) affected) /* 3. Using ERRORLOG Executing the SP xp_readerrorlog, we can see the date and time exactly that SQL Server starts to logged activity. */ exec master..xp_readerrorlog Result. LogDate ProcessInfo Text ----------------------- ------------ ---------------------- 2009-06-03 04:13:08.290 Server Microsoft SQL Server 2005 - 9.00.1399.06(Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation

SQL Server 2012 SP1 Installation With Always on Feature

SQL Server 2012 SP1 Patch Installation screen shots:

1.  First we verify the Always on Availability Group which is the primary replica and secondary replica and type failover modes. Here my primary Replica and Secondary Replica information

Primary Replica:Node1

Secondar Replica's:Node2 (Automatic Failover)

                                   Node3 (Manual Failover)

see the below screen shot .

Secondary replica status of high availability group info.

Before applying the service pack installation we will follow the below steps.

1. We are applying the service pack in Node1 server at that time we will failover to secondary replica using Failover Cluster manager.

Now Primary Replica is Node2 server and Node1 is secondary Replica using automatic failover . now we are applying the Service pack on Node1 server. see the below screen shots.

See the availability group status see the below screen shot.

Now we see the Availability Group status.

see Here Node1 server is secondary replica and Node2 server is Primary Replica.

Once service pack completed we will fail back Availability group to Node1 server.

Now we will see the Availability group status and primary replica and secondary replica information.

Now applying the service pack on node2 Server.

We are verifying the High Availability Group Status.

In sp installation time we insert data into primary replica. After installation whether this data is moved to secondary replica (node2) or not. See the below screen shots.

As per node3 database table result is 17 means 2 records are added.

We will see in node2 server after installation this records is updated or not.

Status of TESTAG always on group.

Now we are verify the data is synch or not in node2 server.

The data is updated into Secondary replica see the above screen shot.

Before applying the SP on node3 server one database count of records see the below.

Now we are applying the SP on node3 server.

Now we are verified in primary replica on node1 server.

Now we are inserting the data into primary replica database on node1 server.

Node2 database output:

Status of Always on Availability groups.

Node3 database count of records.

If we need to insert the data into mirror database, the data can't be update because of the database is read only. See the below screen shot.