How to clone SQL server database for Testing purpose.
generate SQL scripts
Create Azure SQL database server first.
- Connect to your SQL server using Microsoft SQL Server Management Studio.
2. Right click on the server and click on the “Generate Script” and it will generate the “Introduction” page of the wizard process.
3. Click on the Next Button to choose the SQL objects.
4. Choose the “Select specific database objects” option and choose the SQL objects you need to generate scripts and click Next button.
5. Click on the “Advanced” button to show the options before you finally start the generate script process.
6. Select the options as show below.
A. Select option ‘Script DROP and CREATE’ for Script DROP and CREATE.
B. Select option ‘Schema and data’ for Types of data to script.
C. Select ‘True’ for Script Indexes.
D. Select ‘True’ for Script Triggers
7. Provide the path for the script as shown below.
8. Click Next and then it will generate the SQL scripts as shown below.
9. Create a new database server on your new Database on new DB Server. For example: DNServer_Cloned1
10. Add “use DNServer_Cloned1;” SQL command on the top the script generated earlier and save the file if your database is not in Azure Cloud. If you are working on Azure SQL Cloud. then you are not allowed to use the above statement.
In Azure cloud case, you select the newly selected database server and just click on new query button.
11. Check the Syntax (click on Parse button) of the Script before executing the whole script against the newly created database server.
12. “Commands completed successfully” log means your script does not have any syntax error and is likely to run successfully.
13. Click on the execute button now to run the full script. You might see error about drop SQL commands but run execute button again.
Your database has been cloned successfully.
