MS SQL Server Backups & Restore: Steps explained

Yes I know, there are lots of articles and posts about this topic. But I want to show, what are things to be known to recover the database to specific point-in-time.

Backup

As you know there are 3 recovery models supported in MS SQL server.

1. Simple
   • Automatically clear the log file.
   • Only allows  recover to end of a backup.
   • No log backups
2. Full
   • Requires log backups
   • No work is lost, if data (.mdf) file get damaged/lost
   • Can recover to a specific point-in-time.
3. Bulk-logged
   • Requires log backups
   • Use minimal logging for bulk operations, thereby reducing the log space
   • Point-in-time recovery is not supported.

In a mission critical environment, Full recovery model is the most recommended. In this post I’m paying attention to this model and simply explain point-in-time recovery.

From your SQL server database you can take, 3 back up types.

1. Full
2. Differential
3. Transaction Log

In Full recovery model, applicability of above types can be depicted in following way.

Figure 1

You can see in the Figure 1, Full backup will take all the data from start to the end(let say T1). On the other hand Differential backup will take care of data from last successful Full backup(always) to the end. Whereas, Transaction Log back up will incrementally takes data in subsequent attempts from where the last successful transaction log was taken to the end of the log.  By considering this you can create a backup strategy for your database. It has to be more serious when you are dealing with large and mission critical database. In a little later, I will explaining about restoring of database which will helps you to design the backup strategy.

Here is the T-SQL statements for

Connecting DB2/iSeries From .net Application

I need to connect to a DB2 from a .net application. But it’s  in iSeries system. I did not know what is the way do it. I read lot of articles/blog posts and really helped me. But in this post, I thought to consolidate them for fast reference.

There are two .net data providers for DB2.

1. IBM.Data.DB2.iSeries
2. IBM.Data.DB2

Both provider follows same rules/guidelines of common ADO.net components. For an example, both have Connection, Command, DataAdapter, DataReader. But there are differences and limitations.

IBM.Data.DB2.iSeries

Using this provider you can only access to iSeries system. You have to install iSeries Client Access to development machine and/or server. It is quite a bit setup file ( > 3GB with 64Bit version), but we required (refer the bellow screen shots)

• .NET Data Provider
• Header,Library and Documentation.

Figure 1

 

There are limitations/unsupported features [IBM Redbooks 2005 ,Page 38]  in this provider, bellow lists 3 out of 11.

1. User-defined types (UDTs): Although some features may work, extensive testing has not
   been done using the IBM.Data.DB2.iSeries provider with UDTs.
2. Distributed relational database architecture (DRDA®), including the CONNECT and
   DISCONNECT statements.
3. SET TRANSACTION, COMMIT, and ROLLBACK statements: Instead, we recommend
   using the built-in transaction support provided via the iDB2Connection.BeginTransaction()
   method, and the iDB2Transaction object.

I think they have stopped update/improvements for this provider after .net version 1.1

IBM.Data.DB2

This comes as new IBM  data provider for .net 2.0 and having rich features. For the easier development, it is coming with Visual Studio Add-in. Also IBM released beta version for .net 4.0 and add-in for VS 2010. You can see details and download by clicking this link.

Testing DB2 .net connection

After installing either IBM Data Server Client or IBM Data Server Runtime Client, we can test the connection by running following command,

To connect iSeries system, we have to give the port no 446 (default) to the connection string, Server=[IP:PORT].

C:\Program Files\IBM\SQLLIB\BIN>testconn20.exe "User ID=[UserName];Password=[pwd];

Database=[Data Base Or Alias];Server=[IP];"

If this success, it should dump output to the console similar to the following.[Beginning DB2 2008, Page 282]

Step 1:Printing version info

    .NET Framework version: 2.0.50727.42

    DB2 .NET provider version: 9.0.0.2

    Capability bits: ALLDEFINED

    Build: 20070524

    Factory for invairant name IBM.Data.DB2 verified

    Elapsed: 7.15625

Step 2: Connecting using "User ID=fuzzy;Password=fuzzy;Database=SAMPLE;

    Server=localhost;ServerType=db2;pooling=false"

    Server type and version: DB2/NT 09.05.0000

    Elapsed: 4.640625

Step 3: Selecting rows from SYSIBM.SYSTABLES to validate existence of packages

    SELECT * FROM SYSIBM.SYSTABLES FETCH FIRST 5 rows only

    Elapsed: 0.890625

Step 4: Calling GetSchema for tables to validate existence of schema functions

    Elapsed: 1.78125

Test passed.

---

References

[IBM Redbooks 2005]:  Hernando Bedoya,Carlos Carminati,Lorie DuBois,Jarek Miszczyk,Ajit Mungale. Integrating DB2 Universal Database for iSeries with Microsoft ADO .NET. 2005

[Beginning DB2 2008]: Grant Allen,Beginning DB2: From Novice to Professional, Apress 2008

RANK() and alternate Sub Query in T-SQL

Consider, we have data table called Orders_Header which hold order header details.

As you can see, PO number(PONo)  ‘0050426995’ is duplicating. Because this table containing PO amendments too. If you notice, DownloadData is in order, meaning first version of PO received on 2010-06-26 17:07:04.910, rest of them subsequently.
The requirement is we want to get following output.

Normally we can write a sub query for this and get the output.

SELECT PONo,

(

SELECT COUNT(PONo)+1 -- to omit zero

FROM ORDERS_Header WHERE PONo=oh.PONo AND DownloadDate< oh.DownloadDate

) AS Revision,

DownloadDate

FROM ORDERS_Header oh

ORDER BY PONO,DownloadDate

By running this query, you can see the following Actual Execution Plan.





From MS-SQL 2005 onwards this can be easily achieved by RANK() function. 

SElECT PONo, 

RANK() OVER(PARTITION BY PONo ORDER BY DownloadDate) AS Revision ,DownloadDate 

FROM dbo.ORDERS_Header

ORDER BY PONO,DownloadDate

Check its Actual Execution Plan and note the difference.

Update Progress with AJAX UpdatePanel

In this post I will demonstrate how to create update progress with UpdatePanel, a similar one that we can see in our gmail, like following,

Following key things to be addressed.

1. It should be on top-center of the page
2. It should be on top all other HTML controls
3. When scrolling it should be placed on relative position of the current scroll position.

To achieve this we can use many alternatives but I would use a simple approach.
In addition to the UpdatePanel, I will use </div>, CSS style and javaScript.

Here we go.

1. Add a div tag within body element.
   

   <div class="divProg" id="divProg"></div>

   Note that id and class attributes are necessary.
   
   
2. Add following css class to the web page or style sheet file. Keep in mind, without position: absolute; z-index: 10; css properties this won’t work as we expected.
   

   .divProg
   {
    position: absolute;
    left: 0px;
    top: 0px;
    width: 100px;
    height: 0px;
    z-index: 10;
    border: 1px none #000000;
    visibility: hidden;
    background-color: #FFF1A8;
    font-weight: bold;
    padding: 5px 10px 5px 10px;
   }

   
   
3. Now we want to display div on top-center of the screen as well as move its top position relative to the current scroll position. To address this we will creates following 3 javascript functions.
   To get current Y position of scroll,
   

   function getScroll_Y() 
   {
           return document.documentElement.scrollTop;  
   }

   To set current top position of div,
   

   function setDivTop() 
   {
   
           if (document.getElementById("divProg") != null) {
               theDiv= document.getElementById("divProg");
           }
           else {
               return;
           }
           theDiv.style.top = getScroll_Y() + "px";
   }

   Write a function to toggle display for the div.
   

   function displayDiv(display) 
   {
       if (document.getElementById("divProg") != null) {
           theDiv = document.getElementById("divProg");
       }
       else {
           return;
       }
   
       if (display) {
           var width = document.body.offsetWidth;
           var height = document.body.offsetHeight;
           if (!width) {
               width = window.innerWidth;
               height = window.innerHeight;
   
               if (!width) {
                   width = screen.width;
                   height = screen.height;
               }
           }
           // To center the div, we need to deduct its half of
           // width from half of screen width. So we set div width =100
           // in css, so that deduct by 50
           theDiv.style.left = width / 2 - 50;
           theDiv.style.top = getScroll_Y();
           theDiv.style.height = 25;
           theDiv.innerText = 'Working...';
           theDiv.style.visibility = 'visible';
   
       }
       else {
           theDiv.style.visibility = 'hidden';
       }
   }

   
   
   
4. Now we want to handle request start and request end events. we will be able to use following code with assist of ajax extension library.
   
   

   // Requests Events
   // ============================================
   var prm = Sys.WebForms.PageRequestManager.getInstance();
   
   prm.add_initializeRequest(InitializeRequest);
   prm.add_endRequest(EndRequest);
   
   function InitializeRequest(sender, args) {
       if (prm.get_isInAsyncPostBack()) {
           args.set_cancel(true);
       }
       displayDiv(true);
     
   
   }
   function EndRequest(sender, args) {
       if (args.get_error()) {
           alert(args.get_error().message);
           args.set_errorHandled(true);
   
       }
       displayDiv(false);
   }
   
   //===============================================

   
   
5. Set page's scroll event,
   

   <body onscroll="setDivTop()">

   
   

	Download Sample
	File Size: 18K