Tools
Rss Categories

How do I setup the Yearli program to automatically backup the Microsoft SQL Server database?

Views: 1858 Created: 2016-07-21 12:46 Last Updated: 2017-01-10 14:53

SYMPTOM

How do I setup the Yearli program to automatically backup the Microsoft SQL Server database?

RESOLUTION

All Microsoft SQL Server services use an active database connection that always has access to the database files. These instances must be stopped before the data can be transferred or backed up and then must be restarted for Yearli to work.
 
For this to work, the following elements will be required:

  • A batch file named SQLStop.bat
  • A batch file named SQLStart.bat
  • A batch file named backup.bat
  • The Task Scheduler must be enabled

Procedure 1 (Configuring Automatic SQL database backups for Yearli)   

  1. Create the SQLStop.bat file - it needs to contain the following:

Yearli 2014

NET STOP MSSQL$WFP_SS8E

Yearli 2015 or 2016

NET STOP MSSQL$YD2015_SS8E

Yearli 2015 or 2016 (if SQL server YD2015_SS8E does not exist)

NET STOP MSSQL$WFP_SS8E

  1. Create the SQLStart.bat file - it needs to contain the following:

Yearli 2014

NET START MSSQL$WFP_SS8E

Yearli 2015 or 2016

NET START MSSQL$YD2015_SS8E

Yearli 2015 or 2016 (if SQL server YD2015_SS8E does not exist)

NET START MSSQL$WFP_SS8E

  1. Create the backup.bat file - it needs to contain a line to copy the files.  This will vary depending on where database is installed.  The example below will copy the contents of the Data folder to C:\YearliBackup

copy "C:\Program Files or Program Files (x86)\Greatland Corporation\Winfiler Plus or Yearli\20XX\Data\" C:\YearliBackup

  1. Save all three files to C:\.
  2. Access the Windows Run window by pressing the Windows key and R key on the keyboard.
  3. Type "cmd" without the quotes in the Open field.
  4. Click OK.
  5. Type the commands below in the command prompt:

AT 12:00AM /EVERY:MONDAY,TUESDAY,WEDNESDAY,THURSDAY,FRIDAY,SATURDAY,SUNDAY “C:\SQLStop.bat"

  1. Hit Enter.

 AT 12:30AM /EVERY:MONDAY,TUESDAY,WEDNESDAY,THURSDAY,FRIDAY,SATURDAY,SUNDAY ”C:\Backup.bat" 

  1. Hit Enter.

 AT 6:00AM /EVERY:MONDAY,TUESDAY,WEDNESDAY,THURSDAY,FRIDAY,SATURDAY,SUNDAY “C:\SQLStart.bat" 

An explanation:

To back up both the tables and the data they contain, the SQL service must first be stopped on the standalone machine or, for network users, the server.  When run, SQLStop.bat will stop the service.  If the unmodified command line from the preceding paragraph is used the service will be automatically stopped at midnight every day of the week.  Backup.bat will run at 12:30 AM and will copy the data folder to whatever location specified.  SQLStart.bat will restart the service at 6:00 AM every morning, allowing users to login normally.  If necessary, the times Backup.bat will run can be modified at the user’s discretion.

APPLIES TO

  • Yearli 2014 to current