Easily create a Beta environment in SQL Server

We are specialists in creating Access to the cloud solutions using SQL Server. We therefore maintain a number of databases and our clients are constantly asking us for improvements. Of course, we require that those go through a full cycle of testing and deployment:

• We copy an existing database using the same name but with the word “Beta” at the end. A database named Inventory would be copied to InventoryBeta.
• The Access developer will make changes and the clients will test using the Beta version.
• Once we are given the go ahead, we roll out changes to the live database and delete the Beta.
• When the client asks for new changes we copy the live to “Beta” and start anew.

In the past this required having a server admin backup the live database to disk and then restore it using our Beta name convention. This can get onerous for a small firm like ours, so Juan asked me if we can create a stored procedure that could automate the whole process and allow developers to create the Beta database at any time without my intervention.

Assumptions

I designed a stored procedure that is invoked to create the Beta database. The procedure makes 3 main assumptions:

1) The database name does not end with “Beta”. By convention, we suffix our beta database with “Beta”, both for the database name and the file names.

2) The files associated with the database have the database name somewhere in the file name. We have to rename the files when we create the beta database to ensure the new database won’t have files that would be named same as existing production database. We typically just name our files using the database name somewhere in the filename. That then makes it easy for us to simply search and replace the production database’s name with the new name which has “Beta” suffixed.

3) The database are user databases, not system databases. I suppose it might be possible to create a beta copy of system databases but a compelling reason eludes me at the moment…. So, no. Also, the database has to exist, of course. Kind of tough to make a copy of nothing, no?

Who should use this? Hint: Not your clients!

This feature should only allowed to select users, preferably the main developer of the app or a server admin. I don’t see any scenario where a client will need to do a Beta on their own.

Combine this technique with our switch database code for a killer combo

Take a look at this post to easily switch your Access linked tables between production and beta SQL Server databases.

How does it work?

Simply put, we have a control database where various designated SQL Server logins has access to and therefore execute the stored procedure by passing in the name of the database. You could put the same procedure in your master database but we prefer to not alter the master database since it’s a system database and there’s no telling what would happen to user-defined objects in the master database, especially during an upgrade or reinstallation.

Because the stored procedure performs both BACKUP DATABASE and RESTORE DATABASE, this requires server-level permissions, something you might not want to grant to the owners of the database. To help limit the scope, you can sign the procedure with a certificate. If you are unfamiliar with the signing, I recommend that you read SQL Server MVP Erland Sommarskog’s excellent treatise here.

The procedure itself – uspCopyToBeta 

You can get the complete code here. You can see we provide error handling. Because both restore and backup are not allowed within a transaction, we can’t do it within a transaction. You also can see that some validations are performed prior to backup/restore to minimize chances that we may have failed restore/backup.

Since we want the procedure to work with any database, it is necessary to use dynamic SQL. Dynamic SQL is of course problematic if you want to minimize security risks. One way we can help restrict the risk is to hard-code the file path for the backup itself. For that reason, we use convention of a temporary folder and a temporary file that will get overwritten every time the procedure is run and has same filename & location every time. This precludes the procedure from being used in parallel which is fine; it’s unlikely anybody would be creating multiple beta databases at once.

SET @sql =
  N'BACKUP DATABASE ' + QUOTENAME(@DbName) +
  N' TO DISK = N''C:\SQL_Temp\Temp.bak''
  WITH COPY_ONLY, FORMAT, INIT, NAME = N''Backup From Live to Beta'',
  SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM';

Both FORMAT and INIT ensure that the original backup file will be overwritten completely with a new backup. We also use COPY_ONLY so that live database’s regular backup chain remains uninterrupted. Finally, we require, with CHECKSUM, that the data be verified when it is being written.

Most importantly, note that the only dynamic part of the SQL is the database name. If you look at the validation portion in the provided code, we can narrow the valid input to only certain databases. This is the only parameter that a user can directly input and the QUOTENAME() ensures that it won’t get injected in addition to the validation requiring that it be a valid database, which makes injection very hard to do anyway.

The next step is to verify the backup. In order to do so, we need to look up MSDB which holds data about backups and determine which backup we want to verify against.
SELECT
  @backupSetId = backup_set_id,
  @backupSetPosition = position
FROM msdb..backupset
WHERE database_name = @DbName
  AND backup_set_id = (
    SELECT MAX(backup_set_id)
    FROM msdb..backupset
    WHERE database_name = @DbName
  );

