Archive for the 'SQL' Category

SQL Auto Protection Fails

Using DPM 2012 had an error with SQL Auto Protection.  In the error, it says to run “AutoProtectInstances.ps1”  When I did that, I got this error:

Start-DPMAutoProtection : DPM could not enumerate SQL Server instances using Wi
ndows Management Instrumentation on the protected computer <ComputerName> . (ID: 965)
Please make sure that Windows Management Instrumentation for SQL server is in g
ood state.

A quick search turned up this article talking about protecting SharePoint.  The underlying problem was with the SQL instance that they were using so this fix worked:

http://www.mysharepointadventures.com/2013/01/fixed-dpm-could-not-enumerate-sql-server-instances-using-windows-management-instrumentation/

You do have to run the cmd from an elevated prompt.

The stub received bad data – DPM backup of a SQL DB

My DPM Server:
Server 2008 x64
DPM 2007 SP1
SQL 2005 SP3 (local to DPM)

Protecting:
Server 2003 SP2 x64
SQL 2005 SP3

I have about 30 databases being backed up from the one client machine.  All of them backup just fine except one.  Every time I try to do a synchronization or a full backup I get the following error:

Triggering synchronization on *myserver\mydatabase* failed: Error 46: DPM failed to perform the operation because too many objects have been selected. Select fewer objects and then retry this operation.

Error details: The stub received bad data (0x800706F7)

Recommended action: Select fewer objects. 1) If you are trying to protect a large number of data sources on a volume, consider protecting the whole volume instead of individual data sources. 2)If you are trying to recover a large number of folders or files from a volume, consider recovering the parent folder, or divide the recovery into multiple operations.

This happens even if I just select the one database.  All the other databases backup correctly.

After posting in the news group, I got a question from a Microsoft person whether full text indexing was enabled, and if so how many catalogs.  Upon investigation, it appears that this is one of the only databases that i have that has full text indexing enabled.  It has 32 indexes. 

So with the suggestion of a colleague, I did a rebuild of the indexes:

    1. In SQL Server Management Studio (SSMS) expand the database/Storage/Full Text Catalogs
    2. Right click on the Full Text Catalogs folder and select Rebuild All

Then I went back to my DPM server, to the protection group and selected the database in question.  I did a “Create recovery point – Disk”, “Create a recovery point by using express full backup”.

That worked, so maybe that means the problem is fixed…

SQL Queries

I don’t usually do a lot with SQL, but lately I have found myself having to look at, and work with SQL a good bit.  It seems that things come in waves. 

Anyway, I was looking for a quick way to copy a table from one Database to another and came across this blog which I think has a lot of good queries just waiting to be tinkered with:

http://www.sqlservercurry.com/

And the particular post that got me there was:

Copy a table from one database to another in SQL Server 2005

4:09 AM Posted by Suprotim Agarwal
Labels: SQL Server Administration

If you have a table in a database and you would like to copy the table to another database, use this query:
SELECT * INTO AdventureWorks.dbo.CustomersTemp FROM Northwind.dbo.Customers
Just remember that using this query will only transfer the schema and data. It does not transfer the indexes, foreign keys, statistics etc.
If you want to transfer all the objects from one database to another, open Sql Server Management Studio > Right click on your database > All Tasks > Generate SQL Scripts. Then run these scripts against the new database.
Transfer both schema and data
To copy both data and schema, use the Microsoft SQL Server Database Publishing Wizard 1.1. This tool works for both SQL 2000 and SQL 2005 and generates a single SQL script file which can be used to recreate a database (both schema and data).

DPM could not connect to SQL reporting services because of IIS connectivity

I am running Data Protection Manager 2007 on Windows Server 2008 (x64).  I have not been able to use the reporting piece, and haven’t been running DPM long enough to wonder much about it, but when I went to finally figure it out the answer was rather simple.  If you want to fix it, the answer is here, but for more about what the problem is, you can go to the forum post below.

