When will the Database Backup/Restore Complete?

Often time, when doing an ad-hoc backup or restore on SQL Server database (especially for a very large database), we want to get some estimate as to when the backup or restore process will complete. As normally, after the backup or restore process has been completed, you will need to perform some additional tasks. So getting an estimate of when the Backup/Restore process takes will help you to plan ahead.

Using the STATS Option in Backup or Restore Command

For the longest time, I have been using the STATS option when doing ad-hoc backup or restore database in SQL Server. Basically what the STATS option does is that during the backup or restore process, every time the backup or restore process reach certain percentage (by default, if we don’t specify the percentage with the option, it is every 10%), it will display the percentage completed on the Messages window of Microsoft SQL Server Management Studio. Examples of using the STATS option in backup command:

  1: BACKUP DATABASE AdventureWorks2008R2
  2: TO DISK = 'C:\Backup\AdventureWorks2008R2.bak'
  3: WITH COPY_ONLY, STATS=5

This should backup the AdventureWorks2008R2 database onto C:\Backup folder. And, since I use the STATS=5 option, every 5%, I will get a display in the Messages tab, it should be something similar to:

You can do a similar thing with the restore command also. An example of the restore command script:

  1: RESTORE DATABASE AdventureWorks2008R2
  2: FROM DISK = 'C:\Backup\AdventureWorks2008R2.bak'
  3: WITH RECOVERY, STATS=5

Querying the Dynamic Management Objects

Starting with SQL Server 2005, we have dynamic management objects (DMO). The dynamic management objects really helps us (especially database administrator), to get a better view of what’s going on within the SQL Server and databases.

We can query the dm_exec_requests management objects to get an estimate of the backup and/or restore process that are currently running in the SQL Server instance. To do that, you can use the following query:

  1: SELECT a.command,
  2:       b.text,
  3:       a.percent_complete,
  4:       a.start_time,
  5:       total_elapsed_time_minutes = 
  6:            (total_elapsed_time/1000)/60,
  7:       estimated_time_left_minutes = 
  8:            (estimated_completion_time/1000)/60,
  9:       estimated_completion_time = 
 10:            dateadd(ms, estimated_completion_time, GETDATE())
 11: FROM  sys.dm_exec_requests a
 12:       CROSS APPLY sys.dm_exec_sql_text(sql_handle) b
 13: WHERE command LIKE 'RESTORE%'
 14:       OR command LIKE 'BACKUP%'
 15: 

One thing to note is that once the backup and/or restore process is no longer running (either because it has been completed successfully or error out), you will no longer see the backup and/or restore process in the dm_exec_requests.

Finding System Objects in SQL Server Databases

Occasionally, mistakes were made and user database objects, such as tables, stored procedures, and functions are created in master database by accident. Now the question is how to identify the system database objects and the user database objects?

In Microsoft SQL Server Management Studio, on the Object Explorer, if you expand the database tree and then the database object tree, for example tables, you can see a folder called System Tables (for Tables), System Views (for Views), etc..

You can also query the sys.objects. There is a column in there called is_ms_shipped. It is a bit flag to identify if the “object is created by internal SQL Server component”. Please see this article to get more information on the sys.objects. So to see which objects are created by internal SQL Server components (system objects) in master database, you can run the following query:

  1: USE [master];
  2: 
  3: SELECT name,
  4:      object_id,
  5:      type_desc,
  6:      create_date,
  7:      modify_date,
  8:      is_ms_shipped
  9: FROM sys.objects
 10: WHERE is_ms_shipped = 1;

Alternatively, you can also use the following query:

  1: USE [master];
  2: 
  3: SELECT name,
  4:      object_id,
  5:      type_desc,
  6:      create_date,
  7:      modify_date,
  8:      is_ms_shipped
  9: FROM sys.objects
 10: WHERE OBJECTPROPERTY(object_id, 'IsMSShipped') = 1;

Conversely, if you want to find user database objects in the master database, you can run the following query:

  1: USE [master];
  2: 
  3: SELECT name,
  4:      object_id,
  5:      type_desc,
  6:      create_date,
  7:      modify_date,
  8:      is_ms_shipped
  9: FROM sys.objects
 10: WHERE is_ms_shipped = 0;

Or:

  1: USE [master];
  2: 
  3: SELECT name,
  4:      object_id,
  5:      type_desc,
  6:      create_date,
  7:      modify_date,
  8:      is_ms_shipped
  9: FROM sys.objects
 10: WHERE OBJECTPROPERTY(object_id, 'IsMSShipped') = 0;

There is a couple caveat to this. Kalen Delaney (blog | twitter), pointed out in her blog post, that there is an undocumented stored procedure sp_MS_marksystemobject that can marked a user created system database object into system objects. Also if we turned on the allow updates configuration in master database, then any stored procedures that the user created in the master database will be marked as 1 for the IsMSShipped property.

Downloading SQL Server 2012 CTP 3

During the first day of SQL PASS Summit 2011, Ted Kummert, Corporate Vice President of Business Platform Division, revealed that the next generation of SQL Server code name Denali is going to be SQL Server 2012. It is slated to be release during the first half of 2012.

