Azure DB Backup/Restore Via SSMS 2014

This topic will provide details(on high level) of new features introduced in SQL Server 2014 Management Studio (SSMS) to Manage Azure Databases.

 

Enhanced Backup:SQL Server 2014 provides new Windows Azure integration to SQL Server's backup capabilities. You can specify a Windows Azure URL as the target for your SQL Server 2014 database backups. This new Windows Azure backup feature is fully integrated into SSMS.
 

In Backup Interface, you will see additional Option called "URL" as shown in the exhibit below,This option will backup the data on azure(cloud), 

 

Select "URL" as "Back up to" and provide the azure connection and file details to backup the data on azure.

 

Enhanced Restore: Same with the restores, you can choose URL as your Source…and provide the details accordingly as shown below.
 



 

Select "URL" and Click on "Add" Button

 

 

This backup and restore functionality are same or similar to when using DISK or TAPE, with a few differences.

 

Hope This Helps!

Store Query as Template Or Control in SSMS ToolBox

Hope many people are aware of this feature, but I came to know about it on today while watching one of the SQL Server webcast. I just want to share the same in this blog. it will be helpful to new comers.

I used to store the query in a script file for future/reference/reusable purpose, Instead of keeping as separate file, you can store the Query in SSMS Toolbox window, which will act as a Template or control.

Following steps will help you to store a Query in Toolbox:-

1.       Open SQL Server Management Studio, connect to Database server

2.       Check whether Toolbox window is opened or not, if it is not opened then go to View Menu, select Toolbox Menu,  or else open the menu using shortcut keyword (Ctrl + Alt + X)

3.       If you see any control on toolbox, then you can skip the steps 4,5 and 6

4.       When I opened the Toolbox window, it is Empty container as shown below

5.       Right Click on Toolbox, select “Show All” Options

6.       Toolbox is appeared with list of SSIS and DB Maintenance controls as shown below

7.       Open Query Window; write Some Query/Query Template whichever you want to store it. Select the Query, Drag and drop on Toolbox, Or… else copy the query, Right click and paste on the Toolbox.

Look at the following screen, I have a written a Query on Query window, then dropped the same at Toolbox as well.

8.       You can categorize the collection Queries by using Creating Tab in the toolbox. Right Click on the toolbox, select “Add Tab”, Enter the Group Name of the Queries.

9.       To reuse the Query, right click on the Query, select copy; paste the same on Query window for use.

Note: In Toolbox, if the saved Query is Enabled Mode, then you can drag the Query Template and drop into query window like placing control on designer. By default it is disabled mode. So you need to follow as step 9 to use.

How To Create XMLA Script For Cube Process Using SSMS

Following steps will explain “how to generate a XMLA Script for Cube process” using SQL Server Management Studio (SSMS).

1.       Open SQL Server Management Studio by clicking on the SSMS shortcut icon which located in the Start --> All Programs --> Microsoft SQL Server 2008 R2 --> SQL Server Management Studio.

See the given exhibit for reference (select any version whichever you installed)

2.       Click on the “Connect” button, which is available at Object Explorer, Choose “Analysis Services…” as shown Below

3.       Enter the server detail/credential you want to connect

4.       After you connected to the with proper server details, you can see the list of databases available on the server in Object Explorer Windows as shown below

5.       In Object Explorer, Select the cube which you need to process --> Right Click on “Process”

6.       Upon click on process, Following model dialog window will appear  --> Click on Script link

7.       Script link helps you create a script in 3 ways as listed in the exhibit

8.       I’ve selected the option to generate the output script on “New Query Window”, so it generated the script on New XMLA Script window as mentioned below:

Sample XMLA Script:-

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

  <Parallel>

    <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200">

      <Object>

        <DatabaseID>Analysis Services Tutorial</DatabaseID>

      </Object>

      <Type>ProcessFull</Type>

      <WriteBackTableCreation>UseExisting</WriteBackTableCreation>

    </Process>

  </Parallel> 
</Batch>

Note: system will generate the script as per the input you provided on Process window in step 6

Turn Off “Saving Auto Recovery information” in Microsoft SQL Server Management Studio

Today I faced some problem, when I’m writing some query in SQL Server Management studio (SSMS) 2008. The problem is SSMS window is getting hang or stuck some seconds due to “Saving Auto Recovery information”. This is happened some interval of time. Then I tried to turn-off this option, but I can’t find out any option to turn-off.

I found somewhere, we can control it thru Registry, This is not good idea to edit the registry directly, but i couldn't found anyother solutions...

The following are the steps to Turn-off this “Saving Auto Recovery information” option

• Select Start, Goto Run
• Type RegEdit, Enter
• Go to this path

HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\100\Tools\Shell\General\AutoRecover

Here you can find 3 fields as shown in the figure.

Double click on the “Auto Recover Enabled”, you can see the Edit window with current value “1”, change this value to “0” then press ok. If you want, you can Turn on this option by setting value to “1” on the same path.