.net 1.1 & SQL Server 2005 2

[apc2004]

Hi everyone,

My company writes and maintains a .net 1.1 application which connects to an SQL Server 2000 Database.

Using SQLDMO we envoke a Backup of this Database from the client.

We are currently looking into upgrading to SQL Server 2005 and had not encountered any compatibility problems running until one of our testers tried to do a Backup from the client.

Due to SQL Server 2005 no longer using DMO the backup fails... I have been reading up on SQL Server 2005 and now know it uses SMO & RMO.

The problem I have, which by now you clever lot have probably realised, is that SMO / RMO are .net 2.0+ only and currently updating our application to .net 2.0+ is NOT an option much as I would like to but management have overruled.

So... my question, which 'googling' has failed in answering so far, is it possible to envoke a Backup operation from a .net 1.1 application on a SQL Server 2005 database?

If so, any ideas as to how?

Thanks in advance...

 

[chrissie1]

Yeah why not.

You could use T-SQL

Syntax
Backing up an entire database:

BACKUP DATABASE { database_name | @database_name_var }
TO < backup_device > [ ,...n ]
[ WITH
[ BLOCKSIZE = { blocksize | @blocksize_variable } ]
[ [ , ] DESCRIPTION = { 'text' | @text_variable } ]
[ [ , ] DIFFERENTIAL ]
[ [ , ] EXPIREDATE = { date | @date_var }
| RETAINDAYS = { days | @days_var } ]
[ [ , ] PASSWORD = { password | @password_variable } ]
[ [ , ] FORMAT | NOFORMAT ]
[ [ , ] { INIT | NOINIT } ]
[ [ , ] MEDIADESCRIPTION = { 'text' | @text_variable } ]
[ [ , ] MEDIANAME = { media_name | @media_name_variable } ]
[ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ]
[ [ , ] NAME = { backup_set_name | @backup_set_name_var } ]
[ [ , ] { NOSKIP | SKIP } ]
[ [ , ] { NOREWIND | REWIND } ]
[ [ , ] { NOUNLOAD | UNLOAD } ]
[ [ , ] RESTART ]
[ [ , ] STATS [ = percentage ] ]
]

Backing up specific files or filegroups:

BACKUP DATABASE { database_name | @database_name_var }
< file_or_filegroup > [ ,...n ]
TO < backup_device > [ ,...n ]
[ WITH
[ BLOCKSIZE = { blocksize | @blocksize_variable } ]
[ [ , ] DESCRIPTION = { 'text' | @text_variable } ]
[ [ , ] DIFFERENTIAL ]
[ [ , ] EXPIREDATE = { date | @date_var }
| RETAINDAYS = { days | @days_var } ]
[ [ , ] PASSWORD = { password | @password_variable } ]
[ [ , ] FORMAT | NOFORMAT ]
[ [ , ] { INIT | NOINIT } ]
[ [ , ] MEDIADESCRIPTION = { 'text' | @text_variable } ]
[ [ , ] MEDIANAME = { media_name | @media_name_variable } ]
[ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ]
[ [ , ] NAME = { backup_set_name | @backup_set_name_var } ]
[ [ , ] { NOSKIP | SKIP } ]
[ [ , ] { NOREWIND | REWIND } ]
[ [ , ] { NOUNLOAD | UNLOAD } ]
[ [ , ] RESTART ]
[ [ , ] STATS [ = percentage ] ]
]

Backing up a transaction log:

BACKUP LOG { database_name | @database_name_var }
{
TO < backup_device > [ ,...n ]
[ WITH
[ BLOCKSIZE = { blocksize | @blocksize_variable } ]
[ [ , ] DESCRIPTION = { 'text' | @text_variable } ]
[ [ ,] EXPIREDATE = { date | @date_var }
| RETAINDAYS = { days | @days_var } ]
[ [ , ] PASSWORD = { password | @password_variable } ]
[ [ , ] FORMAT | NOFORMAT ]
[ [ , ] { INIT | NOINIT } ]
[ [ , ] MEDIADESCRIPTION = { 'text' | @text_variable } ]
[ [ , ] MEDIANAME = { media_name | @media_name_variable } ]
[ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ]
[ [ , ] NAME = { backup_set_name | @backup_set_name_var } ]
[ [ , ] NO_TRUNCATE ]
[ [ , ] { NORECOVERY | STANDBY = undo_file_name } ]
[ [ , ] { NOREWIND | REWIND } ]
[ [ , ] { NOSKIP | SKIP } ]
[ [ , ] { NOUNLOAD | UNLOAD } ]
[ [ , ] RESTART ]
[ [ , ] STATS [ = percentage ] ]
]
}

