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

  • 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.

  • 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

  • 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.

  • Dear Jonathan,

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

     

     

  • 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

  • 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.

  • 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

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

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