February 11, 2011 at 5:41 am
Comments posted to this topic are about the item Dynamically Script Database Backups
February 11, 2011 at 9:33 am
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
February 12, 2011 at 5:01 am
Short way Backups of All User Databases with sp_msforeachdb undocumented sp.
MS SQL Server DBA
www.mehmetguzel.net
February 14, 2011 at 5:08 am
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
February 14, 2011 at 9:53 am
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
February 15, 2011 at 12:00 am
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:)
March 25, 2011 at 12:25 pm
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..
March 25, 2011 at 12:42 pm
Row_Number() was introduced in SQL 2005. Are you on that or is your SQL Server 2000?
Ken
March 25, 2011 at 12:53 pm
I'm on a SQL 2000 server....
March 28, 2011 at 1:09 am
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
May 17, 2016 at 6:50 am
Thanks for another good script.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply