Controlling Databases with SQL Server 2005 Database Engine
(Page 1 of 5 )
In this conclusion to a six-part series on administering SQL Server 2005 database engine, you will learn some advanced ways to manipulate databases. This article is excerpted from chapter one of the book
SQL Server 2005 Management and Administration, written by Chris Amaris, Alec Minty and Ross Mistry (Sams Publishing, 2008; ISBN: 0672329565).
Scripting Database Objects
SQL Server 2005 has two levels of scripting functionality that assist you in automatically transforming a SQL Server task or action to a TSQL script. The scripting functionality is a great way to automate redundant administration responsibilities or settings. Moreover, you don’t have to be a TSQL scripting expert to create solid scripts.
You can generate a script from within a majority of the SQL Server dialog boxes or pages. For example, if you make changes to the SQL Server Processor Properties page, such as enabling the options Boost SQL Server Priority or User Windows Fibers, you can click the Script button at the top of the screen to convert these changes to a script. In addition, this script can be fired on other SQL Servers to make the configuration automatically consistent across similar SQL Servers.
When you click the Script button, the options available are Script Action to New Query Window, Script Action to File, Script Action to Clipboard, and Script Action to Job.
Another alternative to creating scripts is right-clicking a specific folder within Object Explorer and selecting Script As or right-clicking a database, selecting Tasks, and then selecting Generate Script to invoke the Script Wizard. Some of these tasks include scripting database schemas, jobs, tables, stored procedures, and just about any object within SQL Server Management Studio. Additional scripting statements include Create, Alter, Drop, Select, Insert, and Delete.
Backing Up and Restoring the Database
Creating a backup and recovery strategy is probably the most important task you have on your plate. When you’re creating backups, it is imperative that you understand the recovery models associated with each database such as Full, Simple, and Bulk-Logged and understand the impact of each model on the transaction log and the recovery process. In addition, it is a best practice to back up the user databases, but to restore a full SQL Server environment, the system database should be included the backup strategy.
For more information on recovery models and backing up and restoring a SQL Server environment, see Chapter 17. That chapter focuses on backing up all components of SQL Server, such as the Database Engine, Analysis Services, Reporting Services, and Information Services.
Transferring SQL Server Data
There are many different ways to transfer data or databases from within SQL Server Management Studio. There are tasks associated with importing and exporting data and copying and/or moving a full database with the Copy Database Wizard. To use the transferring tasks, right-click a database, select Tasks, and then select Import Data, Export Data, or Copy Database.
Each of these ways to move data is discussed in its entirety in Chapter 11, “Creating Packages and Transferring Data.”
Next: Taking a SQL Server Database Offline >>
More Database Articles
More By Sams Publishing