IF @backupSetPosition IS NULL
BEGIN
  SET @msg = N'Verify failed. Backup information for database ''' +
    @DbName + ''' not found.';
  RAISERROR(@msg, 16, 1);
END;
RESTORE VERIFYONLY FROM DISK = N'C:\SQL_Temp\Temp.bak'
WITH FILE = @backupSetPosition, NOUNLOAD, NOREWIND;

The verification, in addition to the checksum ensures that we have a good backup and there is also no direct input to the filesystem because we’re using the temporary file with a fixed path.

With a good backup, we can move on to creating a new database. However, as you might know, a database typically has a number of files. A basic database would just have a .mdf (data file) and .ldf (log file) files but some might have additional files. As part of the RESTORE command, we have to specify how to restore those files — we want to rename those files to the new database to ensure those will not conflict with the original file they were backed up from. This requires us to build up a list of files that the database uses and changing the file accordingly. We again use MSDB to help us get the file names and do the necessary formatting:
SET @moves = STUFF((
  SELECT
    N', MOVE ''' + logical_name +
    ''' TO N''' + REPLACE(physical_name, @DbName, @betaDb) + N''' '
  FROM msdb..backupfile
  WHERE backup_set_id = @backupSetId
  FOR XML PATH(N'')
), 1, 2, N'');

Refer to this article for explanation on how STUFF() and FOR XML PATH(”) can be used to get a comma delimited list. Basically, for each file we find, we write out a MOVE clause with the file’s name modified. The RESTORE command allow us to have multiple MOVE clauses so we can control how each file will be created as part of the RESTORE. Again, the user has no direct control over it beyond the @DbName. This also assumes that the beta database will be created in the same directory as the original database’s files were. This might work for some server setup, but if you want to use different directory, you may need to come up with different approach to accommodate this need.

Like the BACKUP command, dynamic SQL is needed for RESTORE to allow us to identify the new database name and identify those files we want to create.
SET @sql =
  N'RESTORE DATABASE ' + QUOTENAME(@betaDb) +
  N' FROM DISK = N''C:\SQL_Temp\Temp.bak'' WITH FILE = 1, ' + @moves +
  N', NOUNLOAD, REPLACE, STATS = 10;';

Note that we specify REPLACE for the RESTORE so that if a beta database already exists, we can overwrite and thus simplify the reloading of the beta database with latest data. In case you’re wondering about “FILE = 1”, it’s actually indicating which backup set within the device we want, and there’s going to be only one backup set so that’s OK to hard-code and has nothing to do with any actual files.

By this point, we’re done with the backing and restoring a beta database. Note that we do not need to use risky procedures such as xp_cmdshell to manipulate files which would only increase vector for attack for little gain. This is why we feel that using a designated temporary backup file in a fixed location helps to narrow the scope.

Great! But how do I get this signed?

Before you can let any database owner use the procedure to create a beta database, you do have to let them have the permission to create backups and restore those backups. Backing up requires at least db_backupoperator database fixed role, sysadmin server fixed role, or creating a server role that has sufficient permissions. Restoring requires dbcreator server fixed role or similar created server role. That’s pretty big permissions to give around. Let’s give it to a certificate instead.

The sample code for creating the certificate and signing the procedure is provided here. As mentioned at the start, you can refer to Erland’s treatise for explanation on the individual steps. Note that you need to manually grant the certificate login the db_backupoperator database fixed role or equivalent permissions for every database on your server. Obviously, you need to edit the code and use strong password and appropriate naming convention.

Whenever you add a new database you will need to repeat the step of adding the login to that role for this new database. You could grant the login sysadmin and thus avoid requirement to remember to add the login to new database’s db_backupoperator fixed role but granting sysadmin to the login, even a certificate user is just too much and besides, you might want to ensure that you know which databases are allowed to have beta database. Alternatively, you could simply alter the model system database to include the certificate and the login with rights. Any new databases created afterward will be based on the model system database and thus will have the login ready. However, we prefer to do it manually to ensure that we always make a conscious decision about the new database’s login and whether it should be privileged to create a beta environment or not. Your mileage may vary.

Running the procedure

To see how this works, log in as a user where the only permission is owning the database and execute the following statement:

EXEC MyControl.dbo.uspCopyToBeta 'NameOfDatabaseToCreateBeta';

Note that this assumes that the user has permission to use MyControl database. You can ensure that users can execute the stored procedure in other database by granting them the needed rights on the MyControl, such as adding designated logins to a custom role within MyControl database. This also ensures that only owners of the database may use the procedure in the other database.

Conclusion

We hope you find the code useful. As you can see, there are number of considerations when granting your users some server level permissions and those are not tied with any particular database, especially for restoring and ensuring that the server’s filesystem is not unnecessarily threatened.