Scenario:
Let's say you just started with fresh Installation of SQL Server Instance. Now you want to create some sample database so you can use for different purposes. You don't want to run Create Database Script 100's of time and there is possibility you want to Data and Log location different than by default.
Solution:
The below script can be used to create databases as many as you want. You have the options to provide the Data Directory and Log directory in which you want to create the database. You can choose the part of Database Name and then it will add the numbers to it.
--Create Variables Declare @DataFilePath VARCHAR(100) Declare @LogFilePath VARCHAR(100) Declare @SubPartDBName VARCHAR(100) Declare @StartCnt int Declare @MaxDBCnt int --Set the Variable Values, @MaxDBCnt is Number of Databases you want to Create SET @StartCnt=1 SET @MaxDBCnt=101 --Provide the Data File Path And Log File Path SET @DataFilePath='C:\Program Files\Microsoft SQL Server\MSSQL12.SQL01\MSSQL\DATA\' SET @LogFilePath='C:\Program Files\Microsoft SQL Server\MSSQL12.SQL01\MSSQL\DATA\' --Chose the First part of your DB name, Let's say TEST is chosen then Databae will be created Test1,Test2....Test100 SET @SubPartDBName='Test' --Create Databases While ( @startCnt<@MaxDBCnt) BEGIN Print CAst(@startCnt AS VARCHAR(100)) DECLARE @DBFullName VARCHAR(500) =@SubPartDBName+CAST(@StartCnt AS VARCHAR(10)) DECLARE @SQL NVARCHAR(MAX) SET @SQL= 'CREATE DATABASE ['+@DBFullName+'] ON ( NAME = N'''+@DBFullName+''', FILENAME = N'''+@DataFilePath+@DBFullName+'.mdf'' ,
SIZE = 4096KB , FILEGROWTH = 1024KB ) LOG ON ( NAME = N'''+@DBFullName+'_log'', FILENAME = N'''+@LogFilePath+@DBFullName+'_log.ldf'' ,
SIZE = 1024KB , FILEGROWTH = 10%)' SET @startCnt=@startCnt+1 Print @SQL Execute (@SQL) END
No comments:
Post a Comment