This problem occurs because of a new permission requirement in IIS 7.0. This
permission requirement is for ISAPI extensions that use a wildcard (*) script
mapping.

To work around this problem, follow these steps:
1. Click Start, click Run, type inetmgr, and then click OK.
2. In Internet Information Services (IIS) Manager, expand Web Sites, expand
Default Web Site, and then click the virtual directory for the report server.
3. Under Features View, double-click Handler Mappings.
4. Under Actions, click Edit Feature Permissions.
5. Click to select the Scripts check box, and then click OK.

How to fix— Data Protection Manager error 3013  SQL reporting tab  ? in Data Protection Manager

SQL for Tivoli Storage Manager

 Found this web page that has sample queries for TSM.

This page has a collection of useful SQL statements for IBM Tivoli Storage Manager (TSM). Here you can find out a lot of selects that will help you to get information from TSM and to construct your own SQL statements.

SQL for Tivoli Storage Manager

SQL and ODBC

I was trying to figure out how to get some data from an ODBC source and put it into SQL, and found a post with an interesting possible answer: 

Recently, I discovered an issue with SQL Server Integration Services (SSIS) regarding how the Execute SQL Task item handles returning a result set to the control flow. Lets start by demonstrating an existing issue with the Execute SQL Task using ODBC then I’ll present a solution. First, create a new DSN that points to the database using the ODBC Data Sources Administrator (Control Panel | Administrator Tools | ODBC Data Sources). If you are only dealing with SQL Server you can still test this out by creating a DSN that uses the SQL Server ODBC driver.

Sql Server Integration Service (SSIS) Execute SQL Task ODBC Result Set Bug/Workaround

Processing the date can be a pain…

 I was trying to migrate a table from one database to another using a script.  It shouldn’t have been too complicated.  It probably wouldn’t be for someone who has done it before.  I had a problem getting some of the fields to copy over.  They all involved date/time information.  My search led me to this interesting post:

SSIS: What’s the difference between DT_DATE and DT_DBTIMESTAMP

Database people don’t generally like date values too much. They’re too fiddly. There’s god-knows-how-many ways of representing them…and don’t even get me started on regional date settings!!

SSIS Junkie : SSIS: What’s the difference between DT_DATE and DT_DBTIMESTAMP

Product instance … has had update 3054 installed…

So when I tried to install the update that I downloaded in my earlier post, I got the following message:

The product instance SQL Tools has had update 3054 installed. You do not need to install this update. An update equal to or greater than 3159 should be downloaded.

Isn’t that special… 

Guess I need to do some more searching…

Please visit the IDE in Script Task Editor…

I was trying to build a tool to move some data between a TSM Database and a SQL 2005 Database.  Mostly so that I can create a few reports to impress people who don’t have any idea what it is that I am reporting on. 

There are a number of issues with trying to do this, not the least of which is the fact that I have no idea what I am really doing.  The fact that I am working with a mixture of x32 and x64 operating systems and applications isn’t helping.  The fact that Microsoft doesn’t really like to work with ODBC drivers and Tivoli doesn’t provide x64 ODBC Drivers makes it more complicated.

I did some searching and I am not the only one who has tried to use ODBC to move data around.  There is a nice blog post here:

http://blogs.simplifi.com/brucet/archive/2006/01/27/668.aspx

But when I tried that I kept getting this error:

Package Validation Error
Error at ImportTastk: The task is configured to pre-compile the script, but binary code is not found. Please visit the IDE in Script Task Editor by clicking Design Script button to cause binary code to be generated.
 (Microsoft.DataTransformationServices.VsIntegration)

I did a google on that and came up with a hotfix that is supposed to fix the issue:

http://www.microsoft.com/downloads/details.aspx?familyid=8AF6C597-0F18-4E61-B3D2-AAD66CDCACF5&displaylang=en

I will be applying that and see what I get…