April 23, 2018 at 12:34 am
I know this isn't something new and people might be doing this in their environment. I have a requirement to do refreshes monthly, weekly etc in lower environments and I wanted to know if there is a quicker approach to this. I know we can do a backup and restore etc through SQL job (I would love to know if there is an automated script which takes care of the entire process). Also, instead of doing a full database restore every month is there a way we can only send changes that happened during the month or the week that way it would save a lot of time and wastage of space. I am not sure on how to achieve the second option of shipping only the changes. We aren't considering any HA technologies for x reasons so please do not give me those options. Any script that you have that can achieve this or if you are doing something similar in your environment and have the necessary details and scripts then please do share the same. Is there any tool that can achieve this but obviously this won't be my first option unless we can't do it via writing t-sql code. Also, our boxes are VMs so is there a possibility we can leverage the features and capabilities by taking file snapshots and delivering it to the lower environments (sorry I am a bit naive on VM capabilities and techniques) rather than doing backup and restore natively through SQL.
Fyi...we want complete data not just bare schema.
Any help would be much appreciated.
Thanks
April 23, 2018 at 4:19 am
As far as tools go, Redgate have SQL Clone, which is very useful for provisioning and also allows you to obfuscate the data as required, can work off backups or the live version and takes seconds to provision.
If you want an in house tool, I would probably look at powershell scripts to do the work for you.
April 23, 2018 at 6:49 am
Without using log shipping or replication or availability groups (all of which you've dismissed), I don't know of any way you can easily move only the data that has changed except by building, by hand, a data migration based on some method of merge. It'll be a ton of work to put together and maintain. This is why people use those other technologies rather than a "roll your own" approach. I know you said you don't want those, but they exist for a reason.
At my previous employer, we built a script that restored a production backup to a non-production environment, cleaned the data, shrank the database, then backed that up. We used the cleaned data to restore to all other non-production environments. It's a very straight forward approach. The scripts aren't ones I can hand you, except the backup and restore script, because our data cleansing needs were unique to our environment. Yours will be unique too. You'll have to build the scripts to clean the data properly. That's the real work because backup & restore scripts are pretty simple and extremely well documented.
I work for Redate (disclaimer), and we have a great tool that helps with all this. SQL Clone. Makes it all very fast. You do have to create an image, which takes about the same amount of time and space as a backup, but then the clones take up almost no space at all and are created in seconds. It is how I recommend provisioning be done now. In addition, we have a tool that assists with data masking. The full suite of tools are in SQL Provision.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 23, 2018 at 9:24 am
Grant Fritchey - Monday, April 23, 2018 6:49 AMWithout using log shipping or replication or availability groups (all of which you've dismissed), I don't know of any way you can easily move only the data that has changed except by building, by hand, a data migration based on some method of merge. It'll be a ton of work to put together and maintain. This is why people use those other technologies rather than a "roll your own" approach. I know you said you don't want those, but they exist for a reason.At my previous employer, we built a script that restored a production backup to a non-production environment, cleaned the data, shrank the database, then backed that up. We used the cleaned data to restore to all other non-production environments. It's a very straight forward approach. The scripts aren't ones I can hand you, except the backup and restore script, because our data cleansing needs were unique to our environment. Yours will be unique too. You'll have to build the scripts to clean the data properly. That's the real work because backup & restore scripts are pretty simple and extremely well documented.
I work for Redate (disclaimer), and we have a great tool that helps with all this. SQL Clone. Makes it all very fast. You do have to create an image, which takes about the same amount of time and space as a backup, but then the clones take up almost no space at all and are created in seconds. It is how I recommend provisioning be done now. In addition, we have a tool that assists with data masking. The full suite of tools are in SQL Provision.
Grant,
Could you please provide me with the backup and restore scripts...we don't have PII data so no need to scrub it. I will look into the option of SQL Clone. I'v seen a few demo videos and I am highly impressed but as I mentioned before. I want to achieve this all via code if not then I can ask management to look into buying a tool that can suffice the need..usually they always want to save money...lol
April 23, 2018 at 9:34 am
Get your infrastructure folks to automate SAN snapshots for you. They're nearly instantaneous.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 23, 2018 at 11:02 am
Jeff Moden - Monday, April 23, 2018 9:34 AMGet your infrastructure folks to automate SAN snapshots for you. They're nearly instantaneous.
Thanks! Jeff. Will this require an outage on the production system or any adverse affects of it?
April 23, 2018 at 11:08 am
Feivel - Monday, April 23, 2018 11:02 AMJeff Moden - Monday, April 23, 2018 9:34 AMGet your infrastructure folks to automate SAN snapshots for you. They're nearly instantaneous.Thanks! Jeff. Will this require an outage on the production system or any adverse affects of it?
Not if it's done correctly. You need to check the SAN documentation or have your SAN administrator check on it. Not all SAN manufacturers build in such a capability and it is sometimes an addition "product" that you need to buy but it's usually very well worth it in such cases as what you describe.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply