Copying a live Sage 200 database to a different company

Overview

This guide covers creating a copy of a live Sage 200 SQL database and restoring it to either a new SQL database or over-writing an existing database. You may be doing this to create a Sage 200 company which is a year end backup, or you may be refreshing the data in a test company.

Much of the work for this is done in SQL Server Management Studio, which is normally only installed on the SQL Server. Only the final step is performed in System Administration.

Requirements

• Login for SQL Server Management Studio.
• Login for System Administration.

Steps

• Backup the existing database
• Over-write the existing database or create a new database
• Update the company in System Administration

Backup the existing database

Log in to Management Studio. Expand Databases, then right-click over the database name and choose Tasks, Back Up.

In the Destination section, ensure that Back up to is set to Disk. If you are not offered a default path and file name below this, you can click Add to set the path and file name. Ensure the file name ends in .bak.

If you are offered a default path and file name, remove this, and use the Add button to create a specific backup file. This avoids any complications with backup sets. Again, ensure the file name ends in .bak.

Optional step: click Media Options and put a tick against Verify backup when finished.

Click OK. The backup will now run to the file specified.

Restoring the backup to a new database

Right-click over Databases in the left-hand menu and choose Restore Database.

Under Source, select Device then click the ellipsis button to select the backup file.

At this point, the Destination field will automatically be populated with the name of the original database – it’s been read from the backup file.

Change the Destination Database as required – here it’s called Sage2002017-MFG-restore.

Click on the Files option. Click into the Restore As box and verify that the file names have changed to reflect the new database name.

The logical file names will stay the same as the source database – this is OK and does not need to be changed.

Click OK.

Once the restore is complete, the company can be set up in System Administration – see step Creating a new company in System Administration

Restoring over an existing database

Right-click over the database you want to refresh/restore over and choose Tasks, Restore, Database. In this example, the database “Sage2002017-MFG-restore.

Under Source, select Device then click the ellipsis button to select the backup file.

At this point, the Destination field will automatically be populated with the name of the original database – it’s been read from the backup file.

Change the Destination Database as required – here, it’s called Sage2002017-MFG-restore.

Click on the Files tab. Click into the Restore As box and verify that the file names have changed to reflect the new database name.

Click on the Options tab. Put ticks against Overwrite the existing database (WITH_REPLACE) and Close existing connections to the destination database.

Once the restore has completed, the company needs to be updated in System Administration – see step 3.5 if the company already exists in System Administration, otherwise follow the next step to create the company.

Creating a new company in System Administration

Please note - you will only be able to do this if you are a member of the Sage200Admins group in Active Directory.

Launch System Administration. Navigate to Companies. Right-click over Companies and choose Add New Companies.

An attachment folder is a prerequisite when adding a new company.

If you have existing companies, you can create an attachment folder in the same location ready for when adding the new company.

Double click on any of the existing companies and highlight the contents of the attachment folder.

Press Ctrl & C to copy, or right click over the highlighted text and select Copy.

Open Windows File Explorer either from your toolbar or via your Windows button by typing This PC.

Click at the end of the address bar to highlight the contents.

Delete this and then press Ctrl and V to paste in the attachments path copied previously and press enter.

Click on the attach or attachments part of the address to go back to this level.

Right click in the white space at the bottom of this window and select New, then Folder.

Enter the new folder name for the company you are creating, this can be abbreviated if required.

Once the new folder has been created double click on it.

Right click at the end of the address bar and copy the contents.

You can then open Notepad and paste this in to copy from when creating the new company, or just move on to adding this new company and pressing Ctrl & V in the attachments field.

Enter the details of the new company in the following fields:

Company Name: Name of the new Sage 200 company as displayed when logging in to Sage 200.

Parent Company: Only applicable if using Consolidation in the Nominal Ledger.

Attachments: Paste the copied attachments folder path by using Ctrl & V, or right clicking and selecting Paste.

If you haven’t already created an attachments folder, go to the Sage server, locate the folder where Sage is installed and create a new attachment folder. There is no naming convention for the attachments folder that must be adhered to but you may have your own that you wish to follow.

Once the attachments folder is created, you can click on the ellipsis beside the Attachments folder and browse to it.

Server: Enter the name of the SQL Server or click on the browse button to select it from a list of available servers. Please contact your IT department if you are unsure which SQL server name to provide.

Database Name: Type in the name of the database as it will be displayed in SQL.

Click Test, then click Update. Click OK.

Please remember to assign access to this newly created company to the users who require it (double-click on a name in the Users list and go to Company Access).

Updating the company in System Administration

Please note - you will only be able to do this if you are a member of the Sage200Admins group in Active Directory.

Launch System Administration. Navigate to Companies. Right-click over the company you have restored.

Click Update.

This should only take a few minutes. Once it has completed, the test company can now be used.