June 24, 2008 at 10:11 am
Hi Experts,
I feel this as strange as i got a backup from client and when i restored the backup its found that some data is missing in one column .The column datatype is text and it contains article having large datas.I checked the data in table with the live site and found datas missing..Please help its very urgent.
TIA
June 24, 2008 at 10:28 am
Is the Database a full Backup or Diff backup,
did the Database have multiple Filegroups or Files.
Is there a case of Partitioned Tables
Are you restoring on same versions/editions.
What is the DB CMPTLEVEL.
This is just, what i could think of right now...
Maninder
www.dbanation.com
June 24, 2008 at 10:32 am
I got backup from client only.
1)Its a full backup
2)Compatability level 80
No idea about partitioned tables and other file groups,But i think if there is some other filegroup then at the time of restoration sql server throws some error right...:w00t:
Tried restoring to both sql server 2000 and 2005 :hehe:
June 24, 2008 at 11:01 am
So, the live database is missing some data and the backup is missing some data. Is that correct?
Is there any chance that someone deleted it?
Do you have a backup that does contain the data?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 24, 2008 at 11:37 am
yes correct and Gila might be right, there might be user fault in here or a job or something... 🙂
Maninder
www.dbanation.com
June 24, 2008 at 12:14 pm
Does the database have "torn page detection" ON ?
I would start with a dbcc checkdb !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 25, 2008 at 1:44 am
Client took the backup from a live database.
No idea about torn page detection ..how to check that???
June 25, 2008 at 3:40 am
use sp_helpdb !
e.g.
exec sp_helpdb 'DDBAStatistics'
result:
status (added linefeeds to narrow the linesize)
-------------------------------
Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER,
Recovery=FULL, Version=611, Collation=SQL_Latin1_General_CP1_CI_AS,
SQLSortOrder=52, IsTornPageDetectionEnabled, IsAutoCreateStatistics,
IsAutoUpdateStatistics
If you find IsTornPageDetectionEnabled then it's ok.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 25, 2008 at 3:54 am
When i run sp_helpdb 'db_name' on database which i restored i got the following message
Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=611, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics
June 25, 2008 at 4:10 am
then TornPageDetection is not enabled
I suggest you enable it or have it enabled as soon as you performed a checkdb and address all issues that may appear.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 25, 2008 at 7:35 am
What happens if i enable torn page detection???
Will it affect performance or any other issues if it is enable in a LIVE Database??
June 25, 2008 at 7:38 am
If it's a 2005 database rather don't enable torn page detection. Enable page checksum. It's a more accurate way of detecting damage to pages.
You want some form of page protection on. Without that, your storage system could be randomly garbling large portions of your database and you'd never know.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 25, 2008 at 8:16 am
Just to add to Gails remark:
this is stated in BOL topic: "alter database"
TORN_PAGE_DETECTION { ON | OFF }
ON
Incomplete pages can be detected by the Database Engine.
OFF
Incomplete pages cannot be detected by the Database Engine.
Important:
The syntax structure TORN_PAGE_DETECTION ON | OFF will be removed in a future version of Microsoft SQL Server. Avoid using this syntax structure in new development work, and plan to modify applications that currently use the syntax structure. Use the PAGE_VERIFY option instead.
PAGE_VERIFY { CHECKSUM | TORN_PAGE_DETECTION | NONE }
Discovers damaged database pages caused by disk I/O path errors. Disk I/O path errors can be the cause of database corruption problems and are generally caused by power failures or disk hardware failures that occur at the time the page is being written to disk.
CHECKSUM
Calculates a checksum over the contents of the whole page and stores the value in the page header when a page is written to disk. When the page is read from disk, the checksum is recomputed and compared to the checksum value stored in the page header. If the values do not match, error message 824 (indicating a checksum failure) is reported to both the SQL Server error log and the Windows event log. A checksum failure indicates an I/O path problem. To determine the root cause requires investigation of the hardware, firmware drivers, BIOS, filter drivers (such as virus software), and other I/O path components.
TORN_PAGE_DETECTION
Saves a specific bit for each 512-byte sector in the 8-kilobyte (KB) database page and stored in the database page header when the page is written to disk. When the page is read from disk, the torn bits stored in the page header are compared to the actual page sector information. Unmatched values indicate that only part of the page was written to disk. In this situation, error message 824 (indicating a torn page error) is reported to both the SQL Server error log and the Windows event log. Torn pages are typically detected by database recovery if it is truly an incomplete write of a page. However, other I/O path failures can cause a torn page at any time.
NONE
Database page writes will not generate a CHECKSUM or TORN_PAGE_DETECTION value. SQL Server will not verify a checksum or torn page during a read even if a CHECKSUM or TORN_PAGE_DETECTION value is present in the page header.
Consider the following important points when you use the PAGE_VERIFY option:
In SQL Server 2005, the default is CHECKSUM. In SQL Server 2000, TORN_PAGE_DETECTION is the default.
When a user or system database is upgraded to SQL Server 2005, the PAGE_VERIFY value (NONE or TORN_PAGE_DETECTION) is retained. We recommend that you use CHECKSUM.
TORN_PAGE_DETECTION may use fewer resources but provides a minimal subset of the CHECKSUM protection.
PAGE_VERIFY can be set without taking the database offline, locking the database, or otherwise impeding concurrency on that database.
CHECKSUM is mutually exclusive to TORN_PAGE_DETECTION. Both options cannot be enabled at the same time.
When a torn page or checksum failure is detected, you can recover by restoring the data or potentially rebuilding the index if the failure is limited only to index pages. If you encounter a checksum failure, to determine the type of database page or pages affected, run DBCC CHECKDB. For more information about restore options, see RESTORE Arguments (Transact-SQL). Although restoring the data will resolve the data corruption problem, the root cause, for example, disk hardware failure, should be diagnosed and corrected as soon as possible to prevent continuing errors.
SQL Server will retry any read that fails with a checksum, torn page, or other I/O error four times. If the read is successful in any one of the retry attempts, a message will be written to the error log and the command that triggered the read will continue. If the retry attempts fail, the command will fail with error message 824.
For more information about checksum, torn page, read-retry, error messages 823 and 824, and other SQL Server I/O auditing features, see this Microsoft Web site.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 25, 2008 at 9:58 am
Now i requested client to
1)run dbcc checkdb
2)enable torn page detection
and then take a full backup.
Is that ok
June 26, 2008 at 2:11 am
it should be ok, as long as they fix the issues that are detected.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply