Admin tasks, by example

ScimoreDB is database, and databases requires maintenance. So, to get you quickstarted, we will walk you through a couple of the most standard tasks.

Backup

Most important of all, backup. You can do backups two ways. Either you can take stop the database server and copy the files in the database folder, or you can use the build in backup command, which takes a snapshop of the database. The last option is the prefered backup method.

Using the manager its simple to backup a database. Right click on the database, and choose backup. If I right click the database "DVD", a query windows with the following command will appear:

        -- Dump a backup of database DVD to the directory c:\backups\DVD        
        BACKUP DATABASE DVD TO SHARE 'C:\\BACKUPS\\'
    

After creating the backup, the files will be located in the folder "C:\BACKUPS\DVD". The folder will contain a schema.sql file, containing sql commands to generate the database schema, and bins files - one each table in the database.

Note that during backup, the database is available for both reads and writes.

Restore

Restoring a database backup is done using the "restore" command. Here is an example where we restore the previously backed up database.

        -- Restore a backup of database DVD from the directory c:\backups\DVD
        RESTORE DATABASE DVD FROM SHARE 'C:\\BACKUPS\\'
    

Rebuild

Rebuild is like a defragement for databases. It should be used if your database files are taking up to much space, relative to the amount of data in the database.

This can occur under many curcumstances. E.g. if you have large transactions on the database, or large amount if insert and deletes that fragments the database.

Here are four different examples of how to rebuild a table, rebuild tables and cached execution plans for the procedures of a database, rebuild only the execution plan for procedures in a database, or everything with on the database instance.

        rebuild table northwind.customers
        rebuild database northwind
        rebuild database northwind procedures
        rebuild all
    

Note that during rebuilding, items are locks for writing.

        rebuild all
    

• Rebuild

Generate statistics

In order for the query optimizer to make good decision on how to execute sql queries it needs statistics on the data in the tables.

Here are three different examples of how to update the statistics.

        generate statistics for table nortwind.customers
        generate statistics for database northwind
        generate statistics for all
    

Either once in a while, or after large modifications, its a good idea to run a "generate statistics" command!

• Generate Statistics

truncate transaction log

If you have been executing large transactions, and your redo log and undo table takes to much space - you want to truncate your redo log and undo table.

        truncate transaction log 
    

The command will shrink redo log and undo table back to the initial size.
Truncating will lock the database.