Backup.ExpirationDate in Sql Server SMO

I'm writing a console app in Visual Studio 2005 to backup databases using SMO and I have run into a minor snag.

It appears that when I have the Initialize property set to false to allow backups to append onto existing backups, the backups that are past their expiration dates do not get overwritten. I know the RetainDays property requires the Intialize property to be set to true but I haven't seen anything that suggests that the ExpirationDate property does as well.

Does anyone have any suggestions?

I don't think you have run into a minor snag, you have run into desired and expected behavior because Backup is the best thing you have to recreate a SQL Server database including the an actual copy of the database. The reason is when backup is copied to tape, then the .bak file can be deleted manually. That said I think you can find some backup code in the BOL (books online) but try the link below to get started. Hope this helps.

http://www.sqldbatips.com/showarticle.asp?ID=37

|||

Thanks for the response Caddre.

I have the backup code written and the application works fine. I'm just afraid that if the backups go unmonitored (and in our case that is a possibility) the backup file will get extremely large.

Every time I run the console app it appends to the end of an existing backup .bak file, thus doubling it in size. I thought if you established an expiration date, "expired" backups would be overwriiten within the .bak file. That way the .bak file would never be more than twice its size; however, my testing shows that it just keeps getting bigger and bigger.

Thanks again

|||That is what I am trying to explain to you Backup files cannot be allowed to be overwritten because someone could make the mistake of overwriting needed .bak files. You could write code to delete expired .bak files or you could do it manually weekly.

Backup to Share with SMO fails, but works to local hard disk

I have a short program (vb.net) which backs up an SQLExpress database. It works fine if the destination is a file on the local drive. However, it always fails if the destination file is a network share or a mapped share. The same program verifies the share is available by trying to read and write a small file before performing the backup.

This is a stripped down version of the code

Dim svr As Server = New Server("Servername")
svr.ConnectionContext.LoginSecure = false
svr.ConnectionContext.Login = TextBoxBULogin.Text
svr.ConnectionContext.Password = TextBoxBUPwd.Text
Dim bkp As Backup = New Backup()
bkp.Action = BackupActionType.Database
bkp.Database = TextBoxBUDatabase.Text
Dim x As New Microsoft.SqlServer.Management.Smo.BackupDeviceItem(destfilepath, DeviceType.File)
bkp.Devices.Add(x)
bkp.SqlBackup(svr)

The exception generated is an SMO.FailedOperationException and reads "System.Data.SqlClient.SqlError: Cannot open backup device 'Z:\v1tbcontacts00.bak'. Device error or device off-line."

Where "Z:\" is a network share. If the backup device string is set to "C:\v1tbcontacts00.bak" the backup succeeds.

Have you tried to use Transact-SQL to backup to the share? In any case you may find it better to back up to the local drive, then copy the backup file using operating system commands to the network share for performance purposes.|||

Henry,

using SQL authentication will make SMO to use the SQL Server Sevice account to authenticate on the network share, so although you might be able to check the network share for existance within your application, the credentials used for this is not used for making the backup on the file share.

Client application (Windows token) --> SQL Server using Windows authentication and connected with Windows Authentication --> SMO uses the Windows credentials of the connected client to access the share

Client application (Windows token) --> SQL Server using Mixed Authentication and connected with SQL Server Authentication --> SMO uses the SQL Server Service account credentials of to access the share

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

The practical solution to my issue is to perform a database backup to the local hard disk and then copy the backup to a share.

Better still. the nugget of information about how authentication works with SMO explains why the problem occured in the first place.

Thanks