SQL SERVER – BACKUP DATABASE ,Mirrored Backup and Restore and Split File Backup

Introduction

This article is based on a real life experience of the author while working with database backup and restore during his consultancy work for various organizations. We will go over the following important concepts of database backup and restore.

1. Conventional Backup and Restore
2. Spilt File Backup and Restore
3. Mirror File Backup
4. Understanding FORMAT Clause
5. Miscellaneous details about Backup and Restore

Note: Before running all the examples, make sure that you have the required folders created on your drive. It is mandatory to create Backup folders prior to creating backup files using SQL Server.

In our example, we will require the following folders:

• C:\Backup\SingleFile
• C:\Backup\MultiFile
• C:\Backup\MirrorFile

Conventional and Split File Backup and Restore

Just a day before working on one of the projects, I had to take a backup of one database of 14 GB. My hard drive lacked sufficient space at that moment. Fortunately, I had two 8 GB USB Drives with me. Now, the question was how to take a backup in two equal sizes, each of 7 GB, so I can fit them on each USB drive. Well, conventional backup takes one large backup in one file. However, SQL Server backup command can take backups in two or more split parts.

Let us see an example of a conventional one-file backup using the AdventureWorks database.

BACKUP DATABASE AdventureWorksTO DISK = 'C:\Backup\SingleFile\AdventureWorks.bak'GO

The result is displayed below. Here, the backup is taken in a single file.

Now, let us see how we can split one database into two different database files. This method is very similar to taking a single-file backup. By simply adding an additional DISK option we can split the files backup files.

BACKUP DATABASE AdventureWorksTO DISK = 'C:\Backup\MultiFile\AdventureWorks1.bak',DISK = 'C:\Backup\MultiFile\AdventureWorks2.bak',DISK = 'C:\Backup\MultiFile\AdventureWorks3.bak'GO

In the previous example, we can clearly see that backup is split into three equal parts of the original backup file size.

Restoring a backup from a single-file backup is quite easy. Let us go over an example where we restore the AdventureWorks database from a single backup file.

RESTORE DATABASE AdventureWorksFROM DISK = 'C:\Backup\SingleFile\AdventureWorks.bak'GO

Running the above script will give a successful message.

Now let us see an example where we restore a database from a split file. This method is very similar to restoring a database from a single file; just add an additional DISK option.

RESTORE DATABASE [AdventureWorks]FROM DISK = N'C:\Backup\MultiFile\AdventureWorks1.bak',DISK = N'C:\Backup\MultiFile\AdventureWorks2.bak',DISK = N'C:\Backup\MultiFile\AdventureWorks3.bak'GO

Running the above script will give a successful message as shown in the image below.

Make sure that while restoring database, the database is not in use, otherwise it will give an error of database in use. In the event of an error taking place, close all the connections and re-attempt to restore the database.

Mirror Backup of the file

It is quite a common practice to create an exact copy of the backup and store it to several places to deal with any catastrophes which might affect the place where the database is stored. Once a full backup is accomplished DBAs generally copy the database to another location in their network using a third party tools like robocopy or native DOS commands like xcopy.

In SQL Server 2005 and later versions, there is a Mirror command that makes a copy of the database backup to different locations while taking the original backup. The maximum limit of additional locations that can be specified with MIRROR clause is 3.

Mirrored backup can be taken in local computer system as well as in a local network. Let us now see two examples of mirror backup.

Example 1. Single File Backup to Multiple Locations using Mirror

BACKUP DATABASE AdventureWorksTO DISK = 'C:\Backup\SingleFile\AdventureWorks.bak'MIRROR TO DISK = 'C:\Backup\MirrorFile\AdventureWorks.bak'WITH FORMAT
GO

If this command is being run for the first time, it is mandatory to use the WITH FORMAT clause; but for sub sequential runs it is not required. WITH FORMATreinitializes the backup.

When checked in both the folders ‘SingleFile’ and ‘MirrorFile’, backup files are exactly the same files. As mentioned earlier, four mirror backup can be specified in total.

Example 2. Split File Backup to Multiple Locations using Mirror

We have earlier seen an example where we can have multiple split files of large database backup files. SQL Server Mirror functionality also supports backup of the split files.

