Backup Database

Database backups are taken for safe keeping and for point in time record keeping. There is also another purpose in that you can take disk based database backup to at memory database for faster performance and then backup that in memory database to disk since in memory database are lost once connection closes. There are four major types of backups

  1. Disk to Disk backup.
  2. Disk to In-Memory backup.
  3. In-Memory to Disk backup.
  4. In-Memory to In-Memory backup.

Following is the syntax of Backup statement. The path or schema name must be enclosed in single quote e.g. 'C:\\File Name' and 'Sales'
BACKUP Database [ Path or Schema Name ] AS [Path or Schema Name] ;

/* backup schema 'sdbn'  to disk FileName.db.bak */
BACKUP database 'sdbn' AS 'C:\\FileName.db.bak' ;
/* assuming default schema name 'sdbn' and memdb1 as attached database (schema to schema backup) */ BACKUP database 'sdbn' AS 'memdb1' ;
/* backup file to schema */ BACKUP database 'C:\\FileName.db.bak' AS 'memdb1' ;
/* backup database file to another file */ BACKUP database 'C:\\FileName.db' AS 'C:\\FileName.db.bak' ;

Backup using .Net

The backup statement is same it will only be executed from CommandText with ExecuteNonQuery.

using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("SchemaName=memdb;datasource=file://@memory;"))
    using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(string.Empty, cnn))
        cmd.CommandText = "BACKUP database 'memdb' AS 'C:\\FileName.db.bak' ;";