Taking backup of all the databases in SQL Server

Using below script you can back up all databases on your SQL Server. Using this you can backup databases in the multiple disk drives in a compress mode.

If you need please change the script according to your requirement.

First create a Backup folder on disk drives.

DECLARE @FileName01 AS VARCHAR(200) -- Filename for backup 1

DECLARE @FileName02 AS VARCHAR(200) -- Filename for backup 2

DECLARE @FileName03 AS VARCHAR(200) -- Filename for backup 3

DECLARE @FileName04 AS VARCHAR(200) -- Filename for backup 4

DECLARE @FileDate AS VARCHAR(10) -- Used for file name (Backup date)

DECLARE @DBName AS VARCHAR(100) -- Database name

DECLARE @Path01 AS VARCHAR(200) -- Path for backup files 1

DECLARE @Path02 AS VARCHAR(200) -- Path for backup files 2

DECLARE @Path03 AS VARCHAR(200) -- Path for backup files 3

DECLARE @Path04 AS VARCHAR(200) -- Path for backup files 4

DECLARE @BKPName AS VARCHAR(200) -- Backup name

DECLARE @ErrorMsg AS VARCHAR(200)

Set @Path01 ='G:\Backup\'

Set @Path02 ='H:\Backup\'

Set @Path03 ='M:\Backup\'

Set @Path04 ='I:\Backup\'

DECLARE db_Cursor CURSOR FOR

  Select  name FROM master.dbo.sysdatabases 

  WHERE name NOT IN ('tempdb') ORDER BY name -- Exclude Tempdb databases

OPEN db_Cursor

FETCH NEXT FROM db_Cursor INTO @DBName

WHILE @@FETCH_STATUS=0

BEGIN

       SELECT @FileDate =CONVERT(VARCHAR(10),GETDATE()-1,112)

       SET @FileName01 =@Path01+@DBName+'_01_'+@FileDate+'.BAK'

       SET @FileName02 =@Path02+@DBName+'_02_'+@FileDate+'.BAK'

       SET @FileName03 =@Path03+@DBName+'_03_'+@FileDate+'.BAK'

       SET @FileName04 =@Path04+@DBName+'_04_'+@FileDate+'.BAK'

       SET @BKPName =@DBName + '-Full Database Backup'

       /* Backup */

       BACKUP DATABASE @DBName TO  DISK = @FileName01, 

                                   DISK = @FileName02, 

                                   DISK = @FileName03, 

                                   DISK = @FileName04 WITH NOFORMAT, INIT, 

              NAME = @BKPName, SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10

       /* Verify Backup */

       declare @backupSetId as int

       select @backupSetId = position from msdb..backupset 

       where database_name=@DBName and backup_set_id=(

            select max(backup_set_id) from msdb..backupset where database_name=@DBName )

       SET @ErrorMsg = 'Verify failed. Backup information for database '

                      +@DBName+' not found.'

       if @backupSetId is null begin raiserror(@Error, 16, 1) end

       print '****************** '+ @DBName  +' Verify Backup ******************'

       RESTORE VERIFYONLY FROM  DISK = @FileName01,  DISK = @FileName02, DISK = @FileName03,         DISK =@FileName04 WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND

       print '******************************************************************'

       FETCH NEXT FROM db_Cursor INTO @DBName

END

CLOSE db_Cursor

DEALLOCATE db_Cursor

And also you can use Maintenance Plan, it will create the script and job for you.

Queries currently run on SQL Server

Using below Dynamic Management View (DMV) query, you can view the queries which are currently running. 

SELECT * FROM sys.dm_exec_requests

Each row represents a currently running query.

Using below query, you can view most required details of the currently running queries.

Output columns of the query:

Blocking Session ID, login  Name, Data Base Name, Status, Query Statement, Duration, Wait Type, Query Plan, Complete Percentage (If applicable eg. Backups), Estimate Completion Time (If applicable eg. Backups), Host Name

WITH cte AS (

  SELECT

              r.session_id, r.request_id, r.database_id, t.objectid, t.[text],                              r.statement_start_offset/2 AS StatementStartOffset

              , CASE WHEN r.statement_end_offset > r.statement_start_offset THEN                            r.statement_end_offset/2 ELSE LEN(t.[text]) END AS StatementEndOffset

              , p.query_plan,CAST(getdate()-r.start_time as time) Duration

              ,percent_complete, dateadd(second,estimated_completion_time/1000
              , getdate()) as estimated_completion_time,R.status,r.wait_type

  FROM sys.dm_exec_requests r

              CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) t

              OUTER APPLY sys.dm_exec_query_plan(r.plan_handle) p

  WHERE r.[sql_handle] IS NOT NULL

), spaceUsage AS (

  SELECT

       session_id, request_id

, SUM(user_objects_alloc_page_count - user_objects_dealloc_page_count) / 128 AS UserObjMB

, SUM(internal_objects_alloc_page_count - internal_objects_dealloc_page_count) / 128 AS InternalObjMB

  FROM sys.dm_db_task_space_usage

  GROUP BY session_id, request_id

)

