Archive for April, 2013

How to schedule database backups using Idera SQLSafe?

In Idera SQLSafe, to schedule database backups, you will need to create a backup policy. The following documents the steps on how it can be done:
1. Open SQLSafe Management Console.
2. Click on the “Policies” section on the left hand column.
3. If no policies existed, you will see the “Create New Policy” button on the right hand side. You can then click on the “Create New Policy” to start creating a policy. Else, right-click on the “Backup Policies” folder on the left hand column and select “Create Backup Policy…” on the popup menu.
4. On the “SQLSafe Backup Policy Wizard” screen, key in the name for the policy and key in the optional description. Under the “Action” section, select the option to “Monitor + Automatically Create Backup Jobs.” Click the “Next” button to continue.
5. On the “Membership” screen, click on the “Add/Remove Instances” button.
6. On the “Edit Instance Selection” screen, select the instance of SQL Server you wish to run backups on. Click on the “>” button to move it to the right hand pane. Click the “OK” button.
7. Back on the “Membership” screen, click on the “Choose Databases” drop-down box to select the databases.
8. On the “Edit Database Selection” screen, you can de-select to omit the database(s) from the backup policy. After you make your selection or omission. Click the “OK” button to continue.
9. Back on the “Membership” screen, click the “Next” button to continue.
10. On the “Options” screen, make your backup type choices and your compression and encryption choices. You can also select the “Include database logins in backup file” option to add the logins so that SQLSafe and restore the logins for you during restores if and when needed. Click the “Next” button to continue.
11. On the “Locations” screen, select your choices for “If Archive Exists:” and “Access Filesystem As:” if you are backing up to a remote location. Under the “Primary Archive” section, modify the backup location if necessary and specify the retention day by checking the “Remove files older than” checkbox and specify the retention days. Click the “Next” button to continue.
12. On the “Schedules” screen, specify the schedules for the backups. Click the “Next” button to continue.
13. On the “Notifications” screen, specify the actions you wish SQLSafe to take in case of failure, completion, cancellation, or other options listed. Key in the list of recipients of email addresses for the notification. You will also need to click on the “Configure Email” button if you had not configured the email settings for SQLSafe. Make sure you have the SMTP server information handy. Click the “Next” button to continue.
14. On the “Summary” screen, review the options you had selected and then click on the “Finish” button to finish.
15. You can verify the SQL Server Agent job that’s created for the policy in the instance of SQL Server you had selected to create the backup policy.

How to use SafeToSQL to extract SQLSafe backups to Native SQL Server backups?

If you need to move a backup file over to a separate network domain that will not have Idera SQLSafe installed, you can use the SafeToSQL Utility to extract and convert the SQLSafe backup file to a Native SQL Server backup file. Prior to starting, you will need to make sure SafeToSQL is installed. To verify if it’s been installed, you can go to the command prompt and browse to C:\Program Files\Idera. If you see the SafeToSQL directory, then it is installed. If it’s not installed, you can run the installation by:

  1. Run the Setup.exe in the root of the installation kit.
  2. On the Install screen, click Install SafeToSQL Utility.
  3. Accept the license agreement.
  4. Then click on the “Install” button.

You can convert a SQLSafe backup file to a Native SQL Server backup file using the following syntax:

