backup work in sproc but not in job...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

backup work in sproc but not in job...

Author

Topic

jhermiz

3564 Posts

Posted - 2011-06-17 : 11:15:11

We have a huge instance of sharepoint 2010. Sharepoint 2010 uses multiple databases so I had to write some code to loop through each database to perform a backup.

Something to this effect:


--Does Backup of all the databases except system databases
	DECLARE @cmd1 nvarchar(4000)
	
	--change cmd1 to include any database that you DON'T want backed up.

	SET @cmd1 = 'IF ''?'' NOT IN(''master'', ''model'', ''DBA'', ''tempdb'', ''msdb'')' + 'BEGIN ' 
	+ 'Print ''Backing up ? database...'';' 
	+ 'BACKUP DATABASE [ ? ] TO DISK=''' + 'L:\Backups\SP2010\?_' + replace(convert(varchar,GETDATE(),120),':','') + '.bak'' WITH RETAINDAYS=21'
	+ 'BACKUP LOG [ ? ] TO DISK=''' + 'L:\Logs\Databases\SP2010\?_LOG_' + replace(convert(varchar,GETDATE(),120),':','') + '.ldf'''
	+ 'END' 
	EXEC sp_MSForEachdb 
	@command1 = @cmd1

If I right click this (I've made it as a stored procedure) and click execute it runs fine and I see the backup files in the L:\ drive. However, I then created a job with one simple step in it to "EXEC mySproc" where mySproc is the code above, it says the job completed successfully but there are no files on the local L drive of this server.

I tried to set it up using "Run as User: dbo" but the issue is it says the job completed successfully but as mentioned no physical files. I also tried using a domain admin account but it keeps failing in this case:

"Executed as user: ah\administrator. The SELECT permission was denied on the object 'sysdatabases', database 'mssqlsystemresource', schema 'sys'. [SQLSTATE 42000] (Error 229). The step failed."

Weblog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-06-17 : 11:56:59

1. does the server know about the L drive
2. Is there an L drive mapped on sql server
3. you might need to use an UNC path that point to the mapped location of L \\servername\Backups\SP20104. Make sure the user that is running the job has access to that folder

If you don't have the passion to help people, you have no passion

jhermiz

3564 Posts

Posted - 2011-06-17 : 12:39:07

quote:
Originally posted by yosiasz

1. does the server know about the L drive
2. Is there an L drive mapped on sql server
3. you might need to use an UNC path that point to the mapped location of L \\servername\Backups\SP20104. Make sure the user that is running the job has access to that folder

If you don't have the passion to help people, you have no passion

For 1 yes it is a local disk on the SQL Server itself.
For 2 yes it is mapped on the sql server
For 3 I tried using \\myServer\Backups\SP2010 as a share...
and no luck...

The user running the job...that is the question. If you saw my post it says if I use ah\administrator I run into an error, but if I use dbo it says the job executed succesfully but no backup files are found.

Weblog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-06-17 : 13:05:03

spin up sql profiler and see who it is running as
create a maintenance plan of type backup and let sql do what it is designed to do. maybe that will help

If you don't have the passion to help people, you have no passion

jhermiz

3564 Posts

Posted - 2011-06-17 : 13:11:34

quote:
Originally posted by yosiasz

spin up sql profiler and see who it is running as
create a maintenance plan of type backup and let sql do what it is designed to do. maybe that will help

If you don't have the passion to help people, you have no passion

Its not a question of who is it running as.
I have it set to run using Domain\Administrator
The job completes successfully, however, there is no physical backup done. If I run and execute the stored procedure it works fine...however when I create a scheduled job with one step to run the stored procedure it runs but no files created...
I dont want to use a maintenance backup as I said, sharepoint has multiple databases that can actually change dynamically. I'd have to go back in there and create another backup and keep track of them.
Ill see if profiler helps.

Weblog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

jhermiz

3564 Posts

Posted - 2011-06-17 : 13:44:42

I ended up calling the sproc from an osql command and I think this is the cleaner solution. It is working thanks.

Weblog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-06-17 : 13:48:21

If you want a more robust backup stored procedure, here you go: http://weblogs.sqlteam.com/tarad/archive/2009/12/29/Backup-SQL-Server-2005-and-2008-DatabasesAgain.aspx

It handles all of our database backup needs, from Sharepoint to other COTS databases to custom in-house databases to system databases, etc...

We have it deployed to hundreds of SQL Server instances here, most are mission critical and some are revenue impacting. Plus it's used around the world by numerous other companies.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources