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.

Transaction Isolation Level

What is default Isolation level for SQL Server 2008 or Crystal Report?

Many people have same above question about Isolation Level, may we have same question. So here share the brief answers of this question.

Transaction Isolation Level:
In database systems, isolation is a property that defines how/when the changes made by one operation become visible to other concurrent operations.

Default Isolation level for SQL Server 2008 is “Read Committed”

SQL Server 2008 has following transaction isolation level

Read Committed
Read Uncommitted
Repeatable Read
Serializable
Snapshot

Learn More about TRANSACTION ISOLATION LEVEL (Transact-SQL)

Crystal Report Isolation Level

The default isolation level for database reads in Crystal Reports 9.0 is set to Read Committed

Crystal Reports .Net 1.1 Patch –> Problem ID # ADAPT00194820

http://search-pdf-files.com/pdf/193365-read-leel-error-description-isolation

SQL Search

Dear Friends,

I have tried the Utility SQL Search, it is really helpful for me…
Thought it will be handy to all of us and it is a free tool from Red Gate.
SQL Search is an add-in for SQL Server Management Studio that lets you quickly search for SQL across your databases.

Click here for more detail.

SQL Server Editions

Dear friends

Most of time we have a question about difference between the SQL server editions, Lot of detailed stuff available over the internet but here sharing with you the brief information about the each SQL server editions so that we can understand easily about all the editions.

SQL server 2008 is available in the following editions


Enterprise:
Designed for largest organizations and those needing to leverage the full power of SQL server 2008 platform. Learn More


Standard:
Designed for small and midsized organizations that do not need all the capability available in SQL Server 2008 Enterprise. Learn More


Workgroup:
Suitable for small departmental project with a limited set of features. Learn More


Express:
A freely re-distribution version of SQL server that is ideal for learning and building desktop and small server applications with small number of users. Learn More

Available as a free download on Microsoft Site: Download Now


Compact:
Designed for emended Databases. Learn More


Developer:
Designed for use by developer in creating SQL server applications. SQL Server 2008 Developer editions has all the features and capability as SQL server 2008 Enterprise, except that is not allowed to be used in production environment. Learn More

After that want to share with you all the Compare Edition Features

Welcome SQL Server 2011-Denali – CTP1

Dear friends,

A new release of SQL Server is available, and if you follow blogs or tweets or check Microsoft’s SQL Server website. You might have seen mention of the new version, code-named “Denali” SQL Server 2011 – CTP1 Released. CTP stands for Community Technology Preview


It’s available in both 32-bit and 64-bit version. May you are getting excited about the new version. So that‘s why I am sharing with you all…

• What‘s New SQL 2011
• Microsoft SQL Server 2011 Requirements
• SQL Server 2011 – Download
• SQL Server 2011 Installation Guide

If anybody have any experience about this, please share as comment