asked
Restore backup from Full-Recovery DB as Simple
I regularly clone our production DB (Full Recovery mode) to a staging area to test changes and qualify tools. Most of the time I do not need the clone to be in full recovery mode so my clone script does a backup, restore with replace, alters the clone to simple recovery, does some data massaging. I'm wondering if I can change things a bit so that the clone DB is build as simple from the get go -> I'm not really worried about it failing so I don't even need transactional integrity during load ... as I do see activity on both the clone Log file and Data file during the recovery step I'm wondering if I can save some IO load and time. thanks
The restore puts your database exactly as it was the time the backup was taken. I think what you're currently doing is fine, and you would not get a benefit even if you could change it earlier in the process.
I maintain several "non-production" systems that are populated by restoring databases from production; however, as the production databases are (obviously) in `FULL` recovery, and we really don't want (or need) that overhead in non-production, I lashed together a script that runs as a scheduled task every day to make sure that *all* databases on a server are in `SIMPLE` recovery. declare @SetSimpleSQL varchar(8000) -- change the "8000" to "max" for SQL2005 or later select @SetSimpleSQL = '' select @SetSimpleSQL = @SetSimpleSQL + 'alter database [' + name + '] set recovery simple' + char(13) + char(10) from sysdatabases where name not in ('master', 'tempdb') exec (@SetSimpleSQL) OK, this was lashed together for a SQL2000 system...
