Differential Database Backups for SQL Server

 

ProblemI know the 'typical' backups are full database backups executed every day.  I have looked into transaction log backups and I am not sure if that is something I can support.  I have seen some information on differential backups, but I am not sure exactly how they work, how they are different and how they could help me?  Could you please explain some of the considerations with differential backups to determine if they would be beneficial to me and my situation? 
SolutionDepending on the frequency of data changes in your SQL Server database(s) and the amount of time\data that your business is willing to lose, should dictate your overall backup strategy.  If a days worth of data is acceptable to lose, then using the full backup strategy could be a viable one, if not, then you need to start looking into differential and transaction log backups.  Regardless of the situation, make sure you understand the business needs and make sure your backup and recovery solution will meet the needs.  Unfortunately, Murphy's law will kick into place one of these days and having the right solution in place can make all of the difference in the world for you and your organization.
Should I consider differential backups in my overall backup strategy?
As far as the differential backups are concerned, they are a viable backup option and may or may not be helpful in a number of situations (see the advantages and disadvantages section below).  Understanding how the differential backups work and how the recovery process would work is vital when you need to restore your databases in short order and get your SQL Server back up and running. 
How do the differential backups work from a backup perspective?

• The catalyst in the differential backup process is issuing a full database backup. 
• Then the differential backups can be issued at a regular interval depending on your needs. 
  • For example, every 2 hours from 7:00 AM to 7:00 PM or at 7:00 AM, 12:00 PM and 7:00 PM.  The schedule is up to you, but keep this in mind during the restoration process outlined in the next section.
• If data changes on any one of the pages in an extent, a flag is set at the extent level to indicate that the extent must be backed up.

What is sample syntax for a differential database backup?

SQL Server 2005

BACKUP DATABASE AdventureWorks TO DISK = 'C:\Temp\DatabaseBackups\AdventureWorks_Full.bak' GO BACKUP DATABASE AdventureWorks TO DISK = 'C:\Temp\DatabaseBackups\AdventureWorks_Diff_1.bak' WITH DIFFERENTIAL GO BACKUP DATABASE AdventureWorks TO DISK = 'C:\Temp\DatabaseBackups\AdventureWorks_Diff_2.bak' WITH DIFFERENTIAL GO

SQL Server 2000

BACKUP DATABASE Northwind TO DISK = 'C:\Temp\DatabaseBackups\Northwind_Full.bak' GO BACKUP DATABASE Northwind TO DISK = 'C:\Temp\DatabaseBackups\Northwind_Diff_1.bak' WITH DIFFERENTIAL GO BACKUP DATABASE Northwind TO DISK = 'C:\Temp\DatabaseBackups\Northwind_Diff_2.bak' WITH DIFFERENTIAL GO

How do the differential backups work from a restore perspective?
Depending on your backup schedule and the time when the failure occurred would dictate the detailed steps that would need to be taken.  If you need to have your current SQL Server databases back up and running, then the full backup would be restored followed by the last differential and then transaction logs if you are issuing them. 
If you are trying to keep 2 SQL Servers in sync via differential backups, then at a high level, the full backup would need to be restored followed by the differential backups.

• To take this down a notch, the full backup would need to be restore using the WITH NORECOVERY option.
• The differential backups would also need to be restored using the WITH NORECOVERY option with the exception of the last differential backup (if no transaction log backups need to be restored) which would use the WITH RECOVERY option.
• Example code is shown in the section below.

What is the sample syntax for a differential database restore?

SQL Server 2005

RESTORE DATABASE AdventureWorks FROM DISK = 'C:\Temp\DatabaseBackups\AdventureWorks_Full.bak' WITH NORECOVERY GO RESTORE DATABASE AdventureWorks FROM DISK = 'C:\Temp\DatabaseBackups\AdventureWorks_Diff_1.bak' WITH NORECOVERY GO RESTORE DATABASE AdventureWorks FROM DISK = 'C:\Temp\DatabaseBackups\AdventureWorks_Diff_2.bak' WITH RECOVERY GO

SQL Server 2000

RESTORE DATABASE Northwind FROM DISK = 'C:\Temp\DatabaseBackups\Northwind_Full.bak' WITH NORECOVERY GO RESTORE DATABASE Northwind FROM DISK = 'C:\Temp\DatabaseBackups\Northwind_Diff_1.bak' WITH NORECOVERY GO RESTORE DATABASE Northwind FROM DISK = 'C:\Temp\DatabaseBackups\Northwind_Diff_2.bak' WITH RECOVERY GO

What are the advantages and disadvantages for using differential database backups and restores?

• Advantages
  • May need less time for the backup if a small percentage of extents are marked to be backed up.
  • May use less storage for the backups if a small percentage of extents are marked to be backed up.
  • May be able to keep more backups on disk as compared to full backups because the differential backups are much smaller.
  • May use less bandwidth when moving backup files around the network because the files are smaller as compared to the full backups.
• Disadvantages
  • Depending on the time interval between the last differential backup and the failure, data loss may occur.  If the amount of data loss is unacceptable, then transaction log backups are needed.
    • Point in time recovery
  • The restore time may not be any faster if the most recent full backup and the last differential backup must be restored either from local disk or tape as compared to issuing full backups on a daily basis and restoring the last full backup.
  • If data is changed across a high percentage of extents, the differential backups may be as large as the full backups.  This means that the time and disk savings for the backups is minimal and the restore time is still as long as the full restore.
  • May need more storage from a recovery perspective because the last full backup and the differential backup would be needed on disk for the fastest possible restore with the native tools.
  • The backups on disk or tape are not encrypted with the native tools.
  • May not be able to use the differential backups as a portion of an automated backup and recovery process to update development, test, pre-production or training environments.
    • COPY_ONLY Backups with SQL Server 2005
  • A third party solution that compresses and encrypts the backups may be the best source for storage savings for your backups and time savings for your restore process.
    • SQL Server 2005 Backup Product Options

Next Steps

• Determine if differential backups would be time and disk savers based on the data changes in your environment.
• Determine if the frequency of the differential backups would be acceptable or if moving to transaction log backups is needed.
• Determine if the need to restore a number of backups would be too time consuming and leveraging a third party product would be a better solution.
• Conduct some testing in your environment and see if differential backups would be beneficial for particular databases by understanding how the data changes then determine the potential time and disk savings.
• Check out the MSSQLTips.com Backup and Recovery tips.
• Stay tuned for tips covering the new partial differential backups and file differential backups.

 

Getting started with SQL Server Management Objects (SMO)

 

ProblemSQL Server 2005 and 2008 provide SQL Server Management Objects (SMO), a collection of namespaces which in turn contain different classes, interfaces, delegates and enumerations, to programmatically work with and manage a SQL Server instance. SMO extends and supersedes SQL Server Distributed Management Objects (SQL-DMO) which was used for SQL Server 2000. In this tip, I am going to discuss how you can get started with SMO and how you can programmatically manage a SQL Server instance with your choice of programming language.

SolutionAlthough SQL Server Management Studio (SSMS) is a great tool to manage a SQL Server instance there might be a need to manage your SQL Server instance programmatically.
For example, consider you are developing a build deployment tool, this tool will deploy the build but before that it needs to make sure that the SQL Server and SQL Server Agent services are running, a database is available and online. For this kind of work, you can use SMO, a SQL Server API object model.
The SMO object model represents SQL Server as a hierarchy of objects. On top of this hierarchy is the Server object, beneath it resides all the instance classes.
SMO classes can be categorized into two categories:

• Instance classes - SQL Server objects are represented by instance classes. It forms a hierarchy that resembles the database server object hierarchy. On top of this hierarchy is Server and under this there is a hierarchy of instance objects that include: databases, tables, columns, triggers, indexes, user-defined functions, stored procedures etc. I am going to demonstrate the usage of a few instance classes in this tip in the example section below.
• Utility classes - Utility classes are independent of the SQL Server instance and perform specific tasks. These classes have been grouped on the basis of its functionalities. For example Database scripting operations, Backup and restore databases, Transfer schema and data to another database etc. I will discussing the utility classes in my next tip.

