2012 - Licesensing

DB RECOVERY - PAGE LEVEL

STEP 1 - Check Database For corruption

We can check database integrity by using DBCC CHECKDB command, to see weather there is corruption in database of not.







Looking at error message, we can clearly identify that there is corruption on page 223 as we can see this message. Object ID 2105058535, index ID 2, partition ID 72057594038910976, alloc unit ID 72057594039828480 (type In-row data): Page (1:223) could not be processed.



STEP 2 – Restore faulty page from a GOOD Backup – PAGE Level Database Restore



Now we need to restore faulty pages from a SQL Server backup, that means restore only faulty pages. This is a new feature in SQL Server 2008, where we can restore only some corrupted pages from a good database backup.



For example you have a 100 Gb database and only 1 page is corrupted than we can save recovery time by restoring a single page instead of a 100 GB database.



SQL Command to perform a page level restore



use master

go

RESTORE DATABASE DBA PAGE = '1:223' FROM DISK = 'C:\temp\DBA_before_curruption.bak';

goSTEP 3 – Backup and Restore Current TRANSACTION LOG Backup



If you read the restore informational messages, which we received in last step states that there is difference between the LSN number.



Processed 1 pages for database ‘DBA’, file ‘DBA’ on file 1.



The roll forward start point is now at log sequence number (LSN) 43000000055600001. Additional roll forward past LSN 43000000058400001 is required to complete the restore sequence.



RESTORE DATABASE … FILE= successfully processed 1 pages in 0.098 seconds (0.079 MB/sec).



To correct this LSN number, we need to backup the current log and restore in a current database, using the following syntax.



use DBA

BACKUP LOG DBA TO DISK = 'C:\DBA_log.bak' WITH INIT;

GO



use master

GO

RESTORE LOG DBA FROM DISK = 'C:\DBA_log.bak';This is going to be pretty quick as only page level transactions will be rolled back or rolled forward, you can see that in message where backup log size was in MB’s but restore was kind of ZERO only.



Processed 5 pages for database ‘DBA’, file ‘DBA_log’ on file 1.



BACKUP LOG successfully processed 5 pages in 0.020 seconds (1.684 MB/sec).



Processed 0 pages for database ‘DBA’, file ‘DBA’ on file 1.



RESTORE LOG successfully processed 0 pages in 0.006 seconds (0.000 MB/sec).











STEP 4 – Verify corruption has been resolved and data is consistent



Re-execute DBCC CHECKDB to ensure and verify that corruption has been removed and database is health now.



OPTION 2 – The corruption example, which I took was of Index and I want to make you understand how page level restore works, if you are looking for a solution to a exact problem, which I demonstrated, can be resolved by rebuilding a non clustered index, as can afford to rebuild index which doesn’t;t result any data loss.

 

DB RECOVERY - PAGE LEVEL

Unable to refresh data for a data connection in the workbook. Try again or contact your system administrator. The following connections failed to refresh: PowerPivot Data

http://msdn.microsoft.com/en-us/library/ff487856.aspx

http://www.cjvandyk.com/blog/Articles/How%20do%20I%20-%20Install%20PowerPivot%20into%20an%20EXISTING%20SharePoint%202010%20farm.aspx


NOTE :- This feature is not available on SQL Server Standard Edition.

http://www.microsoft.com/sqlserver/en/us/product-info/compare.aspx

Microsoft SQL Server 2008 R2 - PowerPivot for Microsoft Excel 2010

http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=7609


How to install and configure please refer,

http://powerpivot-info.com/post/66-step-by-step-guide-on-installing-powerpivot-for-sharepoint

Usefull Queiries

 Following quick script demonstrates last ran queries along with the time it was executed.




SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]

FROM sys.dm_exec_query_stats AS deqs

CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest

ORDER BY deqs.last_execution_time DESC



-------------------------------------------------------------------------------------------------------------
 Gives the total number of tables available for a given schema.



SELECT count(*) TABLES, table_schema

FROM information_schema.TABLES

