Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Monday, 25 July 2016

Database Mirroring Automation script for Controlled Failover & more

Database Mirroring Automation

Automation: Automation is one of my favorite topics. For my part in this month’s T-SQL Tuesday, I am going to combine it with one of my other favorite topics, database mirroring. I did a presentation on automation with database mirroring for SQL Saturday #43 in Redmond, WA back in 2009. Most of that material has never been presented in a blog post before, and I want to share those automation procedures today.
The slide deck and scripts from that presentation can be found here:

Failover Mirror to Original Principal

There is a very good reason to use the first automation procedure …. money. Licenses for SQL Server are not inexpensive. One of the good things about database mirroring is that if the mirror server is used only as failover and is otherwise always inactive, then the license for the principal server covers both servers. Certain limitations apply to this such as the licensing must cover the higher of the two servers. For example, if the principal is a 4 CPU server using Standard Edition and the mirror is an 8 CPU server using Enterprise Edition, the licensing must cover 8 CPUs and must cover Enterprise Edition. Another often overlooked limitation is that upon failover, you can only use the mirror server for 30 consecutive days as the principal. If you go past 30 days, you must license the mirror server separately. This could easily cost a business a lot of money if an automatic failover occurs and nobody notices or bother to fail back.
For this procedure, I set up a job on the designated mirror server and set it to run every 5 minutes. When the procedure runs, ti won’t do any work beyond checking the sys.database_mirroring view unless it detects a database that is ready to be failed back to the original server. In order for a database to be failed back, it has to meet 3 criteria.
  1. It has to in the principal role on the mirror server
  2. It has to be in a synchronized state thus ensuring that the original principal is up, connected, and synchronized
  3. It has to be running in Full Safety
As with most of my automation procedures, I include a @DBName (sysname, default of NULL) parameter and a @Debug (bit, default of 0) parameter. If @DBName is provided, it will only process the named database. Otherwise, it enumerates all mirrored databases that fit the criteria. If @Debug is set to 1, it only returns the code that it would execute to fail over the database without actually issuing the command. For a @Debug value of 0, it issues the command.

Performing a Controlled Failover

The controlled failover procedure does exactly what it says. It is for those times when you need to manually fail over all of your mirrored databases. This procedures does rely on linked servers for communication between the principal server and the mirror server. If the linked server does not exist, it will attempt to create it using another procedure that I provide called dba_ManageLinkedServers. Be sure to create this procedure along with the controlled failover procedure, dba_ControlledFailover.
The controlled failover procedure does a LOT of work for you. First of all, it handles changing databases from asynchronous mode (safety off) to synchronous mode (safety full) so that they can be failed over. It has built-in timing mechanism to have the process wait for the databses that are not yet synchronized to become synchronized before failover. Everything has built-in timeouts so that any one database won’t cause the process to run forever. Next, once failover is complete, it sets the databases back to their original operating mode. If it was originally running in asynchronous mode, it sets it back to asynchronous mode after failover.
This procedure is great for those gotchas that can’t be set upon restore of the database for mirroring configuration until a failover occurs. To ensure smooth operation of the databases after failover, the procedure checks the database owner and sets it on the other database if it is not the same. It also checks the TRUSTWORTHY property for the database and sets it if it does not match the original principal. Within this subroutine, you can add any other steps you need to add. In the past, I have used this section of the code to disable and enable SQL jobs and to sync up the server level objects such as logins.
Speaking of logins, I have already covered transferring logins in a different blog post: Transferring Logins to a Database Mirror.
Once again, I include a @DBName (sysname, default of NULL) parameter and a @Debug (bit, default of 0) parameter. If @DBName is provided, it will only process the named database. Otherwise, it processes all mirrored databases. If @Debug is set to 1, it only returns the code that it would execute to fail over the databases without actually issuing any commands. For a @Debug value of 0, it issues the commands. It also has a parameter for @MaxCounter (int, default of 60). When it is waiting for a database to be synchronized before failover or waiting for the failover to complete, it pauses 5 seconds between each loop. The @MaxCounter parameter is the number of loops. If @MaxCounter is 60, then the timeout is 60 loops times 5 seconds or 3 minutes. If you find that your databases often take longer, you can pass in a larger number for this parameter.


The goal of these automation procedures is to minimize downtime and impact of a mirroring failover thus increasing availability of the mirrored databases. No fumbling about trying to figure out which databases need to have safety enabled. No worry about forgetting to set the safety level back off or setting the TRUSTWORTHY property after failover. In short, these procedures allow anyone to manage the mirroring failover process, not just the primary DBAs. When I assist someone with setting up mirrored databases, I like to set these up for them so they can manage it with relative ease and don’t feel overwhelmed or feel the need to call me every time they need to fail over.


