SQL DB Error

Copy still failing, getting the following errors on destination server

  Event Name: OnError
 Message: ERROR : errorCode=-1073548784 description=Executing the query "CREATE USER [Excel] FOR LOGIN [Excel] WITH DEFAULT..." failed with the following error: "'Excel' is not a valid login or you do not have permission.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Excel is a user I created on the DBA that we use for ODBC reports.

Further errors.

  Event Name: OnProgress
 Message: Database transfer failed for 1 database(s).
 Operator: domainname\SQLAgent
 Source Name: SourceServer_DestinationServer_Transfer Objects Task
 Source ID: {8C9C9789-39F7-48B0-ADD7-543C3DB2F537}
 Execution ID: {78B06876-3544-4ECF-98E3-A53CA6912BA6}
 Start Time: 2014/01/08 09:05:30 AM
 End Time: 2014/01/08 09:05:30 AM
 Data Code: 0

 Event Name: OnWarning
 Message: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

 Operator: Domainname\SQLAgent
 Source Name: old2new
 Source ID: {194D4C19-D2F9-4F8F-B0DF-91052A161529}
 Execution ID: {78B06876-3544-4ECF-98E3-A53CA6912BA6}
 Start Time: 2014/01/08 09:05:30 AM
 End Time: 2014/01/08 09:05:30 AM

SQL Server Scheduled Job 'old2new' (0x9A2089A27EFC2D4B9EC677F33AB91EF9) - Status: Failed - Invoked on: 2014-01-08 08:50:17 - Message: The job failed.  The Job was invoked by User sa.  The last step to run was step 1 (old2new_Step).

Can you read the file reported as erroneous using OS level tools? (e.g. F3 in Total Commander, or copy it to a different location)

I will have to stop the SQL service before I can try to copy the file.

I ran a dbcc checkdb and found the following errors.

Msg 8966, Level 16, State 2, Line 1
Unable to read and latch page (3:1269782) with latch type SH. 1(failed to retrieve text for this error. Reason: 15105) failed.
Msg 8966, Level 16, State 2, Line 1
Unable to read and latch page (3:1269787) with latch type SH. 1(failed to retrieve text for this error. Reason: 15105) failed.
CHECKDB found 0 allocation errors and 2 consistency errors not associated with any single object.

CHECKDB found 0 allocation errors and 8 consistency errors in database 'databasename'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (databasename).

So it sounds like you'll either have outage because you can't access (part of) data or because you restart the server. In the latter case, though, there may be a chance to find more useful pieces of info, or even that server will realise the situation and recover by itself.

Can't you hotswap the failed drive in the RAID? What is the actual RAID configuration? (e.g. 2-disk SATA mirror, or 6-disk SCSI RAID-5, or...)

Hi

Yes I can hotswop the drive, waiting for a new drive from IBM.... sadly the ETA is 7 - 10 Days!

I will run the DBCC CHECKDB command with REPAIR_ALLOW_DATA_LOSS later tonight when production stopped.

Any other suggestions?

well if you can afford data loss...

Should I rather try REPAIR_FAST or REPAIR_REBUILD first?

I'm not familiar with the repair options but "allow data loss" sounds scary.

Hi Jim P

Yes I have another SQL server that can hold the data. I tried copying the data over with the copy data command.

That is exactly what I am scared of, stopping the server to copy the files out.... will I get the DB online again. I am very cautious of running the repair without a backup, but I cant run a backup with part of the DB corrupted.

Honestly I am not sure what to do next... should I take a change and stop the SQL Service to make a backup or must I bite the bullet and run the repair on the live DB without a new backup.

Could I use the import export command after I scripted the DB?

1) Is that db's recovery model full or bulk-logged, rather than simple?

2) Is your SQL edition Enterprise Edition, or is it Standard or another version?

3) Do you have a recent backup of the database from before the drive error?

I'm not saying another server. I'm saying make a copy by creating the structure of the DB, blank tables, etc. Then from the running DB using a series of insert statements to fill the new DB tables from the old one.

You do that first. Then run the checks or copies. But the shutdown is the last thing to do.

Hi Scott

1 Simple
2 Standard
3 Last backup was Friday

HI Jim

Ok. Will try your suggestion.

Hi Scott

I got the following error.

Msg 3203, Level 16, State 1, Line 1
Read on "E:\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\dbname1.ndf" failed: 1(failed to retrieve text for this error. Reason: 15105)
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

Scott,

That is why I developed the technique that I did.

We had a customer that had a self hosted DB using our app. They called in that they had other data issues. I had one of the techs pull a backup of DB and we tried restoring it on our test server.  It was getting essentially a torn page error. The backup looked ok, but the corruption wouldn't let it restore.

I went through and recovered all but about 5K rows out of hundreds of tables and millions of rows by copying it into the empty DB. Then I did the checkdb. The DB was not recoverable, but most of the data that was lost was from static tables.

It's a manual process, but it should work.

Sorry, in too big a hurry, I left off the key option.  It usually allows the backup to work, although of course nothing guarantees results in every specific case:

BACKUP DATABASE [<database_name>]
TO DISK = 'x:\full\path\to\backup\file\<database_name>_backup.BAK'
WITH CONTINUE_AFTER_ERROR, INIT

Naturally you want to get as full a backup as possible before you attempt any repairs/recovery.

Still no luck.

Msg 3203, Level 16, State 1, Line 1
Read on "E:\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\dbname1.ndf" failed: 1(failed to retrieve text for this error. Reason: 15105)
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

Guessing the copy function is not working due to the corruption?

Kim P : I hear what you are saying, the only issue is the time it will take to create a import for each table, Between 500 - 600 tables.

In the query analyzer window set the output column width to like 3000 characters. Then set it to text output (<Ctrl>+T). Then the SELECT       'INSERT .....  ---'  FROM INFORMATION_SCHEMA.TABLES LEFT JOIN query in my post above will build all the queries you need for tables that don't have an identity column.

Then in the other Q I linked to it has a function and another query to build the rest of the insert statement.

You copy the results to another widow and then run it. There are going to be some table(s) that it will error out on. But it should cover the large majority of it.

Basically it is a couple of queries to make queries.

I'm glad you were able to recover it with no data loss.

In my case the J File Recovery did the trick, to get the data of the production server.