Copy-Only Backup

Copy-only backup are new feature in SQL 2005. It allows a backup of any type to be taken without affecting any other backups. Copy-only backup doesn’t affect the restore chain. It cannot be used as a restored base in the event of failure. They are useful in a situation where you want to get a copy of the database for testing purposes or when you want to refresh the staging, development and testing databases.

Copy-only backups are not supported via SQL Server Management Studio(SSMS). It must be performed using T-SQL Backup command.

Backup Database MYDATABASE
TO DISK = 'Drive\Folder\filename.bak'
WITH COPY-ONLY

Page Life Expectancy

Page Life Expectancy value is the number of seconds a page will stay in the buffer pool without references. So a buffer that has 300 seconds page life expectancy will keep any given page in the buffer pool for 5 minutes before the buffer pool flushes the page to disk.To determine page life expectancy value using query.

SELECT [cntr_value]
FROM master..sysperfinfo
WHERE object_name = 'SQLSERVER:BUFFER Manager'
AND counter_name ='Page Life Expectancy'

Error when configuring database mirroring

This error occurs when configuring database mirroring on a stand alone PC. 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.The syntax for a fully-qualified TCP address is:TCP://.[.]
:--------------------------------------------------------------------------------------
To trouble shoot this error, do the following below.
Right click on the "My Computer" then properties.
Click on the "computer name" tab, then click on the change button next to the "To Rename this computer or join a domain.
click change" then you see the "Computer name changes" window, click the "More" button on it. You will see the "DNS suffix and NetBIOS ComputerName" window. Here, type "local" to the "Primary DNS Suffix of this Computer" textbox. then press OK 3 times and restart computer.

After put this "computername.local" to your computer name for the mirroring computer server network address.

If you are in domain environment, just use really the FQDN.This is applicable just in case of stand alone machine.

Find all SQL Servers on the Network

To find all the available SQL Servers on the network that are broadcasting their existence.

Exec Master..XP_CMDSHELL 'SQLCMD -L'

First you have to enable XP_CMDSHELL from the Surface Area configuration before running this command.

ERROR 18452: The User is not associated with a Trusted SQL Server Connection

This can occur due to one of the following reasons:

1: If SQL Server has been configured to operate in “Windows Authentication mode” and doesn’t allow the use of SQL Logins.
Resolution: Change the Authentication mode from “Windows Authentication mode” to “Mixed Mode”(SQL SERVER and “Windows Authentication mode”)

2: You are trying to connect using SQL Server Authentication but the login used does no t exist on the SQL Server.

3: The login could have been using windows Authentication but the login is an unrecognized windows principal. An unrecognized windows principal means that the login cannot be verified by windows. This could be because the windows login is from a domain not trusted by the server. In this case do the following to determine if there is a trust between the client and the server.

PING: The Server on both IP and Name from the Client.
PING: The Client on both IP and Name from the Server.

Enabling Service Broker

To configure database mail, you need to enable service broker on MSDB databse. Make sure that Sql Server Agent is stoped before running the following query below.

ALTER DATABASE MSDB
SET ENABLE_BROKER

To check that service broker has been enabled, run this piece of sql below.
SELECT IS_broker_Enabled
FROM sys.databases
WHERE name = 'MSDB'

If 1= Broker enabled.
If 0 = Broker Not enabled.

Single Quotes

When specifying a value with a single quote, you need to double it.

SELECT 'Hello''s'

Returns: Hello's