Thursday, 21 July 2016

Basics of Database Mirroring? Step by Step for Configuring Database Mirroring in SQL Server

Basics of Database Mirroring? Step by Step for Configuring Database Mirroring in SQL Server
  • What versions of SQL Server is database mirroring available in? 
    • SQL Server 2005, 2008, 2008R2, and 2012
  • What editions of SQL Server is high safety (synchronous) available in?
Synchronous mirroring by SQL Server edition
(Synchronous mirroring by SQL Server edition)
  • What editions of SQL Server is high performance (asynchronous) available in?
(Asynchronous mirroring by SQL Server edition)
  • Do the principal and the mirror need to be on the same version?
    • You can have the principal be one version – say 2008R2 – and the mirror another – say 2012. This is how an upgrade with minimal downtime can be accomplished using mirroring! However, once you fail over to the mirror, you can no longer fail back.
  • Do the principal and the mirror need to be on the same edition?
    • To be fully supported by Microsoft, yes.
  • Can multiple databases on the same instance be in mirroring sessions?
    • Yes, but there are limitations, based on the hardware of your servers.
  • How do I mirror the system database (master, model, msdb, tempdb)?
    • You can’t! This isn’t supported.
  • If I set up mirroring, do I still have to take full, differential, or transaction log backups?
    • Yes! Mirroring increases the availability of your databases. It is not a substitute for regular backups, however.
  • How does index maintenance (rebuilds) affect the mirror?
    • Transactions that fill up the log can affect the performance of mirroring. When the amount of information in the log increases, the amount of information that needs to be sent to and committed to the mirror increases also. If performance is crucial, you may want to do index maintenance more frequently, so it takes less time. If large transactions such as batch inserts are affecting performance, break those into smaller transactions.
Step by Step Demo:
I am going to outline my environment and then walk through the process of setting up Database Mirroring.  This will include the configurations, backups, restores and verification process.  Let's jump in.
My test environment consists of two separate VM's running VM Workstation with Windows 2008 R2 Datacenter Edition and SQL Server 2008 R2 Enterprise named appropriately Principal and Mirror. The SQL Server and SQL Server Agent Services accounts are running as domain users (DOMAIN\User). Windows Firewall is OFF for the sake of this example.
I created a database on the Principal SQL Server instance and named it TestMirror. The recovery model is set to FULL RECOVERY.
1st step: Issue a full backup of the database
BACKUP DATABASE TestMirror TO DISK = 'C:\Program Files\Microsoft SQL 
2nd step: Issue a transaction log backup of the database.

BACKUP LOG TestMirror TO DISK = 'C:\Program Files\Microsoft SQL 

Below are the two files in the file system:
3rd step: Assuming you have the backup folder shared on the Principal Server and you can access it from the Mirror Server, you will need to restore the full backup to the Mirror server with the NORECOVERY option.

RESTORE DATABASE TestMirror FROM DISK = N'\\Principal\Backup\Backup.bak' 
WITH FILE = 1, MOVE N'TestMirror_log' TO 
N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TestMirror_1.ldf', 
4th step: Restore log backup also with the NORECOVERY option
RESTORE LOG TestMirror FROM DISK = N'\\Principal\Backup\Backup.trn' 

Now it's time to dig down and configure Database Mirroring. From the Principal server, right click the database and choose "Tasks" | "Mirror" or choose "Properties" | "Mirroring".


Click the "Configure Security" button and click "Next >" if the Configure Database Mirroring Security Wizard intro screen appears. The next screen should be the Include Witness Server screen:


This is where you would configure a witness server for your mirroring, but since we're just configuring a basic mirror we will skip this part. However, if you are configuring mirroring in an Enterprise environment it is recommended you configure a witness server because without one you will not have synchronous automatic failover option.

Select "No", then click "Next >" to continue the process.

The next screen will give you options to configure the Principal Server Instance:


Here we will be creating our endpoint, which is a SQL Server object that allows SQL Server to communicate over the network. We will name it Mirroring with a Listener Port of 5022.
Click the "Next >" button to continue.
The next screen will give you options to configure the Mirror Server Instance:


To connect to the Mirror server instance we will need to click the "Connect..." button then select the mirror server and provide the correct credentials:


Once connected, we also notice our endpoint name is Mirroring and we are listening on port 5022.
Click "Next >" and you'll see the Service Accounts screen.


