Backup runs in ISQL but not as a 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 2000 Forums

SQL Server Administration (2000)

Backup runs in ISQL but not as a job

Author

Topic

lauramayer
Posting Yak Master

152 Posts

Posted - 2005-03-04 : 13:24:10

I'm very confused. I have this stored proc which goes though and backs up all the DB's except tempdb. It works fine on all my other servers but for some reason that I can't discern it won't run as a job on this server.

Any thoughts would be greatly appreciated.

Thanks

Laura

nr
SQLTeam MVY

12543 Posts

Posted - 2005-03-04 : 14:23:13

What happens when you try to run it?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

lauramayer
Posting Yak Master

152 Posts

Posted - 2005-03-07 : 07:46:02

Job completely fails. I think I have it narrowed down to a group of databases the network admin put on it. All of them have spaces in the name like: 'Heroix Central Performance Collection'. Would having a space in the name kill the backup?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-03-07 : 08:17:25

If the database names are not enclosed in square brackets, then the procedure could fail. You would have to post the code in order for us to to know for certain.

lauramayer
Posting Yak Master

152 Posts

Posted - 2005-03-07 : 08:28:50

Here it is....

SET QUOTED_IDENTIFIER off
select getdate() "Start Time"
set nocount on

declare @dbname varchar(50),
@err int,
--@backup_name varchar(255),
--@backup_dev varchar(255),
@backupdrive varchar(255),
--@print_string varchar(255),
@cmd varchar(255),
@dbnamecursor cursor
--@er_count int
--set @er_count=0
set @backupdrive='"d:\progra~1\micros~1\mssql"'

if @backupdrive Is NULL
select @err = -1
if @err = -1
begin
raiserror( 'parameter error:usage:exec dbsp_fullbackup', 12, 1)
return @err
end

/*
create a cursor with applicable database names
*/
Create table #mydbs
( dbname char( 50),
size char( 20),
dbowner char( 50),
dbid int,
crdate datetime,
status varchar( 1000),
lvl char( 4)
)

Insert #mydbs Exec sp_helpdb

declare dbnamecursor cursor for
select o.dbname
from #mydbs o
where o.dbname not in ('tempdb')
order by o.dbname

open dbnamecursor
fetch dbnamecursor into @dbname

while @@fetch_status = 0
begin
if DATABASEPROPERTYEX(@dbname,'Status') = 'ONLINE'

/*
Check for Directory, if it doesn't exist, create it
*/
select @cmd = 'exec master..xp_cmdshell ''if not exist "' + @backupdrive + '\backup\' + rtrim( @dbname) + '" md "' + @backupdrive + '\backup\' + rtrim( @dbname) + '"'''
exec (@cmd)

/*
Check for old Backup Devices. If they exist, delete the oldest and rename the rest
*/
select @cmd = 'exec master..xp_cmdshell ''del ' + @backupdrive + '\backup\' + rtrim( @dbname) + '\*.ba1'''
exec (@cmd)
select @cmd = 'exec master..xp_cmdshell ''ren ' + @backupdrive + '\backup\' + rtrim( @dbname) + '\*.bak *.ba1'''
exec (@cmd)

/*
Backup Databases
*/
select @cmd = 'backup database ' + rtrim(@dbname) + ' to disk = ''D:\progra~1\micros~1\mssql\backup\' + rtrim(@dbname) + '\' + rtrim(@dbname) + '.bak'' with init, noformat, noskip'
print @cmd
exec (@cmd)
if @@error <> 0
begin
raiserror('Database Backup Failed',16,1)
end

fetch dbnamecursor into @dbname
end

close dbnamecursor
deallocate dbnamecursor
select getdate() "End Time"

Kristen
Test

22859 Posts

Posted - 2005-03-07 : 13:39:07

I havne't looked closely, but where you are referring to Folders I reckon you would be best off doing:

select @cmd = 'exec master..xp_cmdshell ''if not exist "' + @backupdrive + '\backup\' + replace(rtrim( @dbname), ' ', '_') + '" md "' + @backupdrive + '\backup\' + rtrim( @dbname) + '"'''

to replace spaces with underscores in folder names. (You'd need to duplciate this whereevere there are folder related commands).

We only allow databases to be created using a Stored Procedure. The SProc does not allow "weird" characters, forces CAPs, and inflicts a length limit on the database name. We've had no problems with the rest of our maintenance routines!!

Reminds me of the time we discovered that a client had "\" in their product codes. So we get image filenames (for their website) called things like "123\456_large.jpg" matching product code "123\456". So we just created a folder called "123" and put an image called "456_large.jpg" in it and the web pages worked a treat!

Names are A-Z, 0-9 and (if you really REALLY must) "_"

Thats IT !! NO exceptions!! Tell the Network Admin that "Kristen said so" ...

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources