Making a Copy of an Existing DB in SQL using SQL script

Question

Making a Copy of an Existing DB in SQL using SQL script

vimalmv

SSC Enthusiast

Points: 113
More actions
June 2, 2019 at 7:01 am

#3649399

We need a script by which a source DB, the name will be supplied, to be copied into another DB with another name and also create users who have access only to the new DB.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99130 More actions · Answer 1

I assume, therefore, that none of your objects are in Source Control for you to easily create a script from? Have you searched for any solutions to do this, there are plenty out there. For example if you Google "script all objects sql server" the first result you get is Tutorial: Script objects in SQL Server Management Studio. Why did that not provide the solution you need? What was missing from the attempts you made?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 2

You could backup the database then restore it to a different name.

then all you need is a script to drop the existing users and create new ones.

vimalmv SSC Enthusiast Points: 113 More actions · Answer 3

Dear Jonathan,

Thanks for your replay. Actually, I want these are done by a single click. Is it possible ?

Thom A SSC Guru Points: 99130 More actions · Answer 4

vimalmv wrote:

Dear Jonathan, Thanks for your replay. Actually, I want these are done by a single click. Is it possible ?

In a word; no. Considering that a backup and restore appears to be "too many" clicks, then any other solution will be too many clicks as well; as you'd need to use the prompts, or install the scripts/software/etc.

If you wanted a "one click" solution then your database should have been in a source control with some kind of migration scripts created; then you could just run the script on the new database. If this is something you're going to doing again in the future, then I suggest investing time to get your database source controlled.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Tom Uellner SSCrazy Points: 2995 More actions · Answer 5

vimalmv wrote:

Dear Jonathan, Thanks for your replay. Actually, I want these are done by a single click. Is it possible ?

You can get close writing a PowerShell script using dbatools Copy-DbaDatabase.

https://docs.dbatools.io/#Copy-DbaDatabase

I don't know if that moves the users or not. You'll need to do that research. There are many useful utilities in dbatools and I'm sure you could put together what you need from the information on the site.

Sue_H SSC Guru Points: 90860 More actions · Answer 6

vimalmv wrote:

Dear Jonathan, Thanks for your replay. Actually, I want these are done by a single click. Is it possible ?

Then do it all using sqlcmd. There are no clicks from the command line. One of the differences is you would need to know what you each command or statement actually does. And you would need to know that anyway for troubleshooting so essentially, no difference. And then you have no clicks and can do what Jonathan suggested.

Sue