February 4, 2013 at 4:55 pm
Hello,
I've been working in SQL for awhile now as a user and I'm only just now starting to do some DBA work. I'm in the process of going over my companies databases, restore plans, scripts, etc. and I need some help. We have multiple production databases that are all fairly large. We restore these databases to what we call "test" systems every once in a while. It's been requested to change all non-production databases to "Simple", reduce T-Log sizes, reduce VLF's, etc. The problem I see is that when someone does a restore, that database will be set back to FULL (production databases are all FULL) and the T-Logs will once again be massive, erasing all of the work that I've done. What I'd like to do is modify our manual restore job and add in a step that will change the DB to simple, shrink the log file, set the log file to a limited size (say 5,000 as an example) and change the autogrowth to unrestricted and another limited size (say 1,000). I believe this should be very simple but I don't know how to set the autogrowth size to 1,000 and unrestricted.
Here's the basic framework of what I have so far, excluding the restore,
ALTER DATABASE Example
SET RECOVERY SIMPLE
GO
DBCC SHRINKFILE(Example_Log, 5000)
GO
February 4, 2013 at 5:51 pm
Hi Josh - rather than give the answer I'll show you how to get the answer - which is much more valuable.
Do your restore step and then on the new server right click on the database and choose properties. Make all the changes you want made manually. Now instead of clicking OKAY, look up the top of the properties window and you'll see an option to script the changes. Script these to a new query window and you have now generated all the code you need to drop into your automated restore job.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply