Sometimes one of the business requirements of the system is to create a new databse at the runtime. When You using SQL Server this operation can be done easy and quick. The stored procedure below takes two arguments. First 'DbName ' sets a name for new database and the second one gathers information about success of the operation as true or false. Note that second argument is output type which means that in C# code you can get information about create new database.
Now its time for some magic. After creating a new DB there is no object inside. But You don`t have to move all of structure to new DB each time you create a new one. In SQL Server one of the four database names 'model'. When you move all of Your buissnes logic (tables, views, stored procedures and even data) to it, all the structured will be move to new DB when it`s creating. Looks great, but remeber that if you have some PK on the table with data in model DB during cration of new DB PK values will be reset so any of relation won`t be created and some error may occure. Other problem certainly occur when you using CLR assembly in Your databse and want to copy it to new DB. This because in SQL Server you couldn`t set DB property Trustworthy ON and enable CLR integration.
Hope this help someone...
CREATE PROCEDURE proc_DBCreation
@DbName nvarchar(255),
@Result bit output
AS
BEGIN
DECLARE @LogFile nvarchar(255)
SET @LogFile = @DbName+'_log'
DECLARE @PathMDF nvarchar(255)
SET @PathMDF = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\'+@DbName+'.mdf';
DECLARE @PathLOG nvarchar(255)
SET @PathLOG = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\'+@DbName+'_log.ldf';
IF NOT EXISTS(SELECT TOP 1 1 FROM sys.databases WHERE name = @DbName)
BEGIN
Declare @sql nvarchar(max) SET @sql =
'CREATE DATABASE ['+@DbName+'] ON PRIMARY '+
'( NAME = '''+@DbName+''', FILENAME = N'''+ @PathMDF +''', SIZE = 5120KB , FILEGROWTH = 1024KB ) '+
'LOG ON '+
'( NAME = '''+@LogFile+''', FILENAME = N'''+ @PathLOG+''' ,'+
'SIZE = 1024KB , FILEGROWTH = 10%) '
exec(@sql)
SET @Result = 1;
END
ELSE
SET @Result =0;
END
More info:
Now its time for some magic. After creating a new DB there is no object inside. But You don`t have to move all of structure to new DB each time you create a new one. In SQL Server one of the four database names 'model'. When you move all of Your buissnes logic (tables, views, stored procedures and even data) to it, all the structured will be move to new DB when it`s creating. Looks great, but remeber that if you have some PK on the table with data in model DB during cration of new DB PK values will be reset so any of relation won`t be created and some error may occure. Other problem certainly occur when you using CLR assembly in Your databse and want to copy it to new DB. This because in SQL Server you couldn`t set DB property Trustworthy ON and enable CLR integration.
Hope this help someone...
CREATE PROCEDURE proc_DBCreation
@DbName nvarchar(255),
@Result bit output
AS
BEGIN
DECLARE @LogFile nvarchar(255)
SET @LogFile = @DbName+'_log'
DECLARE @PathMDF nvarchar(255)
SET @PathMDF = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\'+@DbName+'.mdf';
DECLARE @PathLOG nvarchar(255)
SET @PathLOG = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\'+@DbName+'_log.ldf';
IF NOT EXISTS(SELECT TOP 1 1 FROM sys.databases WHERE name = @DbName)
BEGIN
Declare @sql nvarchar(max) SET @sql =
'CREATE DATABASE ['+@DbName+'] ON PRIMARY '+
'( NAME = '''+@DbName+''', FILENAME = N'''+ @PathMDF +''', SIZE = 5120KB , FILEGROWTH = 1024KB ) '+
'LOG ON '+
'( NAME = '''+@LogFile+''', FILENAME = N'''+ @PathLOG+''' ,'+
'SIZE = 1024KB , FILEGROWTH = 10%) '
exec(@sql)
SET @Result = 1;
END
ELSE
SET @Result =0;
END
More info: