A backup or restore may fail because of insufficient SQL Server memory. SQL Backup errors 1000, 1010, 1020, 1030 or 1040 may also be returned. 

How SQL Backup uses SQL Server memory

SQL Server provides a limited amount of memory outside the SQL Sever memory pool for loading stored procedures and OLE objects. SQL Backup uses this memory to create the Virtual Device Interface (VDI), which SQL Backup uses to communicate with SQL Server in order to perform backups and restores:

• When SQL Backup performs a backup, the backup data is written by SQL Server to the VDI. SQL Backup then processes the data and writes the backup files.
• When SQL Backup performs a restore, it reads the data from the backup file and sends it to the VDI  for SQL Server to process.

In order to create the VDI, SQL Backup requires, on average, contiguous memory equivalent to 6 times the MAXTRANSFERSIZE value for each thread used to perform the backup or restore. 

• The number of threads used to create a backup is determined by:
  • The THREADCOUNT option when backing up one or more databases to single files. You can specify this option from the Back Up or Schedule Backup Jobs wizards, or add it to the BACKUP command.
  • The number of DISK arguments when backing up a single database and splitting the backup into multiple files. You can specify this option from the Back Up or Schedule Backup Jobs wizards, or add it to the BACKUP command.
  • The FILECOUNT option when backing up multiple databases and splitting each backup into multiple files. You can specify this option from the Back Up or Schedule Backup Jobs wizards, or add it to the BACKUP command.
• You can specify the MAXTRANSFERSIZE value for creating backups from the Back Up or Schedule Backup Jobs wizards, or by adding the option to a BACKUP command.
• When restoring from a backup, the number of threads used to create the backup and the default MAXTRANSFERSIZE value of 1048576 bytes (1MB) are applied, unless you specify otherwise by adding the MAXTRANSFERSIZE option to the RESTORE command (or change the default value in the registry).

If SQL Backup error 1000, 1010, 1020, 1030, or 1040 is raised during a backup operation, SQL Backup attempts the backup up to 5 more times, using the MAXTRANSFERSIZE values 1048576, 524288, 262144, 131072, 65536 respectively, in order to reduce the amount of contiguous memory required.

Dealing with memory errors

If a backup or restore operation fails with SQL Backup error 1000, 1010, 1020, 1030 or 1040, attempt the operation again, writing the backup to a single file without multiple threads, or restoring from a single file created without multiple threads, and set the MAXTRANSFERSIZE value to 65536. This will reduce as much as possible the amount of contiguous memory SQL Backup requires.

If a backup or restore operation fails despite setting the MAXTRANSFERSIZE value to 65536 and not using multiple threads, it is likely that SQL Server does not have enough contiguous memory to perform the operation. To check the amount of contiguous memory available to the SQL Server, use the SQL Backup sqbmemory extended stored stored procedure:

EXECUTE master..sqbmemory

The Maximum value for Free memory indicates how much contiguous memory is available for SQL Backup. To make more memory available, reduce the number of other processes running on the SQL Server.

You can use the SQL Server -g startup option to control how much memory SQL Server leaves available for loading items such as extended stored procedure .dll files. For more information, see the Microsoft documentation on Database Engine Service Startup Options. 

For more information on allocating minimum and maximum memory for SQL Server, see the Microsoft documentation on Server Memory Server Configuration Options.