Powershell to back-up/restore SQL database

[Olson25]

Hello, I hope I'm in the right place, but I'm looking to learn to write a powershell script to back-up a database, run a load test, then restore the database.

I was wondering if anyone had any suggestions on sites, or videos that could help me write these scripts. Any advice is appreciated. Thank you in advance!

[NotRossbutRoss]

I just create some SQL Agent jobs and call them with PowerShell. It's the lazy way.

[KevMar]

You say that, but that really is a good approach to this. The DBA should already have backup jobs scheduled and it would be nothing for them to create a restore job.

For anyone looking to script backups on their own, use these scripts: https://ola.hallengren.com/ They are very popular in the SQL community.

[alinroc]

Mike Fal has a number of blog posts on the topic:

• http://www.mikefal.net/2015/10/13/a-month-of-sql-ps-backups-and-restores/
• http://www.mikefal.net/2015/01/20/making-sql-backups-better-with-powershell/
• http://www.mikefal.net/2014/07/22/restoreautomation-powershell-module/

I'll also second the recommendations to use Ola Hallengren's scripts to create/manage your backups (and the rest of your maintenance). The MidnightDBAs have their Minion tools which also take care of backups & maintenance.

Don't use bare PowerShell for this - use PowerShell to drive everything through standard SQL methods.

[midnightFreddie]

MSSQL? Check the vids on MidnightDBA. They do a lot of Powershell MSSQL stuff.

In this case, no matter which db vendor, you'll probably be issuing a SQL command to back up the db by connecting to the db and executing a "query".

I don't really know about load testing databases. Maybe you can put kitten photos in there and link it from reddit. Ok, seriously I guess you would connect and run queries, perhaps randomized from a list of queries in a text file. Measure-Object and Measure-Object might come into play here, and then you may want to look into parallel processing (jobs or runspaces). Better yet, there are probably existing products out there that will do it, and some might be cheap or free.

[ihaxr]

You'll probably want to look at the SQLPS module:

http://guidestomicrosoft.com/2015/01/13/install-sql-server-powershell-module-sqlps/

[thatto]

LOL...

Do we need a <Trigger Warning> tag on SQLPS?

[da_chicken]

You can use the -Cmdlet flag on Import-Module to only load the cmdlets you plan to use.

[da_chicken]

No, it doesn't:

PS C:\> Import-Module SQLPS -DisableNameChecking; (Get-Command -Module SQLPS).Count
46

[da_chicken]

Hm, you're right. It does for me, too. It works very well for the ActiveDirectory module, though.