How to create database from SQL script.

Question

0.00/5 (No votes)

See more:

Dear Sir,
After creating script from database in MS SQL server 2008,if we want to run all the database in other PC,then we can crate Database using SQL script in SQL server.
In reverse order to create database from given script.
How to create the SQL Database from SQL script in MS SQL server 2008.

Please give steps or links.

Posted 9-Aug-12 21:20pm

hiteshprajpati

Updated 28-Aug-17 2:06am

v2

Add a Solution

3 solutions

Solution 2

Well basically if you have scripted the whole database on one server, you can take that script and run it on another server for example in master database.

The full script should contain the database creation (CREATE DATABASE...) and after that all necessary object creations into that database.

To run the script, you can use for example SSMS (Sql Server Management Studio)

Posted 9-Aug-12 21:29pm

Wendelius

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Santhosh Kumar Jayaraman · Accepted Answer · 2012-08-09T21:27:00

Solution 1

SQL

CREATE DATABASE [test]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'test', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\test.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'test_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\test_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

or simply just

SQL

CREATE DATABASE [test]

Posted 9-Aug-12 21:27pm

Santhosh Kumar Jayaraman

Updated 9-Aug-12 21:28pm

v2

Comments

hiteshprajpati 10-Aug-12 3:43am

Thank you sir for your reply,
Using CREATE Database [test]
Database is created but the inside Tables are not there, is there any other procedure for creating table inside that Database.

Santhosh Kumar Jayaraman 10-Aug-12 3:44am

You want the script for all tables in a database right?

hiteshprajpati 10-Aug-12 7:01am

Hello Sir,
I have script now from Script I need all the database with tables.
In MS SQL server 2008.
Thanks.

hiteshprajpati 10-Aug-12 7:02am

Now I have tried SQL create command to create Database from given sql script.
I have tried as follows:
-------------------------------------------------
Create DATABASE [datauwatchit]
ON PRIMARY
( NAME = N'datauwatchit', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\dbuwatchit.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

LOG ON
( NAME = N'datauwatchit', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\dbuwatchit_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
-----------------------------------------------
Error is as follows:
Msg 1828, Level 16, State 5, Line 1
The logical file name "datauwatchit" is already in use. Choose a different name.
==========================================================
So I have tried Alter command to solve above errror
------------------------------------------------
ALTER DATABASE [$(datauwatchit)]
ADD FILE
(
NAME = [$(dbuwatchit)],
FILENAME = '$(C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\datauwatchit.mdf)$(datauwatchit).mdf',
SIZE = 10 MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 5 MB
)
TO FILEGROUP [PRIMARY];
------------------
Error is as follows:
Msg 911, Level 16, State 1, Line 1
Database '$(datauwatchit)' does not exist. Make sure that the name is entered correctly.
=====================

Please reply where is problem and how to solve in code.

Santhosh Kumar Jayaraman 10-Aug-12 7:03am

How did you get ??Like solution 3?

Santhosh Kumar Jayaraman 10-Aug-12 7:05am

You have to change the mdf and ldf filename.. Add \dbuwatchit1.mdf or \dbuwatchitnew.mdf like this

hiteshprajpati 10-Aug-12 7:39am

Using command "CREATE DATABASE [Test]" database is created but there is no Tables in this database.
I have .sql script,from which I need Database with all the tables.
Is there any other way to do so?

Santhosh Kumar Jayaraman 10-Aug-12 7:40am

Solution 3 is the other way. it will generate script for database creation as well as all tables creation togther.. mark it as solved, if you are able to generate script via solution 3

hiteshprajpati 11-Aug-12 0:34am

Thank you sir for your help, finally with your help and kind support I solved my problem.

Santhosh Kumar Jayaraman · Accepted Answer · 2012-08-09T21:49:00

If you want to generate script for all tables from a database, then you have to do as below.

1. Go to Object explorer. Select the database which you want to generate script. Right click it.

You can see Tasks. click on tasks.. There you can see Generate scripts below restore. click that. it will take you to wizard. In the wizard you can select the tables which you want to generate. By default it will generate script for all tables and that database. Select a path and click on next until finish enables. Then click on finish. Go and check the script in the path you mentioned in the wizard. It will have create script for database and as well for all tables.