BACKUP DATABASE AdventureWorksTO DISK = 'C:\Backup\MultiFile\AdventureWorks1.bak',DISK = 'C:\Backup\MultiFile\AdventureWorks2.bak',DISK = 'C:\Backup\MultiFile\AdventureWorks3.bak'MIRROR TO DISK = 'C:\Backup\MirrorFile\AdventureWorks1.bak',DISK = 'C:\Backup\MirrorFile\AdventureWorks2.bak',DISK = 'C:\Backup\MirrorFile\AdventureWorks3.bak'WITH FORMAT
GO

All the mirror sets will need the same number of DISK clauses as the original backup media.

Mirrored database backup can be restored using the same method as the original backup. Mirrored backup is in fact an exact replica of the original backup.

Understanding the FORMAT Clause

The FORMAT clause is used to reinitiate a backup media. Although it is a very useful clause it should be used with caution. When the clause is used it erases everything present in backup media. I have noticed that some DBAs are confused while taking a backup on a local disk where they have SQL Server installed. They have a misconception that if the format command is used, it will erase the complete disk including the SQL Server installation. However, the fact is that SQL Server format clause is quite different from OS format. The effect of SQL Server format clause is limited to a folder or path specified in the DISKclause.

In our example, when the FORMAT clause is specified, it will format only folders like C:\Backup\MultiFile\ or C:\Backup\SingleFile.

SQL SERVER – Restore Database Backup using SQL Script

Database YourDB has full backup YourBaackUpFile.bak. It can be restored using following two steps.

Step 1: Retrive the Logical file name of the database from backup.
RESTORE FILELISTONLYFROM DISK = 'D:BackUpYourBaackUpFile.bak'GO
Step 2: Use the values in the LogicalName Column in following Step.
----Make Database to single user ModeALTER DATABASE YourDBSET SINGLE_USER WITH
ROLLBACK IMMEDIATE

----Restore DatabaseRESTORE DATABASE YourDBFROM DISK = 'D:BackUpYourBaackUpFile.bak'WITH MOVE 'YourMDFLogicalName' TO 'D:DataYourMDFFile.mdf',MOVE 'YourLDFLogicalName' TO 'D:DataYourLDFFile.ldf'

/*If there is no error in statement before database will be in multiuser
mode.
If error occurs please execute following command it will convert
database in multi user.*/ALTER DATABASE YourDB SET MULTI_USER

C#: WebClient Usage

Microsoft has provided a great utility since .NET 1.0 for doing quick I/O via websites.  The WebClient class provides basic functionality for downloading from and uploading to webservers.  the WebClient example on MSDN leaves a lot to be desired, especially for beginners.  I’d like to expand on that, as well as provide some example code.

C#: Using WebClient to fetch a page:

// create a new instance of WebClient
        WebClient client = new WebClient();
 
        // set the user agent to IE6
        client.Headers.Add("user-agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; .NET CLR 1.0.3705;)");
        try
        {
            // actually execute the GET request
            string ret = client.DownloadString("http://www.google.com/");
 
            // ret now contains the contents of the webpage
            Console.WriteLine("First 256 bytes of response: " + ret.Substring(0,265));
        }
        catch (WebException we)
        {
            // WebException.Status holds useful information
            Console.WriteLine(we.Message + "\n" + we.Status.ToString());
        }
        catch (NotSupportedException ne)
        {
            // other errors
            Console.WriteLine(ne.Message);
        }

(This code uses DownloadString, you can also use DownloadData for a more binary-friendly version)

This is great for fetching simple pages that have data encoded in the querystring, but there are some problems with the basic DownloadString method of WebClient.  It’s synchronous, so it will block until the operation completes.  So for slow connections, or large files, this would need to run in another thread.  There is a better way.  But first, here’s another example of another basic, but important, method,DownloadFile.

// create a new instance of WebClient
        WebClient client = new WebClient();
 
        // set the user agent to IE6
        client.Headers.Add("user-agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; .NET CLR 1.0.3705;)");
        try
        {
            // actually execute the GET request
            client.DownloadFile("http://www.google.com/","google_fetch.txt");
 
            // ret now contains the contents of the webpage
            Console.WriteLine("File Saved.");
        }
        catch (WebException we)
        {
            // WebException.Status holds useful information
            Console.WriteLine(we.Message + "\n" + we.Status.ToString());
        }
        catch (NotSupportedException ne)
        {
            // other errors
            Console.WriteLine(ne.Message);
        }

This example is almost identical to the above, aside from the client.DownloadFile method.  This will save the file to disk, instead of returning a string–often what you would end up doing with the string anyway.

