Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Dynamically Script Database Backups Expand / Collapse
Author
Message
Posted Friday, February 11, 2011 5:41 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 3, 2013 2:28 AM
Points: 357, Visits: 310
Comments posted to this topic are about the item Dynamically Script Database Backups
Post #1062493
Posted Friday, February 11, 2011 9:33 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 2:38 PM
Points: 323, Visits: 1,472
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
Post #1062672
Posted Saturday, February 12, 2011 5:01 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 20, 2013 7:33 AM
Points: 114, Visits: 282
Short way Backups of All User Databases with sp_msforeachdb undocumented sp.

MS SQL Server DBA
www.mehmetguzel.net
Post #1062966
Posted Monday, February 14, 2011 5:08 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 3, 2013 2:28 AM
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


Post #1063437
Posted Monday, February 14, 2011 9:53 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 2:38 PM
Points: 323, Visits: 1,472
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
Post #1063640
Posted Tuesday, February 15, 2011 12:00 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 3, 2013 2:28 AM
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 works:)
Post #1064037
Posted Friday, March 25, 2011 12:25 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, January 17, 2014 1:54 PM
Points: 157, Visits: 221
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..
Post #1084285
Posted Friday, March 25, 2011 12:42 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 2:38 PM
Points: 323, Visits: 1,472
Row_Number() was introduced in SQL 2005. Are you on that or is your SQL Server 2000?

Ken
Post #1084294
Posted Friday, March 25, 2011 12:53 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, January 17, 2014 1:54 PM
Points: 157, Visits: 221
I'm on a SQL 2000 server....
Post #1084305
Posted Monday, March 28, 2011 1:09 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 3, 2013 2:28 AM
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
Post #1084734
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse