Skip to main content

Create a new databse in stored procedure (SQL SERVER 2008 R2)

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:


Labels:

Popular posts from this blog

Persisting Enum in database with Entity Framework

Problem statement We all want to write clean code and follow best coding practices. This all engineers 'North Star' goal which in many cases can not be easily achievable because of many potential difficulties with converting our ideas/good practices into working solutions.  One of an example I recently came across was about using ASP.NET Core and Entity Framework 5 to store Enum values in a relational database (like Azure SQL). Why is this a problem you might ask... and my answer here is that you want to work with Enum types in your code but persist an integer in your databases. You can think about in that way. Why we use data types at all when everything could be just a string which is getting converted into a desirable type when needed. This 'all-string' approach is of course a huge anti-pattern and a bad practice for many reasons with few being: degraded performance, increased storage space, increased code duplication.  Pre-requirements 1. Status enum type definition...
more -->

Creating common partial class with Entity Framework

When we use the Entity Framework (EF) in multilayer information systems sometimes we want to extend classes generated by EF by adding some common properties or functions. Such operation can`t be conduct on *.edmx data model so we need to make some improvement in our solution. Let`s begin... Lets assumed that in our soulution we have only three layer (three project): Client console application which has reference to the second layer  - ' ConsoleApplication ' project name Class library project with class interfaces only - ' Interfaces ' project name Class library class implementation and data model referenced to 'Interfaces' project - ' Classes ' project name. Picture 1. Solution structure. Now when we have all solution structure we can focus on data model. In the ' Classes ' project we create a new folder named ' Model ' and inside add new item of ADO.NET Entity Data Model named ' Learning.edmx ' - it may be empty ...
more -->

Using Hortonworks Hive in .NET

A few months ago I decided to learn a big data. This sounds very complex and of course it is. All these strange names which actually tells nothing to person who is new in these area combined with different way of looking at data storage makes entire topic even more complex. However after reading N blogs and watching many, many tutorials today I finally had a chance to try to write some code. As in last week I managed to setup a Hortonworks distribution of Hadoop today I decided to connect to it from my .NET based application and this is what I will describe in this post. First things first I didn`t setup entire Hortonworks ecosystem from scratch - I`d love to but for now it`s far beyond my knowledge thus I decided to use a sandbox environment provided by Hortonworks. There are multiple different VMs available to download but in my case I`ve choose a Hyper-V. More about setting this environment up you can read here . Picture 1. Up and running sandbox environment. Now whe...
more -->