But can I send POST data?

Yes!  Using WebClient.UploadString or WebClient.UploadData you can POST data to the server easily.  I’ll show an example using UploadData, since UploadString is used in the same manner as DownloadString.

byte[] bret = client.UploadData("http://www.website.com/post.php", "POST",
                System.Text.Encoding.ASCII.GetBytes("field1=value1&field2=value2") );
 
            string sret = System.Text.Encoding.ASCII.GetString(bret);

UploadData returns a byte array (byte[]) which contains the contents of the page.  This can easily be converted to a string by using the System.Text.Encoding.ASCII.GetString() method.  You can also use the other encoding types in System.Text.Encoding (such as UTF8) to do the same.

Also note the use of GetBytes(), the UploadData method will only take a byte array as a buffer for the upload.  This works well if you are uploading a binary file.

What about the asynchronous methods of WebClient?

WebClient provides asynchronous methods for fetching webpages as well, they are named similarly to the synchronous methods, DownloadFileAsync, DownloadDataAsync, DownloadStringAsync, UploadFileAsync, UploadDataAsync, UploadStringAsync, and UploadValuesAsync.

In order to execute an Async request, you will need to attach event handlers before the method is executed.  Without these event handlers you will not have any sense of when the operation finishes, or what stage it is at.

C# WebClient Asynchronous call example:

void do_upload() {
   WebClient client = new WebClient();
   // add event handlers for completed and progress changed
   client.UploadProgressChanged += new UploadProgressChangedEventHandler(client_UploadProgressChanged);
 
   client.UploadFileCompleted += new UploadFileCompletedEventHandler(client_UploadFileCompleted);
  // carry out the operation as normal
   client.UploadFileAsync("http://www.daveamenta.com/up.php", @"c:\somefile.bin");
}
 
void client_UploadProgressChanged(object sender, UploadProgressChangedEventArgs e)
{
   Console.WriteLine(e.ProgressPercentage);
}
 
void client_UploadFileCompleted(object sender, UploadFileCompletedEventArgs e)
{
   if(e.Result != null) {
      Console.WriteLine(System.Text.Encoding.ASCII.GetString(e.Result));
   }
}

The call to client.DownloadFileAsync will no longer block the thread, and will execute in the background, periodically calling the event handlers to display progress.  The other methods can be used in this same way.

5 Ways to Make Ajax Calls with jQuery

What is AJAX

AJAX stands for asynchronous JavaScript and XML. If you see another term XHR, which is shorthand for XML HTTP rqueest, it’s the same thing. Don’t be afraid of this jargon; AJAX is not rocket science. 

