Piecemeal Restore

What is Piecemeal Restore?
Piecemeal restore is a process which allows databases that contain multiple filegroups to be restored and recovered in stages.

Which Version of SQL Server supports Piecemeal restore?
Piecemeal restore was introduced in SQL Server 2005 and is supported in SQL Server 2005 and later versions.

What are the Limitations?

The Database should contain multiple files or filegroups and should have at least One Read-Only filegroup.

Piecemeal restore works with all recovery models, but is more flexible for the full and bulk-logged models than for the simple model.

Types of Piecemeal Restore?

• Offline
  In an offline piecemeal restore, the database is online after the partial-restore sequence. Filegroups that have not yet been restored remain offline, but they can be restored as you need them after taking the database offline.
  All editions of SQL Server 2005 and above support offline piecemeal restores.
• Online
  In an online piecemeal restore, after the partial-restore sequence, the database is online, and the primary filegroup and any recovered secondary filegroups are available. Filegroups that have not yet been restored remain offline, but they can be restored as needed while the database remains online.
  SQL Server 2005 Enterprise Edition and later versions support Online piecemeal restores.

T-SQL Query to find List of Tables that do not have Primary Key

We know that Primary Key is a must for setting up articles in Transactional Replication.
This query list those tables which do not have Primary Key in that database.

USE <DatabaseName>
GO
SELECT SCHEMA_NAME(schema_id) AS [Schema Name], name AS [Table Name]
FROM sys.tables
WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') = 0
Order by name
GO

Update statistics for all user database

In one of my previous post "What is Statistics in SQL Server", I had explained about what is Statistics.
Today, I am posting a Query which will update statistics on all user database in that Instance.

EXEC sp_MSForeachdb 'USE [?];
IF ''?'' not in (''master'',''model'',''msdb'',''tempdb'',''distribution'') 
 AND DATABASEPROPERTYEX(''?'',''Updateability'') = ''READ_WRITE''
BEGIN
Print ''Updating statistics for database "'' + ''?'' + ''"''
EXEC sp_updatestats
END'

This Query will exclude the system database and those database which are not in Read_Write status.

Database Corruption and Recovery

What is database corruption?

Inconsistency in the internal structure of the database with respect to data or log files is known as database corruption

What causes database Corruption?

• Physical Inconsistency - One or more access paths to the data may be invalid.
• Logical Inconsistency  - One or more pointers to the data may be invalid

How to detect Corruption?

• Status of database set to “suspect”
• Evident from Error log and/or Event Viewer logs
• Consistency errors from DBCC CHECKDB
• T-SQL Queries or application throwing corruption related error (Database might be online without being suspect)
• SQL Server Startup Failure
• Failure while Restoring or Attaching databases

What are the causes that leads to database corruption?

• Hardware failure event
• Power outage
• SAN crash
• NTFS or File System Corruption
• FTDisk Errors
• Bad Block or Disk Corruption
• Outdated  or Faulty Drivers
• Failed Restore/Attach
• Due to Bad File/Page
• Abnormal Termination of the process
• Corrupted Header
• User Error
• File Deletion/Renaming
• File Swapping
• 3rd Party Software
• Filter Drivers
• Outdated/Faulty Device Driver

Recovery Flows

• master Database

• model Database

• msdb Database

• tempdb Database

• User Database

Guidelines for choosing antivirus software to run on the computers that are running SQL Server

These are the very general guidelines to help you decide which kind of antivirus software to run on the computers that are running Microsoft SQL Server and I personally recommend to perform some testing before and after the installation of antivirus to determine if there are any performance issues as the antivirus software will utilize some system resources to perform their duties.

To drill own, basically there are two kinds of servers, one is High Risk Servers which are generally exposed to public internet or which have open ports to the servers that are not behind firewall or which hosts file shares or HTTP services like IIS or Apache.
And the servers which do not meet the above criteria of High Risk servers will fall under the category of Low Risk Server although not always.

When you configure antivirus software on a server running SQL Server, make sure to exclude the following

File Extensions and Directories

• SQL Server database files
• .mdf
• .ndf
• .ldf
• SQL Server backup files
• .bak
• .trn
• Full-Text catalog files
• Default instance: Program Files\Microsoft SQL Server\MSSQL\FTDATA
• Named instance: Program Files\Microsoft SQL Server\MSSQL$instancename\FTDATA
• Trace Files
• .trc
• Audit files
• .sqlaudit
• Query files
• .sql
• Directory that holds Analysis Services Data and Temporary files
• Directory that holds Analysis Services Log files
• Analysis Services backup files

Processes

• SQLServr.exe - Process related to SQL Server Database engine
• ReportingServicesService.exe  - Process related to SQL Server Reporting Services
• MSMDSrv.exe  - Process related to SQL Server Analysis Services

We can also run antivirus software on a SQL Server cluster. For this we have make sure that the antivirus software we choose supports cluster. 

When running antivirus on cluster make sure to exclude 

• Q:\ (Quorum drive)
• C:\Windows\Cluster

Doing this improves the performance of the files and helps make sure that the files are not locked when the SQL Server service must use them. However, if these files become infected, the antivirus software cannot detect the infection. So if you suspect a virus infection then you have to scan the entire system without any exclusions.

Reference: http://support.microsoft.com/kb/309422