How SMO is different from SQL-DMO
SMO object model is based on managed code and implemented as .NET Framework assemblies. It provides several benefits over traditional SQL-DMO along with support for new features introduced with SQL Server 2005 and SQL Server 2008.
For example

• It offers improved performance by loading an object only when it is referenced, even the objects properties are loaded partially on object creation and left over objects are loaded only when they are directly referenced.
• It groups the T-SQL statements into batches to improve network performance.
• It now supports several new features like table and index partitioning, Service Broker, DDL triggers, Snapshot Isolation and row versioning, Policy-based management etc.

An exhaustive list of the comparisons between SQL-DMO and SMO can be found here.
Example
Before you start writing your code using SMO, you need to take reference of several assemblies which contain different namespaces to work with SMO. To add a reference of these assemblies, go to Solution Browser - > References -> Add Reference. Add these commonly used assemblies. 

• Microsoft.SqlServer.ConnectionInfo.dll
• Microsoft.SqlServer.Smo.dll
• Microsoft.SqlServer.SmoEnum.dll
• Microsoft.SqlServer.SqlEnum.dll
• Microsoft.SqlServer.Management.Sdk.Sfc.dll // on SQL Server/VS 2008 only

 

There are a couple of other assemblies which contain namespaces for certain tasks, but few of them are essential to work with SMO. Some of the frequently used namespaces and their purposes are summarized in the below table, other namespaces are used for specific tasks like working with SQL Server Agent where you would reference Microsoft.SqlServer.Management.Smo.Agent etc.

Namespaces	Purpose
Microsoft.SqlServer.Management.Common	It contains the classes which you will require to make a connection to a SQL Server instance and execute Transact-SQL statements directly.
Microsoft.SqlServer.Management.Smo	This is the basic namespace which you will need in all SMO applications, it provides classes for core SMO functionalities. It contains utility classes, instance classes, enumerations, event-handler types, and different exception types.
Microsoft.SqlServer.Management.Smo.Agent	It provides the classes to manage the SQL Server Agent, for example to manage Job, Alerts etc.
Microsoft.SqlServer.Management.Smo.Broker	It provides classes to manage Service Broker components using SMO.
Microsoft.SqlServer.Management.Smo.Wmi	It provides classes that represent the SQL Server Windows Management Instrumentation (WMI). With these classes you can start, stop and pause the services of SQL Server, change the protocols and network libraries etc.

C# Code Block 1Here I am using the Server instance object to connect to a SQL Server. You can specify authentication mode by setting the LoginSecure property. If you set it to "true", windows authentication will be used or if you set it to "false" SQL Server authentication will be used.

With Login and Password properties you can specify the SQL Server login name and password to be used when connecting to a SQL Server instance when using SQL Server authentication.

C# Code Block 1 - Connecting to server

Server myServer = new Server(@"ARSHADALI\SQL2008"); //Using windows authenticationmyServer.ConnectionContext.LoginSecure = true; myServer.ConnectionContext.Connect(); //// //Do your work //// if (myServer.ConnectionContext.IsOpen) myServer.ConnectionContext.Disconnect(); //Using SQL Server authenticationmyServer.ConnectionContext.LoginSecure = false; myServer.ConnectionContext.Login = "SQLLogin"; myServer.ConnectionContext.Password = "entry@2008";

C# Code Block 2Once a connection has been established to the server, I am enumerating through the database collection to list all the database on the connected server. Then I am using another instance class Database which represents the AdventureWorks database. Next I am enumerating through the table, stored procedure and user-defined function collections of this database instance to list all these objects. Finally I am using the Table instance class which represents the Employee table in the AdventureWorks database to enumerate and list all properties and corresponding values.

C# Code Block 2 - retrieving databases, tables, SPs, UDFs and Properties

//List down all the databases on the server foreach (Database myDatabase in myServer.Databases) { Console.WriteLine(myDatabase.Name); } Database myAdventureWorks = myServer.Databases["AdventureWorks"]; //List down all the tables of AdventureWorks foreach (Table myTable in myAdventureWorks.Tables) { Console.WriteLine(myTable.Name); } //List down all the stored procedures of AdventureWorksforeach (StoredProcedure myStoredProcedure in myAdventureWorks.StoredProcedures) { Console.WriteLine(myStoredProcedure.Name); } //List down all the user-defined function of AdventureWorks foreach (UserDefinedFunction myUserDefinedFunction in myAdventureWorks.UserDefinedFunctions) { Console.WriteLine(myUserDefinedFunction.Name); } //List down all the properties and its values of [HumanResources].[Employee] tableforeach (Property myTableProperty in myServer.Databases["AdventureWorks"].Tables["Employee", "HumanResources"].Properties) { Console.WriteLine(myTableProperty.Name + " : " + myTableProperty.Value); }

C# Code Block 3This demonstrates the usage of SMO to perform DDL operations.
First I am checking the existence of a database, if it exists dropping it and then creating it.
Next I am creating a Table instance object, then creating Column instance objects and adding it to the created Table object. With each Column object I am setting some property values.
Finally I am creating an Index instance object to create a primary key on the table and at the end I am calling the create method on the Table object to create the table.

C# Code Block 3 - Creating a database and table

//Drop the database if it existsif(myServer.Databases["MyNewDatabase"] != null) myServer.Databases["MyNewDatabase"].Drop(); //Create database called, "MyNewDatabase"Database myDatabase = new Database(myServer, "MyNewDatabase"); myDatabase.Create(); //Create a table instanceTable myEmpTable = new Table(myDatabase, "MyEmpTable"); //Add [EmpID] column to created table instanceColumn empID = new Column(myEmpTable, "EmpID", DataType.Int); empID.Identity = true; myEmpTable.Columns.Add(empID); //Add another column [EmpName] to created table instanceColumn empName = new Column(myEmpTable, "EmpName", DataType.VarChar(200)); empName.Nullable = true; myEmpTable.Columns.Add(empName); //Add third column [DOJ] to created table instance with default constraintColumn DOJ = new Column(myEmpTable, "DOJ", DataType.DateTime); DOJ.AddDefaultConstraint(); // you can specify constraint name here as well DOJ.DefaultConstraint.Text = "GETDATE()"; myEmpTable.Columns.Add(DOJ); // Add primary key index to the tableIndex primaryKeyIndex = new Index(myEmpTable, "PK_MyEmpTable"); primaryKeyIndex.IndexKeyType = IndexKeyType.DriPrimaryKey; primaryKeyIndex.IndexedColumns.Add(new IndexedColumn(primaryKeyIndex, "EmpID")); myEmpTable.Indexes.Add(primaryKeyIndex); //Unless you call create method, table will not created on the server myEmpTable.Create(); Result:

