Backup a database to Windows Azure storage and restore a database from Windows Azure storage

The evaluation of SQL 2014 continues and here is what's new in Backup and Restore of SQL Server 2014.

In SQL Server 2014, we will be able to backup the database to Windows Azure storage and restore the database backup directly from the Windows Azure storage. 

To backup a database to Windows Azure storage, Choose the "Back up To:" to "URL" instead of "Disk"

To restore a database from Windows Azure storage, Choose the "Backup media type:" to "URL" instead of "File"

And here is where you connect to the Windows Azure storage during restore operation

Piecemeal Restore

What is Piecemeal Restore?
Piecemeal restore is a process which allows databases that contain multiple filegroups to be restored and recovered in stages.

Which Version of SQL Server supports Piecemeal restore?
Piecemeal restore was introduced in SQL Server 2005 and is supported in SQL Server 2005 and later versions.

What are the Limitations?

The Database should contain multiple files or filegroups and should have at least One Read-Only filegroup.

Piecemeal restore works with all recovery models, but is more flexible for the full and bulk-logged models than for the simple model.

Types of Piecemeal Restore?

• Offline
  In an offline piecemeal restore, the database is online after the partial-restore sequence. Filegroups that have not yet been restored remain offline, but they can be restored as you need them after taking the database offline.
  All editions of SQL Server 2005 and above support offline piecemeal restores.
• Online
  In an online piecemeal restore, after the partial-restore sequence, the database is online, and the primary filegroup and any recovered secondary filegroups are available. Filegroups that have not yet been restored remain offline, but they can be restored as needed while the database remains online.
  SQL Server 2005 Enterprise Edition and later versions support Online piecemeal restores.

T-SQL Query to get SQL Server Start time and Uptime

Below query gives the SQL Server Start time and Up time details.
This option is available from SQL Server 2008 and above and requires "VIEW SERVER STATE" permission to execute the query

SELECT sqlserver_start_time AS [SQL Server Start Time]
   ,CAST (DATEDIFF(MINUTE,sqlserver_start_time,GETDATE())/60 AS VARCHAR) + ' Hours ' 
   +CAST (DATEDIFF(MINUTE,sqlserver_start_time,GETDATE())%60 AS VARCHAR) + ' Minutes' AS [Uptime]
FROM sys.dm_os_sys_info

Three new DMV's "sys.dm_server_services", "sys.dm_server_registry" and "sys.dm_server_memory_dumps"

The DMV's "sys.dm_server_services", "sys.dm_server_registry" and "sys.dm_server_memory_dumps" are the 3 new DMV's that were introduced in SQL Server 2008 R2 and have been enhanced in SQL Server 2012 AKA Denali.


sys.dm_server_services 
This DMV gives information and status of the services SQL Server, SQL Server Agent and SQL Full-text Filter Daemon Launcher
Usage:

SELECT * FROM sys.dm_server_services

Result:

Reference:
http://msdn.microsoft.com/en-us/library/hh204542.aspx


sys.dm_server_registry
This DMV gives information about installation and configuration data that is stored in the windows registry for the current instance of SQL server.
Usage:

SELECT * FROM sys.dm_server_registry

Result:

Reference:
http://msdn.microsoft.com/en-us/library/hh204561.aspx


sys.dm_server_memory_dumps
This DMV gives information about memory dump files generated by the SQL Server database engine.
Usage:

SELECT * FROM sys.dm_server_memory_dumps

Result:

There are no dump files created yet from my Database engine.
Reference: http://technet.microsoft.com/en-us/library/hh204543.aspx

Startup Parameters - A new tab in Denali's SQL Server Configuration manager

We all know that Denali was launched with many new things built within. In those wide range of new enhancements, a separate tab for Startup parameters is one among them.
To check this out,

1. Go to Denali's "SQL Server Configuration Manager"
2. Right-Click on a SQL Server Service and Choose "Properties"
3. Now, in the properties page you can find a new tab for "Startup Parameters"

The Older versions of SQL Server Configuration Manager used to show the "Startup Parameters" as part of "Advanced" Tab.

Happy New Year 2012

Wishing you all a very happy and prosperous new year.
A Special Query for you all on this new year day :)

SET NOCOUNT ON
Select CHAR(72) + CHAR(97) + CHAR(112) + CHAR(112) + CHAR(121) + 
  SPACE(1) +  
  CHAR(78) + CHAR(101) + CHAR(119) +
  SPACE(1) +
  CHAR(89) + CHAR(101) + CHAR(97) + CHAR(114) +
  SPACE(1) +
CHAR(50)+CHAR(48)+CHAR(49)+CHAR(50) AS 'Surprise'
SET NOCOUNT OFF

Microsoft SQL Server 2012 Release Candidate 0 (RC0) - Available for Download

Microsoft SQL Server 2012 RC0 enables a cloud-ready information platform that will help organizations unlock breakthrough insights across the organization as well as quickly build solutions and extend data across on-premises and public cloud backed by capabilities for mission critical confidence.
To read more and download Microsoft SQL Server 2012 RC0 click here.

Backup Database to multiple locations simultaneously - Mirror Backups

Database backup is one the regular activity a DBA would perform. Some times you might come across a situation where in you need to backup the database to different location. When I say backup database to different locations, it means that a copy of backup file needs to be placed on a different location as well and this is different from the Split Backups.

This is can be achieved by different methods,

