Backup: Some facts

Well, backup is one of the most mandatory duty of database administrator. Let's first answer a couple of backups features:

• If the database contains a small amount of data, but it takes a lot of disk space on the disk, than the backup will be small, as only used pages are backed up, unused pages are skipped
• Every administrator should have a backup and recovery plan and from time to time an exercise should be done. The goal of the exercise is to test if all required factors are fulfilled to successfully restore the database
• To perform backup of the database you need to have permissions. The database owner (dbo). Of course it is enough to be aliased to the dbo. That way sa role is also capable of performing backup of any database. Additionally every member of the server role oper_role also has right to dump any database. Assuming there is login named backup_login, you can assign it to oper_role using following command:
  
  

  sp_role "grant" , oper_role, backup_login

  
  or using the newer syntax:
  
  

  GRANT ROLE oper_role TO backup_login
  
  

• Backup can be done while the database is in use, so generally backups should be performed quite often depending on the load on the server, the number of transactions, the importance of the data stored in the database. Backup increases the load on the server (CPU and disk) ~5%
• Many databases are being backed up once a day. Additionally also incremental backups are performed because this protects transactions that did not get backed up during the day and additionally this frees space in the transaction log. So depending on the number of transactions and the size of log, maybe even more incremental backups will be required.
• The restore time can be much longer than  the backup time. The backup process just takes all the used pages and puts them in the backup file. When restore is done, first of all the database needs to be created (page initialization time), next the backup needs to be loaded, next every transaction log backup needs to be loaded and processed. To speedup the restore, database could be created with the FOR LOAD option. This option creates database, but the initialization part of that process is not performed.
• To automate backups component of Sybase SAP ASE could be used: Job Scheduler. DBA may decide to use other external tools like cron. Another moment when a backup may be performed automatically is, when the free space in the transaction log falls below some limit. This is called a threshold and it can be defined by the DBA individually
• To create a database backup use DUMP DATABASE command. This command backups not only the database but also the active part of transaction log. 
• To create transaction log backup use DUMP TRANSACTION command.