When using Windows Authentication, if the server instances use different accounts, specify the service accounts for SQL Server. These service accounts must all be domain accounts (in the same or trusted domains).
If all the server instances use the same domain account or use certificate-based authentication, leave the fields blank.
Since my service accounts are using the same domain account, I'll leave this blank.
Click "Finish" and you'll see a Complete the Wizard screen that summarizes what we just configured. Click "Finish" one more time.


If you see the big green check mark that means Database Mirroring has been configured correctly. However, just because it is configured correctly doesn't mean that database mirroring is going to start...
Next screen that pops up should be the Start/Do Not Start Mirroring screen:


We're going to click Do Not Start Mirroring just so we can look at the Operating Modes we can use:


Since we didn't specify a witness server we will not get the High Safety with automatic failover option, but we still get the High Performance and High Safety without automatic failover options.
For this example, we'll stick with synchronous high safety without automatic failover so changes on both servers will be synchronized.
Next, click "Start Mirroring" as shown below.


If everything turned out right, Database Mirroring has been started successfully and we are fully synchronized.

Mirror15 Mirror16

If Database mirroring did not start successfully or you received an error here are a few scripts to troubleshoot the situation:
Both servers should be listening on the same port. To verify this, run the following command:
SELECT type_desc, port 
FROM sys.tcp_endpoints;

We are listening on port 5022. This should be the same on the Principal and Mirror servers:


Database mirroring should be started on both servers. To verify this, run the following command:

SELECT state_desc
FROM sys.database_mirroring_endpoints;
The state_desc column on both the Principal and Mirror server should be started:


To start an Endpoint, run the following:

ALTER ENDPOINT <Endpoint Name>
AS TCP (LISTENER_PORT = <port number>)
FOR database_mirroring (ROLE = ALL);
ROLES should be the same on both the Principal and Mirror Server, to verify this run:

SELECT role 
FROM sys.database_mirroring_endpoints;

To verify the login from the other server has CONNECT permissions run the following:

CONVERT(nvarchar(38), suser_name(SP.grantor_principal_id))
FROM sys.server_permissions  SP , sys.endpoints EP
WHERE SP.major_id  = EP.endpoint_id
ORDER BY  Permission,grantor, grantee;

You can see here from the State and Permissions column that the user has been Granted Connect permissions.

Database Mirroring Questions:
1) What is default port of Database Mirroring Endpoint?
Ans : 5022
2) Database Mirroring comes with which edition?
Ans: SQL Server 2005 SP1. Or SQL Server 2005 RTM with trace flag 1400

3) When I configure mirroring I’m receiving the below errror,
One or more of the server network addresses lacks a fully qualified domain name (FQDN). Specify the FQDN for each server, and click start mirroring again
Ans: The fully qualified computer name of each server can be found running the following from the command prompt:
Concatenate the “Host Name” and “Primary DNS Suffix”
Host Name . . . . . . . . . . . . : A
Primary Dns Suffix . . . . . . . :
Then FQDN of your computer name is just

4) How to enable mirroring by Script ?
Ans: – Specify the partner from the mirror server
– Specify the partner from the principal server
Note: Replace the dbname before using the above script

5) How to disable mirroring by script?
Note: Replace the dbname before using the above script

6) How to do manual failover to Mirror when principle is working fine?

7) Why I’m getting the below error message while configuring database mirroring?
Msg 1416, Level 16, State 31, Line 3. Database is not configured for database mirroring
Ans : You need to restore the Full backup from principal server using With NoRecovery option and also one transactional log backup from principal server using With NoRecovery option and then start configuring mirroring.

8) Can we configure mirroring between Standard Edition & Enterprise Edition or Vice Versa?
Ans: Nope its not possible, both principal and mirror should have same edition

9) Is it possible to take backup of mirrored database in mirror server?
Ans: No

10) Is it possible to perform readonly operation at mirrored database in mirror server?
Ans: Yes, You can create database snapshot for the same

11) Can I create multiple endpoints for configuring different databases for mirroring and point each database to unique endpoint.

Ans: No

12) Can I configure a single database to be mirrored to more than one server. i.e) One source & many destination like logshipping
Ans: No

13) How can I increase Heartbeat time between principal and mirror server?? By default its 10 sec.
Note: Before using the script change the dbname.

14) What status of mirroring has if secondary is down?
Ans: If secondary is down principle or Mirror show status disconnected

15) What status of mirroring has if principle is down?
Ans: If principle is down mirror will be disconnected with in recovery instead of synchronized with restoring

16) What status of mirroring has if mirroring is paused?
Ans: Is mirroring is set to paused from principle then then both principle & mirror in suspending

