Solved

SQL DB Error

Posted on 2014-01-07
25
776 Views
Last Modified: 2014-01-17
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
Comment
Question by:Yeaktom
  • 13
  • 5
  • 4
  • +1
25 Comments
 
LVL 2

Author Comment

by:Yeaktom
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
 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).
0
 
LVL 8

Expert Comment

by:Surrano
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

by:Yeaktom
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

by:Surrano
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

by:Yeaktom
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

by:Yeaktom
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

by:Surrano
ID: 39764911
well if you can afford data loss...
0
 
LVL 2

Author Comment

by:Yeaktom
ID: 39764917
Should I rather try REPAIR_FAST or REPAIR_REBUILD first?
0
 
LVL 8

Expert Comment

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

Assisted Solution

by:Jim P.
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;

Open in new window


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

by:Yeaktom
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

by:ScottPletcher
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 38

Expert Comment

by:Jim P.
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

by:Yeaktom
ID: 39766371
Hi Scott

1 Simple
2 Standard
3 Last backup was Friday
0
 
LVL 2

Author Comment

by:Yeaktom
ID: 39766376
HI Jim

Ok. Will try your suggestion.
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher 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

by:Yeaktom
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

by:Jim P.
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

by:ScottPletcher
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

by:Yeaktom
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

by:Yeaktom
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

by:Jim P.
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

by:
Yeaktom earned 0 total points
ID: 39775807
Good Day All

Thanks for all your comments.

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

by:Jim P.
ID: 39776361
I'm glad you were able to recover it with no data loss.
0
 
LVL 2

Author Closing Comment

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

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now