SELECT

       r.Session_id

       ,(SELECT DISTINCT MAX(blocking_session_id) FROM Sys.dm_os_waiting_tasks 

       WHERE blocking_session_id IS not NULL AND session_id = R.session_id) Blocking_Sid

       , REPLACE(s.login_name,'NT AUTHORITY\','') login_name
       , DB_NAME(r.database_id) AS DB_Name,R.Status

       , COALESCE('[' + OBJECT_SCHEMA_NAME(r.objectid, r.database_id) + '].[' +                     OBJECT_NAME(r.objectid, r.database_id) + ']'
       , LEFT(LTRIM(r.[text]), 128)) AS Query_Batch

       , SUBSTRING(r.[text], r.StatementStartOffset
       , r.StatementEndOffset - r.StatementStartOffset) AS Current_Statement
       ,Duration,r.Wait_Type , LEN(LEFT(r.[text], r.StatementStartOffset)) -                         LEN(REPLACE(LEFT(r.[text], r.StatementStartOffset), CHAR(10), '')) + 1 AS Line_Number

       , u.UserObjMB AS [UserObjMB*], u.InternalObjMB, r.Query_Plan

       ,Percent_Complete,   Estimated_Completion_Time,S.[Host_Name]

FROM cte r

  INNER JOIN sys.dm_exec_sessions s ON s.session_id = r.session_id

  LEFT JOIN spaceUsage u ON r.session_id = u.session_id AND r.request_id = u.request_id

And also you can use Activity Monitor to view currently running queries

Business Problems for Data Mining

Data mining techniques can be applied to many applications, answering various types of businesses questions. The following list illustrates a few typical problems that can be solved using data mining:

Churn analysis: Which customers are most likely to switch to a competitor? The telecom, banking, and insurance industries are facing severe competition these days. On average, each new mobile phone subscriber costs phone companies over 200 dollars in marketing investment. Every business would like to retain as many customers as possible. Churn analysis can help marketing managers understand the reason for customer churn, improve customer relations, and eventually increase customer loyalty.

Cross-selling: What products are customers likely to purchase? Crossselling is an important business challenge for retailers. Many retailers, especially online retailers, use this feature to increase their sales. For example, if you go to online bookstores such as Amazon.com or Barnes andNoble.com to purchase a book, you may notice that the Web site gives you a set of recommendations about related books. These recommendations can be derived from data mining analysis.

Fraud detection: Is this insurance claim fraudulent? Insurance companies process thousands of claims a day. It is impossible for them to investigate each case. Data mining can help to identify those claims that are more likely to be false.

Risk management: Should the loan be approved for this customer? This is the most common question in the banking scenario. Data mining techniques can be used to score the customer’s risk level, helping the manager make an appropriate decision for each application. Customer segmentation: Who are my customers? Customer segmentation helps marketing managers understand the different profiles of customers and take appropriate marketing actions based on the segments.

Targeted ads: What banner ads should be displayed to a specific visitor? Web retailers and portal sites like to personalize their content for their Web customers. Using customers’ navigation or online purchase patterns, these sites can use data mining solutions to display targeted advertisements to their customers’ navigators.

Sales forecast: How many cases of wines will I sell next week in this store? What will the inventory level be in one month? Data mining forecasting techniques can be used to answer these types of time-related questions.

Ref. Data Mining with SQL Server 2005

Google Analytics open for all!

Google has finally announced that you no longer need an invitation to use their brilliant free web stats program Google Analytics

If you have a website or blog I strongly suggest that you sign up to this service right now!
More Info

How to make Adobe Reader 7.0 load faster

Whenever I install Adobe’s Acrobat Reader I also uninstall most of the pointless plugins, to speed up its dog-slow startup process.

So here’s what I just did on my machine:

1. In Edit-Preferences, do the following:
   • General tab: turn off “Automatically save document changes”
   • Internet tab: turn off all three checkboxes
   • Page Display tab: turn on “CoolType”
   • Search tab: turn off “Enable fast find”
   • Startup tab: turn off “Show messages and automatically update”
2. In View-Toolbars, turn off “Rotate view” and “Search the internet”. Under “Show button labels”, turn them all on so you can figure out what the heck those icons means.
3. Fire up Windows Explorer and do the following:
   • Navigate to C:\Program Files\Adobe\Acrobat 7.0\Reader\
   • I discovered there's a subdirectory called “Optional” that contains a readme with the following text: "Put unused plug-ins in the optional directory."
   • Move all the .api files from the plug_ins subdirectory to “Optional” subdirectory, except for AcroForm.api (for form-filling) and EScript.api (dependency of AcroForm.api).

What you wanted to actually know what all those plug-ins did so that you can make up your own mind? Move them back again, launch Acrobat Reader, and go to Help-About Adobe Plugins to learn what each plug-in does and what its dependencies are. Oh, and if you speed up Adobe 7.0 by removing some plugins, the update process will have left some subdirectories under C:\Program Files\Adobe\Acrobat 7.0\, so if you’re tidy-minded you can delete those too.

Use Substitution Control for Dynamic sections of a Cached Page

Ways of Caching an ASP.NET Page in which we are limited to two options

i. Caching the whole page using Output Caching - Not applicable in many realtime scenarios where some sections of the page need to be dynamic ex.- Stock Rates

ii. Caching Usercontrols by Fragment Caching - Difficult to implement since page requires to be breaked into separate usercontrols for enabling/disabling caching.

So, if we had situation where we want the whole Page to be cached and only a particular portion of the page not to be cached, we have to separate the page into usercontrols and then provide caching only for those sections for which we need to cache and leave the dynamic section without caching. The reason is that, if you specify outputcaching for the page, then the whole page, including the controls will be cached for that duration.

Thanks to the Substitution Control to specify a section on an output-cached Web page where you want dynamic content substituted for the control.

The Substitution control offers a simplified solution to partial page caching for pages where the majority of the content is cached. You can output-cache the entire page and then use Substitution controls to specify the parts of the page that are exempt from caching.

The syntax for declaring a substitution control is as follows:-

< asp:substitution id="Substitution1" methodname="Provide Method Name Here" runat="Server">
</asp:substitution>

Let us examine how we can check the functionality of the Substitution control on an output cached page.
In our ASPX Page, first we will declare the Output Caching next to the Page Directive as follows:-

<%@ Outputcache duration="60" varybyparam="none" %>

Where the duration="60" specifies that the page will be cached for 60 seconds.
Then, we declare a label, a substitution control and a button control as follows:-

<asp:substitution id="Substitution1" methodname="GetCurrentDateTime" runat="Server">
</asp:substitution>
Label displaying the Current Date !!!
<asp:label id="lblCurrentDate" runat="Server"> </asp:label>
Substitution control displaying the Current Date !!!
<asp:button id="btnRefresh" text="Check Now !!!" runat="Server">
</asp:button>

Then, in the codebehind, we specify the text for the Label in the Page_load event as follows:-

void Page_Load(object sender, System.EventArgs e)
{
lblCurrentDate.Text = DateTime.Now.ToString();
}

We will set the Current Date Time to the label so that when we refresh the page, we can see whether the time is retained or new time is displayed on the Label.

Next, we have to define the method that is specified to the Substitution control. If we examine the substitution control declaration above, we can see that we have specified as methodname="GetCurrentDateTime".

When the Substitution control executes, it calls a method that returns a string. The string that the method returns is the content to display on the page at the location of the Substitution control.

We define the method "GetCurrentDateTime" in the codebehind as follows:-

public static string GetCurrentDateTime(HttpContext context)
{
return DateTime.Now.ToString();
}

After compiling the application, if we browse the page we fill find that the text below Label and Substitution control is the same i.e. the Current Date and Time.

However, if you refresh the page by clicking the Button, you will find that the time displayed in the Label remains the same, while the time displayed in the Substitution control is changed (the change might be in seconds).

For subsequent requests also, the time would be changing in substitution control while it remains the same in the label, until the duration of "60" seconds expires.
Once it expires then both the label and substitution control would display the updated current time.

Thus we can see that though we have cached the whole page using output caching, the substitution control is dynamic to show the current time.

This is really a great advantage for developers who would like to use Caching for building robust applications while maintaining dynamic sections of their page as well.
There are other ways to achieve this functionality which I would discuss in my next articles.

Save Windows Update Downloads

If you use the regular Windows Update Web site, updates you select are downloaded and installed, you are not offered the option to save them for later use, or for distribution to non-Internet connected PC's. Nor can you download updates for other computers (hardware/software drivers for example). Also, a System Restore could overwrite updated files, but Windows Update would still show the update as installed, and won't let you download them again.

To save Windows Update files, connect to the Windows Update Catalog (Also referred to as Corporate Windows Update) site.

Here you can download all updates for all Windows XP OS types, and driver updates. Any updates you select will be collected in your Download Basket. If you have all updates you need, click on the link for your Download Basket. Here you can review/remove the updates you have selected. Press the Browse button to browse for a folder on your system/network where you want to save the files, then press the Download Now button. You have to accept the License Agreement, after which the files will be downloaded.