Microsoft SQL Server 2012 Bible

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 ...