I’m doing a testing my database restores. Notice that I say I’m testing the restores. If the backups don’t work, neither will the restores.

Part of my process is to drop the existing databases in the restore target instance. It’s really the only place I can ever imagine this script seeing the light of day.

SET NOCOUNT ON
DECLARE @v_dbname nvarchar(100)
DECLARE @v_SQL nvarchar(1000)
DECLARE c_databases CURSOR FOR
SELECT [name]
FROM sysdatabases
WHERE name NOT IN ('tempdb', 'pubs', 'master', 'msdb', 'litespeedlocal', 'model')
ORDER BY [name]

OPEN c_databases
FETCH NEXT FROM c_databases INTO @v_dbname
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN

select @v_SQL = 'DROP DATABASE [' + @v_dbname + ']'
exec ( @v_sql )

END
FETCH NEXT FROM c_databases INTO @v_dbname
END

CLOSE c_databases
DEALLOCATE c_databases