Add files to filegroups and log for many dbs

use Filer
go
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'spF_add_files_group' AND type = 'P' )
    DROP PROC spF_add_files_group
GO
CREATE PROC spF_add_files_group 
@seldb varchar(100) = '',--- blank expands all databases
@mydisk varchar(100)--- exemple 'U:\TestDB\'
AS
/*
Created by:Lennart Gerdvall
E-mail:lge@faktab.se
Company:Faktab Finans AB, S:t Hansplan 1, SE-62188 Visby, Sweden
Phone:+46(0)498 20 20 00
Fax:+46(0)498 20 20 90
Created:2003-02-20
Altered:2003-03-06 by Lennart Gerdvall (revised the English text)
Purpose:This SP is used to add a logfile and a datafile to each file group
in a database or all databases.
Consider:Avoid running any large updates when you use this SP. I freeze expansion
of old databasefiles while adding another 10 MB extra for safety when I set
max size (current size plus 10 MB), if someone is adding data anyway.
Performance:Intel 1000 MHz x 2, 2 GB RAM, StorageTek SAN- disks, 500 databases with
a total of 220 GB. Added another SAN-disk with 290 GB. Freezing the old
files for the 500 databases and adding a logfile, a file for each of the
two filegroups took 25 minutes in daytime on a heavily used system. 
*//*
Must first run this table script in a database used for administrative purposes (Filer) to create the nessecary table!
*//*
USE Filer
CREATE TABLE [dbo].[dbfilesgroups] (
[dbname] [varchar] (30) NOT NULL,
[fileid] [smallint] NOT NULL,
[size][int] NOT NULL,
[maxsize] [int] NOT NULL,
[growth] [int] NOT NULL,
[name] [nvarchar](128) NOT NULL,
[filename] [nvarchar](260) NOT NULL,
[groupname] [sysname] NULL 
) ON [PRIMARY]
*/---Populate the dbfilesgroups table with original file information
---use Filer  -- Select database where above table (dbfilesgroups) is created. DDL is above.
set nocount on
declare @dbasename varchar (100),
 @mydb varchar (100),
 @mygroup varchar (100),
 @mycount int,
 @sqlstmt varchar(4000),
 @myattach varchar(8000),
 @mydetach varchar(1000),
 @mynewfile varchar(1000),
 @mymove varchar(500),
 @filetomove varchar(500),
 @movecommand varchar(4000),
@killcommand varchar(500),
@singlecommand varchar(500),
@multicommand varchar(500),
 @groupcounter NUMERIC,
 @filegroupcounter NUMERIC,
 @maxfileid NUMERIC,
@filecounter NUMERIC,
 @globalcounter NUMERIC

set @mycount = 0
truncate table dbfilesgroups -- Empty all records in dbfilesgroups table

---Open cursor with all or one user db name
BEGIN
IF @seldb <> '' -- A named database has been specified
declare cur_databasetab cursor for select name from master..sysdatabases
where name like ('"' + @seldb + '%"') for read only
ELSE---Use all databases but system dbs and demo dbs
declare cur_databasetab cursor for select name from master..sysdatabases
where name not in ('master','model','msdb','pubs','northwind', 'tempdb') 
order by name for read only
END

open cur_databasetab 
fetch next from cur_databasetab into @dbasename
WHILE (@@FETCH_STATUS = 0) 
BEGIN 
select @sqlstmt = ('insert into dbfilesgroups select ' + 
"'"+@dbasename + "',"+ ' fileid, [size], maxsize, growth, ltrim(rtrim(name)), 
ltrim(rtrim(filename)),  ltrim(rtrim(groupname))' + 
'from ' + "["+ @dbasename +"]"+ '..sysfiles LEFT OUTER JOIN ' +
"["+ @dbasename +"]"+ '..sysfilegroups ON ' + 
"["+ @dbasename +"]"+ '..sysfiles.groupid = ' + 
"["+ @dbasename +"]"+ '..sysfilegroups.groupid')
exec (@sqlstmt)
fetch next from cur_databasetab into @dbasename
END
close cur_databasetab
deallocate cur_databasetab

---Loop through the databases
---Open cursor with all or one user db name
BEGIN
IF @seldb <> '' -- A named database has been specified
declare cur_selecteddbs cursor local for select name from master..sysdatabases
where name like ('"' + @seldb + '%"') for read only
ELSE---Use all databases but system dbs and demo dbs
declare cur_selecteddbs cursor local for select name from master..sysdatabases
where name not in ('master','model','msdb','pubs','northwind', 'tempdb') 
order by name for read only
END

open cur_selecteddbs 
fetch next from cur_selecteddbs
into @mydb
WHILE (@@FETCH_STATUS = 0) 
BEGIN 
---Reset counter for files in the regular filegrous (groupname inte NULL) for the current database
select @filegroupcounter = 0
---Reset counter for number of files in a filegroup
select @groupcounter = 1
---Reset counter for number of files in a database
select @filecounter = 1
---Reset counter for max fileid in a database
select @maxfileid = 1

---Create a new file for every filegroup including the NULL filegroup (which is the log filegroup)
declare cur_selectgroups cursor local for select groupname from dbfilesgroups
where dbname = @mydb group by groupname for read only
open cur_selectgroups 
fetch next from cur_selectgroups into @mygroup
WHILE (@@FETCH_STATUS = 0) 
BEGIN 
---Check if filegroup is a normal group or a NULL-group (which is the log filegroup)
IF @mygroup IS NULL
BEGIN
---How many files are there in the log filegroup for the current database?
select @groupcounter = count(fileid) from dbfilesgroups  where dbname = @mydb and groupname IS NULL
---Add a new file on the new disk to the log filegroup for the current database!
select @groupcounter = @groupcounter + 1
select @mynewfile = 'ALTER DATABASE ' + '[' + @mydb + ']' + ' ADD LOG FILE ' + char(13) +
'( NAME = ' + @mydb + '_GenLog' + CAST(@groupcounter AS varchar(3)) + ',' + char(13) +
'  FILENAME = ' + CHAR(39) + @mydisk + @mydb + '_GenLog' + CAST(@groupcounter AS varchar(3)) + '.LDF' + CHAR(39) + ',' + char(13) +
'  SIZE = 10MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10MB)'
exec (@mynewfile)
END

ELSE
BEGIN
select @filegroupcounter = @filegroupcounter + 1
---How many files are there in the current filegroup for the current database?
select @groupcounter = count(fileid) from dbfilesgroups  where dbname = @mydb and groupname IS NOT NULL
---Add a new file on the new disk to the current filegroup for the current database!
select @groupcounter = @groupcounter + @filegroupcounter
select @mynewfile = 'ALTER DATABASE ' + '[' + @mydb + ']' + ' ADD FILE ' + char(13) +
'( NAME = ' + @mydb + '_GenData' + CAST(@groupcounter AS varchar(3)) + ',' + char(13) +
'  FILENAME = ' + CHAR(39) + @mydisk + @mydb + '_GenData' + CAST(@groupcounter AS varchar(3)) + '.NDF' + CHAR(39) + ',' + char(13) +
'  SIZE = 10MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10MB)' + char(13) + 'TO FILEGROUP [' + @mygroup + ']'
exec (@mynewfile)
END

fetch next from cur_selectgroups into @mygroup
END
close cur_selectgroups
deallocate cur_selectgroups

BEGIN
--- Create ALTER DATABASE FILE STATEMENTS FOR EXISTING DATABASE FILES TO STOP FURTHER GROWTH
select @mycount = count(fileid) from dbfilesgroups where dbname = @mydb
SELECT @globalcounter = 1

WHILE (@globalcounter <= @mycount)
BEGIN
select @mymove = 'ALTER DATABASE ' + '[' + @mydb + ']' + char(13) + ' MODIFY FILE ' + char(13) + '(NAME = '
select @filetomove = (select b.name from dbfilesgroups b where b.fileid = @globalcounter and a.dbname = b.dbname) + ',' + char(13)
from dbfilesgroups a where dbname = @mydb and fileid = 1 --so records are not listed twice
select @movecommand = @mymove + @filetomove ---+ ' ' + @filetomove +  + CHAR(39)

/*Create the MAXSIZE statement*/select @myattach = 'MAXSIZE = ' + 
(select CAST(((b.size * 8/1024) + 10) AS varchar(10)) from dbfilesgroups b where b.fileid = @globalcounter and a.dbname = b.dbname)+ 'MB)' +
char(13)
from dbfilesgroups a where dbname = @mydb and fileid = 1 --so records are not listed twice
select @myattach = @movecommand + @myattach
exec (@myattach)
SELECT @globalcounter = @globalcounter + 1
END
END
fetch next from cur_selecteddbs into @mydb
END
close cur_selecteddbs
deallocate cur_selecteddbs
GO