• In Gmail, switch from inbox to draft. Part of the page is changed, but the page is not refreshed. You remain on the same page. Url has not changed (except for the #draft at the end of the url, but that’s still the same webpage).
• In Google Reader, select a feed. The content changes, but you are not redirected to another url.
• In Google Maps, zoom in or zoom out. The map has changed, but you remain on the same page.

The key to AJAX’s concept is “asynchronous”. This means something happens to the page after it’s loaded. Traditionally, when a page is loaded, the content remains the same until the user leaves the page. With AJAX, JavaScript grabs new content from the server and makes changes to the current page. This all happena within the lifetime of the page, no refresh or redirection is needed.

Caching AJAX

Now we should know what AJAX actually is. And we know that, when Gmail refreshes some content without redirection, an AJAX call is made behind the scenes. The requested content can either be static (remains exactly the same all the time, such as a contact form or a picture) or dynamic (requests to the same url get different responses, such as Gmail’s inbox where new mails may show up any time).

For static content, we may want the response cached. But for dynamic content, which can change in a second’s time, caching AJAX becomes a bug, right? It should be noted that Internet Explorer always caches AJAX calls, while other browsers behave differently. So we’d better tell the browser explicitly whether or not AJAX should be cached. With jQuery, we can accomplish this simply by typing:

view plaincopy to clipboardprint?

1. $.ajaxSetup ({  
2.     cache: false  
3. });  

1. load(): Load HTML From a Remote URL and Inject it into the DOM

The most common use of AJAX is for loading HTML from a remote location and injecting it into the DOM. With jQuery’s load() function, this task is a piece of cake.

Minimal Configuration

Click on the first button named “load().” A piece of HTML is injected into the page, exactly what we were talking about. Let’s see what’s going on behind the scenes.

Below is the JavaScript code for this effect:

view plaincopy to clipboardprint?

1.     $.ajaxSetup ({  
2.         cache: false  
3.     });  
4.     var ajax_load = "<img src='img/load.gif' alt='loading...' />";  
5.   
6. //  load() functions  
7.     var loadUrl = "ajax/load.php";  
8.     $("#load_basic").click(function(){  
9.         $("#result").html(ajax_load).load(loadUrl);  
10.     });  

1. $.ajaxSetup forces the browser NOT to cache AJAX calls.
2. After the button is clicked, it takes a little while before the new HTML is loaded. During the loading time, it’s best to show an animation to provide the user with some feedback to ensure that the page is currently loading. The “ajax_load” variable contains the HTML of the loading sign.
3. “ajax/load.php” is the url from which the HTML is grabbed.
4. When the button is clicked, it makes an AJAX call to the url, receives the response HTML, and injects it into the DOM. The syntax is simply $(“#DOM”).load(url). Can’t be more straightforward, hah?

Now, let’s explore more details of the request with Firebug:

1. Open Firebug.
2. Switch to the “Net” tab. Enable it if it’s disabled. This is where all HTTP request in the browser window are displayed.
3. Switch to “XHR” tab below “Net”. Remember the term “XHR?” It’s the request generated from an AJAX call. All requests are displayed here.
4. Click on the “load()” button and you should see the following.

The request is displayed, right? Click on the little plus sign to the left of the request, more information is displayed.

Click on the “Params” tab. Here’s all parameters passed through the GET method. See the long number string passed under a “_” key? This is how jQuery makes sure the request is not cached. Every request has a different “_” parameter, so browsers consider each of them to be unique.

Click on the “Response” tab. Here’s the HTML response returned from the remote url.

Load Part of the Remote File

Click on “load() #DOM” button. We notice that only the Envato link is loaded this time. This is done with the following code:

view plaincopy to clipboardprint?

1. $("#load_dom").click(function(){  
2.     $("#result")  
3.         .html(ajax_load)  
4.         .load(loadUrl + " #picture");  
5. });  

With load(url + “#DOM”), only the contents within #DOM are injected into current page.

Pass Parameters Through the GET Method

Click on the “load() GET” button and open firebug.

view plaincopy to clipboardprint?

1. $("#load_get").click(function(){  
2.     $("#result")  
3.         .html(ajax_load)  
4.         .load(loadUrl, "language=php&version=5");  
5. });  

By passing a string as the second param of load(), these parameters are passed to the remote url in the GET method. In Firebug, these params are shown as follows:

Pass Parameters Through the POST Method

Click on the “load() POST” button and open Firebug.

view plaincopy to clipboardprint?

1. $("#load_post").click(function(){  
2.     $("#result")  
3.         .html(ajax_load)  
4.         .load(loadUrl, {language: "php", version: 5});  
5. });  

If parameters are passed as an object (rather than string), they are passed to the remote url in the POST method.

Do Something on AJAX Success

Click on “load() callback” button.

view plaincopy to clipboardprint?

1. $("#load_callback").click(function(){  
2.     $("#result")  
3.         .html(ajax_load)  
4.         .load(loadUrl, null, function(responseText){  
5.             alert("Response:\n" + responseText);  
6.         });  
7. });  

A function can be passed to load() as a callback. This function will be executed as soon as the AJAX request is completed successfully.

2. $.getJSON(): Retrieve JSON from a Remote Location

Now we’ll review the second AJAX method in jQuery.

JSON (JavaScript Object Notation) is a lightweight data-interchange format. It’s very convenient when exchanging data programmatically with JSON.

Let’s review an example.

Find the $.getJSON() section in the demo page, type in some words in your native language, and click detect language.

view plaincopy to clipboardprint?

1. //  $.getJSON()  
2.     var jsonUrl = "ajax/json.php";  
3.     $("#getJSONForm").submit(function(){  
4.         var q = $("#q").val();  
5.         if (q.length == 0) {  
6.             $("#q").focus();  
7.         } else {  
8.             $("#result").html(ajax_load);  
9.             $.getJSON(  
10.                 jsonUrl,  
11.                 {q: q},  
12.                 function(json) {  
13.                     var result = "Language code is \"<strong>" + json.responseData.language + "\"";  
14.                     $("#result").html(result);  
15.                 }  
16.             );  
17.         }  
18.         return false;  
19.     });  

Let’s jump to Line 9:

1. $.getJSON doesn’t load information directly to the DOM. So the function is $.getJSON, NOT $(“#result”).getJSON. (There are pairs of similar looking functions in jQuery such as $.each() andeach(). Check out their respective documentation for more information.)
2. $.getJSON accepts three parameters. A url, parameters passed to the url and a callback function.
3. $.getJSON passes parameters in GET method. POSTing is not possible with $.getJSON.
4. $.getJSON treats response as JSON.

$.getJSON’s function name is NOT camel-cased. All four letters of “JSON” are in uppercase.

Look at the response in JSON format in Firebug. It’s returned from Google Translate API. Check out ajax/json.php in source files to see how language detection works.

3. $.getScript(): Load JavaScript from a Remote Location

We can load JavaScript files with $.getScript method. Click on “Load a Remote Script” button in the demo page; let’s review the code for this action.

view plaincopy to clipboardprint?

1. //  $.getScript()  
2.     var scriptUrl = "ajax/script.php";  
3.     $("#getScript").click(function(){  
4.         $("#result").html(ajax_load);  
5.         $.getScript(scriptUrl, function(){  
6.             $("#result").html("");  
7.         });  
8.     });  

1. $.getScript accepts only two parameters, a url, and a callback function.
2. Neither the GET nor POST params can be passed to $.getScript. (Of course you can append GET params to the url.)
3. JavaScript files don’t have to contain the “.js” extension. In this case, the remote url points to a PHP file. Let your imagination fly and you can dynamically generate JavaScript files with PHP.

See the response JavaScript in Firebug.

4. $.get(): Make GET Requests

$.get() is a more general-purpose way to make GET requests. It handles the response of many formats including xml, html, text, script, json, and jonsp. Click on the “$.get()” button in the demo page and see the code.

view plaincopy to clipboardprint?

1. //  $.get()  
2.     $("#get").click(function(){  
3.         $("#result").html(ajax_load);  
4.         $.get(  
5.             loadUrl,  
6.             {language: "php", version: 5},  
7.             function(responseText){  
8.                 $("#result").html(responseText);  
9.             },  
10.             "html"  
11.         );  
12.     });  

1. $.get() is completely different, as compared to get(). The latter has nothing to do with AJAX at all.
2. $.get accepts the response type as the last parameter, which makes it more powerful than the first functions we introduced today. Specify response type if it’s not html/text. Possible values are xml, html, text, script, json and jonsp.

5. $.post(): Make POST Requests

$.post() is a more general-purpose way to make POST requests. It does exactly the same job as $.get(), except for the fact that it makes a POST request instead.

view plaincopy to clipboardprint?

1. //  $.post()  
2.     $("#post").click(function(){  
3.         $("#result").html(ajax_load);  
4.         $.post(  
5.             loadUrl,  
6.             {language: "php", version: 5},  
7.             function(responseText){  
8.                 $("#result").html(responseText);  
9.             },  
10.             "html"  
11.         );  
12.     });  

The use of $.post() is the same as its brother, $.get(). Check the POST request in Firebug (shown in the following image).

Finally… $.ajax():

Up to this point, we’ve examined five commonly used jQuery AJAX functions. They bear different names but, behind the scenes, they generally do the exact same job with slightly different configurations. If you need maximum control over your requests, check out the $.ajax() function.

This is jQuery’s low-level AJAX implementation. See $.get, $.post etc. for higher-level abstractions that are often easier to understand and use, but don’t offer as much functionality (such as error callbacks). -jQuery’s official Documentation

In my opinion, the first five functions should satisfy most of our needs. But if you need to execute a function on AJAX error, $.ajax() is your only choice.

Conclusion

Today, we took an in-depth look of five ways to make AJAX calls with jQuery.

• load(): Load a piece of html into a container DOM.
• $.getJSON(): Load a JSON with GET method.
• $.getScript(): Load a JavaScript.
• $.get(): Use this if you want to make a GET call and play extensively with the response.
• $.post(): Use this if you want to make a POST call and don’t want to load the response to some container DOM.
• $.ajax(): Use this if you need to do something when XHR fails, or you need to specify ajax options (e.g. cache: true) on the fly.

Before we conclude, here’s a comparison table of these functions. I hope you enjoyed this lesson! Any thoughts?