As a SQL Server DBA, you may be familiar with using T-SQL and cursors to backup all your user databases. However, if you’re starting to learn PowerShell, you might be wondering how you can leverage it for common DBA tasks like database backups. In this article, we will walk you through the process of creating a basic PowerShell script to backup all your user databases.

Step 1: Loading the SQL Server Snap-ins

Before we begin, we need to load the SQL Server snap-ins in PowerShell. This allows us to access the SQL Server cmdlets and perform database operations. You can do this by using the following command:

Add-PSSnapin *SQL*

Step 2: Setting the Backup File Location and Format

Next, we need to set the location where the backup files will be stored. In this example, we will set it to “C:\temp\”, but you can change it to your desired location. Additionally, we will pull the current date and format it as “YearDayMonth” using the following command:

$date = Get-Date -Format yyyyddMM
$directory = "C:\temp\"

Step 3: Retrieving the Database Names

Now, we need to retrieve the names of all the user databases on the SQL Server instance. We can do this by using the following command:

$dbname = dir 'SQLSERVER:\SQL\YourServerName\DEFAULT\Databases' | Select Name

Note that the above command only returns user databases. If you want to include system databases in the backup, you can add the “-Force” parameter after the path.

Step 4: Backup Each User Database

Finally, we will use a ForEach loop to iterate through each database name and perform the backup operation. The backup file will be named “DBName_YearDayMonth.bak”. Here’s the script:

$dbname | foreach { $_.Name.ToString() } | foreach {
    $bakfile = "$directory" + $_ + "_" + $date + ".bak"
    "Backing up Database: $_"
    Invoke-Sqlcmd -SuppressProviderContextWarning -Query "BACKUP DATABASE $_ TO DISK=N'$bakfile' WITH INIT"
}

Make sure to replace “YourServerName” with the actual name of your SQL Server instance.

Conclusion

In this article, we have shown you how to create a basic PowerShell script to backup all your user databases in SQL Server. By leveraging PowerShell’s ForEach loop and SQL Server cmdlets, you can automate this common DBA task and save time. Feel free to customize the script according to your specific requirements, such as changing the backup file location or including system databases in the backup.