MS SQL Backup issues

Hi,

I am running on MS SQL 2000 of this version:

Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

I have created a backup procedure to make automated db backup. However it is working only from the query analyzer but not from dts package. Does it have any particular reason to behave this way in your opinion ? Is it some limitation of backup statement ?

Here is the code i am using:

DECLARE @.dirname VARCHAR(255)
DECLARE @.filename VARCHAR(255)
DECLARE @.statement VARCHAR(255)
DECLARE @.date_value VARCHAR(255)

SET @.dir_value = (SELECT dirname FROM Extend_ObalServis.dbo.backup_dir_name WHERE id_dirname = (SELECT MAX(id_dirname) FROM Extend_ObalServis.dbo.backup_dir_name))

SET @.dirname = 'c:\db\archive\source\' + @.dir_value

SET @.filename = 'obal_source.dat'

SET @.filename = @.dirname + '\' + @.filename

SET @.statement = 'mkdir ' + '"' + @.dirname + '"'

select @.statement, @.filename

EXECUTE master.dbo.xp_cmdshell @.statement

-- Create a logical backup device for the full obal_target database backup.
USE master
EXECUTE sp_addumpdevice 'disk', 'SOURCE_BCKP', @.filename

-- Back up the full source database.
BACKUP DATABASE obal_source TO SOURCE_BCKP

EXECUTE sp_dropdevice 'SOURCE_BCKP'

I created a simple dts package with an execute sql task and saved it as a structured storage file to the files system and run it by dtsrun. It creates the needed directory. It starts to create a backup file (I see that the space on the disk is decreasing). A placeholder of a file with 0B size appears. But right after the package finishes its work the file disappears. Any suggestion would be very appreciated.

Thanks

You say that you are running the package via dtsrun....which indicates that you are likely using a sql job to invoke it. SQL jobs run under a different security level than you. Check to see what user the sql job is running under. That is likely your problem. If you can change that user so that it has rights to do what is in the script, it should fix it for you.
Tim|||

Make sure the job owner is a sysadmin.

One other option - you still need to fix the job owner and security context of the job - but you could also run the script directly in a job. Cleaner and fewer points of failure if there is no real driving reason to use a DTS package for this. If there is something you need to run after the backup or before, you can still use jobs steps to control the flow of tasks. You may have some other reason for doing this through DTS but just a thought.

-Sue

|||

Thanks for your answers,

however there is a strange thing that if I run that package from SQL Server the result is the same. Coul you please let know how to change the owner of the package in SQl Server ? How to set what user it would run under ?

Many thanks

|||

The owner of the package doesn't really cause problems for DTS on SQL 2000.

For the job owner, you view the properties for the job and on the General tab you can set the job owner.

You aren't getting any error messages anywhere? Did you view the job history? Do you get an error when you run the package manually on the server? Does the directory exist the you are referencing in the script?

If the package runs fine somewhere, you can most likely find the answer in the following:

INF: How to Run a DTS Package as a Scheduled Job

http://support.microsoft.com/kb/269074

-Sue

|||

HI,

so I have tried many scenarios with various settings of ownership and run options. Unfortunately there is no progress on the issue so it still does not want to leave the created file in the directory. I created a brand new package just with one sql execute task to make the job done. Basicly I did not use any jobs. But when I tried it it finished without the file created. When I created a package with the mentioned task and I run it just by clicking right mouse button and choose execute step from the context menu it finished with the very same result. I am really lost at the moment what is going on.

Service of ms sql server is running under loacl administrator account. I am logged in as the same local administrator. I am running the package from the enterprise manager as local administrator. Still the same result. I cannot understand what other permissions should I give it to make the statement successsfully run.

BTW No errors appeared anywhere.

Akos|||

One more thing. How is it possible that beside these settings the Query Analyzer runs the statement sucessfully everytime regardless of what user I am logged in under. Don't you have any experience with this kind of strange issues ?

|||

Is it possible to not be allowed to create backup for some reasons ? I mean that I run the services (mssqlserver and sqlserveragent) under the same account I am using to develop the package to do the backup of the db. So I assume it should be some reasonable cause there to forbid me to create that backup file. That account is in administrators group locally. It is included into the db users and sysadmins as builtin/administrators on the db server ...

Please let me know what other restriction affects this or give me any idea of any sources I can use to get rid of this issue as this can seriously harm the whole project I am working on :-(

thanks in advance

|||

First thing is to enable logging for this DTS package, that gives better understanding on the errors or warning to complete that particular task.

BACKUP DATABASE and BACKUP LOG permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles.

Also I'm not able to understand why you are using a DTS package to perform a backup for a database?