Backup of ROB-EX Multiuser Server database

If you have SQL Server Management Studio installed we recommend that you use it for creating database backups (right click on database and select Tasks->Backup). The explanation below can be used in case you use MS SQL without any management options available, or you are scheduling daily backups from a .bat command script.

Create a database backup

Execute the BACKUP DATABASE statement to create the database backup, specifying:

• The name of the database to back up.
• The backup device where the database backup will be written. To backup to a disk file a logical backup device needs to be created.

Optionally, specify:

*The INIT clause to overwrite the backup media, and write the backup as the first file on the backup media. If no existing media header exists, one is automatically written.

• The SKIP and INIT clauses to overwrite the backup media even if there are either backups on the backup media that have not yet expired, or the media name does not match the name on the backup media.
• The FORMAT clause when using media for the first time to completely initialize the backup media and rewrite any existing media header. The INIT clause is not required if the FORMAT clause is specified.
  

Important Use extreme caution when using the FORMAT or INIT clauses of the BACKUP statement, as this will destroy any backups previously stored on the backup media.

Example

This example backs up the entire GanttMultiuser database to a disk file:

osql.exe -U ganttAdmin -P -Q
  "BACKUP DATABASE GanttMultiuser TO DISK = 'c:\temp\GanttMultiuser.bak' WITH INIT"

The above line assumes no password is needed for the user ganttAdmin. The above line can be executed from the Windows scheduling utility e.g. every night. It is then important that the file 2 c:\temp\GanttMultiuser.dat is backed up by the normal backup procedure.

To restore a database backup

*The system administrator restoring the database backup must be the only person currently using the database to be restored. If this is not the case see below for a solution.

To restore the database from the file c:\temp\GanttMultiuser.bak execute the following command:

osql.exe -U ganttAdmin -P -Q "RESTORE DATABASE GanttMultiuser FROM DISK = 'c:\temp\GanttMultiuser.bak'"

The above line assumes no password is needed for the user ganttAdmin

The restore may in some cases fail if unable to gain exclusive access to the database. This happens if another user is already connected to the database. If this happens one way to solve the problem is by first killing all connected users, next put the database in single user mode (to avoid new users to re-connect). Do the restore and finally put the database back in multi user mode again

/* To get list of connected users */
select spid from master..sysprocesses where dbid = db_id('GanttMultiuser') and spid <> @@spid
go

/* Now kill the user - id's returned from above statement should be used */
KILL 54
go

/* Next put database in single user mode */
ALTER DATABASE GanttMultiuser SET SINGLE_USER WITH NO_WAIT
go

/* Now do the restore .....*/

/* Finally put the database back in Multi User mode */
ALTER DATABASE GanttMultiuser SET MULTI_USER WITH NO_WAIT
go

Shrinking transaction log file

This paragraph explains how to shrink the transaction log file if it is getting too big. Early versions of ROB-EX Multiuser database installed the GanttMultiuser database with the “Full” recovery model of SQL Server, which will tell SQL Server to create transaction log files. If the transaction log is not being backed up and shrunk, it may eventually end up filling up the entire disk space.

New version of ROB-EX Multiuser database is installed using the “Simple” recovery model, which is not using transaction log files. Below is explained how to shrink a transaction file being too large and subsequently changing to use the “Simple” recovery model in the future.

First you must stop ROB-EX Multiuser Server to ensure that not clients are connected to the database.

Next start the osql utility by selecting “Start/Run…” and enter the command “osql -U sa”

Enter the sa password (typically empty) and then enter the following commands in the sequence specified below:

USE GanttMultiuser
DBCC SHRINKFILE (GanttMultiuser_log, TRUNCATEONLY ) go BACKUP LOG GanttMultiuser WITH TRUNCATE_ONLY
go

/* Select simple recovery model - e.g. no transaction log file */
ALTER DATABASE GanttMultiuser SET RECOVERY SIMPLE
GO
exit

The following command (stored procedure) can in the osql utility program be used to query the current status, log file names etc. for the GanttMultiuser database.

exec sp_helpdb GanttMultiuser
go 
exit