How to: Drop multiple databases via SQL Script (no worries backup/restore is covered too :) )

Recently I did rather a lot of test requiring me to work with non-consolidated K2 DBs. Test included multiple DB restore/delete operations and I realized that I need some script to quickly drop all my K2 DBs and start from scratch. Here is this script:

USE master;

GO

SELECT 'ALTER DATABASE ' + name + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE ' + 'DROP DATABASE '+ name

FROM sys.databases WHERE name like 'K2%';

GO

Script selects every database prefixes with “K2” and you just need to copy its output into new query window and execute.

And in case you tend to backup things before you delete them, similar script for backup:

USE master;

GO

SELECT 'BACKUP DATABASE ' + name + ' TO DISK=''C:\DBs\' + name + '.bak' + Char(39)

FROM sys.databases WHERE name like 'K2%';

GO

And for restore you can use the script below. Unfortunately it uses hard coded file paths but assuming your back up files have default DB names (and for example were created by the script above) you can get away with minimum find and replace adjustments (path to backup files and your SQL instance data directories may need to be adjusted). Here is the script for restore:

USE MASTER

RESTORE DATABASE [K2Categories]

FROM DISK = 'c:\DBs\K2Categories.bak'

WITH MOVE 'K2Categories' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLK2\MSSQL\DATA\K2Categories.mdf',

MOVE 'K2Categories_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLK2\MSSQL\DATA\K2Categories.log',

REPLACE;

GO

RESTORE DATABASE [K2Dependencies]

FROM DISK = 'c:\DBs\K2Dependencies.bak'

WITH MOVE 'K2Dependencies' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLK2\MSSQL\DATA\K2Dependencies.mdf',

MOVE 'K2Dependencies_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLK2\MSSQL\DATA\K2Dependencies.log',

REPLACE;

GO

RESTORE DATABASE [K2EnvironmentSettings]

FROM DISK = 'c:\DBs\K2EnvironmentSettings.bak'

WITH MOVE 'K2EnvironmentSettings' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLK2\MSSQL\DATA\K2EnvironmentSettings.mdf',

MOVE 'K2EnvironmentSettings_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLK2\MSSQL\DATA\K2EnvironmentSettings.log',

REPLACE;

GO

RESTORE DATABASE [K2EventBus]

FROM DISK = 'c:\DBs\K2EventBus.bak'

WITH MOVE 'K2EventBus' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLK2\MSSQL\DATA\K2EventBus.mdf',

MOVE 'K2EventBus_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLK2\MSSQL\DATA\K2EventBus.log',

REPLACE;

GO

RESTORE DATABASE [K2EventBusScheduler]

FROM DISK = 'c:\DBs\K2EventBusScheduler.bak'

WITH MOVE 'K2EventBusScheduler' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLK2\MSSQL\DATA\K2EventBusScheduler.mdf',

MOVE 'K2EventBusScheduler_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLK2\MSSQL\DATA\K2EventBusScheduler.log',

REPLACE;

GO

RESTORE DATABASE [K2HostServer]

FROM DISK = 'c:\DBs\K2HostServer.bak'

WITH MOVE 'K2HostServer' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLK2\MSSQL\DATA\K2HostServer.mdf',

MOVE 'K2HostServer_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLK2\MSSQL\DATA\K2HostServer.log',

REPLACE;

GO

RESTORE DATABASE [K2Server]

FROM DISK = 'c:\DBs\K2Server.bak'

WITH MOVE 'K2Server' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLK2\MSSQL\DATA\K2Server.mdf',

MOVE 'K2Server_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLK2\MSSQL\DATA\K2Server.log',

REPLACE;

GO

RESTORE DATABASE [K2ServerLog]

FROM DISK = 'c:\DBs\K2ServerLog.bak'

WITH MOVE 'K2ServerLog' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLK2\MSSQL\DATA\K2ServerLog.mdf',

MOVE 'K2ServerLog_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLK2\MSSQL\DATA\K2ServerLog.log',

REPLACE;

GO

RESTORE DATABASE [K2SQLUM]

FROM DISK = 'c:\DBs\K2SQLUM.bak'

WITH MOVE 'K2SQLUM' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLK2\MSSQL\DATA\K2SQLUM.mdf',

MOVE 'K2SQLUM_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLK2\MSSQL\DATA\K2SQLUM.log',

REPLACE;

GO

RESTORE DATABASE [K2WebDesigner]

FROM DISK = 'c:\DBs\K2WebDesigner.bak'

WITH MOVE 'K2WebDesigner' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLK2\MSSQL\DATA\K2WebDesigner.mdf',

MOVE 'K2WebDesigner_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLK2\MSSQL\DATA\K2WebDesigner.log',

REPLACE;

GO

RESTORE DATABASE [K2SmartBox]

FROM DISK = 'c:\DBs\K2SmartBox.bak'

WITH MOVE 'K2SmartBox' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLK2\MSSQL\DATA\K2SmartBox.mdf',

MOVE 'K2SmartBox_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLK2\MSSQL\DATA\K2SmartBox.log',

REPLACE;

GO

RESTORE DATABASE [K2SmartBroker]

FROM DISK = 'c:\DBs\K2SmartBroker.bak'

WITH MOVE 'K2SmartBroker' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLK2\MSSQL\DATA\K2SmartBroker.mdf',

MOVE 'K2SmartBroker_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLK2\MSSQL\DATA\K2SmartBroker.log',

REPLACE;

GO

RESTORE DATABASE [K2WebWorkflow]

FROM DISK = 'c:\DBs\K2WebWorkflow.bak'

WITH MOVE 'K2WebWorkflow' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLK2\MSSQL\DATA\K2WebWorkflow.mdf',

MOVE 'K2WebWorkflow_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLK2\MSSQL\DATA\K2WebWorkflow.log',

REPLACE;

GO

RESTORE DATABASE [K2Workspace]

FROM DISK = 'c:\DBs\K2Workspace.bak'

WITH MOVE 'K2Workspace' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLK2\MSSQL\DATA\K2Workspace.mdf',

MOVE 'K2Workspace_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLK2\MSSQL\DATA\K2Workspace.log',

REPLACE;

GO