How can we help you today? How can we help you today?

How to change recovery mode of an image or clone

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.
OrionUK
0

Comments

1 comment

Sort by
  • 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.)
    Mark R
    0

Add comment

Please sign in to leave a comment.