9

Is there a PowerShell command that will execute a backup of all databases on a server?

Alternatively, can I call a stored proc from PowerShell (that will execute the backup)?

2
  • 1
    It will be nice to know how the scripts will perform in this scenario. Any reason why would you prefer this than the native way of backing up a SQL Server DB? May 5 '09 at 21:42
  • What is the native way of backing up a SQL Server DB? Currently I open up SSMS and run a stored proc that backs up all the DB's into a directory. Then I run a powershell script that moves all of this to a backup location. My intention is to do this in one step from Powershell instead of 2 steps.
    – Guy
    May 14 '09 at 3:30
9

if you are running sql server 2008 you have powershell bindings. See this link

SQL Server 2008 powershell has a cmdlet invoke-sqlcmd.

4

It is possible, there are some pre-requisites you need to have installed (namely the Microsoft SQL Server 2005 Management Objects Collection) and theres quite alot of powersehll code needed to do it, however there are a number of articles out there that have it allready done for you. I have used this method successfully in the past.

1
  • +1 for SMO .....
    – dance2die
    May 6 '09 at 14:19
2

You can use ADO.NET from PowerShell to execute a stored proc..

See this example.

1
  • Good alternative.
    – dance2die
    May 6 '09 at 14:18
2

There's functions for PowerShell-based SQL Server database backups included in the CodePlex project SQL Server PowerShell Extensions (SQLPSX). Take look at Invoke-SqlBackup or if you want to implement a full backup and maintenance routine take a look at the SQLMaint module.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.