1. Take backup and then copy to multiple location
2. Take backup of the same database multiple times pointing to different locations
3. Use "MIRROR TO" Option in the Backup command 

Using the option "MIRROR TO" is very simple, you just need to mention "MIRROR TO" and "WITH FORMAT" options in the normal BACKUP DATABASE Statement and you are done. The backup database statement with these two options will take the backup of the same database to multiple locations at the same time.

This option "MIRROR TO" is introduced in SQL Server 2005 and this works only in SQL Server 2005 Enterprise Edition and later versions.

This can be used for all backup types and the Maximum number of "MIRROR TO" clauses that you can specify is three.

Example: 

BACKUP DATABASE AdventureWorks TO DISK = 'C:\Backup\AdventureWorks_Full.bak' MIRROR TO DISK = 'C:\Mirror\AdventureWorks_Full.bak' WITH STATS=10, FORMAT BACKUP DATABASE AdventureWorks TO DISK = 'C:\Backup\AdventureWorks_Differential.bak' MIRROR TO DISK = 'C:\Mirror\AdventureWorks_Differential.bak' WITH STATS=10, DIFFERENTIAL, FORMAT BACKUP LOG AdventureWorks TO DISK = 'C:\Backup\AdventureWorks_log.trn' MIRROR TO DISK = 'C:\Mirror\AdventureWorks_log.trn' WITH STATS=10, FORMAT

When it comes to restoring the database, we can use either of the backup copies to restore or recover the database.

SQL Server 2011 - Zoom Query Window and Results pane - New Feature

SQL Server 2011 comes with lots of new features and here is one such new feature.
SQL Server 2011 has introduced zooming of Query window and results pane.

Query Window - Normal

Query Window - Zoomed

Results Pane - Normal

Results Pane - Zoomed

SQL Server 2011 (Denali) - First Look

First look of SQL Server Denali after Installation.

Splash Screen

Login Window

SQL Server Management Studio

SQL Server 2011 (Denali) - Installation

The Installation process of SQL Server Denali is similar to the installation of SQL Server 2008 with little bit of modification to accommodate the new features installation.

Surface Area Configuration in SQL Server 2008

When I ask the question, what is the difference between SQL Server 2005 and SQL server 2008, one of the differences told by many people is that the Surface Area Configuration has been removed in SQL Server 2008.
But in reality the options that were managed using the Surface Area Configuration tool in SQL Server 2005 are now being managed using Facets in Policy Based Management in SQL Server 2008 onwards.

Facet in general means “a predefined set of properties that can be managed”

To access the Surface Area Configuration in SQL server 2008 onwards, follow the steps below.

1.  Right Click on the Server and choose "Facets"
   
   
   
2. In the resulting page, choose the facet “Surface Area Configuration” to manage the its properties
   

Configuring Database Mail

Database Mail in SQL Server 2005 and up is the replacement of the SQL Mail that existed in SQL Server 2000. Database Mail is more enhanced than SQL mail and does not require a MAPI mail client like outlook or outlook express installed on the system for it to work.

Here is the procedure to setup or configure the Database mail.

1. Enable Database Mail XPs
   
   EXEC SP_CONFIGURE 'show advanced options',1
   GO
   RECONFIGURE
   
   
   EXEC SP_CONFIGURE 'Database Mail XPs',1
   GO
   RECONFIGURE
2. Expand the "Management" Folder under your server. Right-Click on the "Database Mail" and Choose "Configure Database Mail"
   
3. This will open up the Database Mail Configuration Wizard.
   Now in the welcome page, click "Next" and in the "Select Configuration Task" Page choose the option "Setup Database Mail by performing the following tasks" and click "Next"
   
4. Now in the next page "New Profile" the wizard gives you the option to create your mail profile and Add accounts to this profile. Fill in the Mail profile details and click on "Add" button to add the Mail accounts.
   
5. In the "New Database Mail Account" page, fill in the Account Name, Description, Email Address, Display Name, Reply e-mail, Exchange Server Name and its Port Number. By default the port number will be 25. If it is different for your exchange server, then you have to mention that port number here.
   Then Specify the SMTP Authentication and click "OK"
   
6. Click "Next" in the resulting Page
   
7. In the "Manage Profile Security" page, select the appropriate security for your profile.
   Profiles are either Public or Private. A Private Profile is accessible only to specific Users or Roles. A Public Profile allows any Users or Roles with access to msdb database to send e-mail using that profile.
   
   
   
8. In the next Screen, you can configure the System Properties.
   
9. After configuring the System Parameters, click "Next". In the Summary Page, the wizard will display what actions will be performed. If you are satisfied then click "Finish" else Click "Back" button and do the changes as required.
    
   
   
10. Now Send a Test E-mail. Right-Click on "Database Mail" and Choose "Send Test E-Mail..."
    Now in the resulting Screen, select the "Mail Profile", enter the "To" E-Mail Address, Subject and Body and then Click on "Send Test E-Mail".
    
    
    
    You can also send e-mail by using the SP sp_send_dbmail which will be present in the msdb database.
    
    Exec msdb..sp_send_dbmail
          @profile_name='DBA'
          ,@recipients='segu.sandesh@gmail.com'
          ,@Subject='Test Email'
          ,@Body='Test Email from my Laptop'
    
       
    
    

This article is also available in pdf format for downloading.

Please Click here to get your copy.