The SQL Server Management studio contains many pop-up utilities and tools that help in improving the productivity of our day-to-day database administration and maintenance tasks. While most of these would be implemented using T-SQL code in production, these tools & utilities are a great way to get started. Practice using these tools in your development environments, and then see if they suit your needs for production use.

Copying/Moving a database from one SQL Server Instance to another

There are multiple ways to achieve this goal:

1. Detach/Attach
2. Backup/Restore
3. Copy database wizard
4. Script database wizard

The Detach/Attach and Backup/Restore are fairly common methods, and there is sufficient documentation in Books On Line (hyperlinks embedded above) to get started. However, I rarely see a database administrator using the Copy Database Wizard or the Script Database Wizard. So, let’s take a look at these two methods.

Copy Database Wizard

The Copy Database wizard is nothing but a customized version of the Attach/Detach method and Integration services under the hood. Perhaps the only hesitation from an administration perspective is that this mechanism requires the SQL Server Agent to be running on the destination server. If you are not a system administrator, you must specify a SQL Server Agent Proxy account that has access to the Integration Services (SSIS) Package execution subsystem.

NOTE: This method is not available for moving system databases. That has to be done via the manual procedures documented on MSDN here.

Here are the brief steps involved in the copying of a database using the copy database wizard.

Right-click the database to copy/move >> Tasks >> Copy Database

Welcome page

Connect to the source & destination instances. Then select the transfer method. You have a choice between the following:

1. Detach & Attach method – faster, requires source database to go offline
2. SMO method – slower, source database remains online

For this demo, I will use the faster, detach-attach method.

Choose which databases are to be copied and/or moved.

Supply details of the destination database.

Choose which objects are to be copied/moved to the new database on the destination instance

Configure the SSIS package that would be used to transfer these objects.

Choose whether or not to run the transfer immediately

Point of no return: Verify the selected options

Monitor the progress. At the end of the process, the database has been copied/moved.

Script Database Wizard – Script database with data in it!

The above method is great when you can actually connect to both source and destination instances. But what do you do when the destination instance is a remote instance which you cannot connect to? The answer: Generate scripts!

But, generating scripts by default never scripts the associated data, which may be the real point of interest. We need to set a small option to ensure that associated data is scripted.

Right-click the database to copy/move >> Tasks >> Generate scripts

Welcome screen

Choose whether you need to script all the objects within the database, or script only one.

For demonstration purposes, we will only script one object (keep the check-box unchecked).

Choose Script Options: Change the scripting options as necessary by your database coding and deployment standards.

IMPORTANT STEP: Change the “Script data” option from False to True.

Choose the object types to script

Based on the options selected above, the wizard would ask you to choose the schemas, stored procedures and other objects to script.

Choose destination for the scripting process. You can:

1. Script to File
2. Script to clipboard
3. Script to New Query Editor window (default)

The required objects are now scripted. Notice the INSERT statements for the pre-existing data.

You can now use this script to deploy this database on any SQL Server instance for which the script has been generated.

Multi-Sever queries – assisting the discovery process

Whenever I take charge of a server or a couple of servers, I like to know about the databases on the server and about the existing configuration. Not only that, I like to prepare an inventory of the details found, so that I can maintain a change log of whatever changes and tweaks I make going forward. Most of you from the audience would also have such pre-developed scripts that you would be using during the discovery process.

However when taking charge of an entire set of servers, running these scripts against each of these servers is a very time consuming (and depending upon whether you multi-task or not - possibly erroneous) process. SSMS allows us to run a single query against all “registered server” instance from a single window using multi-server query support. You can read more about this feature here: http://beyondrelational.com/blogs/nakul/archive/2011/02/04/multi-server-queries-underappreciated-features-of-microsoft-sql-server.aspx

Practice Exercises…

1. How can you upgrade your SQL Server 2000 database to a SQL Server 2008 database with the copy database wizard?
2. Adjust the options in the script database wizard to generate a database deployment script for your database that confirms to your organization’s standards

Further reading/Other tools & utilities…

1. Multi-Server Queries – Underappreciated features of Microsoft SQL Server
2. Activity Monitor – Underappreciated features of SQL Server
3. Blocked Process Report & Event Class - Underappreciated Features of SQL Server
4. Generate SQL Server Scripts Wizard F1 Help