< backup_device > ::=
{
{ logical_backup_device_name | @logical_backup_device_name_var }
|
{ DISK | TAPE } =
{ 'physical_backup_device_name' | @physical_backup_device_name_var }
}

< file_or_filegroup > ::=
{
FILE = { logical_file_name | @logical_file_name_var }
|
FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var }
}

Truncating the transaction log:

BACKUP LOG { database_name | @database_name_var }
{
[ WITH
{ NO_LOG | TRUNCATE_ONLY } ]
}

And the rest can be found in BOL.

Christiaan Baes
Belgium

My Blog
"In a system where you can define a factor as part of a third factor, you need another layer to check the main layer in case the second layer is not the base unit." - jrbarnett

 

[onpnt]

i think you may need to go into what your app is doing with sqldmo because it is still fully supported in 2005 so your 1.1 app should have no problem creating and utilizing the objects. The only thing that has happened to dmo is there will be no further upgrades to it.

Although I would seriously look to using SQL Server 2005 and CLR using SMO or something to do your backup process because DMO will more than likely be gone in 2008 and any future releases. Why do you have an app doing a backup call anyhow? This is a database task and you should call the database to ahve a process that lies there to do it.

[sub]____________ signature below ______________
I am Tedward Keyboardhands!!!
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]

 

[apc2004]

onpnt our software performs a backup before the user selects to do a particular task (which is very complex and takes about 1 hour to complete) in case something goes wrong we can roll the database back to the state before the task is performed... this mechanism was put in before I joined the company, I don't like it to be honest but I cannot remove it unless authorised by my managers so I have to maintain it for now.

What you're saying about the DMO, you say it should still work? What happens when we try to envoke the backup is an exception is throw with a message saying "the server has been diconnected, please reconnect to perform this action" or words to that effect.

Any ideas why this is? The DMO code was written by another developer who no longer works here so this area of the .net frameword is all new to me.

This is an extract of code, everything works fine until the BackupObj.SQLBackup(SqlServer); is called when an error is then thrown.

try
{
				this.Connect();
				BackupObj = new Interop.SQLDMO.Backup2Class();

				BackupObj.Devices = BackupObj.Files;
				BackupObj.Files = "[" + backupPath + "]";
				if (description != "")
					BackupObj.BackupSetDescription = description;
				
				BackupObj.PercentCompleteNotification = 1;

				CnPtContainer = (UCOMIConnectionPointContainer) BackupObj;				
				bkSink = new BackupSink();
				int iCookie;

				Guid guid = new Guid("10021F09-E260-11CF-AE68-00AA004A34D5"); // UUID of SQLDMO Backup Event Sink
				CnPtContainer.FindConnectionPoint(ref guid, out CnPt);				
				CnPt.Advise(bkSink, out iCookie);

				BackupObj.SQLBackup(SqlServer);							

				CnPt.Unadvise(iCookie);
			}
			catch(System.Runtime.InteropServices.COMException comEx)
			{
				throw new Exception(comEx.Message);
			}

bare in mind I have removed the odd line of unimportant code for privacy reasons... nothing major though that would cause a problem.

Thanks...

 

[apc2004]

Thanks for all the help, have just found the answer to my problems...

Microsoft have produced an .msi with Backward Compatibilty components to allow DMO to continue to work correctly with SQL Server 2005...

It says on the website that if you do not update the SQLDMO to the newest version the SQL Server will reject the connection to backup the database (which is exactly what I am getting).

Thanks again... much appriciated.