Microsoft® SQL Server® 2012 Bible

793

Chapter 30: Confi guring and Managing SQL Server with PowerShell

30

Scripting SQL Server Tasks

Although using PowerShell interactively to perform maintenance tasks may be fun and

interesting, it doesn’t save much time. Scripting enables administrators to perform the

same function the same way every time, saving the time it might take to remember how

to solve a problem and enabling the administrator to focus on new problems as they occur.

Typically, administrators create scripts for two basic categories of tasks: administrative

tasks, those that perform normal administrative functions; and data-based tasks.

Administrative Tasks

Listing 30-5 shows a script to create a database, but nearly every administrative activity

required of a SQL Server DBA can be scripted using PowerShell and SMO. One task that you

can completely avoid having to write script against the SMO in SQL Server 2012 is backing

up databases.

LISTING 30-5 Backup.ps1

Backup-SqlDatabase -Database AdventureWorks2012 -ServerInstance localhost

To back up all the databases on an instance, it’s as simple as querying the instance for a

list of databases and then piping that information to the Backup-SQLDatabase cmdlet.

When you pipe the list to the backup cmdlet you must wrap the command in a foreach

loop because the Backup-SQLDatabase cmdlet accepts only one database name at a time.

Dir SQLSERVER:\SQL\localhost\default\Databases\ |

foreach{

Backup-SqlDatabase -Database $_.name -ServerInstance localhost

}

One of the great improvements in SMO over its predecessor, DMO, is in the area of scripting.

With SMO, Transact-SQL scripts can be created from objects even if they don’t yet exist.

Almost all maintenance dialogs in SQL Server Management Studio include a button that

enables a script to be generated from the changes made in that dialog. That way, the script

can be executed, rather than making the changes from the dialog, and the script can be

saved for future use.

Another useful feature of scripting existing objects is the capability to generate scripts

of all database objects for documentation or to put into source code control. This enables

administrators to rebuild a database in the form it existed at the time the script was cre-

ated, should some problem arise requiring that effort.

At any time while creating or working with objects in SMO, those objects can be scripted for

archival or later use (see Figure 30-3).

c30.indd 793c30.indd 793 7/31/2012 9:46:23 AM7/31/2012 9:46:23 AM

http://www.it-ebooks.info