WHERE table_schema= 'dbo' and TABLE_TYPE ='BASE TABLE'

GROUP BY table_schema





Information_schema can used for getting many other information like column details, domain, privileges and many more



 Want to find a function or procedure you can use this query



SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'FUNCTION'






 Tables that do not have clustured indexes

SELECT SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS table_name

FROM sys.tables AS t

WHERE NOT EXISTS

(

SELECT * FROM sys.indexes AS i

WHERE i.object_id = t.object_id

AND type_desc = 'CLUSTERED'

)

ORDER BY schema_name, table_name;



 Find dependies of specified function



SELECT OBJECT_NAME(object_id) AS referencing_object_name

,COALESCE(COL_NAME(object_id, column_id), '(n/a)') AS referencing_column_name,*

FROM sys.sql_dependencies

WHERE referenced_major_id = OBJECT_ID('o2_AccountUsers')

ORDER BY OBJECT_NAME(object_id), COL_NAME(object_id, column_id);

SSRS Maximum request length exceeded

Error : Maximum request length exceeded.

Change the values as mentioned below,

1. C:\Program Files\Microsoft SQL Server\MSRS__.SQL_\Reporting Services\ReportServer
2. Open web.config file 
3. Search
4. Change

Note :- This is max limit.


5. iisreset

The Windows Server 2008 failover cluster cannot start because of a deadlock condition in the Cluster service

RESOLUTION


Service pack information

To resolve this problem, obtain the latest service pack for Windows Server 2008 Service Pack 2. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

968849 How to obtain the latest service pack for Windows Server 2008

Back to the top

Hotfix information

A supported hotfix is available from Microsoft. However, this hotfix is intended to correct only the problem that is described in this article. Apply this hotfix only to systems that are experiencing the problem described in this article. This hotfix might receive additional testing. Therefore, if you are not severely affected by this problem, we recommend that you wait for the next software update that contains this hotfix.



If the hotfix is available for download, there is a "Hotfix download available" section at the top of this Knowledge Base article. If this section does not appear, contact Microsoft Customer Service and Support to obtain the hotfix.



Note If additional issues occur or if any troubleshooting is required, you might have to create a separate service request. The usual support costs will apply to additional support questions and issues that do not qualify for this specific hotfix. For a complete list of Microsoft Customer Service and Support telephone numbers or to create a separate service request, visit the following Microsoft Web site:

http://support.microsoft.com/contactus/?ws=support

Note The "Hotfix download available" form displays the languages for which the hotfix is available. If you do not see your language, it is because a hotfix is not available for that language.



Important Windows Vista and Windows Server 2008 hotfixes are included in the same packages. However, only one of these products may be listed on the “Hotfix Request” page. To request the hotfix package that applies to both Windows Vista and Windows Server 2008, just select the product that is listed on the page.

Prerequisites

To apply this hotfix, you must have Windows Server 2008 installed.

Restart requirement

You must restart the computer after you apply this hotfix.

Hotfix replacement information

This hotfix does not replace a previously released hotfix.

File information

The English version of this hotfix has the file attributes (or later file attributes) that are listed in the following table. The dates and times for these files are listed in Coordinated Universal Time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time item in Control Panel.

Windows Vista and Windows Server 2008 file information notes

The .manifest files and the .mum files that are installed in each environment are listed separately in the "Additional file information for Windows Server 2008" section. These files and their associated .cat (security catalog) files are critical to maintaining the state of the updated component. The .cat files are signed with a Microsoft digital signature. The attributes of these security files are not listed.

For all supported 32-bit versions of Windows Server 2008

File name File version File size Date Time Platform

Srvnet.sys 6.0.6001.22178 98,816 14-May-2008 02:47 x86

For all supported 64-bit versions of Windows Server 2008

File name File version File size Date Time Platform

Srvnet.sys 6.0.6001.22178 141,312 14-May-2008 03:15 x64

For all supported Itanium-based versions of Windows Server 2008

File name File version File size Date Time Platform

Srvnet.sys 6.0.6001.22178 286,208 14-May-2008 02:57 IA-64