How to create a copy of your database on the same server

This article will explain the procedure required to create a duplicate copy of an ESC database within the same version of SQL Server. This procedure is used when a copy of an existing database is desired for testing purposes or to retain a copy as an seperate database file.

Details:

There are two ways of doing this.

Method 1:

1. From within ESC, click Help → About. Note the name of your server, database, network library and authentication method. Close the About screen when done.
2. Make a backup of your database in ESC.
3. Get everyone out of ESC. If you have other programs accessing the database, such as the ESC Connections Server, stop them as well.
4. If you don't already have it, download and install SQL Server Management Studio Express on your server. It can be downloaded directly from Microsoft's web site using the following link:

https://www.microsoft.com/en-us/download/details.aspx?id=7593

5. Open SQL Server Management Studio Express and log on to your Server using Windows authentication.
6. In the left-hand pane, click the plus sign next to the Database folder.
7. Right-click the folder that has the same name as your database and select Rename. Change the name of the folder to Test (or something similar). The new name cannot have any spaces or weird characters in it. It also cannot start with a number.
8. Create a new database in ESC that has the same name as the old one. This can be accomplished by selecting File → New Company in ESC. See the data you wrote down in step 1 to help you through this. Don't worry about the settings in the setup wizard, these will be overwritten shortly.
9. Once the database has been created restore the backup you made in step one. This is your main company.
10. Click File → Open Company and select the same settings you wrote down in step one. This time, however, change the database name to the one you renamed in step 7. This is your sample company.

Method 2: 

1. Backup up your ESC database and secure this backup in the event you need to restore it.
2. Go to the command prompt screen and use this command to detach your ESC database from SQL Server:
   osql -E -S.\ESC -Q "sp_detach_db @dbname = N'service'"
   Note: In this article we will use the database name of service to illustrate the process but you will need to replace the name service with your exact database name if it is different.
3. Go to C:\Program Files\Microsoft SQL Server\MSSQL\Data folder and find the 2 files that represent the database you want to duplicate:
   Service.mdf
   Service_log.ldf
4. Make a copy of these two files within this same directory:
5. Now rename the copies of the files to a new database name. We will use the name ServiceNEW:
6. Now use the Attach.bat file to reattach both databases into SQL Server. Before doing this, please open the batch file within Notepad and make sure that the database names have been properly modified. Some examples:
   For the Service database:
   osql -E -S.\ESC -Q "sp_attach_db @dbname = N'service', @filename1 = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\service.mdf', @filename2 = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\service_log.ldf'"
   For the ServiceNEW database:
   osql -E -S.\ESC -Q "sp_attach_db @dbname = N'servicenew', @filename1 = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\servicenew.mdf', @filename2 = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\servicenew_log.ldf'"
7. After attaching both databases into SQL Server, you can now launch both companies from the same SQL Server.