Restore a Database

 

Back Office > Administration > SQL Tools > Database Maintenance > Restore Database

 

Description

 

This menu item is used to restore SwiftPOS SQL databases from a backup file.

 

To Be Considered

 

  1. Note : You cannot restore a database while SwiftPOS services are running. Please ensure all services are stopped. If unable to open SwiftPOS Back Office, then the only way to stop the services is to end their respective tasks via Windows Task Manager.
  2. It is important to check that the correct database has been selected to be restored. This can be checked by viewing the Backup Information.
  3. If the backed up database is a ZIP file, then first ensure the .bak file is extracted to a working directory before restoring.
  4. It is possible to restore a database to a different location and with a different database name. The file name of the restored file will remain the same as the backup file name (ie. SWIFTPOS.MDF) but the new database name will appear in SwiftPOS Back Office > SQL Tools. Note : that performing such an action will result in the restored database being unregistered.
  5. It is not essential to close down the SwiftPOS Back Office application prior to performing a backup. Use of the software is allowed while a backup is in progress.
  6. It is not possible to restore a SQL Server 2014 database onto a PC that has got SQL Server 2008 installed. Restoring to an older version of SQL Server is not possible.
  7. If you keep the database name the same and the folder the same, then there will be no change required to your existing Software Registration. Eg: C:\SwiftPOS\SWIFTPOS.MDF with an SQL database name of SWIFTPOS.
  8. If your restored database is identical to your currently registered database then all you need to do is import your previous SwiftPOS Registration into the database that you have just restored.

 

Restore Database

 

 

Note : Make sure you stop all SwiftPOS services before restoring a database. Do this by "right clicking" on the SwiftPOS Service Monitor in the system tray and "stop all services and exit".

 

  • Database Name - Displays/Enter the name to be assigned to the database being restored. The default is SWIFTPOS.
  • Backup File Location - Select the Browse button to change the location path to that in which the backup file will is located.
  • Destination Path - Select the Change button to change the default location path 'C:\SwiftPOS' to which the backup file will be restored to.
  • Backup Information - The information displayed here relates to the Backup File selected in the Backup File Location field above.
  • Restore Now - Select to commence the Restore.
  • Close - Select to exit.

 

Databases can also be restored using SQL Server Management Studio.

 

Restore a Database using Microsoft Management Studio Express

 

 

Note : Make sure all SwiftPOS services are stopped before restoring a database.

 

Using SQL Server Management Studio you can restore the databases. For example : the SwiftPOS Back Office database or the SwiftPOS Table Tracking database (You may wish to do this when moving a Table Tracking database from one PC to another).

 

  1. Expand Databases.
  2. Right click on the database to be restored.
  3. Select Tasks > Restore > Database.
  4. The Restore Database screen is displayed.

 

For information on how to restore a database using SQL Server Management Studio ...

 

Scheduling a Task to Backup a Database each day of the week

 

  1. First create a new batch file named SwiftPOS-BackupWeekDay.bat and insert the following code :

OSQL -E -Q "DECLARE @Now as char(17) DECLARE @Filename VARCHAR(200) SELECT @Now = DATENAME(dw,GETDATE()) SELECT @Filename = 'C:\SwiftPOSConnect\SwiftPOSConnect-' + RTRIM(LTRIM(@NOW)) + '.bak' BACKUP DATABASE SwiftPOSConnect TO DISK = @Filename WITH FORMAT"

OSQL -E -Q "DECLARE @Now as char(17) DECLARE @Filename VARCHAR(200) SELECT @Now = DATENAME(dw,GETDATE()) SELECT @Filename = 'C:\SwiftPOSConnect\TableTracking-' + RTRIM(LTRIM(@NOW)) + '.bak' BACKUP DATABASE TableTracking TO DISK = @Filename WITH FORMAT"


OSQL -E -Q "DECLARE @Now as char(17) DECLARE @Filename VARCHAR(200) SELECT @Now = DATENAME(dw,GETDATE()) SELECT @Filename = 'C:\SwiftPOS\SwiftPOS-' + RTRIM(LTRIM(@NOW)) + '.bak' BACKUP DATABASE SwiftPOS TO DISK = @Filename WITH FORMAT"


  1. This will create a backup file named SwiftPOSConnect-Monday.bak in the C:\SwiftPOS folder. The name of the backup file created will change with each day of the week.
  2. Now open Microsoft Scheduled Tasks and create a new schedule to run the newly created batch file at the times you prefer. For information regarding scheduling a task in Windows ...

 

 

Exporting Binary Files out of an SQL Database. BCP - Bulk Copy

 

This may be required if SwiftPOS requests some data files from an installation. Also this is the only way to convert a data file from SQL Server 2014 to SQL Server 2008 if required.

If you run the SQLCMDUtilities.exe application you get an option to export the data out to binary files that are much smaller than the size of the SQL database. This is an easy way to get a backup of a customer's database that may be too large to transfer if you were to just backup the SQL database.

 

 

  • BCP Location - Find the path to the file on your installation of SQL Server. This is normally something like "C:\Program Files\Microsoft SQL Server\100\Binn\bcp.exe" or  "C:\Program Files (x86)\Microsoft SQL Server\100\Binn\bcp.exe".
  • Export Folder -Select your Export folder where you want all the files put.
  • Server - Displays/Enter the name of the SQL Server. Enter localhost if the SQL Server is running on the local PC.
  • Database - Displays/Enter the database name. The default database name is SWIFTPOS.
  • User Name - Displays/Enter the user name. The default user name is sa.
  • Password - Displays/Enter the password. The default password is swiftpos.
  • Exclude Options - Select to exclude Electronic Journals/Transactions and Stock Movements and Location Stock for a smaller data file.
  • Upload to Samford Software: This will zip up the files and put them in a special folder on the server ftp://ontime.swiftpos.com.au/UPLOADS/  Server.

 

Steps for doing the bulk import:

 

  1. Make sure that the database version is the same as the file you are importing. This is important because it will add any fields that are not present in the database.
  2. Create a blank database.
  3. The under Program Files (x86) or just Program Files>Samford Software>SwiftPOS Back Office and run the Swiftpos.SQLCMDUtilities.exe.
  4. The BCP Location should be in either C:\ProgramFiles\Microsoft SQL Server\100Tools\Binn or in the Programs Files (x86) folder.
  5. The Import Folder is the file with all the data you wish to Import.
  6. Selected the Blank Database you created and then Import data after entering the "daily code" from SwiftPOS Support. This protection is to stop people accidently importing into a live database.

 

 

SwiftPOS SQL Command Utilities - Swiftpos.SQLCMDUtilities.exe

 

How to back up a Table Tracking databases in real time for redundancy.

 

Description

 

The Swiftpos.SQLCMDUtilities.exe is an application that is part of the SwiftPOS suite of software and it is designed to create scheduled tasks that can be automated in Windows to occur on a daily basis. For information regarding scheduling a task in Windows ...

 

  1. Example text to add to a file called C:\SwiftPOS\Backup.BAT - When installed on Windows 32bit Operating Systems

"C:\Program Files\Samford Software\BackOffice\SwiftPOS.SQLCMDUtilities.exe" /B1:SwiftPOS /L:C:\Backups "C:\Program Files\Samford Software\BackOffice\SwiftPOS.SQLCMDUtilities.exe" /B1:TableTracking /L:C:\Backups "C:\Program Files\Samford Software\BackOffice\SwiftPOS.SQLCMDUtilities.exe" /R:SwiftPOS "C:\Program Files\Samford Software\BackOffice\SwiftPOS.SQLCMDUtilities.exe" /R:TableTracking


  1. Example text to add to a file called C:\SwiftPOS\Backup64.BAT - When installed on Windows 64bit Operating Systems

"C:\Program Files (x86)\Samford Software\BackOffice\SwiftPOS.SQLCMDUtilities.exe" /B1:SwiftPOS /L:C:\Backups "C:\Program Files (x86)\Samford Software\BackOffice\SwiftPOS.SQLCMDUtilities.exe" /B1:TableTracking /L:C:\Backups "C:\Program Files (x86)\Samford Software\BackOffice\SwiftPOS.SQLCMDUtilities.exe" /R:SwiftPOS "C:\Program Files (x86)\Samford Software\BackOffice\SwiftPOS.SQLCMDUtilities.exe" /R:TableTracking

 

The above examples will backup the SwiftPOS Back Office and SwiftPOS Table Tracking database to the C:\Backups folder. Add the /Z command line option to zip the back up created. For example:

SQLCMDUtilities.exe /B1:SwiftPOS /L:C:\Backups /Z

 

SQLCMDUtilities Command Line Options

 

SwiftPOS.SQLCMDUtilities.exe [/B[:databasename]] [/C:[copy backup to location]] [/L:[backup location]]
[/B1[:databasename]]
[/M:[destination path]]
[/O:[sqlservername]]
[/R[:databasename]]
[/S[:databasename]]
[/P:[password]] [/U:[username]]
[/Z]

/? Display this information.

/B: Initiate a backup of the specified database
Filename format is: yyyyMMddTHHmmss-{Databasename}.bak

/B1: Initiate a backup of the specified database
Filename format is: {Databasename}-{DayOfWeekName}.bak

/BC This option has been removed from v3.10.322.76 and higher.

/BT This option has been removed from v3.10.322.76 and higher.

/C: Specify a location to copy the backup file to.
Note : UNC path security limitations when setting up a scheduled task.

/L: Specify a location to create the backup file in.
Default is to use same path as the MDF database file.
Note : UNC paths ARE not supported by SQL Server.

/M: Move backup file/zip to destination path.
Useful if you wish to move the final backup file to a UNC Path.
Note : UNC path security limitations when setting up a scheduled task.

/O: Override the SQL server to connect too.

/R: Initiate a reindex of the specified database.

/S: Initiate a shrink of the last SwiftPOS Back Office connected database.

/P: Specify a password to login into the SQL server with.

/U: Specify a username to login into the SQL server with.

/Z Compress backup to ZIP file of same name as backup file.
THIS SETTING IS ONLY VALID WHEN RUNNING ON THE SAME MACHINE AS THE SQL SERVER

 

Related Topics

 

  1. Backup a Database
  2. Scheduled Tasks
  3. SQL Server Management Studio
  4. SQL Tools

 

Published 28 Mar 2024