Script to Copy SQL db to new name in same instance

1

I am fairly new to SQL scripting:
server 2008R2
MSSQL 2014
Within the same SQL instance I am trying to find a way to copy SQLDB1 to a new DB with a new name. I need this to be in a script and not via the copy wizard

2

If you only need the database schema/structure, and you have the service packs applied, DBCC CLONEDATABASE is the easiest method:

https://support.microsoft.com/en-us/help/3177838/how-to-use-dbcc-clonedatabase-to-generate-a-schema-and-statistics-only-copy-of-a-user-database-in-sql-server-2014-sp2-and-sql-server-2016-sp1

If you require the data as well, then backing up and restoring the database is the simplest method:

https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql
https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql

Example:

BACKUP DATABASE myDB TO DISK='E:\BackupFolder\myDB.bak' WITH COPY_ONLY, CHECKSUM;

RESTORE DATABASE myOtherDB FROM DISK='E:\BackupFolder\myDB.bak' WITH
MOVE 'Data_File' TO 'E:\DataFolder\MyOtherDB.mdf',
MOVE 'Log_File' TO 'L:\LogFolder\MyOtherDB.ldf'
3

What's your objective?

One time? Repeat often / unattended? A "self installation kit"?

4

DB is a software database that we need to have our IC team be able to customize for clients, once customization is complete the script will create a copy of the DB (ENTIRE), then I already have scripted the backup of the original or TEMPLATE db, the deletion of custom DB takes place and then the original is restored so they next customer system can be built.

Jamey L. Crider
Senior Network Analyst
Settlement Management Solutions

200 Commerce, Suite 100 | Irvine, CA 92602
253-278-4017 Office
jcrider@smscorp.commailto:yourname@smscorp.com
[Description: cid:image001.png@01D1E41B.F2944BF0]

“Have courage for the great sorrows of life and patience for the small ones; and when you
have laboriously accomplished your daily task, go to sleep in peace. God is awake.”
Victor-Marie Hugo 1826

5

You just need a BACKUP of the "template DB" and then restore from that backup file to get back to Ground Zero.

Make sure nothing deletes the backup file - if you just use a regular backup file there is the risk that some Housekeeping Maintenance purges it after X-days ! Copying it to a safe location should do the trick.