Currently Microsoft has released the Community Technology Preview (CTP) 3 of Denali, or shall I say SQL Server 2012. SQL Server 2012 CTP 3 has been released for awhile. But I have not got the chance to get it … until now. I’ve decided to try it out.

So I created a virtual environment using VMWare player. For the operating system, I decided to use Microsoft Windows Server 2008 R2 Standard edition (64-bit). Once I got that setup, I tried to download SQL Server 2012 CTP 3 from Microsoft site. You can find the installation files/ISO (for both the 32-bit and 64-bit version) on the following URL:

https://www.microsoft.com/betaexperience/pd/SQLDCTP3CTA/enus/

When I tried to download it from the guest environment (Microsoft Windows Server 2008 R2 Standard edition 64-bit) using Internet Explorer 8 and 9, I run into several problems. The first problem that I ran into was that, when I click the download button, nothing seems to happen. The second problem was that I keep getting the following message:

an add-on for this website failed to run

Solutions

There are a couple of ways in which you can resolve this:

IE Enhanced Security Configuration (ESC)

By default, on Windows Server 2008 IE Enhanced Security Configuration is enabled (I believe we also have this setting on Windows Server 2003). This setting is basically to prevent attacks on your computer from untrusted websites. So basically it is not allowing IE to run scripts.

You can turn this setting off. When you do so, you should be able to download SQL Server 2012 CTP 3 without any problem. To turn this setting off, you can go to Server Manager. Under the Security Information section, you should see a link for Configure IE ESC. Click on that one, and you should see a window similar to below:



Here you can turn on/off the IE Enhanced Security Configuration (ESC). This might not be ideal, since basically you are turning off that extra protection.

Trusted Sites

Alternatively, you can add a couple of sites to your Trusted Sites setting. To do this, you can do the following:

• Open Internet Explorer
• Go to Tools – Internet Options.
• On Internet Options window, click on the Security tab.
• Click on the Trusted sites, and then the Sites button.
• On the Add this website to the zone, type in *.microsoft.com (uncheck the Require server verification (https) for all sites in this zone), then click on Add button.
• On the Add this website to the zone, type in *.akamai.com (uncheck the Require server verification (https) for all sites in this zone), then click on Add button.
• Click on the Close button on the Trusted sites window.
• Click on the Ok button.

Now you should be able to download the SQL Server 2012 CTP without any problem. So basically on this one, you are saying to Internet Explorer that you are trusting Microsoft and Akamai website, and depending on the security level that you have for trusted sites (normally it is Medium-High), you are allowing scripts to run. You might ask, why we need to trust Akamai site? The Microsoft site is pretty obvious, since the download page is located on Microsoft site. But then Microsoft use Akamai download manager.

24 Hours of PASS: Fall 2011

Professional Association for SQL Server (PASS) is going to have two days of SQL Server online training on September 7 – 8, 2011. The training event is called 24 Hours of PASS. Just as its name indicates, there are going to be 24 training sessions, with each running for about an hour long. The sessions will be conducted by SQL experts, such as Brent Ozar, Gail Shaw, Denny Cherry, and many others. The event is going to covers many aspects of SQL Server, such as SQL Server Business Intelligence, PowerShell for SQL Server Administration, SQL Server Integration Services, and many more. You can see the list of sessions that they are going to have here.

While the training will not make you an expert in the subject, but it is a great way to quickly expose yourself to the subject. The thing about this event is that it is free. You can sign up for as many sessions as you like. To register for the sessions, you can go here.

So, to quickly recap:

What: 24 Hours of PASS (24HOP)
Where: Virtual
When: September 7 – 8, 2011

For more information, you can click here. To register you can click here.

SQL Server Export to Excel 2007

The ability to export SQL Server data into Excel is very useful. Often time, I need to extract data from SQL Server for users and for most part they want it in Excel format since the data can be manipulated, analyzed and re-formatted very easily.

In Microsoft Excel 2003, each worksheet can only have a maximum of 64,000 rows. However, in Microsoft Excel 2007, we can have a maximum of 1,000,000 rows on each worksheet. Because of this, in some cases, I need to do data export from SQL Server to Microsoft Excel 2007. The problem is that I don’t have Microsoft Excel 2007 installed on my development system. I only have Microsoft Excel 2003. Now the question is that can I use SQL Server data export feature to export data from SQL Server to Excel 2007 without having Microsoft Excel 2007 installed in my system? The answer to that as I found out is yes (but with a few caveats).

On my system, I have SQL Server 2008 Management Studio Express (you can get this free from here), and a blank Microsoft Excel 2007 xlsx file (that I copy from another system that have Microsoft Excel 2007 installed).

Now if I tried to export data from SQL Server to Excel 2007 using SQL Server 2008 Management Studio Express, I would get the following error message:

The operation could not be completed.

Additional information:

The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine. (System.Data)

To resolve this error, I just need to install Microsoft Excel 2007 Viewer. Microsoft Excel 2007 Viewer is free and can be downloaded from here.

Once I installed Microsoft Excel 2007 Viewer, I am able to use the data export feature on Microsoft SQL Server 2008 Management Studio to export data from SQL Server into Microsoft Excel 2007 without any problem.