How to change recovery mode of an image or clone

OrionUK

Production database uses FULL recovery mode but I want the image to use SIMPLE. I know how to run a TSQL script to do this however I tried this as a modification but I get the following error at the end of the image creation process:

Changed database context to 'master'.

User does not have permission to alter database 'SQLDBA', the database does not exist, or the database is not in a state that allows access checks.

ALTER DATABASE statement failed.

I would try this as part of a clone template however I will not know what the clone name database is so I do not know how to solve this.

Thanks.

Mark R

Hi, when image scripts are running, the image database is attached with a temporary name, starting with SqlCloneTemp_. SQL Clone can't attach as the name of the image, because that database might already exist. You'll need to use DB_NAME() to get the temporary database name. Here's a script that sets the recovery mode to simple, that I've tested in SQL Clone:

declare @dbname sysname = DB_NAME();<br>declare @sql nvarchar(180) = 'ALTER DATABASE [' + @dbname + '] SET RECOVERY SIMPLE;';<br>exec sp_executesql @sql;

(The temporary database name chosen by SQL Clone is always alphanumeric, so won't require escaping.)