MSSQLTips

http://www.mssqltips.com/tip.asp?tip=1862

Edwin,

Thank you for this tip as another alternative to perform SQL Server database backups.  It is always good to have options.

How many folks from the community have shifted SQL Server database backups from tradition T-SQL scripts to PowerShell or SMO?  Is it better or worse?  Have either of these languages become your new standard for backup\restore related tasks?

Thank you,
The MSSQLTips Team

We still use native backup scripts as scheduled jobs.  For databases > 100GB we use Litespeed, also scripts scheduled as jobs.

I see no reason at the moment to use Powershell or SMO for backups from a production support point of view. We keep it simple.

Our Release Configuration management team are starting to use Powershell for their build scripts instead of traditional osql/sqlcmd batch files.

Unfortunately, not too many people from the community have shifted fully to PowerShell for managing SQL Server. There is still this notion of clinging on to old habits as TSQL is still the way to go for managing SQL Server. The main barrier to using PowerShell for managing SQL Server is installing it. Many customers don't want anything installed on the servers other than what is needed. This is very important for DBAs managing servers remotely via VPN connections as security is another hoop to skip. While not designed to be a replacement to manage SQL Servers, at least DBAs and IT Pros wh oare tasked to manage SQL Server will have other options available.

Alan,

Thank you for the insight on how you work with your backups based on size.

Maybe a tip on how your Configuration Management Team is using PowerShell would benefit the community.

Just let us know if you are interested.

Thank you,
The MSSQLTips Team

Bass_Player,

The installation aspect is interesting. 

How do you think Microsoft is going to treat that issue (i.e. installation)?  Do you think it is going to come to a point where T-SQL is just replaced with SMO or PowerShell in an upcoming version of SQL Server?  Or do you think it will be a gradual shift with T-SQL still available?

Do you know what happened with Exchange 2007?  How is PowerShell used with that platform?

Thank you,
The MSSQLTips Team

My opinion is that TSQL will always be there and will never be replaced as SQL is the language for the database. As far as installation is concerned, Windows PowerShell is already included as a feature in Windows Server 2008 and Windows 7. You just need to install that feature to enable the scripting engine.Still, not too many customers have had Windows PowerShell enabled on their Windows Server 2008 installations simply because of adoption. If server administrators already have a lot of VBScript scripts that they use for managing servers, it would be quite difficult to convince them to rewrite those scripts in PowerShell.

As for Exchange Server 2007, the entire server was built with Windows PowerShell in mind. In fact, the Exchange Management Interface was designed in such a way that all the mouse-clicks and menu-clicks are actually calls to Exchange-PowerShell cmdlets. This is Microsoft's direction by defining this in their common engineering criteria

Bass_Player,

Thank you for the insight.

Thank you,
The MSSQLTips Team