17) How to bring mirror DB online if Principle is down?
If you try to do failover like normal situation when principle is online [ALTER DATABASE <DB Name> SET PARTNER FAILOVER] then you will receive below error.
Msg 1404, Level 16, State 10, Line 1
The command failed because the database mirror is busy. Reissue the command later.

18) System Store Procedure to monitor Mirroring?

19) What are different possible Mirroring Stats?


 20)What is Database Mirroring End Points? Usage of End Points? More info about End Points in DATABASE MIRRORING

Usage of Endpoints:

Endpoints on both the principal, mirror and witness (if being used) 
which allows the SQL server instances to communicate with each other.

Database mirroring is a very handy HA(High Availability) solution available per database for SQL 2005/2008 and is available in both Enterprise and Standard edition. This solution also provides both automatic and manual failover. Therefore it is a HA solution available to almost all SQL server users.

Database mirroring overview is made up of a principal server role and a mirroring server role plus an optional witness server. The principal database is available for serving client and user connections. When transactions are applied to the principal database these transactions are then submitted to the mirror servers database, however the mirror server database cannot serve user requests as the mirror database is left in a recovering state.
The general steps for configuring database mirroring involves the following steps: –
1. Create Endpoints on both the principal, mirror and witness (if being used) which allows the SQL server instances to communicate with each other.
2. Create a copy of the principal database on the mirror server by restoring a backup of the database from the prinicpal server to the mirror server with the NO RECOVERY option.
3. Start the mirroring session to tell SQL server which endpoints are connected to which partner and witness server.
It seems that the most often overlooked or misconfigured components are the endpoints. Since all mirroring information is done over the endpoints it is obviously a critical component to check if things are not working.
Many people seem to believe that the endpoints are stored in the principal, mirror and witness databases. Why each component involved in the mirroring communication requires an endpoint to actual endpoint configuration is stored in the master database of the instance. Therefore it is possible to check the configuration on all nodes in the mirroring configuration.
The following T-SQL code will output information for the endpoints and if you are having issues with your new mirroring session you should run this to check the endpoints are configured correctly on your principal, mirror and witness (if being used).
USE master 
SELECT perm.class_desc, perm.permission_name, endpoint_name =, e.state_desc, e.type_desc, t.port, perm.state_desc, grantor =, grantee =
FROM master.sys.server_permissions perm
 INNER JOIN master.sys.server_principals prin1 ON perm.grantor_principal_id = prin1.principal_id
 INNER JOIN master.sys.server_principals prin2 ON perm.grantee_principal_id = prin2.principal_id
 LEFT JOIN master.sys.endpoints e ON perm.major_id = e.endpoint_id
 LEFT JOIN master.sys.tcp_endpoints t on t.endpoint_id = e.endpoint_id
WHERE perm.class_desc = 'ENDPOINT' AND e.type_desc = 'DATABASE_MIRRORING' 
order by endpoint_name ASC

This will provide you with very useful information such as the endpoint state (started, stopped, etc), TCP port that the endpoint is using and permissions that have been assigned to the endpoint along with which user has been granted connect permissions to the endpoint. You should execute this on each of the mirror instances such as the principal, mirror and witness.
Obviously with this information you can confirm that the SQL servers can communicate over the TCP ports. You can confirm this via telnet <servername> <PORTNUMBER>. If there is a firewall issue, etc this simple check and test will let you know.

You should also pay particular attention to which grantee has been assigned connect permissions to which endpoint. Your designated user from the principal instance needs connect permissions on the endpoint in the mirror instance and the mirror instance user requires connect permissions to the endpoint on the principal instance.


Wednesday, 20 July 2016

DBA Skills

 DBA Skills

