Taking backup of all the SQL server databases using a single TSQL Command

Is it possible to take the backup of all the SQL server databases in one single command?

Yes, It is possible to take the backup of SQL server databases in one single command. But it is an undocumented command so needless to say it is "AS-IS".
The command I used was sp_msforeachdb. The command given below takes the backup of all the databases including the system databases.

sp_msforeachdb 'backup database ? to disk="D:\\BACKUPLocation\?_dev.bak"'

You may see error in the output message about TEMPDB but that is fine. This cannot be tweaked further to take only user databases.     

Please leave a command if you like this tip.

Error: "Incorrect syntax near ')'." When Passing function to a Stored procedure

Let us say I have a requirement to pass the current date time to a stored procedure. 
For example let us consider the following SP
 
CREATE PROCEDURE [dbo].[DisplayDate](@Curr_Date DATETIME)

AS SELECT @Curr_Date
 
When I try to execute it with the parameter as getdate(). I would receive the following error

exec [DisplayDate] getdate()

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near ')'.

 
Theorically it should have worked. There is a Limitation on the way we can use it with a stored procedures.

According to the Books online article on
Parameters (Database Engine)
http://technet.microsoft.com/en-us/library/ms190248(v=SQL.105).aspx

"When a stored procedure or function is executed, input parameters can either have their value set to a constant or use the value of a variable. Output parameters and return codes must return their values into a variable. Parameters and return codes can exchange data values with either Transact-SQL variables or application variables."

So it is clear that we cannot use any functions directly with an stored procedure.
So we have to look at other alternatives.

For example
The above stored procedure can be rewritten as
CREATE PROCEDURE [dbo].[DisplayDate]

AS SELECT Getdate()

So we need to consider the fact that we cannot pass the function directly to the stored procedure instead we have to either use it in variable or use the function directly.

 

Error: Unable to load rule class 'ASSPIExistingFarmUnconfiguredWarningCheck Error code 0x84BE0004.” while installing the SQL server 2012.

 

How to solve the Error

“Unable to load rule class 'ASSPIExistingFarmUnconfiguredWarningCheck

Error code 0x84BE0004.” while installing the SQL server 2012.

 

I was given a task to install the SQL server 2012 on the machine. I was very enthusiastic in doing the installation. I ignored the fact that my C drive was running out of disk space. As expected the installation failed. The installation was not able to even rollback to the previous state.  I had to kill the installation.

Since the cause for failure was known, I started adding some disk space to the drive on my virtual machine. Also I cleaned up the failed install. Ensured that all the SQL 2012 components were cleaned up successfully.

 

I started with the installation of SQL 2012 once again. Now I faced a new error during the installation as soon I was done with feature selection page and clicked on the next.

I got the following error

 

“Unable to load rule class 'ASSPIExistingFarmUnconfiguredWarningCheck

Error code 0x84BE0004.”

 

Since I had previous experience in troubleshooting the installation issue with SQL server. I started to look into the SQL server setup logs

 

The details.txt showed the following errors

 

(08) 2013-05-30 15:13:41 Slp: Send result to channel : RulesEngineNotificationChannel

(08) 2013-05-30 15:13:41 Slp: Loading rule: ASSPIExistingFarmUnconfiguredWarningCheck

(01) 2013-05-30 15:13:41 Slp: Error: Action "Microsoft.SqlServer.Configuration.UIExtension.WaypointAction" threw an exception during execution.

(01) 2013-05-30 15:13:41 Slp: Microsoft.SqlServer.Setup.Chainer.Workflow.ActionExecutionException: Thread was being aborted. ---> System.Threading.ThreadAbortException: Thread was being aborted.

(01) 2013-05-30 15:13:41 Slp:    at System.Threading.WaitHandle.WaitOneNative(SafeWaitHandle waitHandle, UInt32 millisecondsTimeout, Boolean hasThreadAffinity, Boolean exitContext)

(01) 2013-05-30 15:13:41 Slp:    at System.Threading.WaitHandle.WaitOne(Int64 timeout, Boolean exitContext)

(01) 2013-05-30 15:13:41 Slp:    at Microsoft.SqlServer.Configuration.UIExtension.Request.Wait()

(01) 2013-05-30 15:13:41 Slp:    at Microsoft.SqlServer.Configuration.UIExtension.UserInterfaceProxy.NavigateToWaypoint(String moniker)

(01) 2013-05-30 15:13:41 Slp:    at Microsoft.SqlServer.Configuration.UIExtension.WaypointAction.ExecuteAction(String actionId)

(01) 2013-05-30 15:13:41 Slp:    at Microsoft.SqlServer.Chainer.Infrastructure.Action.Execute(String actionId, TextWriter errorStream)

(01) 2013-05-30 15:13:41 Slp:    at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.ExecuteActionHelper(TextWriter statusStream, ISequencedAction actionToRun, ServiceContainer context)

(01) 2013-05-30 15:13:41 Slp:    --- End of inner exception stack trace ---

(01) 2013-05-30 15:13:41 Slp:    at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.ExecuteActionHelper(TextWriter statusStream, ISequencedAction actionToRun, ServiceContainer context)

(01) 2013-05-30 15:13:41 Slp:    at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.ExecuteActionWithRetryHelper(WorkflowObject metaDb, ActionKey action, ActionMetadata actionMetadata, TextWriter statusStream)

