Developing Matt

My Technical Journal

T-SQL Script to bring online, backup, and drop

leave a comment »

I’m not sure that it is helpful to anyone but me, but when is it about anyone else?  : )

Here is a script to query all the offline databases and take and back them up and generate a drop database script.  I’m not very comfortable writing cursors so I probably have it wrong somewhere.  But it works, nonetheless.

SET QUOTED_IDENTIFIER ON

DECLARE @dbname varchar(255)
declare @name table (dbname varchar(255))
DECLARE datanames_cursor CURSOR FOR SELECT name FROM dbo.sysdatabases WHERE status = 528

Open datanames_cursor

FETCH NEXT FROM datanames_cursor INTO @dbname
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status = -2)
FETCH NEXT FROM datanames_cursor INTO @dbname
BEGIN
–PRINT @dbname
–step 1: bring online
    DECLARE @tsql varchar(200)
    set @tsql = ‘ALTER DATABASE ‘ + @DBNAME + ‘ SET ONLINE’
    execute (@tsql)

–step 2: backup
    set @tsql = ‘BACKUP DATABASE ‘ + @dbname + ‘ TO DISK = ”d:\Admin\’ + @dbname + ‘_04202010_0340pm.bak”’
    execute ( @tsql)

–step 3: generate drop database
    insert into @name (dbname) values ( ‘DROP DATABASE ‘ + @dbname)

FETCH NEXT FROM datanames_cursor INTO @dbname
END

END

DEALLOCATE datanames_cursor
select * from @name

Advertisements

Written by matt

April 21, 2010 at 1:53 pm

Posted in T-Sql

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: