June 13, 2003 at 2:50 pm
I wonder if someone could help me with a script to create 100 databases. I have a table containing the database names, data and log sizes.
Thank you very much for your help.
WM
June 13, 2003 at 4:00 pm
Please Heeeeeeeeeeeeeelp.
THANK YOU!
WM
June 13, 2003 at 4:22 pm
Hello. This should get you on your way. The key to this solution is to create a CURSOR.
DECLARE
@DatabaseName varchar(50),
@DataSize int,
@LogSize int,
@CreateDatabase varchar(500)
DECLARE TableCursor CURSOR FOR
SELECT DatabaseName, DataSize, LogSize
FROM DatabaseTableName
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @DatabaseName, @DataSize, @LogSize
WHILE @@FETCH_STATUS = 0
BEGIN
Set @CreateDatabase = 'CREATE DATABASE ' + @DatabaseName +
' ON ' +
'(Name = ' + @DatabaseName + '_dat, ' +
'FILENAME = ''c:\program files\microsoft sql server\mssql\data\' + @DatabaseName + '_dat.mdf'', ' +
'size = 10) ' +
'LOG ON ' +
'(NAME = ' + @DatabaseName + '_log, ' +
'FILENAME = ''c:\program files\microsoft sql server\mssql\data\' + @DatabaseName + '_log.ldf'', ' +
'size = 10) '
exec @CreateDatabase
END
CLOSE tablecursor
DEALLOCATE tablecursor
Everett
Everett Wilson
ewilson10@yahoo.com
June 13, 2003 at 4:25 pm
Whoops. I forgot to substitute @DataSize and @LogSize. Those lines should be:
'size = ' + @DataSize + ')' +
'size = ' + @LogSize + ')' +
Everett
Everett Wilson
ewilson10@yahoo.com
June 13, 2003 at 4:55 pm
Thank you very much, Everett. I will try that and let you know how it goes.
Thanks again for your help!
WM
June 13, 2003 at 5:40 pm
I've tested this script and it should work.
Try it and if you have questions, I'll check your reply.
Note: I've generated my own data, so if you want you can comment out
that portion and change all table and column references with yours
Declare @DB_Name VARCHAR(14), @DataF_Name VARCHAR(50), @LogF_Name VARCHAR(50),
@DataF_Size INT, @LogF_Size INT, @DataMax_Size INT, @LogMax_Size INT,
@DataF_Path VARCHAR(70), @LogF_Path VARCHAR(70),
@File_Path VARCHAR(100), @File_Growth INT, @Counter INT
/** Generate Test Data ***/
Create Table #Temp_DBS (DB_Names VARCHAR(20),
Data_Size INT,
Log_Size INT)
Set @Counter = 1
While(@Counter <= 4)
Begin
Insert #Temp_DBS
Values('Test_DB'+Convert(varchar(2), @Counter), 100, 50)
Set @Counter = @Counter + 1
End
/** Test Data Code Ends Here **/
/** Set Constant variables value **/
Set @DataMax_Size = 150
Set @LogMax_Size = 100
Set @File_Growth = 5
Set @File_Path = 'c:\MSSQL\Data\' -- (Make sure Directory exists or change it)
/** Loop through Table and create DBS **/
Select @DB_Name = Min(DB_Names)
From #Temp_DBS
While (@DB_Name IS NOT NULL)
Begin
Select @DataF_Size = Data_Size,
@LogF_Size = Log_Size
From #Temp_DBS
Where DB_Names = @DB_Name
Set @DataF_Name = @DB_Name+'_Data'
Set @LogF_Name = @DB_Name+'_Log'
Set @DataF_Path = @File_Path+@DataF_Name+'.mdf'
Set @LogF_Path = @File_Path+@LogF_Name+'.ldf'
Exec('Create DataBase '+@DB_Name+'
ON
(NAME = '''+@DataF_Name+''',
FILENAME = '''+@DataF_Path+''',
SIZE = '+@DataF_Size+',
MAXSIZE = '+@DataMax_Size+',
FILEGROWTH = '+@File_Growth+'
)
LOG ON
(NAME = '''+@LogF_Name+''',
FILENAME = '''+@LogF_Path+''',
SIZE = '+@LogF_Size+',
MAXSIZE = '+@LogMax_Size+',
FILEGROWTH = '+@File_Growth+'
)
')
Select @DB_Name = Min(DB_Names)
From #Temp_DBS
Where (DB_Names) > @DB_Name
End
MW
Edited by - mworku on 06/13/2003 5:43:52 PM
MW
June 16, 2003 at 10:43 am
Thank you, mworku. I will modify and run it today. I will post the result.
WM
June 16, 2003 at 12:17 pm
Hi,
I am still working on the script supplied by Everett. I am getting the following error when putting the int value into the create database statement. Any suggestions to make this work? Changing the datasize and logsize to varchar may work. However, keeping it a int value would help calculate the MAXSIZE and FILEGROWTH values. I would like to have Datasize plus 20% as the MAXZIZE value. Any input on how to script that would also be appreciated.
Thanks again for any input.
WM
----------------
Syntax error converting the varchar value 'CREATE DATABASE TEST1 ON (Name = TEST1_db, FILENAME = 'e:\database\TEST1.mdf', size = ' to a column of data type int.
------
Script
-------
/* ************************************************************************
-- DECLARE GLOBAL VARIABLES
*************************************************************************** */
DECLARE
@DatabaseName varchar(50),
@DataSize int,
@LogSize int,
@CreateDatabase varchar(500)
/* ************************************************************************
-- DEFINE CURSOR
*************************************************************************** */
DECLARE TableCursor INSENSITIVE CURSOR FOR
SELECT DBName, DBSize, LogSize
FROM msdb..test
/* ************************************************************************
-- OPEN CURSOR csrDBNames AND FETCH FIRST ROW
*************************************************************************** */
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @DatabaseName, @DataSize, @LogSize
/* ************************************************************************
-- PROCESS DATABASE CREATION
*************************************************************************** */
WHILE @@FETCH_STATUS = 0
BEGIN
Set @CreateDatabase = 'CREATE DATABASE ' + @DatabaseName +
' ON ' +
'(Name = ' + @DatabaseName + '_db, ' +
'FILENAME = ''e:\database\' + @DatabaseName + '.mdf'', ' +
'size = ' + @DataSize + ')' +
'LOG ON ' +
'(NAME = ' + @DatabaseName + '_log, ' +
'FILENAME = ''f:\tranlogs\' + @DatabaseName + '.ldf'', ' +
'size = ' + @LogSize + ')'
exec @CreateDatabase
FETCH NEXT FROM TableCursor INTO @DatabaseName, @DataSize, @LogSize
END
/* ************************************************************************
-- CLOSE, DEALLOCATE CURSORS
*************************************************************************** */
CLOSE tablecursor
DEALLOCATE tablecursor
June 16, 2003 at 12:47 pm
Hello.
Use CONVERT(char(2),@DataSize) and CONVERT(char(2),@LogSize).
If you print out the string @CreateDatabase you'll see that it's being treated as an integer.
Everett
Everett Wilson
ewilson10@yahoo.com
June 16, 2003 at 4:19 pm
Hi Everett,
Thanks for your reply.
Where should I put this line? It does not seem to work:
CONVERT(char(2),@DataSize) and CONVERT(char(2),@LogSize).
Also, for the MaxSize, can I do something like set @MaxSize = @DataSize * 0.20
Thanks again for your help.
WM
June 16, 2003 at 4:32 pm
Hello.
The quick answer is replace
'size = ' + @DataSize + ')' +
'size = ' + @LogSize + ')' +
with
'size = ' + CONVERT(char(2),@DataSize)+ ')' +
'size = ' + CONVERT(char(2),@LogSize) + ')' +
The long answer: I took the building part of your code
********************************************
Set @CreateDatabase = 'CREATE DATABASE ' + @DatabaseName +
' ON ' +
'(Name = ' + @DatabaseName + '_db, ' +
'FILENAME = ''e:\database\' + @DatabaseName + '.mdf'', ' +
'size = ' + @DataSize + ')' +
'LOG ON ' +
'(NAME = ' + @DatabaseName + '_log, ' +
'FILENAME = ''f:\tranlogs\' + @DatabaseName + '.ldf'', ' +
'size = ' + @LogSize + ')'
exec @CreateDatabase
*********************************************
modified it to print out the statement to be executed
*********************************************
DECLARE
@DatabaseName varchar(50),
@DataSize int,
@LogSize int,
@CreateDatabase varchar(500)
Set @DatabaseName = 'Test1'
Set @DataSize = 10
Set @LogSize = 10
Set @CreateDatabase = 'CREATE DATABASE ' + @DatabaseName +
' ON ' +
'(Name = ' + @DatabaseName + '_db, ' +
'FILENAME = ''e:\database\' + @DatabaseName + '.mdf'', ' +
'size = ' + CONVERT(char(2),@DataSize) + ')' +
'LOG ON ' +
'(NAME = ' + @DatabaseName + '_log, ' +
'FILENAME = ''f:\tranlogs\' + @DatabaseName + '.ldf'', ' +
'size = ' + CONVERT(char(2),@LogSize) + ')'
print @CreateDatabase
*******************************************
and then ran the resulting code, modifying it for my path (D drive ...)
*******************************************
CREATE DATABASE Test1 ON (Name = Test1_db, FILENAME = 'e:\database\Test1.mdf', size = 10)LOG ON (NAME = Test1_log, FILENAME = 'f:\tranlogs\Test1.ldf', size = 10)
*******************************************
As for the MaxSize, your right on target, just convert the values to chars.
Everett
Everett Wilson
ewilson10@yahoo.com
June 16, 2003 at 5:28 pm
Thanks again, Everett.
I got the MaxSize worked out and printed out the Create Database statements. However, when I tried to run the code with the
EXEC @CreateDatabase statement, I got the following error:
================
Could not locate entry in sysdatabases for database 'CREATE DATABASE test1 ON (Name = test1_db, FILENAME = 'e:\database\test1'. No entry found with that name. Make sure that the name is entered correctly.
===========================
Any idea? Thanks again.
WM
June 16, 2003 at 5:52 pm
Urk
Can you print out the @CreateDatabase and post it? I took your line, modified it
************************************
CREATE DATABASE test1 ON (Name = test1_db, FILENAME = 'd:\Program Files\Microsoft SQL Server\MSSQL\Data\test1')
*************************************
and had no trouble.
One idea, it's looks like it's trying to do something else than creating a database. Review the syntax of the printed out statement and see if anything at the beginning of the statement jumps out.
Everett
Everett Wilson
ewilson10@yahoo.com
June 16, 2003 at 5:58 pm
The printed statements are correct and can be run successfully manually. But I got the error when it is executed with the EXEC @CreateDatabse command.
WM
June 16, 2003 at 7:02 pm
Sorry. It should be: exec(@CreateDatabase)
Here's hoping
Everett
Everett Wilson
ewilson10@yahoo.com
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply