I am using an SqlExpress 2012 (I have to, no other choice).
The database runs full under some rare circumstances.
Now I want to make a backup of the full database and then truncate some tables and shrink back the database.
For the backup I use the following code:
public void BackupDatabase(string backupFilePath, string databaseServer, string databaseName, out string errorMessage)
{
var connection = new ServerConnection(databaseServer);
connection.StatementTimeout = 360;
Backup backup = new Backup();
backup.Devices.AddDevice(backupFilePath, DeviceType.File);
backup.Database = databaseName;
backup.Initialize = true;
backup.Incremental = false;
backup.Action = BackupActionType.Database;
backup.SqlBackup(new Server(connection));
}
But if I do that, I get an error after some time. This is my error log:
2020-01-20 13:42:54.89 spid10s Starting up database 'msdb'.
2020-01-20 13:42:54.90 spid12s Starting up database 'mssqlsystemresource'.
2020-01-20 13:42:54.91 spid12s The resource database build version is 12.00.6108. This is an informational message only. No user action is required.
2020-01-20 13:42:54.93 spid12s Starting up database 'model'.
2020-01-20 13:42:55.69 spid12s Clearing tempdb database.
2020-01-20 13:43:00.07 spid12s Starting up database 'tempdb'.
2020-01-20 13:43:00.37 spid18s The Service Broker endpoint is in disabled or stopped state.
2020-01-20 13:43:00.37 spid18s The Database Mirroring endpoint is in disabled or stopped state.
2020-01-20 13:43:00.39 spid18s Service Broker manager has started.
2020-01-20 13:43:01.02 spid10s Recovery is complete. This is an informational message only. No user action is required.
2020-01-20 13:43:02.66 Server Common language runtime (CLR) functionality initialized using CLR version v4.0.30319 from C:\Windows\Microsoft.NET\Framework64\v4.0.30319.
2020-01-20 13:43:15.65 spid51 Starting up database 'TESTDB'.
2020-01-20 13:43:36.75 spid51 Error: 1827, Severity: 16, State: 1.
2020-01-20 13:43:36.75 spid51 CREATE DATABASE or ALTER DATABASE failed because the resulting cumulative database size would exceed your licensed limit of 10240 MB per database.
2020-01-20 13:49:04.59 Backup Error: 3041, Severity: 16, State: 1.
2020-01-20 13:49:04.59 Backup BACKUP failed to complete the command BACKUP DATABASE PHASIS. Check the backup application log for detailed messages.
2020-01-20 14:14:56.76 Backup Error: 3041, Severity: 16, State: 1.
2020-01-20 14:14:56.76 Backup BACKUP failed to complete the command BACKUP DATABASE PHASIS. Check the backup application log for detailed messages.
It seems to me that the spid51 tries to do a "CREATE DATABASE" after collecting all infos. But then it tries to make the backup file as part of the server instance and that crashes because the 10 GB cannot increase anymore.
Has someone an idea on how to alter the backup, so that the backup is not counted anymore against the database limit? Maybe explicitly give it a new database or something the like?
The infos of Microsoft regarding the SMO is not really helpful: https://docs.microsoft.com/de-de/dotnet/api/microsoft.sqlserver.management.smo.backup?redirectedfrom=MSDN&view=sql-smo-140.17283.0
edit: When I do a backup with the Management Studio it works, but I have to do it with smo programmatically.