The complete code listing (created using SQL Server 2008 and Visual Studio 2008, although there is not much difference if you are using SQL Server 2005 and Visual Studio 2005) can be found in the below text box.

 

 

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Management.Smo;
namespace LearnSMO2008
{
    class Program
    {
        static void Main(string[] args)
        {
            Server myServer = new Server(@"ARSHADALI\SQL2008");
            try
            {
                //Using windows authentication
                myServer.ConnectionContext.LoginSecure = true;
                //Using SQL Server authentication
                //myServer.ConnectionContext.LoginSecure = false;
                //myServer.ConnectionContext.Login = "SQLLogin";
                //myServer.ConnectionContext.Password = "entry@2008";
                myServer.ConnectionContext.Connect();
                //AccessingSQLServer(myServer);
                DatabaseObjectCreation(myServer);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (myServer.ConnectionContext.IsOpen)
                    myServer.ConnectionContext.Disconnect();
                Console.ReadKey();
            }
           
        }
        private static void AccessingSQLServer(Server myServer)
        {
            //List down all the databases on the server
            foreach (Database myDatabase in myServer.Databases)
            {
                Console.WriteLine(myDatabase.Name);
            }
            Database myAdventureWorks = myServer.Databases["AdventureWorks"];
            //List down all the tables of AdventureWorks
            foreach (Table myTable in myAdventureWorks.Tables)
            {
                Console.WriteLine(myTable.Name);
            }
            //List down all the stored procedures of AdventureWorks
            foreach (StoredProcedure myStoredProcedure in myAdventureWorks.StoredProcedures)
            {
                Console.WriteLine(myStoredProcedure.Name);
            }
            //List down all the user-defined function of AdventureWorks
            foreach (UserDefinedFunction myUserDefinedFunction in myAdventureWorks.UserDefinedFunctions)
            {
                Console.WriteLine(myUserDefinedFunction.Name);
            }
            //List down all the properties and its values of [HumanResources].[Employee] table
            foreach (Property myTableProperty in myServer.Databases["AdventureWorks"].Tables["Employee",
                "HumanResources"].Properties)
            {
                Console.WriteLine(myTableProperty.Name + " : " + myTableProperty.Value);
            }
        }
        private static void DatabaseObjectCreation(Server myServer)
        {
            //Drop the database if it exists
            if(myServer.Databases["MyNewDatabase"] != null)
                myServer.Databases["MyNewDatabase"].Drop();
            //Create database called, "MyNewDatabase"
            Database myDatabase = new Database(myServer, "MyNewDatabase");
            myDatabase.Create();
            //Create a table instance
            Table myEmpTable = new Table(myDatabase, "MyEmpTable");
            //Add [EmpID] column to created table instance
            Column empID = new Column(myEmpTable, "EmpID", DataType.Int);
            empID.Identity = true;
            myEmpTable.Columns.Add(empID);
            //Add another column [EmpName] to created table instance
            Column empName = new Column(myEmpTable, "EmpName", DataType.VarChar(200));
            empName.Nullable = true;
            myEmpTable.Columns.Add(empName);
            //Add third column [DOJ] to created table instance with default constraint
            Column DOJ = new Column(myEmpTable, "DOJ", DataType.DateTime);
            DOJ.AddDefaultConstraint(); // you can specify constraint name here as well
            DOJ.DefaultConstraint.Text = "GETDATE()";
            myEmpTable.Columns.Add(DOJ);
            // Add primary key index to the table
            Index primaryKeyIndex = new Index(myEmpTable, "PK_MyEmpTable");
            primaryKeyIndex.IndexKeyType = IndexKeyType.DriPrimaryKey;
            primaryKeyIndex.IndexedColumns.Add(new IndexedColumn(primaryKeyIndex, "EmpID"));
            myEmpTable.Indexes.Add(primaryKeyIndex);
            //Unless you call create method, table will not created on the server           
            myEmpTable.Create();
        }

    }
}

Notes:

• If you have an application written in SQL-DMO and want to upgrade it to SMO, that is not possible, you will need to rewrite your applications using SMO classes.
• SMO assemblies are installed automatically when you install Client Tools.
• Location of assemblies in SQL Server 2005 is C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies folder.
• Location of assemblies in SQL Server 2008 is C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies folder.
• SMO provides support for SQL Server 2000 (if you are using SQL Server 2005 SMO it supports SQL Server 7.0 as well) but a few namespaces and classes are not supported in prior versions.

Get script for every action in SQL Server Management Studio

 

Problem

I am always conscious to keep a record of all operations performed on my database servers. Operations through T-SQL in an SSMS query pane can easily be saved in query files. For table modifications through SSMS designer I have predefined setting to generate T-SQL scripts. However there are numerous database and server level tasks that I use the SSMS GUI and I would like to have a script of these changes for later reference. Examples of such actions through the SSMS GUI are backup/restore, changing compatibility level of a database, manipulating permissions, dealing with database or log files or creating/manipulating any login/user. I am looking for any way to generate T-SQL code for such actions, so that it may be kept for later reference. Also I would like to be able to reuse this T-SQL code for database tasks or scheduled jobs if needed.

Solution

SQL Server Management Studio (SSMS) provides a very good option to generate scripts for any operation performed through the GUI. It is an effective way to save the T-SQL code of actions performed through SSMS. Here is list of some of the tasks categories for which you may generate T-SQL scripts from SSMS GUI actions

• Changing any server instance level option
• Changing any database level option
• Managing server roles, logins, permissions
• Managing database roles, users, permissions
• Backup/Restore operations
• Managing policies (SQL Server 2008)

The above mentioned categories cover almost all operations that you may require to have script for. Now here are the simple steps to use this powerful option of SSMS.

• Open SSMS GUI for any required task
• Configure values in the GUI window
• Before clicking OK, find the Script option in the upper top of the GUI frame as shown below

• Click on the down arrow pointer and four options will be displayed to manipulate the action script

• Choose the appropriate option and click OK to complete the required task

The options shown are pretty self explanatory. You can directly open the script in a SSMS query window, directly save the script to a .SQL file or put the script in the Windows clipboard to paste where required. The last option is related to scheduled jobs and it will not be enabled in SSMS SQL Server Express edition.

---

Here are some scenarios to use this simple yet powerful option of SSMS. You may go through any of these examples to get familiar with the functionality.
Example 1: Enable filestream "Transact-SQL access enabled" option and open script for this action in a new SSMS query pane

• Right click on SQL Server 2008 instance in SSMS and select Properties
• Click on Advanced option in left panel
• Select 'Transact-SQL access enabled" for the Filestream Access Level option
• Before clicking OK to save the setting, click on arrow pointer next to the Script option

• Select first option "Script Action to New Query Window"
• The script is now in a new SSMS query pane and you can click the OK button to complete the action or Cancel to just have the script.

It is notable that instead of choosing the option from the drop list through small arrow, if you click directly on the Script option the script will be created in a new query Window, because this is the default option.

---

Example 2: Create a new database through SSMS and save the script for this action in .SQL file

• Right click on Databases folder
• Choose to "New Database.." from menu
• Enter name for new database and configure any other required options
• Before clicking OK to save the setting, click on arrow pointer provided with Script option

• Select second option "Script Action to File"
• Save the script file by providing a name in the file save dialogue and you may click OK button to create the database or Cancel to just have the script.

---

Example 3: Disable a Login through SSMS and copy the script for this operation to clipboard

• Right click on a login in Security folder in SSMS and select Properties
• Click on Status option in left pane
• Check the "Disabled" radio button
• Before clicking OK to save the action, click on arrow pointer provided with Script option

• Select third option "Script Action to Clipboard"
• Click OK button to disable the particular login or Cancel to just have the script.

Now you can paste the script as needed to confirm the operation.

---

Example 4: Create database backup through SSMS (non express edition) and directly create a schedule job for this action

• Right click on appropriate database in SSMS
• Go to Tasks and click on Back Up option
• Provide backup name and path along with other required customized options
• Before clicking OK to save the action, click on arrow pointer provided with Script option

• Select the fourth option "Script Action to Job"

A job configuration window will open with the create backup script already present in the job step. Note: this option will not be enabled in SSMS SQL Server Express edition.

---

Shortcuts
Instead of using the options through the menu in upper part of the GUI frame, we can also use shortcuts for any of the four options. These are noted below:

 

SQL Server Management Studio customized startup options

 

ProblemSQL Server Management Studio (SSMS) is now the primary tool that we all use to manage SQL Server.  Whenever I open up SSMS I always go through the same steps to connect to a server and open certain query files.  Are there any shortcuts or alternative ways of starting SSMS?
Solution
SQLWB (sqlwb.exe) is the executable file that launches SQL Server Management Studio (SSMS). Most-likely the name corresponded to the original working name for Management Studio during the development phase of Yukon (the project title for what would eventually become SQL Server 2005): SQL Server Workbench. What many SQL Server professionals fail to realize is that the startup behavior of SSMS is customizable.  By simply passing parameter values along with the command to launch sqlwb, you can open default queries, projects, or connections.  You can also control whether to launch SSMS with the application's splash screen.  Let's take a look at the list of parameters available, courtesy of SQL Server Books Online:

sqlwb [scriptfile] [projectfile] [solutionfile] [-S servername] [-d databasename] [-U username] [-P password] [-E use Windows security] [-nosplash] [-?]

Arguments
The [scriptfile], [projectfile], and [solutionfile] parameters specify a file (or in the case of [scriptfile], the possibility of multiple files to open upon launch of SSMS.  If  you do not specify parameter values for servername, databasename, username, or password when you launch sqlwb and specify a script(s), project, or solution you will be prompted for the applicable security context for the file(s) you are opening.
Let's look at some examples of the behavior associated with the various options for launching SQL Server Management Studio from a Run command or Command Prompt:
Open a single script (.sql) file upon SSMS startup

sqlwb "C:\Temp\Config1.sql"

This command launches SQL Server Management Studio and prompts you for the connection information.  Note the query name in the background of this screen shot.

Open multiple sql query (.sql) files upon SSMS startup
The process for opening multiple SQL query files is only slightly different, simply list each of the full file paths for each query file you wish to open, separated by a space, after the call to sqlwb as shown in this screen image:

sqlwb "C:\Temp\Config1.sql" "C:\Temp\Config2.sql"

Without specifying the connection information in the run command for sqlwb you will be prompted for the SQL instance and security information upon launch of SSMS.  Note that once authenticated each of the queries connect using that same criteria.  This bears repeating: each .sql file will connect to the instance you specify, as the login you specify, when launching SSMS in this manner.

Open a SQL Server Management Studio Project (.ssmssqlproj) file upon SSMS startup
Microsoft provided an additional layer of project management with the release of SQL Server 2005.  The concept of a solution and project was nothing new to the developers out there.  This concept has been a component of the Visual Studio architecture for many previous releases.  However, in the continuing streamlining between SQL Server and Visual Studio interfaces, the concept finally was incorporated into the SQL Server management tools.  Simply put, a SQL Server Project file is a collection of various connections,  queries, and other objects that are organized to be utilized for a common purpose.  I personally use SSMS Projects for such purposes as Standard Installations, Daily Maintenance Checks, and Security Audits.  Just as SSMS Projects are a collection of multiple components, SSMS Solutions are a collection of multiple SSMS Projects.  The syntax for launching an SSMS Project or Solution is no different than launching a single .sql file from the command line or Run menu. 
Let's look at the following example; we will specify an existing .ssmssqlproj file, connecting with Integrated (Windows) security to the Sauron.Northwind database.

sqlwb "C:\Config\Config.ssmssproj" -S sauron -d Northwind -E

Additionally, you can open a SSMS Solution by providing a solution file path (...\*.ssmssln) as a parameter.  The following command opens the solution file Config.ssmssln, passing the connection information for the Foo login against the sauron instance of SQL Server:

sqlwb "C:\Temp\SSMS Projects\Configuration\Configuration.ssmssln"-S sauron -d Northwind -U Foo -P pwFoo

Additional Parameters
Here are some additional options.

• Launches SQL Server Management Studio without the splash screen

sqlwb -nosplash

• At any time you are unsure about the parameters available for sqlwb, you can append the -? parameter to display the following help information.

sqlwb -?

 Don't feel that you're forced into launching SQL Server Management Studio with the default splash screen and an initial instance connection.  The parameters associated with sqlwb.exe allow you to specifically control your startup parameters for files and connections.

 

SSMS keyboard shortcuts

 

Problem

As DBA and Developer responsibilities grow on a daily basis, we how can we improve our productivity? Due to the time it takes to use the toolbar, menu bar or mouse, do I have any other options? Are there keyboard shortcut for SQL Server Management Studio? Could these help me improve my productivity? Check out this tip to learn more.

Solution

We often overlook different SSMS shortcut keys which provide a boost in DBA and Developer productivity. In the second tip of this series (SQL Server Management Studio keyboard shortcuts - Part 1), I am going to further explain shortcut keys for managing Intellisence, debugging, running your code and many more. Let's jump right in.

---

SQL Server Management Studio Intellisense

SQL Server 2008 and later versions include IntelliSense which let's you know about objects in the database, supports T-SQL syntax, gives the parameter info of the stored procedures or functions. Although IntelliSense works by default (you can enable or disable it as and when required), there are times when you want to manually list a set of objects or members. This can be accomplished by pressing CTRL+J as shown below:

Stored procedures and functions normally accept parameters, when you want to execute a stored procedure it is good to know the name, data type and number of parameters to pass it for execution. To accomplish this, press CTRL+SHIFT+SPACE after the stored procedure name to view the list parameters as shown below:

To minimize the typing effort, you can use the auto complete feature of IntelliSense. Simply press ALT+RIGHT ARROW and it will complete your word by the best and first matching member list. If it finds more than one item it shows a list as shown below to choose one. You can also press CTRL+SPACE to bring up this list.

When we first connect to the database, list members are queried and stored in the local cache for better performance. Unfortunately, if someone creates additional objects, those new objects will not be listed. When this occurs, you need to refresh the local cache. Refreshing the local cache can be accomplished by pressing the CTRL+SHIFT+R keys or you can also perform this actions using the menu bar (Edit | IntelliSense | Refresh Local Cache) shown below:

---

Execute Scripts in SQL Server Management Studio

Now lets move on and see how to execute scripts using the shortcut keys. If you want to parse all the scripts without executing them in the current query window, simply press CTRL+F5 or you can select some lines of code to be parsed then press CTRL+F5. If you want to execute all the scripts in the current query window simply press F5 or you can select some lines of code to be executed then press F5. After the execution a result pane appears on the bottom section of SSMS. You can press CTRL+R to toggle the result pane as shown below. As you can notice in the image below it has a Results tab which shows the result sets returned after execution of the query and a Messages tab which shows all the messages generated/printed during execution, for example number of records affected etc.

By default the result set is displayed in the grid format, but you can change it to display as text or send the results directly to a file. To change the setting to display results as text, simply press CTRL+T before execution and result would display as shown below next time when you execute your script. To send the results to a file press CTRL+SHIFT+F before script execution. If you want to revert the setting back to return the results in a grid you can press CTRL+D before script execution and next time you will see your results in the grid.

---

Query Execution Plans in SQL Server Management Studio

During query analysis you might need to know the execution plan of a query even before executing it. To find this out, press CTRL+L to display the estimated query execution plan as shown below without actually running the whole query (in fact it runs for top 1 row).

On the other hand, you might need more detailed information and review the query execution plan with the query execution results. To capture this information, press CTRL+M to include the actual execution plan as shown below with the query results. You can even press SHIFT+ALT+S to include the client statistics with the query results.

If for some reason you would like to cancel a long running query, for that hit ALT+BREAK to cancel the current query execution.

---

SQL Server Management Studio Debugging

In SQL Server Management Studio, you can debug scripts. To start debugging from the first line press ALT+F5. To toggle a breakpoint on the line simply traverse to that line and press F9. To delete all the breakpoints from the query window at once, press CTRL+SHIFT+F9. A breakpoint is denoted by a tiny red circle placed on the left side of the query window as shown below:

To manage all the breakpoints in one place, SSMS has Breakpoint window as similar to the Bookmark window. To launch the Breakpoint window press CTRL+ALT+B and you will see one window as shown below:

While debugging you can press F11 to step into the called module or F10 to step over the called module. You can also press F5 to go to the next breakpoint instead of going one statement at a time.
Sometimes when you are working on a long script in the SSMS query window, it might appear small for you, in that case you can hit SHIFT+ALT+ENTER to toggle the query window in full screen mode as you can see below:

FFor help, you can select the object and press ALT+F1 to display the meta information about that object stored in the database as shown below.

If you are looking for help in Books Online you can select the keyword and press F1 to launch BOL with information about that selected keyword if it exists.

---

Accessing common windows in SQL Server Management Studio

Here are the shortcut keys for common SSMS windows:

• F8 - Object Explorer
• CTRL+ALT+T - Template Explorer
• CTRL+ALT+L - Solution Explorer
• F4 - Properties window
• CTRL+ALT+G - Registered Servers explorer

---

Standard shortcut keys

Please note apart from the above mentioned shortcut keys here are some standard shortcut keys which work fine in SSMS:

• CTRL+A - Select all the text in the current query window
• CTRL+C - Copy text in the current query window
• CTRL+V - Paste text in the current query window
• CTRL+X - Cut text in the current query window
• DEL - Delete text in the current query window
• CTRL+P - Launch the print dialog box
• CTRL+HOME - Top of the current query window
• CTRL+END - End of the query window
• CTRL+SHIFT+HOME - Select all the text from the current location to the beginning of the query window
• CTRL+SHIFT+END - Select all the text from the current location to the end of the query window
• ALT+F4 - Close the SSMS application

 

SQL Server Management Studio keyboard shortcuts

SQL Server Management Studio keyboard shortcuts 

 

Problem

As responsibilities are growing every day, a DBA or developer needs to improve his/her productivity. One way to do this is to use as many shortcuts as possible instead of using your mouse and the menus. In this tip we take a look at common tasks you may perform when using SSMS and the associated shortcut keys.

Solution

We often overlook the shortcut keys which SSMS (SQL Server Management Studio) provides for increasing our productivity as a DBA or developer. In this tip series I will talk about some of these shortcut keys to help you use SSMS for more proficiently.

Launching SSMS

When launching SSMS you probably go through START-> All Programs -> SQL Server 20XX and click on SQL Server Management Studio, but this is not the only way.
Another options is to click on START -> Run or press Windows + R, type ssms and click OK (or hit ENTER) which will launch SSMS.
You can also specify different parameters or switches

• The -E switch will let you connect to the local instance using Windows authentication.
• The -U switch is used to specify a user and -P to specify the password
• If you want SSMS to connect to a specific database you can use the -d switch
• If you want a script file to be opened in SSMS you can specify the location and name of the file. This will just open the file in SSMS and will not execute the code. If you need to execute a script file you can use the SQLCMD utility.
• To close SSMS you can use ALT+F4.

As I said before, you can simply open the SSMS or you can specify the -E switch to open SSMS and connect using Windows authentication. If the current user does not have sufficient permissions obviously it will fail.

When we open SSMS a splash screen appears while loading SSMS in the memory. You can specify -nosplash switch which opens SSMS without the splash screen.

If you are scratching your head trying to remember all these switches, you don't need to because you can use -? which gives you the different command options as shown below. You can find more information about this here.

---

Changing Databases

Once you are in a Query Window in SSMS you can use CTRL+U to change the database. When you press this combination, the database combo-box will be selected as shown below. You can then use the UPDOWN arrow keys to change between databases (or type a character to jump to databases starting with that character) select your database and hit ENTER to return back to the Query Window. and

---

Changing Code Case (Upper or Lower)

When you are writing code you may not bother with using upper or lower case to make your code easier to read. To fix this later, you can select the specific text and hit CTRL+SHIFT+U to make it upper case or use CTRL+SHIFT+L to make it lower case as shown below.

---

Commenting Out Code

When writing code sometimes you need to comment out lines of code. You can select specific lines and hit CTRL+K followed by CTRL+C to comment it out and CTRL+K followed by CTRL+U to uncomment it out as shown below.

---

Indenting Code

As a coding best practice you should to indent your code for better readability. To increase the indent, select the lines of code (to be indented) and hit TAB as many times as you want to increase the indent likewise to decrease the indent again select those lines of code and hit SHIFT+TAB.

---

Bookmarking Code

When you have hundreds of lines of code, it becomes difficult to navigate. In this case you can bookmark lines to which you would like to return to. Hit CTRL+K followed by CTRL+K again to toggle the bookmark on the line. When you bookmark a line a tiny light blue colored square appears on the left side of the query window to indicate that the line has been bookmarked as you can see in the image below.
You can press CTRL+K followed by CTRL+N to move to the next bookmarked line from the current cursor location likewise to move back to the last bookmarked line you can hit CTRL+K followed CTRL+P from the current cursor location.
To clear all bookmarks from the current window you can hit CTRL+K followed by CTRL+L.

There might be several bookmarks you have placed in your query window and to manage these easily SSMS provides a Bookmarks window. To launch this window simply hit CTRL+K followed by CTRL+W and you can manage almost every aspect of bookmarking from this window as shown below including renaming the bookmarks.

---

Search and Find / Replace Text

Sometimes you need to find specific keywords or replace some specific keyword with another keyword. To launch the Quick Find dialog box press CTRL+F or CTRL+H for the Quick Replace dialog box. You can even fine tune your search with other options available in the dialog box or you can bookmark all the lines which contain your search string. To close the dialog box press ESC. You can also press F3 to find the next keyword match.

---

Goto Line Number

If you know the line number you want to go to you can use CTRL+G to open the Go To Line dialog box and type the line number and press OK (or hit ENTER) to go to that particular line number.

---

Opening Query Windows and Switching Tabs

Next to open a new query window you can hit CTRL+N or to open a existing script file hit CTRL+O. You can hit CTRL+TAB to switch between open query windows.

---

More Query Window Shortcuts

Please note apart from the above mentioned shortcut keys these are some standard shortcut keys which work in SSMS as well.

• CTRL+A to select all the text in the current query window
• CTRL+C to copy selected text
• CTRL+V to paste text
• CTRL+X to cut selected text
• DEL to delete text
• CTRL+P to launch Print dialog box
• CTRL+HOME to go to the beginning of the query window
• CTRL+END to go to the end of the query window
• CTRL+SHIFT+HOME to select all text from the current location to the beginning of the query window
• CTRL+SHIFT+END to select all the text from the current location to the end of the query window
• ALT+F4 to close SSMS

Dynamic SQL Server stored procedure execution form in SSMS

 

Problem
The purpose for most stored procedures is for execution within applications, but there are some stored procedures that may be used for administrative purposes and only get executed ad hoc.   In addition, during testing you run stored procedures interactively to make sure things are working correctly.  You have the ability to run any stored procedure directly from a query window and include the necessary parameters, but is there any easier way to know what parameters a stored procedure requires and to pass the parameters directly to a stored procedure?
Solution
In SQL Server Management Studio you have the ability to execute a stored procedure directly from the object browser tree.  Just browse to the desired stored procedure and right click and select "Execute Stored Procedure..." as shown below.

When you select "Execute Stored Procedure..." a window such as the following will pop up that will give you the list of parameters to use for the stored procedure.

Enter the value you want to use for the parameter and select "OK" to run the stored procedure.

The stored procedure will execute based on the parameters you pass as well as create sample code such as the following that is used for the execution of the stored procedure.

That's all there is to it.  This is a pretty simple tip, but could save you a lot of time if you need to run a stored procedure and don't want to mess with having to type the commands or if you don't remember the exact parameters that the stored procedure needs.
The only downside to this is that it only works for user defined stored procedures.  System stored procedures do not give you the option to use the "Execute Stored Procedure...".
 Next Steps

• Next time you need to run a stored procedure use this technique as a faster way to execute the code

Reff ..to Posted by ==By: Greg Robidoux

 

Backup and Restore SQL Server databases programmatically with SMO

 

 

ProblemIn my last set of tips, I discussed SMO at a basic level.  In this tip I am going to provide examples to SQL Server Database Administrators on how to backup and restore SQL Server databases with SMO.  I will start with how you can issue different types (Full, Differential and Log) of backups with SMO and how to restore them when required programmatically using SMO.

SolutionAs I discussed in my last tip, SMO provides utility classes for specific tasks. For backup and restore, it provides two main utility classes (Backup and Restore) which are available in Microsoft.SqlServer.Management.Smo namespace.

Before you start writing SMO code, you need to reference several assemblies which contain the SMO namespaces. For more details on these assemblies and how properly to reference them in your code, refer to my tip Getting started with SQL Server Management Objects (SMO). Examples

C# Code Block 1 - Full Backups - This example shows how to issue full database backups with SMO. First, create an instance of the Backup class and set the associated properties. With the Action property you can specify the type of backup such as full, files or log backup. With the Database property specify the name of the database being backed up.  The device is the backup media type such as disk or tape, so you need to add a device (one or more) to the Devices collection of backup instance. With the BackupSetName and BackupSetDescription properties you can specify the name and description for the backup set.  The Backup class also has a property called ExpirationDate which indicates how long backup data is considered valid and to expire the backup after that date. The backup object instance generates several events during the backup operation, we can write event-handlers for these events and wire them up with events. This is what I am doing for progress monitoring.  I am wiring up CompletionStatusInPercent and Backup_Completed methods (event-handlers) with PercentComplete and Complete events of backup object instance.  Finally, I am calling the SqlBackup method for starting up the backup operation, SMO provides a variant of this method called SqlBackupAsync if you want to start the backup operation asynchronously.

C# Code Block 1 - Full Database Backup

Backup bkpDBFull = new Backup(); /* Specify whether you want to back up database or files or log */ bkpDBFull.Action = BackupActionType.Database; /* Specify the name of the database to back up */ bkpDBFull.Database = myDatabase.Name; /* You can take backup on several media type (disk or tape), here I am  * using File type and storing backup on the file system */ bkpDBFull.Devices.AddDevice(@"D:\AdventureWorksFull.bak", DeviceType.File); bkpDBFull.BackupSetName = "Adventureworks database Backup"; bkpDBFull.BackupSetDescription = "Adventureworks database - Full Backup"; /* You can specify the expiration date for your backup data  * after that date backup data would not be relevant */ bkpDBFull.ExpirationDate = DateTime.Today.AddDays(10); /* You can specify Initialize = false (default) to create a new  * backup set which will be appended as last backup set on the media. You  * can specify Initialize = true to make the backup as first set on the  * medium and to overwrite any other existing backup sets if the all the  * backup sets have expired and specified backup set name matches with  * the name on the medium */ bkpDBFull.Initialize = false; /* Wiring up events for progress monitoring */ bkpDBFull.PercentComplete += CompletionStatusInPercent; bkpDBFull.Complete += Backup_Completed; /* SqlBackup method starts to take back up  * You can also use SqlBackupAsync method to perform the backup  * operation asynchronously */ bkpDBFull.SqlBackup(myServer);

private static void CompletionStatusInPercent(object sender, PercentCompleteEventArgs args) {     Console.Clear();     Console.WriteLine("Percent completed: {0}%.", args.Percent); } private static void Backup_Completed(object sender, ServerMessageEventArgs args) {     Console.WriteLine("Hurray...Backup completed." );     Console.WriteLine(args.Error.Message); } private static void Restore_Completed(object sender, ServerMessageEventArgs args) {     Console.WriteLine("Hurray...Restore completed.");     Console.WriteLine(args.Error.Message); }

Result:

C# Code Block 2 Differential Backups - The process of issuing differential backups is not much different from issuing full backups. To issue a differential backup, set the property Incremental = true. If you set this property the incremental/differential backup will be taken since last full backup.

C# Code Block 2 - Differential Database Backup

Backup bkpDBDifferential = new Backup(); /* Specify whether you want to backup database, files or log */ bkpDBDifferential.Action = BackupActionType.Database; /* Specify the name of the database to backup */ bkpDBDifferential.Database = myDatabase.Name; /* You can issue backups on several media types (disk or tape), here I am * using the File type and storing the backup on the file system */ bkpDBDifferential.Devices.AddDevice(@"D:\AdventureWorksDifferential.bak", DeviceType.File); bkpDBDifferential.BackupSetName = "Adventureworks database Backup"; bkpDBDifferential.BackupSetDescription = "Adventureworks database - Differential Backup"; /* You can specify the expiration date for your backup data  * after that date backup data would not be relevant */ bkpDBDifferential.ExpirationDate = DateTime.Today.AddDays(10); /* You can specify Initialize = false (default) to create a new  * backup set which will be appended as last backup set on the media.  * You can specify Initialize = true to make the backup as the first set  * on the medium and to overwrite any other existing backup sets if the  * backup sets have expired and specified backup set name matches  * with the name on the medium */ bkpDBDifferential.Initialize = false; /* You can specify Incremental = false (default) to perform full backup  * or Incremental = true to perform differential backup since most recent  * full backup */ bkpDBDifferential.Incremental = true; /* Wiring up events for progress monitoring */ bkpDBDifferential.PercentComplete += CompletionStatusInPercent; bkpDBDifferential.Complete += Backup_Completed; /* SqlBackup method starts to take back up  * You cab also use SqlBackupAsync method to perform backup  * operation asynchronously */ bkpDBDifferential.SqlBackup(myServer);

Result:

C# Code Block 3 Transaction Log Backups - Again the process of issuing transactional log backup is not much different from issuing full backups. To issue transactional log backups, set the property Action = BackupActionType.Log instead of BackupActionType.Database as in the case of a full backup.

C# Code Block 3 - Transaction Log Backup

Backup bkpDBLog = new Backup(); /* Specify whether you want to back up database or files or log */ bkpDBLog.Action = BackupActionType.Log; /* Specify the name of the database to back up */ bkpDBLog.Database = myDatabase.Name; /* You can take backup on several media type (disk or tape), here I am  * using File type and storing backup on the file system */ bkpDBLog.Devices.AddDevice(@"D:\AdventureWorksLog.bak", DeviceType.File); bkpDBLog.BackupSetName = "Adventureworks database Backup"; bkpDBLog.BackupSetDescription = "Adventureworks database - Log Backup"; /* You can specify the expiration date for your backup data  * after that date backup data would not be relevant */ bkpDBLog.ExpirationDate = DateTime.Today.AddDays(10); /* You can specify Initialize = false (default) to create a new  * backup set which will be appended as last backup set on the media. You  * can specify Initialize = true to make the backup as first set on the  * medium and to overwrite any other existing backup sets if the all the  * backup sets have expired and specified backup set name matches with  * the name on the medium */ bkpDBLog.Initialize = false; /* Wiring up events for progress monitoring */ bkpDBLog.PercentComplete += CompletionStatusInPercent; bkpDBLog.Complete += Backup_Completed; /* SqlBackup method starts to take back up  * You cab also use SqlBackupAsync method to perform backup  * operation asynchronously */ bkpDBLog.SqlBackup(myServer);

Result:

C# Code Block 4 Backup with Compression - SQL Server 2008 introduces a new feature to issues backups in a compressed form.  As such, SMO for SQL Server 2008 has been enhanced to support this feature. If you look at the image below you will notice the compressed backup size is almost 25% of full backup, though the level of compression depends on the several factors.

C# Code Block 4 - Backup with Compression (SQL Server 2008)

Backup bkpDBFullWithCompression = new Backup(); /* Specify whether you want to back up database or files or log */ bkpDBFullWithCompression.Action = BackupActionType.Database; /* Specify the name of the database to back up */ bkpDBFullWithCompression.Database = myDatabase.Name; /* You can use back up compression technique of SQL Server 2008,  * specify CompressionOption property to On for compressed backup */ bkpDBFullWithCompression.CompressionOption = BackupCompressionOptions.On; bkpDBFullWithCompression.Devices.AddDevice(@"D:\AdventureWorksFullWithCompression.bak", DeviceType.File); bkpDBFullWithCompression.BackupSetName = "Adventureworks database Backup - Compressed"; bkpDBFullWithCompression.BackupSetDescription = "Adventureworks database - Full Backup with Compressin - only in SQL Server 2008"; bkpDBFullWithCompression.SqlBackup(myServer);

Result:

C# Code Block 5 Full or Differential Restores - Thus far we have worked through SMO backup examples. Now let's change gears to restore with SMO.  SMO provides a Restore class to restore a database, similar to the Backup class.  With these classes it is necessary to specify the Action property to indicate the type of restore i.e. database, files or log.  In a scenario where if you have additional differential or log backups to be restored after it is necessary to specify the NoRecovery = true except for the final restore.  In this example, I am wiring up events of the Restore object instance to event-handlers for progress monitoring. Finally the SqlRestore method is called to start the restoration. If you want to start the restore operation asynchronously you would need to call SqlRestoreAsync method instead.

C# Code Block 5 - Database Restore - Full or Differential

Restore restoreDB = new Restore(); restoreDB.Database = myDatabase.Name; /* Specify whether you want to restore database, files or log */ restoreDB.Action = RestoreActionType.Database; restoreDB.Devices.AddDevice(@"D:\AdventureWorksFull.bak", DeviceType.File); /* You can specify ReplaceDatabase = false (default) to not create a new  * database, the specified database must exist on SQL Server  * instance. If you can specify ReplaceDatabase = true to create new  * database image regardless of the existence of specified database with  * the same name */ restoreDB.ReplaceDatabase = true; /* If you have a differential or log restore after the current restore,  * you would need to specify NoRecovery = true, this will ensure no  * recovery performed and subsequent restores are allowed. It means it  * the database will be in a restoring state. */ restoreDB.NoRecovery = true; /* Wiring up events for progress monitoring */ restoreDB.PercentComplete += CompletionStatusInPercent; restoreDB.Complete += Restore_Completed; /* SqlRestore method starts to restore the database  * You can also use SqlRestoreAsync method to perform restore  * operation asynchronously */ restoreDB.SqlRestore(myServer);

Result:

To restore a database SQL Server needs to acquire exclusive lock on the database being restored.  If you try to restore a database which is in use, SQL Server will throw the following exception:

C# Code Block 6 Transaction Log Restore - The process of restoring a transactional log is similar to restoring a full or differential backup. While restoring a transactional log, it is necessary to set the property Action = RestoreActionType.Log instead of RestoreActionType.Database as in case of full/differential restore.  Here is an example:

 

C# Code Block 6 - Database Restore - Log

Restore restoreDBLog = new Restore(); restoreDBLog.Database = myDatabase.Name; restoreDBLog.Action = RestoreActionType.Log; restoreDBLog.Devices.AddDevice(@"D:\AdventureWorksLog.bak", DeviceType.File); /* You can specify NoRecovery = false (default) so that transactions are  * rolled forward and recovered. */ restoreDBLog.NoRecovery = false; /* Wiring up events for progress monitoring */ restoreDBLog.PercentComplete += CompletionStatusInPercent; restoreDBLog.Complete += Restore_Completed; /* SqlRestore method starts to restore database  * You cab also use SqlRestoreAsync method to perform restore  * operation asynchronously */ restoreDBLog.SqlRestore(myServer);

Result:

C# Code Block 7 Database Restore to a new location - At times you need to create a new database and restore to a new physical location which differs from the original database. For that purpose, the Restore class has the RelocateFiles collection which can be completed for each file with the new location as shown in the code below.

 

C# Code Block 7 Database Restore - Different location

Restore restoreDB = new Restore(); restoreDB.Database = myDatabase.Name + "New"; /* Specify whether you want to restore database or files or log etc */ restoreDB.Action = RestoreActionType.Database; restoreDB.Devices.AddDevice(@"D:\AdventureWorksFull.bak", DeviceType.File); /* You can specify ReplaceDatabase = false (default) to not create a new  * database, the specified database must exist on SQL Server instance.  * You can specify ReplaceDatabase = true to create new database  * regardless of the existence of specified database */ restoreDB.ReplaceDatabase = true; /* If you have a differential or log restore to be followed, you would  * specify NoRecovery = true, this will ensure no recovery is done  * after the restore and subsequent restores are completed. The database  * would be in a recovered state. */ restoreDB.NoRecovery = false; /* RelocateFiles collection allows you to specify the logical file names  * and physical file names (new locations) if you want to restore to a  * different location.*/ restoreDB.RelocateFiles.Add(new RelocateFile("AdventureWorks_Data", @"D:\AdventureWorksNew_Data.mdf")); restoreDB.RelocateFiles.Add(new RelocateFile("AdventureWorks_Log", @"D:\AdventureWorksNew_Log.ldf")); /* Wiring up events for progress monitoring */ restoreDB.PercentComplete += CompletionStatusInPercent; restoreDB.Complete += Restore_Completed; /* SqlRestore method starts to restore database  * You can also use SqlRestoreAsync method to perform restore  * operation asynchronously */ restoreDB.SqlRestore(myServer);

Result:

Complete code listing (created on SQL Server 2008 and Visual Studio 2008, though there is not much difference if you are using it on SQL Server 2005 and Visual Studio 2005) can be found in the below text box.

using System; using System.Collections.Generic; using System.Text; using Microsoft.SqlServer.Management.Smo; using Microsoft.SqlServer.Management.Common; namespace BackupAndRestoreWithSMO2008 { class Program { static void Main(string[] args) { Server myServer = new Server(@"ARSHADALI-LAP\ARSHADALI"); try { //Using windows authentication myServer.ConnectionContext.LoginSecure = true; myServer.ConnectionContext.Connect(); Database myDatabase = myServer.Databases["AdventureWorks"]; BackupDatabaseFull(myServer, myDatabase); //BackupDatabaseDifferential(myServer, myDatabase); //BackupDatabaseLog(myServer, myDatabase); //BackupDatabaseFullWithCompression(myServer, myDatabase); RestoreDatabase(myServer, myDatabase); //RestoreDatabaseLog(myServer, myDatabase); //RestoreDatabaseWithDifferentNameAndLocation(myServer, myDatabase); } catch (Exception ex) { Console.WriteLine(ex.Message); Console.WriteLine(ex.InnerException.Message); } finally { if (myServer.ConnectionContext.IsOpen) myServer.ConnectionContext.Disconnect(); Console.WriteLine("Press any key to terminate..."); Console.ReadKey(); } } private static void BackupDatabaseFull(Server myServer, Database myDatabase) { Backup bkpDBFull = new Backup(); /* Specify whether you want to back up database or files or log */ bkpDBFull.Action = BackupActionType.Database; /* Specify the name of the database to back up */ bkpDBFull.Database = myDatabase.Name; /* You can take backup on several media type (disk or tape), here I am using * File type and storing backup on the file system */ bkpDBFull.Devices.AddDevice(@"D:\AdventureWorksFull.bak", DeviceType.File); bkpDBFull.BackupSetName = "Adventureworks database Backup"; bkpDBFull.BackupSetDescription = "Adventureworks database - Full Backup"; /* You can specify the expiration date for your backup data * after that date backup data would not be relevant */ bkpDBFull.ExpirationDate = DateTime.Today.AddDays(10); /* You can specify Initialize = false (default) to create a new * backup set which will be appended as last backup set on the media. You can * specify Initialize = true to make the backup as first set on the mediuam and * to overwrite any other existing backup sets if the all the backup sets have * expired and specified backup set name matches with the name on the medium */ bkpDBFull.Initialize = false; /* Wiring up events for progress monitoring */ bkpDBFull.PercentComplete += CompletionStatusInPercent; bkpDBFull.Complete += Backup_Completed; /* SqlBackup method starts to take back up * You cab also use SqlBackupAsync method to perform backup * operation asynchronously */ bkpDBFull.SqlBackup(myServer); } private static void BackupDatabaseDifferential(Server myServer, Database myDatabase) { Backup bkpDBDifferential = new Backup(); /* Specify whether you want to back up database or files or log */ bkpDBDifferential.Action = BackupActionType.Database; /* Specify the name of the database to back up */ bkpDBDifferential.Database = myDatabase.Name; /* You can take backup on several media type (disk or tape), here I am using * File type and storing backup on the file system */ bkpDBDifferential.Devices.AddDevice(@"D:\AdventureWorksDifferential.bak", DeviceType.File); bkpDBDifferential.BackupSetName = "Adventureworks database Backup"; bkpDBDifferential.BackupSetDescription = "Adventureworks database - Differential Backup"; /* You can specify the expiration date for your backup data * after that date backup data would not be relevant */ bkpDBDifferential.ExpirationDate = DateTime.Today.AddDays(10); /* You can specify Initialize = false (default) to create a new * backup set which will be appended as last backup set on the media. You can * specify Initialize = true to make the backup as first set on the mediuam and * to overwrite any other existing backup sets if the all the backup sets have * expired and specified backup set name matches with the name on the medium */ bkpDBDifferential.Initialize = false; /* You can specify Incremental = false (default) to perform full backup * or Incremental = true to perform differential backup since most recent * full backup */ bkpDBDifferential.Incremental = true; /* Wiring up events for progress monitoring */ bkpDBDifferential.PercentComplete += CompletionStatusInPercent; bkpDBDifferential.Complete += Backup_Completed; /* SqlBackup method starts to take back up * You cab also use SqlBackupAsync method to perform backup * operation asynchronously */ bkpDBDifferential.SqlBackup(myServer); } private static void BackupDatabaseLog(Server myServer, Database myDatabase) { Backup bkpDBLog = new Backup(); /* Specify whether you want to back up database or files or log */ bkpDBLog.Action = BackupActionType.Log; /* Specify the name of the database to back up */ bkpDBLog.Database = myDatabase.Name; /* You can take backup on several media type (disk or tape), here I am using * File type and storing backup on the file system */ bkpDBLog.Devices.AddDevice(@"D:\AdventureWorksLog.bak", DeviceType.File); bkpDBLog.BackupSetName = "Adventureworks database Backup"; bkpDBLog.BackupSetDescription = "Adventureworks database - Log Backup"; /* You can specify the expiration date for your backup data * after that date backup data would not be relevant */ bkpDBLog.ExpirationDate = DateTime.Today.AddDays(10); /* You can specify Initialize = false (default) to create a new * backup set which will be appended as last backup set on the media. You can * specify Initialize = true to make the backup as first set on the mediuam and * to overwrite any other existing backup sets if the all the backup sets have * expired and specified backup set name matches with the name on the medium */ bkpDBLog.Initialize = false; /* Wiring up events for progress monitoring */ bkpDBLog.PercentComplete += CompletionStatusInPercent; bkpDBLog.Complete += Backup_Completed; /* SqlBackup method starts to take back up * You cab also use SqlBackupAsync method to perform backup * operation asynchronously */ bkpDBLog.SqlBackup(myServer); } private static void BackupDatabaseFullWithCompression(Server myServer, Database myDatabase) { Backup bkpDBFullWithCompression = new Backup(); /* Specify whether you want to back up database or files or log */ bkpDBFullWithCompression.Action = BackupActionType.Database; /* Specify the name of the database to back up */ bkpDBFullWithCompression.Database = myDatabase.Name; /* You can use back up compression technique of SQL Server 2008, * specify CompressionOption property to On for compressed backup */ bkpDBFullWithCompression.CompressionOption = BackupCompressionOptions.On; bkpDBFullWithCompression.Devices.AddDevice(@"D:\AdventureWorksFullWithCompression.bak", DeviceType.File); bkpDBFullWithCompression.BackupSetName = "Adventureworks database Backup - Compressed"; bkpDBFullWithCompression.BackupSetDescription = "Adventureworks database - Full Backup with Compressin - only in SQL Server 2008"; bkpDBFullWithCompression.SqlBackup(myServer); } private static void CompletionStatusInPercent(object sender, PercentCompleteEventArgs args) { Console.Clear(); Console.WriteLine("Percent completed: {0}%.", args.Percent); } private static void Backup_Completed(object sender, ServerMessageEventArgs args) { Console.WriteLine("Hurray...Backup completed." ); Console.WriteLine(args.Error.Message); } private static void Restore_Completed(object sender, ServerMessageEventArgs args) { Console.WriteLine("Hurray...Restore completed."); Console.WriteLine(args.Error.Message); } private static void RestoreDatabase(Server myServer, Database myDatabase) { Restore restoreDB = new Restore(); restoreDB.Database = myDatabase.Name; /* Specify whether you want to restore database or files or log etc */ restoreDB.Action = RestoreActionType.Database; restoreDB.Devices.AddDevice(@"D:\AdventureWorksFull.bak", DeviceType.File); /* You can specify ReplaceDatabase = false (default) to not create a new image * of the database, the specified database must exist on SQL Server instance. * If you can specify ReplaceDatabase = true to create new database image * regardless of the existence of specified database with same name */ restoreDB.ReplaceDatabase = true; /* If you have differential or log restore to be followed, you would need * to specify NoRecovery = true, this will ensure no recovery is done after the * restore and subsequent restores are allowed. It means it will database * in the Restoring state. */ restoreDB.NoRecovery = true; /* Wiring up events for progress monitoring */ restoreDB.PercentComplete += CompletionStatusInPercent; restoreDB.Complete += Restore_Completed; /* SqlRestore method starts to restore database * You cab also use SqlRestoreAsync method to perform restore * operation asynchronously */ restoreDB.SqlRestore(myServer); } private static void RestoreDatabaseLog(Server myServer, Database myDatabase) { Restore restoreDBLog = new Restore(); restoreDBLog.Database = myDatabase.Name; restoreDBLog.Action = RestoreActionType.Log; restoreDBLog.Devices.AddDevice(@"D:\AdventureWorksLog.bak", DeviceType.File); /* You can specify NoRecovery = false (default) so that transactions are * rolled forward and recovered. */ restoreDBLog.NoRecovery = false; /* Wiring up events for progress monitoring */ restoreDBLog.PercentComplete += CompletionStatusInPercent; restoreDBLog.Complete += Restore_Completed; /* SqlRestore method starts to restore database * You cab also use SqlRestoreAsync method to perform restore * operation asynchronously */ restoreDBLog.SqlRestore(myServer); } private static void RestoreDatabaseWithDifferentNameAndLocation(Server myServer, Database myDatabase) { Restore restoreDB = new Restore(); restoreDB.Database = myDatabase.Name + "New"; /* Specify whether you want to restore database or files or log etc */ restoreDB.Action = RestoreActionType.Database; restoreDB.Devices.AddDevice(@"D:\AdventureWorksFull.bak", DeviceType.File); /* You can specify ReplaceDatabase = false (default) to not create a new image * of the database, the specified database must exist on SQL Server instance. * You can specify ReplaceDatabase = true to create new database image * regardless of the existence of specified database with same name */ restoreDB.ReplaceDatabase = true; /* If you have differential or log restore to be followed, you would need * to specify NoRecovery = true, this will ensure no recovery is done after the * restore and subsequent restores are allowed. It means it will database * in the Restoring state. */ restoreDB.NoRecovery = false; /* RelocateFiles collection allows you to specify the logical file names and * physical file names (new locations) if you want to restore to a different location.*/ restoreDB.RelocateFiles.Add(new RelocateFile("AdventureWorks_Data", @"D:\AdventureWorksNew_Data.mdf")); restoreDB.RelocateFiles.Add(new RelocateFile("AdventureWorks_Log", @"D:\AdventureWorksNew_Log.ldf")); /* Wiring up events for progress monitoring */ restoreDB.PercentComplete += CompletionStatusInPercent; restoreDB.Complete += Restore_Completed; /* SqlRestore method starts to restore database * You cab also use SqlRestoreAsync method to perform restore * operation asynchronously */ restoreDB.SqlRestore(myServer); } } }

Notes:

• Location of assemblies in SQL Server 2005 is the C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies folder.
• Location of assemblies in SQL Server 2008 is the C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies folder.
• In SQL Server 2005, the Backup and Restore classes are available in the Microsoft.SqlServer.Management.Smo namespace and in the Microsoft.SqlServer.Smo (microsoft.sqlserver.smo.dll) assembly.
• In SQL Server 2008, the Backup and Restore classes are available in the Microsoft.SqlServer.Management.Smo namespace and in the Microsoft.SqlServer.SmoExtended (microsoft.sqlserver.smoextended.dll) assembly.
• If you are restoring a transaction log, you can specify a particular point in time with ToPointInTimeRestore class. property of the
• The Restore class methods (SqlVerify, SqlVerifyAsync and SqlVerifyLatest) to verify and validate (backup set is complete and the entire backup is readable) the backup media before restoration.
• The SQL Server service account must have access to the folders where backup or restore operations are executed.
• You need to have sufficient permissions to perform backup and restore operations. For example, for backup you need to be either in sysadmin/db_owner/db_backupoperator role or must have BACKUP DATABASE or BACKUP LOG permission on the database.
• If you try to connect SQL Server 2008 from SMO 2005, you will get an exception "SQL Server <10.0> version is not supported".

Next Steps

• Review the following tips:
  • Getting started with SQL Server Management Objects (SMO)
  • SQL Script Generation Programmatically with SMO
  • Exploring Database Backup Compression in SQL Server 2008
    
    
    
    
    
    
    Reff ...Posted by  By: Arshad Ali