Announcement for Blog

Dear/Respected All,

I am pleased to announce that my blog is now available at http://www.varindersandhu.in for better features and presentation for users or readers.

The primary objective of my blog is to share my experience and notes with all technical persons. It would mean the world to me if you’d stop by my blog at some point.

I really appreciate the response of readers on my blog and I hope the same response will be continuing in future.

This Message/Post can be forwarded to anyone who might be interested.

Sincerely,
Varinder Sandhu

SQL Server – Policy Based Management

SQL Server 2008 has a new feature called Policy Based Management, also know as Declarative Management Framework (DMF), to tackle the problem of standardizing your SQL server instance. Although Policy Based Management can be used just to alert an administrator when an object is out of compliance, depending upon the type of policy, you can also enforce compliance by preventing changes that would violate a policy. Policy Based Management introduces the following new objects that are used to design and check for compliance:

• Facts
• Conditions
• Policies
• Policy Targets
• Policy Categories

For more information click here

SQL Server – Distributing and Partitioning

Key Points of Distributing and Partitioning

• Partition allow you to divide a table or index into multiple filegroups.
• Table and index are partition horizontally, based on rows by specifying a partitioning column.
• To create a portioned table or index you need to perform the following actions:
• Create a partition function
• Create a partition schema mapped to partition function
• Create a table or index on the partition schema
• SPLIT function to add a new boundary point and hence partition.
• MERGE function to remove a boundary point and hence partition.
• SWITCH function to move a partition of data between tables.

SQL Server – Backup Type for Each Recovery Model

Before looking about Backup type for each Model, sharing with brief information about Recovery and Recovery Model

Recovery

Recovery options determine the behavior of transaction log and how damaged pages are handled.

Recovery Model

Every database within a SQL server a SQL server instance has a property setting called recovery model. The recovery model determines the types of backups you can perform against a database. The recovery models available in SQL server 2008 are:

• Full
• Bulk-Logged
• Simple

SQL Server – Backup all the sql server databases

Sharing with you script to backup all the SQL server databases


Script:


DECLARE @name VARCHAR(50) — database name
DECLARE @path VARCHAR(256) — path for backup files
DECLARE @fileName NVARCHAR(256) — filename for backup
DECLARE @fileDate VARCHAR(20) — used for file name

SET @path = ‘D:\Backup\’

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),113)
SELECT name,flag=0 INTO #temp FROM master.dbo.sysdatabases
WHERE name NOT IN (‘master’,'model’,'msdb’,'tempdb’)

–according to requirement we can filter databases in where clause

SET ROWCOUNT 1

WHILE (exists(SELECT * FROM #temp WHERE flag=0))

BEGIN

SELECT @name=name FROM #temp WHERE flag=0
SET @fileName = @path + @name + ‘_’ + @fileDate + ‘.BAK’
BACKUP DATABASE @name TO DISK = @fileName WITH INIT
UPDATE #temp SET flag=1 WHERE flag=0

END

SET ROWCOUNT 0

DROP TABLE #temp

if anybody have any other idea please share.

SQL Server – Understanding the System Database of SQL Server

We all know there are some system databases available in SQL Server. But we should have understanding about system databases. Here just try to give you understanding of system databases very briefly.

There are four system databases available in SQL Server 2008

• Master
• Model
• msdb
• tempdb

Master Database


The master database records all the system-level information for a SQL Server system.The master is the database that records the existence of all other databases and the location of those database files and records the initialization information for SQL Server. Therefore, SQL Server cannot start if the master database is unavailable. In short, Master database is the logical repository for the system objects residing in the sys schema.

Model Database

The model database is used as the template for all databases created on the instance of SQL Server. Template can include specific settings and all sorts of useful stuff.


Msdb Database

The msdb database is used by SQL Server Agent for scheduling alerts and jobs. If the MSDB database is corrupted or damaged then scheduling information used by SQL Server Agent will be lost.

Tempdb Database

The tempdb database is a workspace for holding temporary objects or intermediate result sets, temporary table creation/processing. The TempDB is recreated every time when ever SQL Server restarts


For more Detail go to below URL

http://msdn.microsoft.com/en-us/library/ms178028%28v=SQL.105%29.aspx
 

SQL Server – Linked Server – Mapped all users with one statement

Hello Friends,
I have setup the Linked server with the help of script here. My one of colleague helps me to write the script with we can map all user with one statement. This script will map all local SQL server users with remote user that you provide in the script.

You don’t need to map all user manually.

Script:

EXEC master.dbo.sp_addlinkedserver @server = N‘dsql2k’, @srvproduct=N”, @provider=N‘SQLOLEDB’, @datasrc=N‘servername’

EXEC master.dbo.sp_addlinkedsrvlogin@rmtsrvname=N‘dsql2k’,@useself=N‘False’,
@locallogin=NULL,@rmtuser=N‘sa’,@rmtpassword=‘XXXXXX’


if anybody have any other experience, please share as comment.