Backup of PALSTAT CAQ MSSQL

Introduction

The following procedure is only a recommendation and application may differ depending on the version of Management Studio or the version of MSSQL itself. This procedure was developed and tested on MSSQL2008R2. In addition, the document contains a sample batch file that can be used for free versions of MSSQL EXPRESS  and serves only as a sample template and not as a fully functional procedure.

Regularly check the functionality and completeness of backups.

What to backup

Databases can be named differently on the SQL server, depending on how they are created. If an IT company sets up a database, it must know which databases to back up. If the application was set up by PALSTAT, the databases will be named. Related section "Application backup". 

„pal3g_data“ , „pal3g_archiv“ , „pal3g_attach“ , „pal3g_qsd“ or they will contain a suffix containg the name of the company or specifying different versions, eg. „pal3g_data_cvicna“ a „pal3g_data_ostra“

If unsure what to backup properly, please contact PALSTAT.

Where to backup

Backup as you want but, the security and efficiency of a backup is always directly proportional to its price, so it is up to each company to decide how to consistently backup and how much they value their own data. A technical accident will certainly happen before an audit and not during a company-wide holiday.

Design of possible backup and operation of databases:

• Operate data on MSSQL in RAID the choice of a suitable solution is up to the company itself. As a minimum, use RAID1 MIRROR (http://cs.wikipedia.org/wiki/RAID)
• Perform daily backups(they may be more frequent) and store them on another disk cluster. Regularly move backups to another device, eg once a week copy a backup to a separate machine or NAS. Keep backups a minimum of 1 week back and once a month store long-term backups (External HDD, Cloud, DVD, BLURAY, Tape)

Backup tips can be found here. http://cs.wikipedia.org/wiki/Z%C3%A1loha_(informatika)

Idiom

Better SAVE than sorry

We backup using SQL Server Agent

The server agent is only available with the full version of MSSQL and is not present in EXPRESS versions. When using the EXPRESS database to run the system, use batch backup, which is decribed in the following section of this doument, "Backing up in". 

What to backup

Created JOB

 

Create a JOB and set the basic data.

Set steps for JOB

Insert the individual steps of the JOB, each step can backup one database. Below is a sample of the script in the "command" section, of course it is necessary to modify it for a specific environment (highlighted) 

BACKUP DATABASE [nazev_databaze] TO  DISK = N'C:/BackupMSSQL/nazev_databaze.bak' WITH NOFORMAT, INIT,  NAME = N'nazev_databaze-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

Setup startup scheduling JOBS

Set how often the backup should runand at which time.

Detailed settings

The steps described are the basic settings for creating a backup. However, additinal details such as warnings and notifications for certain backup states can be set.

Ex.

http://msdn.microsoft.com/en-us/library/bb934498.aspx

http://msdn.microsoft.com/en-us/library/ms174173.aspx

http://www.google.com

 

We backup using batches

This type of backup is usually required for EXPRESS versions of MSSQL, but can also be used for sharper versions of MSSQL.

Below is the content of the batch file, which again must be modified for specific use. The batch itself must then be run regularly from the task scheduler.

cls
@ECHO OFF
REM Batch to move database backup to the backup server, backup once a day BAT must run scheduled tasks
REM define a backup password in each part of the backup
REM mapping the drive for backup
net use w: [IP MACHINE/NAME MACHINE]/backup


rem ATTENTION parsing DATE may differ depending on the national environment over which it is run
rem format data je Tue 09/07/2010
set date new=%date:~11,4%_%date:~7,2%_%date:~3,2%

REM define the path where to backup
set savedir=w:/%datenovy%/firm/

REM temporary directory on the local machine to create a backup
set tempdir=c:/BackupMSSQL
echo Zalozeni adresare %savedir%
mkdir %savedir%


set dbname=palstat_archiv
echo zaloha databaze %dbname%
"C:/Program Files/Microsoft SQL Server110/Tools/Binn/SQLCMD.EXE" -S FIRM -U pal -P [HESLO MSSQL] -Q "BACKUP DATABASE %dbname% TO DISK = '%tempdir%%dbname%.bak';" > %savedir%%dbname%.log

set dbname=palstat_attach
echo zaloha databaze %dbname%
"C:/Program Files/Microsoft SQL Server110/Tools/Binn/SQLCMD.EXE" -S FIRM -U pal -P [HESLO MSSQL] -Q "BACKUP DATABASE %dbname% TO DISK = '%tempdir%%dbname%.bak';" > %savedir%%dbname%.log

set dbname=palstat_data
echo zaloha databaze %dbname%
"C:/Program Files/Microsoft SQL Server110/Tools/Binn/SQLCMD.EXE" -S FIRM -U pal -P [HESLO MSSQL] -Q "BACKUP DATABASE %dbname% TO DISK = '%tempdir%%dbname%.bak';" > %savedir%%dbname%.log

set dbname=palstat_qsd
echo zaloha databaze %dbname%
"C:/Program Files/Microsoft SQL Server110/Tools/Binn/SQLCMD.EXE" -S FIRM -U pal -P [HESLO MSSQL] -Q "BACKUP DATABASE %dbname% TO DISK = '%tempdir%%dbname%.bak';" > %savedir%%dbname%.log

move /Y %tempdir%*.bak %savedir%



rem for /f "tokens=1-3 delims=/ " %%a in ('doff mm/dd/yyyy -12') do (
rem set datedelete=%%c_%%a_%%b)

rem echo Mazani adresare se starou zalohou w:%datedelete%
rem rmdir /S /Q "w:/%datedelete%"
rem nekdy nefunguje a treba ted to smazalo vsechny zalohy

 

Application backup

 

The application part, including documentation and configuration (APL, DOC, CONFIG, SETUP) can be backed up simply copying. All you have to do is copy the old version to another data storage when performing any upgrade. The application itself does not represent a big problem for the operation of the company, because in the event of deletion or other damage arising from an accident, it can be re-delivered by PALSTAT s.r.o. 

Pay special attention to the backup of the application part only in the CONFIG directory where it is defined how and where the application should connect and there may be some finer or more specific settings for each company. Therefore, backup this directory with the same priority as the database itself.

 

Location	Name	Content	Key to operation	Recommended min. backup frequency	Notes	If not backed up
SQL Server	Database Palstat CAQ	Data	YES	Daily	See. Database backup manual	Unable to restore
	Specific customer settings	Various	NO	After instalation After changing settings	SQL user accounts, Linked servers, Batch Jobs, if they were created beyond the standard installation, eg for th purpose of connection with another system	Can be set manually (detailed information is required)
Instalation Palsat CAQ	CONFIG/config.xml	Customer system configuration	NO	After installation After changing a file		Can be set manually (it is necessary to know the settings)
	CONFIG/database.enc	Database connection configuration		After installation After changing a file		Can be set manually (it is necessary to know the connection paramters)
	CONFIG/keys.enc CONFIG/licence.enc	License files	YES YES	After installation After license changes		Provided by PALSTAT
	CONFIG/extensions.xml	Information on documentation	NO	Only in case of changes by the customer		Can be set manually (it is necessary to know the settings)
	APL/*.* DOC/*.* SETUP/*.* SUPPORT/*.*	Supporting documentation	YES NO NO NO	After installation After actualization		Provided by PALSTAT
	APL/Drivers*.* APL/Imp3D.dll		Customer drivers, connection to measuring devices NO	After installation After actualization		Provided by PALSTAT

 

Check

Regularly check the functionality of the backups, both by checking whether the backups are being created and by performing database restores to the MSSQL server to verify (under a name other than sharp databases, of course) that the backups are not corrupted and are functional. Scheduled tasks can be paused for any reason (MSSQL agent or task scheduler does not start), which can lead to loss of regular backups.