Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Dynamically Script Database Backups


Dynamically Script Database Backups

Author
Message
shanu.hoosen
shanu.hoosen
Old Hand
Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)

Group: General Forum Members
Points: 357 Visits: 310
Comments posted to this topic are about the item Dynamically Script Database Backups
ken.trock
ken.trock
Old Hand
Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)

Group: General Forum Members
Points: 390 Visits: 1713
Nice idea. But I'm getting error while trying to create the proc.

Msg 102, Level 15, State 1, Procedure USP_BackupDatabasesOnPr01, Line 32
Incorrect syntax near ' '.

Seems that there's something on the previous line SQL doesn't like.

Ken
mehmetguzel
mehmetguzel
SSC-Enthusiastic
SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)

Group: General Forum Members
Points: 114 Visits: 282
Short way Backups of All User Databases with sp_msforeachdb undocumented sp.

MS SQL Server DBA
www.mehmetguzel.net
shanu.hoosen
shanu.hoosen
Old Hand
Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)

Group: General Forum Members
Points: 357 Visits: 310
Hi Ken,

Sorry about that.

please try below:

USE [master]
GO
/****** Object: StoredProcedure [dbo].[USP_BackupDatabasesOnPr01] Script Date: 02/14/2011 09:04:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter Proc [dbo].[USP_BackupDatabasesOnPr01] -- exec USP_BackupDatabasesOnPr01 'V:\Data\January Backups\'
@path nvarchar(4000)
as
/*
Author: Shanu Hoosen
Date: Created:31/01/2011
Execute as: Exec USP_BackupDatabasesOnPr01 'drive:\FolderPath'
*/


declare @sql nvarchar(4000)
declare @i nvarchar(3)
declare @mx int
declare @DBName nvarchar(500)

--declare @path nvarchar(4000)
--set @path = 'V:\Data\January Backups\'


select ROW_NUMBER() OVER(ORDER BY name)id,name
into #d
from sysdatabases
where sid <> 0x01
set @i = (select min(id) from #d)
set @mx = (select max(id)+1 from #d)

while @i <> @mx
begin
set @DBName=(select '['+name+']' from #d where id = @i)

set @sql = 'BEGIN TRY
BACKUP DATABASE '+ @DBName +'
TO DISK = N'''+@path+ replace(replace (@DBName, '[',''),']','') +'-'+ replace(cast(getdate() as nvarchar(500)),':','')+'.bak'''+
' WITH NOFORMAT, NOINIT,
NAME = N'''+@DBName +'-Full Database Backup'''+','
+'SKIP, NOREWIND, NOUNLOAD, STATS = 10
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,

END CATCH'


--print(@sql)
exec(@sql)



set @i =@i+1
end
drop table #d



ken.trock
ken.trock
Old Hand
Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)

Group: General Forum Members
Points: 390 Visits: 1713
Ok, I don't know what you did there but this version compiles and executes (I took out the call to executing the actual backups in favor of printing out the string).

Thanks,
Ken
shanu.hoosen
shanu.hoosen
Old Hand
Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)

Group: General Forum Members
Points: 357 Visits: 310
This was the original version that I created.

The only difference is, should it fail eg space limitations, it will not give the effected DB.

By adding '@DBName' + errormessage() on the last line will overcome the shortfall.

But as long as it worksSmile
SQL33
SQL33
SSC-Enthusiastic
SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)

Group: General Forum Members
Points: 158 Visits: 225
Just ran this on a WIN2K Sql SERVER providing the UNC path to the backup shared network drivespace which I verified I have access to and received this error...

Server: Msg 195, Level 15, State 10, Procedure USP_BackupDatabasesOnPr01, Line 18
'ROW_NUMBER' is not a recognized function name.
Server: Msg 170, Level 15, State 1, Procedure USP_BackupDatabasesOnPr01, Line 32
Line 32: Incorrect syntax near ' '.


Any thoughts?

Thanks..
ken.trock
ken.trock
Old Hand
Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)

Group: General Forum Members
Points: 390 Visits: 1713
Row_Number() was introduced in SQL 2005. Are you on that or is your SQL Server 2000?

Ken
SQL33
SQL33
SSC-Enthusiastic
SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)

Group: General Forum Members
Points: 158 Visits: 225
I'm on a SQL 2000 server....
shanu.hoosen
shanu.hoosen
Old Hand
Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)

Group: General Forum Members
Points: 357 Visits: 310
An alternate to the row_number() function would be to create a table with an identity (1,1) column.

All we are trying to acheive is a sequential increase by 1, which the loop can pick up. During the insert ignore the Identity column.

Let me know if this helps.

Shanu
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search