Create Databse dynamically with name which send as a paramert

I  need to created Database  dynamically with the name which send me as a parameter
so it's should be dynamic statement
then I should attach mdf , ldf files  which contain database structure and sp proc
I should run this from .net app
What is the best way to do it?
YampolkaValeriya YampolskaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark WillsTopic AdvisorCommented:
Have a read of the link below. It has a few references to other languages as well...

https://support.microsoft.com/en-us/help/305079/how-to-create-a-sql-server-database-programmatically-by-using-ado-net
0
Nitin SontakkeDeveloperCommented:
The best approach probably is to write appropriate methods in a class and a single method which would do the job, to which you will pass the parameter which is a database name.

I don't see anything which is hugely complicated here. Except probably a part in which you are saying that you will attach mdb + ldf which would have structure. This is all fine, but you will need to keep on renaming the template mdf + ldf files so as to have a unique name each time.

If it is possible, it would be nicer to place all of this in a model database, which is the one get copied over while creating the new database. So second part is not required at all.
0
Olaf DoschkeSoftware DeveloperCommented:
The native concept SQL Server provides for that is dacpac/bacpac. (Extract/Export Data-tier application). You should make use of that instead of mdf/ldf attaching.

You can maintain and version these pacs in VS and have, compute diff scripts of two dacpacs etc. Very nice concept to maintain different db versions and updates and also good for the initialization.

Just notice in advance, before stumbling uppon it, the DAC Deploy wizard of SSMS has it's limitations, so use sqlcmd.exe andsqlpackage.exe for deployment.

I won't go into detail, just google dacpac and you'll get staarting points for what'S necessary to know.

Bye, Olaf.
1
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
If you use the restore database command with the wtih move clause, baesd on a full backup of the original database, you can do the creation, renaming and attaching in one go. The above stated is correct - you need to make sure you either use different folders for each DB, or rename the files to unique names (usually the db name), to avoid conflicts.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
A copy of the model database is used to create new databases. If you create your future databases structure in the model database then you'll only need the new database name to be sent as parameter and all that you'll need to do is to run the CREATE DATABASE command:
CREATE DATABASE newDBname

Open in new window

0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Guys, modifying the model DB is a quite uncommon and useless feature. Most of the time you do not want to have only copies of the same database structure, but different databases, in your instance.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
@Qlemo, that really depends on how your organization works.
I've been working with SQL Server from long and mostly in finance institutions where you have hundreds to thousands of SQL Server instances. Organizing those SQL Server instances logically so you can distribute your databases through those SQL Server instances, is the first step of a big strategy. Inside the logic that you use (application, customer department, business area, etc...), you can easily have a specific model database for each SQL Server instance or group of SQL Server instance.
So, what's uncommon and useless to you, doesn't mean that it is for others.
1
YampolkaValeriya YampolskaAuthor Commented:
I need to create database with special rights  and security requirements.
I  have a text file with    database script. I can modify here database name, rights etc.
Is it possible  to run this script from ASP.NET application?
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
All you need to do is construct and run a T-SQL command, so yes, ASP.Net works.
0
Mark WillsTopic AdvisorCommented:
Well, got the "Help Close" email and got me to thinking (dangerous as that is).

1) copy your MDF and LDF to your desired location.
2) You can create a DB via ASP.Net specifying the names and locations of above - add the 'FOR ATTACH' clause when creating
3) If SIMPLE recovery mode, then just copy the MDF and use 'FOR ATTACH_REBUILD_LOG'
4) you can then run scripts

If using SQL Server Express, there are a couple of "gotchas" from https://social.msdn.microsoft.com/Forums/sqlserver/en-US/afdf71b3-213f-473c-8047-32387dd8faf3/how-to-deploy-mdf-files?forum=sqlexpress 
The SQL Express service account is Network Service, which does not have permissions to attach database from the user profile directory where your database is located. (i.e. My Documents) You have a couple options:

Use a User Instance by adding User Instance=True to your connection string. This is a special type of instance tha SQL Express supports that runs a separate SQL process in your user context and has permissions to your profile directories. (Check out the User Instance white paper for more info)
Move the database to a different directory where Network Service can see it, for example the Data directory for the server.
Give Network Service permissions to your profile directories. This probably isn't the best idea, since any service that runs as Network Service will now have permissions to your folders.
It really depends on how you're using this database. If this is a single user application and you are deploying the mdf file with the application, then you should probably be using User Instances, otherwise, I'd opt with #2 and install the database to a common directory.

Regards,

Mike Wachal
SQL Express team

Both SQL Server and SQL Server Express require installation before you begin. And SQL Express has a 'special' instance name of "SQLEXPRESS" by default and will impact your connection string..

If running just as a standalone / private version, then SQL Compact Edition might serve your requirements - it can be part of your package and doesnt require pre-install, and can pre-populate. https://www.microsoft.com/en-us/download/details.aspx?id=17876

Thats not to discredit any of the other experts in this thread, but I needed to clarify your options.

Now, is there any reason why you cannot close ? Or anything more that any of the experts in this thread can help you with ?
2

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
YampolkaValeriya YampolskaAuthor Commented:
Thank you , Mark
It was extremely  helpful , you provide detailed instructions..
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.