How to regenerate a DB with original name using SQL script

Question

0.00/5 (No votes)

See more:

SQL-Server-2014

I generated a database schema script for a particular database from one MS SQL Server. I intended to create the exact same copy on another MS SQL Server. But when i ran the script in SSMS of the target system, it modified the master database with all the tables instead of creating a separate database with original name.

What is going wrong? Am i creating the script the wrong way or regenerating the database the wrong way?

Kindly help

What I have tried:

MS SQL Server 2014 -> SSMS -> File -> Open -> File -> Choose .sql script -> Open -> Execute

Posted 4-Jan-18 11:03am

marjaan

Updated 4-Jan-18 23:56pm

v2

Add a Solution

Comments

RedDk 4-Jan-18 18:30pm

You'll have to edit the generated script to target an existing SCHEMA on the target system. And this, only assuming, that any "generation" also included CREATEs for all the tables and stoprocs/etc where the genesis is to take place.

Master will serve to contain the generation (by default). Under schema ".dbo".

But that might not be where you want it.

marjaan 4-Jan-18 23:50pm

I think I should not need to edit the script, I am just not setting the proper options in Advanced options before generating the script. Just can't figure out which option has excluded the creation and then usage of that database, so that the generated script had to create all tables in master. Any thoughts on that?

Karthik_Mahalingam 4-Jan-18 23:08pm

use Reply button, to post Comments/query to the user, so that the user gets notified and responds to your text.

marjaan 4-Jan-18 23:49pm

Oh ok. Why deleted comments are still shown to me? It's a bit confusing.

Maciej Los 5-Jan-18 1:54am

It's because, you may want to restore them.

1 solution

Add a Solution

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

marjaan · Accepted Answer · 2018-01-04T23:56:00

I got around by creating the script setting the Script USE DATABASE option's value to True. It created the use [DBName] statement in the generated script. Then on target system, before executing this script, I simply created a new database with exact same name as the original one and then executed the script.

How to regenerate a DB with original name using SQL script

1 solution

Solution 1

Add your solution here

Preview 0

How to regenerate a DB with original name using SQL script

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us