Backing up SQL2005 Express database

[netnut1234]

Hi,

I am having some issues with backing up a SQL 2005 database.

I am running a full version of SQL 2005 on server A, this server has all our databases, maintenance plans.
I am running SQL2005 express server on server B. This runs a database for a small application that we use.

What I am trying to do is to create a maintenance plan, on Server A that will back up the database on server B.

I can on Server A, connect to the instance on Server B, right click on the database and create backup of the database and sort the backup file locally on Server B.

The issue that I have is with the maintenance plan that I have created, when its run, I get the following error,
Date 02/07/2008 11:33:56
Log Job History (Trackit database)

Step ID 1
Server TROY
Job Name Trackit database
Step Name Trackit database_Job_Step
Duration 00:00:00
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: ABC\xyz. The package could not be loaded. The step failed.

The account xyz is a domain admin and have permissions to access both servers. The SQL Server Agent account is also using the same user account.

I am not sure why the maintenance plan isn't working.
Can anyone help me out on this issue.

Thanks
Tim

 

[JayKusch]

Have you checked the permissions on the folder/share you are spooling the backup to?

Is it set for Everyone with FULL permissions?

Thanks

J. Kusch

 

[netnut1234]

The folder isnt shared, the security permissions on the main folder is set for domain admins to have full control access to the folder. The account that I am using to run the job is a domain admin.
I have even shared the folder to allow everyone full control. This still doesnt work.
Thanks
Tim

 

[JayKusch]

Have you tried to create this backup job as a SQL job and not as a maintenance plan?



Thanks

J. Kusch

 

[netnut1234]

As part of the maintenance plan that I have created, there is a SQL job, which is underneath SQL Server Agent. Is this the same as what you are referring to. If not how do I create the job.

Thanks
Tim

 

[drtree]

Hi,

I usually use a SQL script to do this - here's what it looks like:

DECLARE @sql nvarchar(1000)

SET @sql = N'BACKUP DATABASE Some_DB TO DISK = ''C:\SQL_Data\MSSQL.2\MSSQL\Backup\' + DATENAME(dw, getdate())  
		  + '\some_db_full.dmp ''WITH INIT , NOUNLOAD , NAME = N''Your DB'', SKIP , STATS = 10, DESCRIPTION = N''Your DB'', NOFORMAT'

EXECUTE SQL_Inst2.master.dbo.sp_executesql @sql

This is for a full back up, but you can just modify it for your database. I then set this up as an sql job, but you should be able to set this up as an Execute SQL task within SSIS. Also, I believe the SQL Agent account will need Modify rights (you don't need to give it Full Control, as that only gives it the ability to take ownership of files) so it can add/delete/overwrite existing files.

HTH,

Doc Tree