(01) 2013-05-30 15:13:41 Slp:    at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.InvokeAction(WorkflowObject metabase, TextWriter statusStream)

(01) 2013-05-30 15:13:41 Slp:    at Microsoft.SqlServer.Setup.Chainer.Workflow.PendingActions.InvokeActions(WorkflowObject metaDb, TextWriter loggingStream)

(01) 2013-05-30 15:13:41 Slp:    at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionEngine.RunActionQueue()

 Now I started to look into what is the rule ASSPIExistingFarmUnconfiguredWarningCheck corresponding to  found this in the SystemConfigurationCheck_Report.htm

ASSPIExistingFarmUnconfiguredWarningCheck

Checks whether SharePoint is configured, and if not, recommends adding the Database Engine to the installation.

Not applicable

This rule does not apply to your system configuration

Well this check is if sharepoint is configured on the db server. But I do not have sharepoint configured.

I tried to check if anyone else is facing the same issue but unfortunately I was not able find many hits on this. So I started to check what next step to resolve this issue is. I started the SQL server installation and selected the repair

 

I ran through the set up wizard. The fact that the installation was running proves that even though I had removed the components from my server, still there were some left overs which may be due to the low disk condition the rollback my not have happened correctly.

Once the repair completed I was able to run the installation without any issues.

 

Thank you for you time in viewing this blog. Please feel free comment with your questions regarding this article.

How to get a SSL Configured on SQL server.

Getting a valid Certificate:

---

The first steps involves getting a valid certificate from the certificate autority. Since I do not have a Certificate Authority (CA) of my own. I will make use of a utility called MakeCert.exe which is part of .net Framework SDK. You can download it from the following link .NET Framework 2.0 Software Development Kit (SDK) (x86).

Just any certificate cannot be used to enable SSL on SQL server. There are certain requirements a certificate should meet. These are described in the following knowledge base article Encrypting Connections to SQL Server.

Certificate Requirements

---

For SQL Server to load a SSL certificate, the certificate must meet the following conditions:

• The certificate must be in either the local computer certificate store or the current user certificate store.
• The current system time must be after the Valid from property of the certificate and before the Valid to property of the certificate.
• The certificate must be meant for server authentication. This requires the Enhanced Key Usage property of the certificate to specify Server Authentication (1.3.6.1.5.5.7.3.1).
• The certificate must be created by using the KeySpec option of AT_KEYEXCHANGE. Usually, the certificate's key usage property (KEY_USAGE) will also include key encipherment (CERT_KEY_ENCIPHERMENT_KEY_USAGE).
• The Subject property of the certificate must indicate that the common name (CN) is the same as the host name or fully qualified domain name (FQDN) of the server computer. If SQL Server is running on a failover cluster, the common name must match the host name or FQDN of the virtual server and the certificates must be provisioned on all nodes in the failover cluster.
• SQL Server 2008 R2 and the SQL Server 2008 R2 Native Client support wildcard certificates. Other clients might not support wildcard certificates. For more information, see the client documentation and KB258858.

So inorder generate a proper certficate on MakeCert utility I used the following command

MakeCert "c:\temp\MyCertnew.cer" -pe -n "CN=Hostname.Contoso.com" -ss my -sr LocalMachine -a sha1 -sky exchange -eku 1.3.6.1.5.5.7.3.1 -sp "Microsoft RSA SChannel Cryptographic Provider" -sy 12 -iR LocalMachine 

Once the command is completed you can check the Certificate store in the local computer for the certificate. It should look similar to the ones shown below.

 Now let us map each of the command to see if the certificate fulfils our requirement for SQL server.

-pe-- Create a Private Exportable Key.
-n "CN=Hostname.Contoso.com" -- Set the subject propert with the host FQDN name.
-ss -- Load the certificate in the local store.
-sr-Load the certificate in the local computer store.
-a Signature algorithm used.
-sky exchange-- Set Key spec option on AT_Exchange
-eku-- Enhanced Key used for setting certificate for server authentication.
-sp-- Cryptographic API provider
-sy-- CryptoAPI providers
-ir-- issue certificate store.

So we have got pretty much what is required for configuring SSL on the SQL Server.

Now the questions is how do we confirm that the certificate I have from the CA is valid
Let us verify the certificate properties.

The Hostname.contoso.com is a valid FQDN name
The validity of the certificate
and the message " You have a private key that corresponds to this certificate"

The Enhanced key usage property should show the purpose as Server authentication
the subject should also show the valid FQDN name.


Once we have validated it we can use this certificate to configure SQL server using SQL server configuration manager.
Right click on the SQL server 2005 Network configuration
Right click on Protocols for MSSQLSERVER and then select the certificate tab
Under the drop down you can see the certificate.

Also under the flags tab select " Force protocol encryption"

Now go ahead and restart the SQL server. Once we have restarted successfully we can look up the SQL server errorlog to confirm if the certificate is loaded correctly

We should see a message similar to

2013-01-25 13:14:27.150 Server The certificate was successfully loaded for encryption.

Now let us connect to the SQL server and verify if our connections are encrypted.

We could use the following DMV to check the status of the connections

 
please feel free to ask your question using the comment section.