O'Reilly logo

Microsoft SQL Server 2012 Bible by Aaron Nelson, Jose Chinchilla, Patrick LeBlanc, Jorge Segarra, Adam Jorgensen

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required