SafeToSQL source_file_path [-backupfile file_name] [ -backupset #] [-password pwd] [-list]

If you need more explanations for the parameters, can you go to http://www.idera.com/Help/SQLsafe/7-2/web/.

In the following example, we will extract a .safe file to a .back file:

  1. On the server that has SafeToSQL installed, browse to the command prompt and the directory: C:\Program Files\Idera\SafeToSQL>
  2. Run the SafeToSQL command to extract. For example: C:\Program Files\Idera\SafeToSQL>SafeToSQL “D:\SQL_Backups\SQLsafeRepository_Full_201304251724 (1 of 1).safe”

Note that we add “” for the .safe file because it has (). You will need to add the backupset # if there are more than one and the password if it is encrypted.

REFERENCES:

SafeToSQL command
http://www.idera.com/Help/SQLsafe/7-2/web/

How to backup databases manually using Idera SQLSafe?

To backup databases manually using Idera SQLSafe you can:
1. Open the SQLSafe Management Console.
2. Under the “Servers” section on the left hand pane, expand “SQL Server Instances,” right-click on the instance you wish to backup the databases, select “Backup Database(s)…” from the pop-up menu.
3. On the “SQLSafe Backup Wizard” and on the first screen, which is the “Databases” screen, select the databases you wish to backup. Click the “Next” button to continue.
4. On the “General” screen, select the backup type and key in the “Name” and “Description” for the backups if needed. Click the “Next” button to continue.
5. On the “Locations” screen, select the “Location Type” and “If Archive Exists” options and then choose the “Primary Archive” location. Click the “Next” button to continue.
6. On the “Options” screen, select the “Compression” and “Encryption” options. You can also select the “Include database logins in backup file” under the “Advanced Options” section to restore the logins for you during the restore when needed. Click the “Next” button to continue.
7. On the “Notifications” screen, you can specify the email address(s) to get notified in case of failure or success and among other options. If the email notification system has not been configured, you will need to click the “Configure –Email” button to configure the email notification system for Idera. Make sure you have your SMTP server information handy and also the user name and password for authentication if needed. Click the “Next” button to continue.
8. On the “Summary” screen, review the location and options selected and then click on the “Backup” button to start the backup process.

How to Resolve the “Cannot resolve the collation conflict between…in the equal to operation?”

As a Production Support DBA, I have encountered the collation conflict error, “Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CS_AS” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation” in the SQL Servers that we support and usually they are reported to us either by the Report Writers, SQL Server Developers, or via our monitoring system. While the fast and quick fix solution is to add the “COLLATE DATABASE_DEFAULT” keywords to the columns in the JOIN or WHERE clauses, it may be worthwhile to understand the root cause.

SQL Server collation specifies the sort order for character data types. You can specific collations at different levels:

  • Instance
  • Database
  • Column

When you install SQL Server, you specify the default collation for the instance. During database creation, you can specify the collation. If omitted, it will default to the instance collation. When you create a character column, you specify the column collation. If not specified, it will default to the database collation.

It is critical to note that one of the most common mistakes for installation of SQL Server is not obtaining the correct collation from the application support team or application vendor before running the installation. Usually, this is causes real royal pain, as you will have to rebuild the master database to change the collation. It sounds simpler than the hard work and hours required. You will need to backup all databases, script out all logins and permissions, script out all the SQL Server Agent jobs, operators, and alerts, and then rebuild the master database to change the collation. If successful, then restore all databases, create the logins and resolve the orphaned users, create all the SQL Server Agent jobs, operators, and alerts. If you remember to always ask and confirm the server collation, you may be able to save yourself the trouble and at least a few hours or days depending on the sizes of the databases. Be very careful, if you are about to build a new server for Great Plains or Microsoft Dynamics CRM. Make sure you get the correct server collation or else you will get burned. Hopefully, I have helped avoid you the trouble.

We will now create an example with the following steps to generate the collation error and then resolving the error:

  1. Find the server collation.
  2. Create a test database with a different collation and create a table with data.
  3. Create a second test database with the default server collation and create a table with data the same as in step 2 except in lower cases.
  4. Generate the collation error by joining the columns from the two databases. The error is caused by collation differences between the two databases.
  5. Resolve the collation error by adding the COLLATE DATABASE_DEFAULT clause to the JOIN.

Implementation steps:

  1. Find the server collation. You can use the SERVERPROPERTY() function. In my test server, the collation is: SQL_Latin1_General_CP1_CI_AS. I will create a database with Case Sensitive for testing.
  2. SELECT SERVERPROPERTY('collation') as [Server Collation]
    
  3. Create a test database with a different collation and create a table with data.
  4. -- create the test database in collation case sensitive
    CREATE DATABASE CollationCaseSensitive
    COLLATE SQL_Latin1_General_CP1_CS_AS -- specify the non default server collation
    GO
    
    USE CollationCaseSensitive
    GO
    CREATE TABLE CollationCS
    (
    ID int Identity (1,1)
    , FirstName varchar(50)
    , LastName varchar(50)
    )
    GO
    INSERT CollationCS (FirstName, LastName)
    VALUES ('Lady', 'GaGa')
    ,('Kim', 'Kardashian')
    ,('Barack', 'Obama')
    
  5. Create a second test database with the default server collation and create a table with data the same as in step 2.
  6. -- create the test database in the server default collation: case insensitive
    CREATE DATABASE CollationCaseInsensitive
    GO
    USE CollationCaseInsensitive
    GO
    CREATE TABLE CollationCI
    (
    ID int Identity (1,1)
    , FirstName varchar(50)
    , LastName varchar(50)
    )
    GO
    INSERT CollationCI (FirstName, LastName)
    VALUES ('lady', 'gaga')
    ,('kim', 'kardashian')
    ,('barack', 'obama')
    
  7. Generate the collation error by joining the columns from the two databases. The error is caused by collation differences between the two databases.
  8. -- generate the collation error
    SELECT *
    FROM CollationCaseInsensitive.dbo.CollationCI AS CI 
    	JOIN CollationCaseSensitive.dbo.CollationCS AS CS
    	ON CI.FirstName = CS.FirstName
    

    Your will get this error:

    Msg 468, Level 16, State 9, Line 5
    Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CS_AS” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation.

  9. Resolve the collation error by adding the COLLATE DATABASE_DEFAULT clause to the JOIN.
  10. -- to resolve the collation error, add the COLLATE DATABASE_DEFAULT 
    -- on the columns in the JOIN
    SELECT *
    FROM CollationCaseInsensitive.dbo.CollationCI AS CI 
    	JOIN CollationCaseSensitive.dbo.CollationCS AS CS
    	ON CI.FirstName COLLATE DATABASE_DEFAULT = CS.FirstName COLLATE DATABASE_DEFAULT 
    

 

REFERENCES:

SQL Server Collation Fundamentals
http://msdn.microsoft.com/en-us/library/aa174903(v=sql.80).aspx

DATABASEPROPERTY
http://msdn.microsoft.com/en-us/library/aa258275(v=sql.80).aspx

SERVERPROPERTY
http://msdn.microsoft.com/en-us/library/ms174396(v=sql.105).aspx

IDENTITY
http://msdn.microsoft.com/en-us/library/aa933196(v=sql.80).aspx

How to install Idera SQLSafe Backup?

Idera is one of the well-known backup software for SQL Server. As per its website, it can backup 50% faster than native SQL Server backup and with the compression ratio up to 95%. You can also automate the SQL Server backups and restores with its backup policies. This blog post briefly documents the preparation work required.

Preparation work before you run the installation:
1. Download it from the link provided to you by the Idera and make sure you have the license key. You will need to key in the license key after the installation.
2. Location to install SQLSafe.
3. The SQL Server instance to host the Repository database. The Repository database is used to store all SQLSafe backup and restore operations, including all the file paths for the backups. If you are installing SQLSafe to manage the backups and restores for multiple instances, it is recommended that you select a dedicated instance as the central management instance.
4. Select the user account you want the Management Service to use. You can use the SQL Server service account.
5. Choose a location to store the backup files and make sure the user account for the Management Service in step 4 has permission to write to the folder.
The installation is simply filling in the blanks you had done with the preparation work. After the installation, you may want to consider installing the SafeToSQL Utility. You can use the SafeToSQL Utility to extract the Idera SQLSafe backup files to native SQL Server backup file to restore to the network domains that do not have licenses for Idera SQLSafe.

After the completion of the installation, make sure you key in the license key or else Idera SQLSafe will expire after the trial period. To key in the license key:
1. Open SQLsafe Mangement Console.
2. Click on the Tools menu.
3. Click on the “License Key Manager” option on the drop-down menu.
4. On the “License Key Manager” screen, click the “Add” button and then copy and paste the license key that the Idera Sales or Support Team had sent you. Click the “Ok” button and then the “Close” button.

How to Remove Database Backups Older Than X Number of Days?

Have you ever created a GUI Maintenance Plan to remove database backups older than X number of days and noticed that the full backups, transaction log backups, and differential backups older than X numbers of days are removed based on the specified retention days which is technically correct, but since the full baseline backups are removed the differential and/or transaction log backups that still exist and depend on the full baseline backups are rendered invalid in case restores are required?  I know that’s a fully loaded question. Let’s slow down and break it into pieces.

There are generally 3 common database backup types in SQL Server:

  • Full
  • Differential
  • Transaction log

Simply put, full database backup type is considered as the baseline backup as it is required to be restored first in case of disaster recovery before any other backup types. If you have differential backups and transaction log backups, they are then restored depending on the specific time and point of restore is needed.

Backup schedules and types are usually based on the 5 common factors:

  • Size
  • Restorability requirements
  • Disk space availability
  • Speed
  • Common sense (don’t laugh because common sense is not common practice and they are very different)

All of the above 5 common factors are interrelated. For example, if the database you are backing up is over 1 TB, most likely you should consider weekly full backup, daily differential backup, hourly or more frequent transaction log backups. Depending on the restorability requirements, you may need to adjust the frequencies provided that you have enough disk space storage. Usually, the C-level executives will wish to have database backups for the past year or since the company been in business. It is your job to give them the facts about the required budget. Notice that I did not say disk space because budget will make more sense to them than megabytes, gigabytes, and terabytes. Just translate GB to GBP if you are in the United Kingdom or GB to USD if you are in the United States. Speed of the backups and restores can also be a critical factor. You don’t want your backups to run and overlap their next subsequent scheduled runs. Don’t laugh, it happened too often. To increase the speed of the backups and restores, you can consider splitting the backups and enable backup compression if your SQL Server instance is running SQL Server 2008 Enterprise Edition or SQL Server 2008 R2 Standard Edition or later. You can even consider third party backup software such as Idera SQL Safe Backup, Red Gate SQL Backup Pro, Dell (Quest) Litespeed, EMC Avamar, and many more if the budget permits.

Let’s head back to the main discussion without further digression. If you had ever created maintenance plans to remove backups older than 3 days for databases that are less than 100 GB with daily full backups and hourly transaction log backups, most likely you had noticed that the full backups and transaction log backups that are older than 3 days are removed. But, since the full baseline backups older than 3 days are removed the transaction log backups before the next full backup are invalid for restore as the baseline full backups are removed. How can we resolve this issue and make sure the transaction log backups that have dependencies on the full baseline backups are also removed?

The key to resolution can be described in the following steps:

  1. Decide on the retention day as per your business requirement for the database backups.
  2. Based on the retention days on step 1, find the next earliest full backup start date and time for all databases.
  3. When the full backups are removed, you need to make sure the transaction logs before the next earliest full backups are also removed.

The following is the example code. Make sure you review it carefully and modify it to suite your retention day and backup path folder.  Sure, you can code it in many different ways. For example, put it in a stored proc, find the backup path using the msdb.dbo.backupfile, and many others just to show off your talent.  Please share with us your solutions because great talents are terrible things to waste.

--/*
--Remove database backups older than # of day.

--Note: If you had removed the full baseline backup, all the diff and tlog backups between the full baseline backup up to the next full backup can't be restored if the baseline backup is missing. Therefore, they should be removed too.
--*/

DECLARE @RetentionDays tinyint 
SET @RetentionDays = 3 -- keep 3 days of backups

DECLARE @DeleteDateForFullBackups NVARCHAR(50)
DECLARE @DeleteDateTimeForFullBackups DATETIME

DECLARE @DeleteDateForTlogBackups NVARCHAR(50)
DECLARE @DeleteDateTimeForTlogBackups DATETIME

-- find the latest full backup for the threshold day
SELECT @DeleteDateTimeForFullBackups = MIN(bckset.backup_start_date)
FROM msdb..backupset bckset
                INNER JOIN msdb..backupmediafamily bckfmly
ON bckset.media_set_id = bckfmly.media_set_id
WHERE (bckset.[Type] = 'D') AND (DATEDIFF(dd, bckset.backup_start_date, GETDATE()) = @RetentionDays)

-- find the latest diff or tlog backup for the threshold day
-- the date and time range should be from the full backup to be removed up and before the next full backup
SELECT @DeleteDateTimeForTlogBackups = MIN(bckset.backup_start_date)
FROM msdb..backupset bckset
                INNER JOIN msdb..backupmediafamily bckfmly
ON bckset.media_set_id = bckfmly.media_set_id
WHERE (bckset.[Type] = 'D') AND (bckset.backup_start_date > @DeleteDateTimeForFullBackups) -- this is where the magic happens

select @DeleteDateTimeForFullBackups, @DeleteDateTimeForTlogBackups

-- remove the full backups
SET @DeleteDateForFullBackups = (SELECT REPLACE(CONVERT(NVARCHAR, @DeleteDateTimeForFullBackups, 111), '/', '-') + 'T' + CONVERT(NVARCHAR, @DeleteDateTimeForFullBackups, 108))
EXECUTE master.dbo.xp_delete_file
0 -- file type: 1 report files, 0 backup files
, N'C:\SQLBackup\' -- file path, make sure there is a trailing '\'
, N'bak' -- file extension, no dot in front of it
, @DeleteDateForFullBackups -- delete all files before this date and time
, 1 -- searching subfolders to delete: 1 yes, 0 no


-- remove the diff and tlog backups, create another block of code with modifications if you have diff backups
SET @DeleteDateForTlogBackups = (SELECT REPLACE(CONVERT(NVARCHAR, @DeleteDateTimeForTlogBackups, 111), '/', '-') + 'T' + CONVERT(NVARCHAR, @DeleteDateTimeForTlogBackups, 108))
EXECUTE master.dbo.xp_delete_file
0 -- file type: 1 report files, 0 backup files
, N'C:\SQLBackup\' -- file path, make sure there is a trailing '\'
, N'trn' -- file extension, no dot in front of it
, @DeleteDateForTlogBackups -- delete all files before this date and time
, 1 -- searching subfolders to delete: 1 yes, 0 no

REFERENCES:

Backupset
http://msdn.microsoft.com/en-us/library/ms186299(v=sql.90).aspx

backupmediafamily
http://msdn.microsoft.com/en-us/library/ms190284(v=sql.90).aspx

How to resolve “Error:11009 No passive nodes were successfully patched” when patching SQL Server 2005 SP4 CU3?

This past week, we had encountered the “Error: 11009 No passive nodes were successfully patched” when patching SQL Server 2005 SP4 CU3 for a clustered instance. Where SP4 is Service Pack 4 and CU 3 is Cumulative Update Package 3. It is critical to note that while SP4 is the latest service pack for SQL Server 2005, Microsoft has released more updates since SP4 and called it Cumulative Update Package 3.  For our scenario, it is a passive/active clustered instance of SQL Server 2005 running on Windows Server 2008 Enterprise edition. After digging around for help, we have found the suggestion on one of the postings to pause the passive node resolved this error. http://social.msdn.microsoft.com/Forums/en-US/sqlsetupandupgrade/thread/c83861e7-c88f-4a2a-bd4e-1cbd3830facb/

To pause the passive node, you can go to the Failover Cluster Manger, expand the console tree under nodes, right-click on the passive node and then click Pause.

You will need to first pause the passive node, install CU3, reboot (this will cause failover), pause the passive node, install CU3, then reboot (this will cause failover). Problems solved and have a nice day!

REFERENCES:

Error 11009 No passive nodes were successfully patched
http://social.msdn.microsoft.com/Forums/en-US/sqlsetupandupgrade/thread/c83861e7-c88f-4a2a-bd4e-1cbd3830facb/

How to pause a clustered node
http://technet.microsoft.com/en-us/library/cc731291.aspx