How do I map a differential/transaction log backup to its full backup without using MSDB?

If you have multiple full, differential and transaction log backups, there are chances you may end up facing the error below during the restore process. “This differential backup cannot be restored because the database has not been restored to the correct earlier state”   You know that any differential/T-log  backup is mapped to particular full…


Change Data Capture gets disabled when you break mirroring and bring the database online

  Issue ================   Recently we encountered an issue where customer performed a planned failover for a mirrored database and after the failover the new principal went into suspect mode because of some reason (which is outside the scope of this blog) and was not accessible. The database which was mirrored had CDC(Change Data Capture)…


Do NOT delete files from the Windows Installer folder

C:\windows\Installer is not a temporary folder and files in it should not be deleted. If you do it on machines on which you have SQL Server installed you may have to rebuild the operating system and reinstall SQL Server.   The Windows Installer Cache, located in c:\windows\installer folder, is used to store important files for…

5

How to monitor deadlock using extended events in SQL Server 2008 and later

Before I talk about how to monitor the deadlocks let us have a small brief what is deadlock and how we were handling them in the previous versions of SQL Server.   Deadlock: A deadlock occurs when there is a cyclic dependency between two or more threads, or processes, for some set of resources within…

9

Ventured into BULK INSERTS

Recently, whilst working with bulk insert queries I came across an unexpected result set which led to some more research. Therefore, following facts/tips/tricks/workarounds might help you:   If you execute the following code for example:   NOTE: Please bear in mind that the log file used to import the data in this example (errorlog.1) has…

1

How to Login to SQL Server with NT AUTHORITYSYSTEM using PSExec Tool

PSExec tool gives you an option to run a remote process or an application using System account, if SYSTEM account has permission on that application. Although there are many more features that are  For example, if SQL Server has NT AUTHORITY\SYSTEM as one of its logins, then you can use this account to login to…

3

How to limit connections to SQL Server until all databases are ONLINE!

Strange requirement, right? Recently, I got a peculiar case wherein the demand made by the customer was to make sure no application should be able to connect to SQL Server unless and until all the databases are ONLINE. The business requirement of this company was to have all databases online before any of their ten…

8

Unable to open excel files using a cscript in SQL Server Jobs

·         Disclaimer : Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment. ·         The following article discusses the complications that…