Search code examples
sql-serverpowershell-remoting

Individual DB start in SQL DB Stacking

We have a scenario where multiple SQL DBs are installed under single SQL Instance (SQL DB Consolidation)

VM Name = MyHostQAS

SQL Instance Name = MyHostQAS\NISQLSERVER2

SQL DB1 = QWA

SQL DB2 = QWB

My Requirement is to write a PS script which remotely starts/stops Individual SQL DB (not the entire SQL Instance)

I tried with sqlcmd by first running it locally (in cmd) on the server where SQL Instance is installed

sqlcmd -S MyHostQAS\NISQLSERVER2 -Q "ALTER DATABASE QWA SET online;"

This works fine and QWA DB is started. Where as when we execute the following PS query we get errors

Invoke-Sqlcmd -Username "admin" -Password "Admin123#" -ServerInstance "MyHostQAS\NISQLSERVER2" -Database "QWA" -Query "ALTER DATABASE QWA SET online;"

Errors:

Invoke-Sqlcmd : Login failed for user 'admin'. At line:1 char:1 + Invoke-Sqlcmd -Username "admin" -Password "Admin123#" -ServerIn ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlException + FullyQualifiedErrorId : SqlExectionError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand Invoke-Sqlcmd : At line:1 char:1 + Invoke-Sqlcmd -Username "admin" -Password "Admin123#" -ServerIn ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : ParserError: (:) [Invoke-Sqlcmd], ParserException + FullyQualifiedErrorId : ExecutionFailureException,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

Can you please help in troubleshooting this?

Solution

  • Perhaps the database specified with the -Database argument is not online so the login fails. Specify a system database like master or tempdb for the argument:

    Invoke-Sqlcmd -ServerInstance MyHostQAS\NISQLSERVER2" -Database "master" -Query "ALTER DATABASE QWA SET online;"

    Not sure what is driving your requirement to toggle ONLINE/OFFLINE but you might consider turning on the database AUTO_CLOSE option. That will free resources automatically when the database is not in use, but at the cost of startup overhead (not much different than setting the database ONLINE via script).