Solved

# SQL DB Error

Posted on 2014-01-07
787 Views
Our SQL Production pc lost a hard drive but the raid is still intact and the system is still operational.

I need to move the data to another SQL server without taking the current server offline. I cant make a backup in Management Tools, get the error below.

Tried to copy the data via the copy function in SQL Management but get a error there two. I attached the summary screen of the copy command under copy.png.

I am trying the copy command again, will paste the error info if I receive it again.
SQL-Error.png
Copy.png
0
Question by:Yeaktom
• 13
• 5
• 4
• +1

LVL 2

Author Comment

ID: 39764426
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
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).
0

LVL 8

Expert Comment

ID: 39764645
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)
0

LVL 2

Author Comment

ID: 39764707
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).
0

LVL 8

Expert Comment

ID: 39764756
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...)
0

LVL 2

Author Comment

ID: 39764849
Hi

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

LVL 2

Author Comment

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

Any other suggestions?
0

LVL 8

Expert Comment

ID: 39764911
well if you can afford data loss...
0

LVL 2

Author Comment

ID: 39764917
Should I rather try REPAIR_FAST or REPAIR_REBUILD first?
0

LVL 8

Expert Comment

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

LVL 38

Assisted Solution

Jim P. earned 250 total points
ID: 39766017
First off -- you don't want to stop the SQL Services unless you have to.

The errors mean that part of that DB is corrupted. Trying to bring it back online afterward may not happen.

Do you have another undamaged drive large enough to hold all the files available? Or even  use an external USB drive for this case.

I had to do this back in this Q.

The way I would do it is use the SQL Server to script out the DB creation and structure (its in the right click menu). Then modify the drives and DB name, etc. to create an empty DB in parallel to the current DB.

Then after that I scripted the straight tables with no identity columns using this code:
SELECT 	'INSERT INTO [DBNAmeRecover].[' + TABLE_SCHEMA + '].[' + TABLES.TABLE_NAME + ']' + CHAR(13) +
'SELECT * FROM [' + TABLE_CATALOG + '].[' + TABLE_SCHEMA + '].[' + TABLES.TABLE_NAME + '];' + CHAR(13) +
'-------------------------------- END ' + TABLES.TABLE_NAME + ' --------------------------------'
FROM INFORMATION_SCHEMA.TABLES LEFT JOIN
(SELECT   OBJECT_NAME(OBJECT_ID) AS TABLE_NAME,
NAME AS COLUMNNAME,
SEED_VALUE,
INCREMENT_VALUE,
LAST_VALUE,
IS_NOT_FOR_REPLICATION
FROM     SYS.IDENTITY_COLUMNS ) ID_TBLS
ON TABLES.TABLE_NAME = ID_TBLS.TABLE_NAME
WHERE	TABLE_TYPE = 'BASE TABLE'
AND		COLUMNNAME IS NULL
AND		TABLES.TABLE_NAME NOT IN( 'dtproperties')
AND		TABLES.TABLE_NAME NOT LIKE 'MS%'
AND		TABLES.TABLE_NAME NOT LIKE 'sys%'
ORDER BY TABLES.TABLE_NAME;


Then look at the other Q to get the code for handling tables with an identity column.

That will probably allow you to recover about 99% of your data. Then you can do what you want with the new DB and or do the CHECKDB with a little more comfort level.
0

LVL 2

Author Comment

ID: 39766268
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?
0

LVL 69

Expert Comment

ID: 39766298
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?
0

LVL 38

Expert Comment

ID: 39766307
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.
0

LVL 2

Author Comment

ID: 39766371
Hi Scott

1 Simple
2 Standard
3 Last backup was Friday
0

LVL 2

Author Comment

ID: 39766376
HI Jim

0

LVL 69

Assisted Solution

Scott Pletcher earned 250 total points
ID: 39766377
Well, #1 and #2 definitely rule out page restores.  Too bad, might have saved the situation.

Be sure to try a backup using the command rather than the GUI:

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

LVL 2

Author Comment

ID: 39766434
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.
0

LVL 38

Expert Comment

ID: 39766459
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.
0

LVL 69

Expert Comment

ID: 39766468
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.
0

LVL 2

Author Comment

ID: 39766486
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.
0

LVL 2

Author Comment

ID: 39766500
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.
0

LVL 38

Expert Comment

ID: 39766537
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.
0

LVL 2

Accepted Solution

Yeaktom earned 0 total points
ID: 39775807
Good Day All

I used a program called J File Recovery to transfer the mdf and ndf from the production server. I then did a DBCC CHECKDB ('DBNAME', REPAIR_REBUILD) on the data, that was able to repair. At first I tried it with only repair, but the database was extremely slow... guessing some of the indexes was dropped and the rebuild fixed it.
0

LVL 38

Expert Comment

ID: 39776361
I'm glad you were able to recover it with no data loss.
0

LVL 2

Author Closing Comment

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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.