CODEWITHSUNDEEP
sql-serverpowershell
u123
u123

Reputation: 16287

SQL Server : drop all databases except the system ones

In PowerShell I am using the following code to delete all non system SQL Server databases:

invoke-sqlcmd -ServerInstance $sqlInstanceName -U $sqlUser -P $sqlPass -Query "
EXEC sp_MSforeachdb
  'IF DB_ID(''?'') > 4
  BEGIN
    ALTER DATABASE [?] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE [?]
  END'
"

And it seems to do the job. But when I re-run it I get:

invoke-sqlcmd : Option 'SINGLE_USER' cannot be set in database 'master'.
Option 'SINGLE_USER' cannot be set in database 'tempdb'.
At C:\tmp\drop.ps1:19 char:5
+ invoke-sqlcmd -ServerInstance $sqlInstanceName -U $sqlUser -P $sq ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

I thought IF DB_ID(''?'') > 4 would skip any system dbs:

How can I omit system databases and allow SQL Server 2008 agent job to move past ERROR_NUMBER 208?

How do I make it terminate gracefully if only system dbs (master, model, msdb, tempdb) are found?

Upvotes: 3

Views: 2785

Answers (1)

Alex
Alex

Reputation: 5157

I suspect what is happening here is syntax checking before the actual evaluation of IF. You need to introduce another level of "dynamism" to your query.

EXEC sp_MSforeachdb
  'IF DB_ID(''?'') > 4
  BEGIN
    EXEC (''ALTER DATABASE [?] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE [?]'' )
  END'

Upvotes: 5

Related Questions