December 20, 2012 at 12:58 pm
Sorry I meant to add this to the 2005 forum, but I can't figure out how to remove.
I'm trying to deal with a server space limitation that requires me to perform a backup and move it off the server immediately after. So I've written a tsql mainenance step to do this for me, below. And it seems to work for most of my databases, but it seems randomly to stop. Sometimes it'll back up 10 of my databases before stopping without failure, and sometimes it'll go through 60, but it never finishes for all databases. I've also included the stored proc being called.
declare @databasename varchar(200)
declare DatabaseCursor cursor for
select name from sys.databases where name not like 'tempdb'
order by name
open DatabaseCursor
fetch next from DatabaseCursor into @databasename
while @@fetch_status=0
begin
exec('exec mydb.dbo.BackupAndMove '''+@databasename+'''');
fetch next from DatabaseCursor into @databasename
end
close DatabaseCursor
deallocate DatabaseCursor
CREATE PROCEDURE [dbo].[BackupAndMove]
@databasename varchar(200)
AS
BEGIN
declare @time varchar(123)
declare @sql varchar(300)
declare @fileloc varchar(300)
set @time = CONVERT(CHAR(8),GETDATE(),112)+REPLACE(CONVERT(CHAR(5),GETDATE(),108),':','')
select @fileloc = 'f:\MSSQL\BACKUP\UserDB\' + @databasename+'_db_' + @time + '.bak'
select @sql = 'backup database ' + @databasename+ ' to disk = ''' + @fileloc + ''''
execute(@sql)
select @sql = 'exec xp_cmdshell ''move "'+@fileloc+'" "\\server\f$\Backups"'''
execute(@sql)
END
December 20, 2012 at 1:20 pm
Triple-double idiot: Why didn't i just backup directly across the network? It seems to be working now that I did the obvious.
December 20, 2012 at 2:00 pm
I spoke too soon. It still stops randomly. It did about 60 of the 70 databases and stopped. No error, just it just quit.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply