Refreshing SQL Server development databases

Refreshing the Dev environment

I started a new contract recently.  This is a global company, but I am on a relatively small product team within it.  10 developers, 3 admins (SQL, O/S, Storage), 2 Business Analysts.

The company has a formal Change Management process, which is great.  However, this team manages and operates outside of that structure...not so good.   Devs have sysadmin to production. For now.

I and the other DBAs are working on all of the things you would expect to be missing from this sort of environment...consistent backup strategy, documentation, proper security, etc.

As with most places our Developers have a Dev copy of the database and are told not to touch the Pre-Prod version.  But, since we are test loading data there, Dev is way out of date.  Also, there are objects in Dev not moved to Pre-Prod yet.  So, they regularly point their queries, procedures and even the website to the PP database.  And then forget what they are connected to after lunch.

This makes for interesting hallway conversations!

The solution of course is to refresh the Dev db from Pre-prod, without losing any Dev code or users.

DBAs that have been around for awhile know where I am going with this...

Basic steps:

1. Backup the Production (or Pre-prod in my case) database.   Or pull from your overnight backup.   You DO have an overnight backup, right?   RIGHT?!?!?   ;)

2. Restore that backup to the Dev server as MyDB_Refresh:

-- Get the logical and physical filenames

Restore filelistonly

From disk ='B:\Backup\FULL\MyDB_FULL_20160810_000012.bak'

---Restore, not overwriting the existing Dev db

Restore Database MyDB_Refresh

From disk ='B:\Backup\FULL\MyDB_FULL_20160810_000012.bak'

With move 'MyDB' to 'B:\Data\MyDB_Refresh.mdf'

       ,move 'MyBD_log' to 'B:\Log\MyDB_Refresh_log.ldf'

       ,recovery

3.  Use SQL Server Data Tools/Visual Studio, or a 3rd party tool such as Red Gate SQL Compare to compare and synchronize the schema in MyDB (Dev copy) to MyDB_Refresh.  SSDT works well for this if you don't have a license for a vendor tool, or don't want to download a trial version.  The Refresh database is the Target...

4. Once you have the tables, procedures, USERS, and other objects exactly the same as the old Dev database, you need to rename both databases:

--Make sure all connections are closed:

Use master;

Go

exec Sp_renameDB 'MyDB', 'MyDB_Old'

exec sp_renameDB 'MyDB_Refresh', 'MyDB'

If you are having trouble getting exclusive use because Devs or apps keep re-connecting, you need to use a 2-step process (run both at the same time):

--Take the database offline and kill connections

Alter database MyDB Set offline

with rollback immediate

--Bring it back online and rename all at once

Alter database MyDB set online

use master;

go

Sp_renamedb 'MyDB', 'MyDB_Old'

Change the database names if you need to do the same on MyDB_Refresh.  You can use the same offline/online code when you are doing an emergency restore in prod and the app servers won't let go of their connections.

Compare a select of tables between Prod and Dev to make sure the data is refreshed, and double check using schema compare that Dev is different than Prod (likely, since that is what Devs do...).

This is a basic process, best done in a maintenance window, early in the morning or late at night.  And you can automate most of this.  I have not tried to automate schema compares other than using SQLPackage.exe in the SSDTools to "upgrade" a target data-tier application, or create a Drift report.   But that is way beyond the scope of this post.  Maybe another time :)

Please feel free to post up your processes, or enhancements to this one that some new DBA or Windows admin may be able to use 3 years from now when he finds this post on Google.

Have a fantastic day!

Kevin3NF