Question:
I am writing a SQL statement that dynamically dumps the databases on the SQL servers. However, when I run the procedure as a stored procedure it falls out of the WHILE loop and doesn’t update the cursor:
DECLARE @counter int, @db_count int, @dbname char(30), @rename_file sysnameDECLARE dbname_cursor CURSOR for select name from master..sysdatabases where name <> "tempdb" and name <> "pubs" and name <> "model" and name <> "msdb" select @db_count = count(*) from master..sysdatabases where name <> "tempdb" and name <> "pubs" and name <> "model" select @counter = 0 exec master..xp_cmdshell 'del e:dumps*.old /q /f' exec master..xp_cmdshell 'ren e:dumps*.* *.old' OPEN dbname_cursor while @counter < @db_count BEGIN FETCH NEXT FROM dbname_cursor into @dbname dump database @dbname to disk= "e:dumps emp" select @rename_file='ren e:dumps emp ' +@dbname exec master..xp_cmdshell @rename_file select @counter = @counter +1 select @@fetch_status ENDCLOSE dbname_cursorDEALLOCATE dbname_cursor
Answer:
I ran this code and the only problem I found was that your @counter variable is set to 0, whereas the count of databases would start with one. So the code tries and dumps the last databases twice and generates an error. This is because if the number of databases you have is 3 this line of code
while @counter < @db_count
will cause the code to loop 4 times instead of 3.
Changing the line "select @counter = 0" to "select @counter = 1" causes the backups to work with no errors.