• Strong experience in Database Administration in SQL Server ( 2008,2008R2,2012, 2014) with Proven experience in Database Technologies and able to Architect / Design / implement a DB solution which meet client requirement of High Availability, Performance and Reliability in one or more of the following, Oracle, DB2 UDB, MS SQL Server, Sybase and Informix,MySQL
• Strong experience in troubleshooting and resolving database integrity issues, performance issues, blocking and deadlocking issues, replication issues, log shipping issues, connectivity issues, security issues etc.
• Strong experience in Performance Tuning, Query Optimization, using Performance Monitor, SQL Profiler and other related monitoring and troubleshooting tools.
• Ability to detect and troubleshoot SQL Server related CPU,memory,I/O, disk space and other resource contention.
• Strong knowledge of backups, restores, recovery models, database shrink operations, DBCC commands, Clustering, Database mirroring, Replication and AlwaysON.
• Expert experience in implementing operational automation.
• Strong knowledge of how indexes, index management, integrity checks, configuration, patching. How statistics work, how indexes are stored, how they can be created and managed effectively.
• Knowledge of SQL Server tools (Foglight, Profiler, DTA, SSMS, SSCM, PerfMon, DMVs, SSRS, SSAS)
• SQL Development – ability to write and troubleshoot SQL Code and design ( stored procs, functions, tables, views, triggers, indexes, constraints )
• Documentation skills for processes and procedures ( creating KBs, runbooks, topology etc )
• Deploy and support SSIS and DTS packages
• Strong experience database upgrades from one SQL version to another SQL version
• DB engineering support for patching, IT security requests, Auditing, Automation of DB Admin tasks
• Database engineering support for Line of Business applications,
• Support Application Development Requests for Database/Schema Refresh in SDLC
• Troubleshoot application performance issues and recommend changes for the database
• Solid technical and problem solving abilities
* Partner with delivery teams, Infrastructure, database teams in fine tuning the processes and supporting the initiatives in their respective domains.
* Work with delivery teams to understand the pain points in using the technology and provide recommendations
* Assist in consolidation of the technologies used across the IM
* Work on pre-planning of the sustainment efforts and help strategize options to execute the projects efficiently
* Work with product vendors to understand the roadmap of the product and how it can be integrated with the current processes in IM
* Implement best practices for Backup, Recovery & DR strategy.
* Implement security framework for managing users and groups within different functions on the platform.
* Implementing alerts for proactive monitoring.
Knowledge, Experience and Skills
Must have skills
* Strong verbal and written communication skills
* Experience in designing databases suitable for business intelligence projects
* Experience working in windows/unix environments.
* Experience in Performance optimization techniques, partitioning, inmemory and SQL tuning.
*Performance engineer will work with multiple track leads and optimize the SQL queries.
*He / She must be very proficient in Performance Tuning.
*Incumbent must be extremely good in trouble-shooting and a must be a good problem-solver.
*He must identify Performance issues related to Memory areas, SQL queries, access paths etc.
*The performance engineer will first assess the query and decide if it requires performance tuning from database end. The person must be hands on technical expert in writing SQL queries and interpreting the Query and spot the performance bottleneck. Quick turnaround and following conventional tuning approach to issues are necessary prerequisite  

The job includes working with the various technology teams, vendors, and management of new hardware and operating systems infrastructure.
The Engineer will be responsible for the installation, configuration and implementation of complex systems for multiple projects.
Specific responsibilities include:
* Architect, design and implement complex system configurations at an expert level in demanding customer environments.
* Assess, design, plan, and implement complex infrastructure projects
* Directly interact with cross-functional teams and solution architects to identify, develop, and obtain complete information for solutions.
* Plan and execute technical program including developing, validating, executing, and delegating project technical processes and procedures. Serve as escalation point for technical project issues.
* Apply best practices, strategies, methodologies and approaches to large scale projects.
* Achieve high level of Customer Satisfaction on all consulting engagements. The candidate for this position must demonstrate strong team leadership and focus on results enabling businesses to operate more effectively
* Strong communications skills and ability to document and present complex designs to a wider audience.

SQL Server 2000/2005/2008 (MCDBA/MCTS) certified with exposure to 24*7 Production support.
Working Knowledge of Ms-SQL Desired
1. Substantive (10+ years) experience providing database administration in the following environments - MS SQL Server 2000/2005/2008.
2. Expertise on Installation, Backup, Restores/ refreshes, etc.
3. Must have worked on Log shipping, Replication, DB Mirroring, Migration, performance tuning, Cluster environment and DTS/SSIS.
4. Expertise in mentoring and coaching peers.
5. Ability to deal with conflicting deadlines on a regular basis when managing work packages associated with projects.
7. Good working knowledge of Windows XP/2000/2003 Operating environments.
8. Ability to utilize various tools to provide system management functions such as: system backup/recovery, code promotion, system monitoring.
9. Ability to perform software upgrades, and apply patches.
10. Ability to formulate overall strategies and support development effort in areas of major difficulties and organizational complexity.
11. Risk awareness is vital. A simple change can have major unintended consequences.
12. University Degree or College Diploma in Information Systems and demonstrated experience

 Useful Links,-part-1/,-part-2/

Fix & Causes of Issue Use WITH MOVE to identify a valid location for the file While Restore Database one instance to other

Fix & Causes of Issue Use WITH MOVE to identify a valid location for the file While Restore Database one instance to other

Whenever you Restored DB from one instance to another you will get above Error

When you take a backup all information including the file path is included as part of the backup. As you found out if you just run restore database it will try to restore to the exact same location.
when you include with replace, the backup overwrites the existing .mdf and .ldf files. I normally also have the data and log path directives to specify where to put the files (on my phone will edit to include the exact command).
this is normal behavior since if I am restoring a database on the same server you are most likely recovering from something and would want the db files to be the same.

