In this post I'll explain how to copy a database from an on-premises environment, and restore it to a Tier1 (also know as onebox, dev box) environment. Why would you want to do that? Well typically so that you have some realistic data to develop against, or to debug a problem that you can only reproduce with the data.
If you've already read this post about copying a database in the other direction - tier1 to on-premises, then this process will be very familiar.
Overview
This process is relatively simple compared to the cloud version, because we're not switching between Azure SQL and SQL Server - it's all SQL Server. The basic gist of the process is:
1. Backup the database on the on-premises environment (no preparation needed)
2. Restore the database to the Tier 1 environment
3. Run a script against the restore DB to update some values
4. Open Visual Studio and run a database synchronize
Process
First back up the database on the on-premises environment and restore it to the Tier 1 environment - don't overwrite the existing Tier 1 database, keep that one and restore the new one with a different name - because we're going to need to copy some values across from the old DB to the new DB.
Now run this script against the newly restored DB, make sure to set the values for the database names correctly:
--Remove the database level users from the database
--these will be recreated after importing in SQL Server.
use AXDB_onpremises --******************* SET THE NEWLY RESTORED DATABASE NAME****************************
declare
@userSQL varchar(1000)
set quoted_identifier off
declare userCursor CURSOR for
select 'DROP USER [' + name +']'
from sys.sysusers
where issqlrole = 0 and hasdbaccess = 1 and name != 'dbo' and name != 'NT AUTHORITY\NETWORK SERVICE'
OPEN userCursor
FETCH userCursor into @userSQL
WHILE @@Fetch_Status = 0
BEGIN
exec(@userSQL)
FETCH userCursor into @userSQL
END
CLOSE userCursor
DEALLOCATE userCursor
--now recreate the users copying from the existing database:
use AXDB --******************* SET THE OLD TIER 1 DATABASE NAME****************************
go
IF object_id('tempdb..#UsersToCreate') is not null
DROP TABLE #UsersToCreate
go
select 'CREATE USER [' + name + '] FROM LOGIN [' + name + '] EXEC sp_addrolemember "db_owner", "' + name + '"' as sqlcommand
into #UsersToCreate
from sys.sysusers
where issqlrole = 0 and hasdbaccess = 1 and name != 'dbo' and name != 'NT AUTHORITY\NETWORK SERVICE'
go
use AXDB_onpremises --******************* SET THE NEWLY RESTORED DATABASE NAME****************************
go
declare
@userSQL varchar(1000)
set quoted_identifier off
declare userCursor CURSOR for
select sqlcommand from #UsersToCreate
OPEN userCursor
FETCH userCursor into @userSQL
WHILE @@Fetch_Status = 0
BEGIN
exec(@userSQL)
FETCH userCursor into @userSQL
END
CLOSE userCursor
DEALLOCATE userCursor
--Storage isn't copied from one environment to another because it's stored outside
--of the database, so clearing the links to stored documents
UPDATE T1
SET T1.STORAGEPROVIDERID = 0
, T1.ACCESSINFORMATION = ''
, T1.MODIFIEDBY = 'Admin'
, T1.MODIFIEDDATETIME = getdate()
FROM DOCUVALUE T1
WHERE T1.STORAGEPROVIDERID = 4 --Files stored in local on-premises storage
--Clean up the batch server configuration, server sessions, and printers from the previous environment.
TRUNCATE TABLE SYSSERVERCONFIG
TRUNCATE TABLE SYSSERVERSESSIONS
TRUNCATE TABLE SYSCORPNETPRINTERS
--Remove records which could lead to accidentally sending an email externally.
UPDATE SysEmailParameters
SET SMTPRELAYSERVERNAME = ''
GO
UPDATE LogisticsElectronicAddress
SET LOCATOR = ''
WHERE Locator LIKE '%@%'
GO
TRUNCATE TABLE PrintMgmtSettings
TRUNCATE TABLE PrintMgmtDocInstance
--Set any waiting, executing, ready, or canceling batches to withhold.
UPDATE BatchJob
SET STATUS = 0
WHERE STATUS IN (1,2,5,7)
GO
--Update the Admin user record, so that I can log in again
UPDATE USERINFO
SET SID = x.SID, NETWORKDOMAIN = x.NETWORKDOMAIN, NETWORKALIAS = x.NETWORKALIAS,
IDENTITYPROVIDER = x.IDENTITYPROVIDER
FROM AXDB..USERINFO x --******************* SET THE OLD TIER 1 DATABASE NAME****************************
WHERE x.ID = 'Admin' and USERINFO.ID = 'Admin'
Now the database is ready, we're going to rename the old Tier 1 database from AXDB to AXDB_old, and the newly restored database from AXDB_onpremises to AXDB. This means we don't have to change the AOS configuration to point to a new database - we're using the same users and the same database name.
NOte that to do the rename, you'll need to stop the Management reporter, batch, IIS and/or iisexpress services - otherwise it'll say the database is in use.
Then open Visual Studio and run a database synchronize. A tier 1 environment doesn't have the same auto-DB-synch mechanism like the on-premises environment does, so you have to run it yourself.
Notes
A few other things to note:
- Only the Admin user can log in - because I'm assuming that the users from the onebox environment were all AAD cloud users, and that's not what the on-premises environment uses. The script above fixed the Admin user, but left the others as-is.
- To get Management Reporter working again, perform a reset.
- Storage (things like document handling documents) aren't kept in the database, so copy the database hasn't copied those things across. In the script above we cleared the links in the DocuValue table, so that we don't try and open docs from local on-premises storage which aren't there.
- The script has withheld all batch jobs, to stop anything running which shouldn't.
- Data stored in fields that were encrypted in the Tier1 environment, won't be readable in the restored database - there aren't many fields that are like this, details are in the "Document the values of encrypted field" section here.