SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by shanu hoosen / Dynamically Script Database Backups / Latest Posts

RE: Dynamically Script Database Backups

shanu.hoosen — Mon, 28 Mar 2011 01:09:02 GMT

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

RE: Dynamically Script Database Backups

SQL33 — Fri, 25 Mar 2011 12:53:23 GMT

I'm on a SQL 2000 server....

RE: Dynamically Script Database Backups

ken.trock — Fri, 25 Mar 2011 12:42:42 GMT

Row_Number() was introduced in SQL 2005. Are you on that or is your SQL Server 2000?Ken

RE: Dynamically Script Database Backups

SQL33 — Fri, 25 Mar 2011 12:25:37 GMT

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 32Line 32: Incorrect syntax near ' '.Any thoughts? Thanks..

RE: Dynamically Script Database Backups

shanu.hoosen — Tue, 15 Feb 2011 00:00:06 GMT

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 works:)

RE: Dynamically Script Database Backups

ken.trock — Mon, 14 Feb 2011 09:53:35 GMT

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

RE: Dynamically Script Database Backups

shanu.hoosen — Mon, 14 Feb 2011 05:08:01 GMT

Hi Ken,Sorry about that. please try below:[code="sql"]USE [master]GO/****** Object: StoredProcedure [dbo].[USP_BackupDatabasesOnPr01] Script Date: 02/14/2011 09:04:17 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOalter Proc [dbo].[USP_BackupDatabasesOnPr01] -- exec USP_BackupDatabasesOnPr01 'V:\Data\January Backups\'@path nvarchar(4000)as/* Author: Shanu HoosenDate: Created:31/01/2011Execute as: Exec USP_BackupDatabasesOnPr01 'drive:\FolderPath'*/declare @sql nvarchar(4000)declare @i nvarchar(3)declare @mx intdeclare @DBName nvarchar(500)--declare @path nvarchar(4000)--set @path = 'V:\Data\January Backups\' select ROW_NUMBER() OVER(ORDER BY name)id,name into #dfrom sysdatabaseswhere sid <> 0x01set @i = (select min(id) from #d)set @mx = (select max(id)+1 from #d)while @i <> @mxbegin 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+1enddrop table #d[/code]

RE: Dynamically Script Database Backups

mehmetguzel — Sat, 12 Feb 2011 05:01:22 GMT

Short way [url=http://translate.google.com/translate?client=tmpg&hl=en&u=http%3A%2F%2Fmehmetguzel.blogspot.com%2F&langpair=tr|en]Backups of All User Databases[/url] with sp_msforeachdb undocumented sp.

RE: Dynamically Script Database Backups

ken.trock — Fri, 11 Feb 2011 09:33:43 GMT

Nice idea. But I'm getting error while trying to create the proc. Msg 102, Level 15, State 1, Procedure USP_BackupDatabasesOnPr01, Line 32Incorrect syntax near ' '.Seems that there's something on the previous line SQL doesn't like.Ken

Dynamically Script Database Backups

shanu.hoosen — Fri, 11 Feb 2011 05:41:24 GMT

Comments posted to this topic are about the item [B]Dynamically Script Database Backups[/B]