EDIT: Here is my normal restore command, whether on the same server or different. I like to specify exactly where things are going.

   FROM DISK = N'Path\To\Backup.bak'
   WITH MOVE 'dbName_DataFileLogicalName' TO 'Path\To\Data.mdf',
        MOVE 'dbName_LogFileLogicalName' TO 'Path\To\Log.ldf',
       STATS = 10
By always specifying the files I am writing to it cuts out some uncertainty and when some one comes and looks it is very clear what is going on.

let see how to solve that

Let see some Source and destination instance

Source DB

Destination DB see below

Now am going to take backup on Source Instance Welcome-PC\MSSQL05 as see below

Now am going to try it normal RESTORE With Recovery It will give error as see below

To Fix this issue i used to do /try to do RESTORE WITH Norecovery,Replace option it will give error as we saw Earlier

Here Problem is with Logical Name in Sp_helpdb bcos Source DB and Destination DB is differ

Source db sp_helpdb


destionation db sp_helpdb

Solution 1:
Normally we can able to solve using GUI as choose ellipses to locate/point out Destination db physical file

as see below

If we need to achieve this Result we should consider With Move option or put Logical Name as correct as see below

1) We should give Source Logical name with Destionation Path Location

RESTORE database VT
From Disk='F:\To move and check on db\VirendraTest_bk.Bak'
 WITH MOVE 'VirendraTest' TO 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.3\MSSQL\DATA\VT.mdf',
 MOVE 'VirendraTest_log' TO 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.3\MSSQL\DATA\VT_log.LDF',

as see below

Otherwise it will give Error

Also if you need furthur more files needs to restore use WITH MOVE,REPLACE,NORECOVERY
as see below

RESTORE database VT
From Disk='F:\To move and check on db\VirendraTest_bk.Bak'
 WITH MOVE 'VirendraTest' TO 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.3\MSSQL\DATA\VT.mdf',
 MOVE 'VirendraTest_log' TO 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.3\MSSQL\DATA\VT_log.LDF',

Now you can see it is restoring mode so it allows to add differential or log or both

Kindly see now finally use WITH RECOVERY to finish RESTORE

When you click Refresh on Server it is ready you can see tables

but you need to solve orphan users using sp_change_users_login and sp_help_rev_login to copy login script

That is it.

Keep Learning!!!!!!!!!!!!!!!!!!!!!!!

Tuesday, 19 July 2016

RECOVER Database In RECOVERY Mode & SQL Server Restore Error 3183 : How to Fix?

By far the most common search engine query leading to the blog is about fixing a suspect or unrecovered database. The very best way to do this is to use your backups, and to have a backup strategy that allows you to recover in the smallest amount of time and with no data loss. But what if you don’t have a backup for some reason? Well, it depends what’s damaged in the database and when the damage is noticed.
There are three states the database can be in when its damaged:
    • If it’s one of the data files that is damaged and the damage is hit during a query or some other operation then the database will remain online and accessible.
    • The database will be in this state if SQL Server knows that recovery needs to be run on the database but something is preventing recovery from starting. This is different from SUSPECT because there’s nothing to say that recovery is going to fail – it just hasn’t started yet.
    • An example of this is when the database wasn’t cleanly shut down (i.e. there was at least one uncommitted transaction active at the time the database was shut down) and the log file has been deleted.
    • The database will be in this state if the transaction log is damaged and it prevents recovery or a transaction rollback from completing.
    • Some examples of this are:
      • When the database wasn’t cleanly shut down and recovery tries to read a corrupt data page or comes across a corrupt log record.
      • A regular transaction rolls back and tries to read a corrupt data page or comes across a corrupt log record.
You can check the state of a database in the sys.databases catalog view:

SELECT [state_desc] FROM [sys].[databases] WHERE [name] = N'master';
or by using the DATABASEPROPERTYEX function:

SELECT (N'master', N'STATUS');GO
Beware however, as DATABASEPROPERTYEX returns SUSPECT when the database is RECOVERY PENDING or In Recovery as you cant able to Restore/Backup

 I’ll show you below.

DB State is IN RECOVERY Mode due to Transaction Files not recover or still recovering
So after Truncate DB is in normal mode kindly see below


So the state the database is in determines what you can do if you don’t have a backup. The easiest case is when it’s still ONLINE. In this case you can probably run repair to remove the damage, most likely with some data loss (see my previous post on REPAIR_ALLOW_DATA_LOSS for more details), and then take steps to prevent the damage occurring again. If repair can’t fix all the errors then your only option without a backup is to extract as much data as you can into a new database.

The other two database states are more difficult and are what’s causing people to search for help. In this case the database isn’t accessible at all, because recovery hasn’t run or completed and so the database is in an inconsistent state. It could just be logically inconsistent (e.g. a transaction modifying data hasn’t recovered) or worse it could structurally inconsistent (e.g. a system transaction modifying index linkages has’t recovered). Either way, SQL Server wants to prevent you from getting into the database because it doesn’t know what state the data and structures in the database are in. But if you don’t have a backup, you need to get into the database, no matter what state things are in.

You can do this using EMERGENCY mode. In versions prior to SQL Server 2005, EMERGENCY mode wasn’t documented and you had to hack the sysdatabases table to get a database into it (worse still, the exact hack to use was changed from version to version in a bid to obfuscate things). In 2005 though, EMERGENCY mode was documented and proper syntax added to support it. Members of the sysadmin role can put the database into EMERGENCY mode using:

Once in EMERGENCY mode, the database is accessible only by members of the sysadmin role. The database is also read-only as nothing can be written to the transaction log.
Let’s see an example of this. I’m going to create a database and a sample table:

CREATE DATABASE [emergencydemo];
USE [emergencydemo];
CREATE TABLE [salaries] (
    [FirstName]    CHAR (20),
    [LastName]     CHAR (20),
    [Salary]       INT);
INSERT INTO [salaries] VALUES ('John', 'Williamson', 10000);
INSERT INTO [salaries] VALUES ('Stephen', 'Brown', 12000);
INSERT INTO [salaries] VALUES ('Jack', 'Bauer', 10000);
I’m going to start an explicit user transaction and update a row in the table:

UPDATE [salaries] SET [Salary] = 0 WHERE LastName = 'Brown';
Now I’m going to force the data page holding the updated row to be written to disk:

So we have an active, uncommitted transaction that’s modified the table, and the table modification has been written to disk. If the power failed at this point, crash recovery would run and the transaction would be rolled back. I’m going to simulate this by shutting down SQL Server. In another connection:


Server shut down by NOWAIT request from login ROADRUNNERPR\paul.
SQL Server is terminating this process.
I’m also going to simulate damage to the transaction log by just deleting the log file.
Now when I start up SQL Server again, we see the following in the error log:

2007-10-02 11:39:47.14 spid18s     Starting up database 'emergencydemo'.
2007-10-02 11:39:47.46 spid18s     Error: 17207, Severity: 16, State: 1.
2007-10-02 11:39:47.46 spid18s     FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\emergencydemo_log.LDF'. Diagnose and correct the operating system error, and retry the operation.
2007-10-02 11:39:47.60 spid18s     File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\emergencydemo_log.LDF" may be incorrect.
2007-10-02 11:39:47.60 spid18s     The log cannot be rebuilt because the database was not cleanly shut down.
The database wasn’t cleanly shut down and the transaction log isn’t available so recovery couldn’t run. The final message is interesting – there’s a feature in SQL Server 2005 that if you attach or start up a database without a transaction log file, and the database was cleanly shut down, SQL Server will create a new log file automatically. In our case that can’t happen though.
What happens if I try to get into the database?

USE [emergencydemo];

Msg 945, Level 14, State 2, Line 1
Database 'emergencydemo' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
So what state is the database in?

SELECT (N'emergencydemo', N'STATUS');
returns SUSPECT. But checking the sys.databases table using

SELECT [state_desc] FROM [sys].[databases] WHERE [name] = N'emergencydemo';
returns RECOVERY PENDING. This is what I’d expect, as recovery didn’t get a chance to even start.
Now I’ll set the database into EMERGENCY mode so I can get in and see what state things are in:

In the errorlog you can tell when a database has been put into EMERGENCY mode:

2007-10-02 11:53:52.57 spid51      Setting database option EMERGENCY to ON for database emergencydemo.
2007-10-02 11:53:52.59 spid51      Starting up database 'emergencydemo'.
2007-10-02 11:53:52.62 spid51      The database 'emergencydemo' is marked EMERGENCY_MODE and is in a state that does not allow recovery to be run.
Let’s try using the database again:

USE [emergencydemo];
This time it works. What’s the state of the data?

SELECT * FROM [salaries];

FirstName            LastName             Salary
-------------------- -------------------- -----------
John                 Williamson           10000
Stephen              Brown                0
Jack                 Bauer                10000

It’s inconsistent, as I’d expect.

That’s the catch with EMERGENCY mode – you can get into the database but recovery hasn’t run or completed so you don’t know whether the database is logically or structurally consistent. However, at least you can get into the database to extract data out or repair any damage.

SQL Server Restore Error 3183 : How to Fix?

The Scenario is..
Suppose you have a full backup (.bak file) of a SQL Server 2008 database, with partitions and you want to restore this backup file but you are unable to perform the restore operation and you are getting the error msg as shown following. Prompted Error Message
Msg 3183, Level 16, State 2, Line 1
RESTORE detected an error on page (8481:553819295) in database "db_name" as read from the backup set.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Microsoft SQL Server Error 3183

When you try to restore/verify the SQL backup (.bak file) you may have found this error some time because the backup may be corrupted. You have maintained the full backup of the SQL Server database and now you are trying to restore it’s BAK files on the different SQL Server database versions.
Sometimes in middle of the process, suddenly SQL Error 3183 is encountered and prompts Restore detected an error page in the database.

Reasons Behind the SQL Error 3183

The occurrence of the Microsoft SQL Error 3183 is common for database administrators. The SQL Server restore Error 3183 mainly arises due to the pages of the BAK files which have got damaged. There are multiple reasons of SQL error 3183. Here I have listed some common reasons that are responsible for this error:
  1. The very first reasons is that the backup file that you want to restore has got corrupted or damaged.
  2. The viruses or malfunctions can also harm the .bak file and it may be the reason responsible for the Error 3183.
  3. Over storage or we can say the unavailability of free space on temporary disk can also raise the SQL error 3183.
  4. Hardware failure or installation errors can raise lead to the 3183 in SQL Server database.

How to Fix SQL Restore Error 3183?

Suppose you have not accessed your original database from a long time and your only copy of the particular database is corrupted. Then the best option in this scenario is to use the RECOVER DATABASE ... WITH CONTINUE_AFTER_ERROR command. This will attempt to complete the restore past some errors. Your database will be left in a SUSPECT state if the restore actually encounters errors. From this state, you can attempt to repair the database, or extract as much data as possible from it. Some times the manual methods get failed due to highly corrupted database backup file or if the database have gone in suspect mode. At this time it is recommended that you should take the help of third party software.
NOTE: It is recommended that if any error occurs in the database, then perform the DBCC CHECKDB command to check out what reasons are causing the error.

Monday, 18 July 2016

Difference between Database Mirroring and Log Shipping in SQL Server

Difference between Database Mirroring and Log Shipping in SQL Server:

Database mirroring is a functionality in the SQL Server engine that will read from the
transaction log and then copies those transactions from the principal server instance to the
mirror server instance. Database mirroring can operate synchronously or asynchronously. If
configured to operate synchronously, the transaction on the principal will not be committed until
it is hardened to disk on the mirror. Database mirroring supports only one mirror for each
principal database. Database mirroring also supports automatic failover if the principal
database becomes unavailable. The mirror database is always offline in a recovering state, but
you can create snapshots of the mirror database to provide read access for reporting, etc.

Log shipping is based on SQL Server Agent jobs that periodically take log backups of the
primary database, copy the backup files to one or more secondary server instances, and restore
the backups into the secondary database(s). Log shipping supports an unlimited number of
secondaries for each primary database. It also can provide you with a more flexible solution to
use a reporting server instance with older data.

In my experience, database mirroring is preferable to log shipping in most cases, although log
shipping does have the following advantages:

1. Provides backup files as part of the process
2. Multiple secondaries are supported
3. Possible to introduce a fixed delay when applying logs to allow the secondary to be used for
recovering from user error or data corruption

Database Mirroring
Log Shipping



Jobs are not involved. Manual Copy/Restore method copy data principal to Mirror supported. Attach/detach method not supported

SQL Server Agent jobs that periodically take log backups of the
primary database, copy the backup files to one or more secondary server instances, and restore
the backups into the secondary database(s).
Server Limitation
Only one mirror Server Supported
Can be applied to multiple standby server
Recovery model
Full Recovery Model only
Full & Bulk Recovery Model
Read from Destination
Mirror DB always recovering state To Read it we need to use Database Snapshot
We can use stand-by option to read db on stand by server
DB support
We can support more than one db between principal and Mirror. It works database level
We can support multiple db between instances and it works both server and db level
Role Change
Fully automatic
Auto page Recovery
Auto page Recovery supported from SQL 2008 onwards. It will recover damage pages
Auto Recovery Page not supported
It s automatic in high safety with automatic failover only
Manual changes required
Failover Duration
Failover is Fast, sometimes <3seconds but not more than 10 seconds
Can make more than 30 mints
Data Transfer
Individual T-Log Records are Transferred using TCP endpoints
T-Logs are Backedup and Transferred to Secondary server.