July 10, 2012 at 3:34 am
Hi,
This morning I have been looking at some code - and the code is staring right back at me - so I'm stuck.
The goal is to create script that I can run once a week or so to change all databases on a **non-production** system to Simple recovery. That way I do not need to make any log backups, and I do not need any transactionlevel rollbacks on those systems either. As developers may deploy new databases, they are not always able to create the database with the Simple Recovery model, or they do not want to change it afterwards (or do not know how to). As they use the same code to create the databases in Production environments, where we *do* wish to use Full Recovery, changing the code is not really an option either (for them).
As I created the script, the ALTER DATABASE statement is being a real pain. For some reason, it will not accept the databasename as input. Please check my code below, and tell me what I am missing here. Using the same variable in a PRINT statement worked fine, so it must be something special with ALTER DATABASE I think, though I cannot find it anywhere.
-- First, declare the variable @databasename
declare @databasename varchar(128)
-- Next declare the cursor to contain all databases with the
-- recovery model 1 (which is FULL) but not the systemdatabases
declare dbcursor CURSOR for
select name from master.sys.databases
where name not in ('master','tempdb','model','msdb')
and recovery_model=1
-- Open the cursor and put the databasename in to the @databasename
-- variable.
open dbcursor
fetch next from dbcursor into @databasename
while @@FETCH_STATUS = 0
-- Now run the code to change the database
Begin
alter database @databasename set recovery simple
-- When that is done, get the next name from the cursor until all
-- have been modified
fetch next from dbcursor into @databasename
end
-- Close and deallocate the cursor
close dbcursor
deallocate dbcursor
I hope there is someone here who can enlighten me on this - to me - weird issue.
Cheers,
Rick
July 10, 2012 at 3:59 am
I havent checked your syntax but just going by your word, I am expecting you looking for some solution which can change the database to simple recovery and which you can schedule in job.
So, have a look of this script where I commented the execution. Run it in any non-live system and see if this is what you looking as result then you can un-comment --exec(@sql)
declare @sql varchar(200),@name varchar(20)
declare cur cursor for
select name from sys.databases --where name in ()
open cur
fetch next from cur into @name
while @@FETCH_STATUS = 0
begin
set @sql = 'alter database '+ @name + ' set recovery simple;'
print @sql
--exec(@sql)
fetch next from cur into @name
end
close cur
deallocate cur
----------
Ashish
July 10, 2012 at 4:10 am
Ashish,
Thank you, your code does work. However, I would like to learn why my code does not work. Does it have anything to do with datatypes or something?
Cheers,
Rick
July 10, 2012 at 4:15 am
its because, sql not able to understand
alter database @databasename set recovery simple
in your code.
If you put this in some variable, like I did in @sql and then exec it, it will work.
----------
Ashish
July 10, 2012 at 5:18 am
Thanks.
Still weird, as it did understand what I meant with PRINT @databasename 🙂
July 10, 2012 at 5:44 am
crazy4sql (7/10/2012)
its because, sql not able to understand
alter database @databasename set recovery simplein your code.
If you put this in some variable, like I did in @sql and then exec it, it will work.
it's because the ALTER command doesn't accept parameters, i believe.
the command expects an object name, and not a parameter that will be substituted and resolved into the object name;
there's quite a few commands that are that way;
even the TOP command required an integer, and not a variable up until recently, for example.
Lowell
July 10, 2012 at 6:02 am
Lowell,
Thanks, now I understand the how and why. Always good to learn something.
Cheers
Rick
July 10, 2012 at 8:10 am
please ignore . sorry.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply