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:
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.
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.
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
- How can you upgrade your SQL Server 2000 database to a SQL Server 2008 database with the copy database wizard?
- 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…
- Multi-Server Queries – Underappreciated features of Microsoft SQL Server
- Activity Monitor – Underappreciated features of SQL Server
- Blocked Process Report & Event Class – Underappreciated Features of SQL Server
- Generate SQL Server Scripts Wizard F1 Help
Until we meet next time,