01Why you’d do this

A customer reports a bug you can’t reproduce on your own reference database, or you’re planning an upgrade and want to rehearse it against real data before touching production. Either way, the answer is the same: get a copy of their database onto your own machine. The mechanics of a SQL Server backup and restore aren’t LabVantage-specific — but the moment you try to land two customer databases side by side on the same local SQL Server instance, or import a database whose original schema name collides with one you already have, you run into a problem that’s specific to how LabVantage stores its schema name inside the application data itself. Renaming a SQL Server schema after restore isn’t a built-in operation — it has to be done object by object.

This article covers both cases: the straightforward restore where you keep the original schema name, and the case where you need to rename it on the way in.

SQL Server Management Studio sysadmin access on the target instance A backup (.bak) or export from the customer

02Taking the export

If you’re the one pulling the export — on-site, or via remote access to the customer’s server — this part is plain SQL Server: right-click the database in Object Explorer, Tasks → Back Up…, full backup, and hand off the resulting .bak file. Nothing LabVantage-specific happens at this stage; treat it like backing up any other SQL Server database. The interesting decisions all happen on the import side.

03Two import paths

Which path you need depends entirely on whether the schema name in the backup is already free on your target instance.

Path A

Same schema name

You’re restoring one customer’s database and nothing else on this instance uses that schema name. Restore, fix the login mapping, done. See Same schema name.

Path B

Different schema name

You already have a database using that schema name, or you want a name that tells you at a glance which customer this is. Restore, then transfer every object into a freshly named schema. See Different schema name.

04Path A — restoring with the same schema name

  1. Restore the database In SSMS, right-click Databases → Restore Database…, point at the .bak file, and restore under whatever database name you want locally.
  2. Create a matching login Under Security → Logins → New Login…, create a SQL login with the same name as the schema owner in the source database.
  3. Fix the orphaned user A restored database’s internal user still points at the old server’s login SID, not your new local login — even though the names match. You’ll know this has happened if the new login can’t connect. Re-link it from a query window connected as sa:
Re-link an orphaned database user to a new login
— run in the restored database, connected as sa or sysadmin EXEC sp_change_users_login ‘Auto_Fix’, ‘customer_login’;

That’s it for the simple case — the restored database is now usable with a login local to your machine, under its original schema name.

05Path B — renaming the schema on the way in

SQL Server has no built-in “rename schema” command — only ALTER SCHEMA…TRANSFER, which moves objects one at a time. To rename an entire schema you script that transfer across every table and stored procedure it contains. Below is the procedure I use for exactly that.

  1. Restore and re-link the login Same as Path A, steps 1–3 — get the database usable under its original schema name first.
  2. Create the target schema and its owner Under Security → Users, create the new login/user if it doesn’t exist yet. Under Schemas, create the new schema and set that user as its owner. Double-check the user’s default schema is set correctly before moving on — this is the setting that quietly causes “invalid object name” errors later if it’s wrong.
  3. Install and run the transfer procedure Create the stored procedure below inside the restored database (it only needs to exist there once), then call it to move every table and routine from the old schema into the new one.
Install once per database T-SQL
CREATE PROCEDURE dbo.LV_TransferSchema (@oldschema_in VARCHAR(30), @newschema_in VARCHAR(30)) AS DECLARE @name_local VARCHAR(100), @sql_local NVARCHAR(400), @ret NUMERIC, @me_local VARCHAR(20); SET @me_local = ‘LV_TransferSchema’; — both schemas must already exist SELECT @ret = COUNT(*) FROM sys.schemas WHERE name = @newschema_in; IF @ret <> 1 BEGIN PRINT ‘There is not one schema named ‘ + @newschema_in + ‘ in this database’; RETURN; END SELECT @ret = COUNT(*) FROM sys.schemas WHERE name = @oldschema_in; IF @ret <> 1 BEGIN PRINT ‘There is not one schema named ‘ + @oldschema_in + ‘ in this database’; RETURN; END — every table and stored procedure/function in the old schema DECLARE t1 CURSOR FOR SELECT table_name FROM Information_Schema.tables WHERE table_schema = @oldschema_in UNION SELECT specific_name FROM Information_Schema.routines WHERE specific_schema = @oldschema_in AND specific_name <> @me_local; OPEN t1; FETCH NEXT FROM t1 INTO @name_local; WHILE @@FETCH_STATUS = 0 BEGIN SET @sql_local = ‘ALTER SCHEMA ‘ + @newschema_in + ‘ TRANSFER ‘ + @oldschema_in + ‘.’ + @name_local; EXEC sp_ExecuteSQL @sql_local; FETCH NEXT FROM t1 INTO @name_local; END CLOSE t1; DEALLOCATE t1;
Run it T-SQL
EXEC dbo.LV_TransferSchema ‘oldcustomerschema’, ‘newlocalschema’;
If the transfer errors partway through

It’s almost always the target schema or its owner being misconfigured — go back and confirm the new schema exists, its owning user exists, and that user’s default schema is actually set to the new schema before re-running. The procedure is safe to re-run: anything already transferred simply won’t show up in the cursor’s source list a second time.

06Reconnecting the database to LabVantage

Once the schema is sitting where you want it, the rest is the same application-server work as connecting any new LabVantage database: create a datasource on your JBoss instance pointing at this schema, then register it in the LabVantage Console with the matching database ID, JNDI name, and credentials. If you haven’t set up that connection before, see Setting Up Your Database Server for LabVantage for the datasource and schema-ownership groundwork this builds on.

07Checklist

  • Backup restored under a database name you’ll recognize six months from now
  • Login created locally and re-linked with sp_change_users_login if orphaned
  • Target schema exists, with an owner whose default schema is set correctly — checked before running the transfer
  • Every table and routine confirmed moved (query Information_Schema.tables / .routines for the old schema name — it should return nothing)
  • JBoss datasource and LabVantage Console database registration point at the correct schema name and credentials

08Field notes

✎

What actually bites people

  • The orphaned-user error message doesn’t mention orphaning at all — it just looks like a bad password. If a freshly restored login “won’t authenticate” even though you’re certain the password is right, check for an orphaned SID first.
  • Give renamed schemas an obvious naming convention tied to the customer, not the database version or date — you’ll thank yourself the next time three customer copies are sitting on the same dev instance at once.
  • Run the object count check in the checklist above every time. A transfer that silently skips one stored procedure because of a permissions issue is a debugging session waiting to happen weeks later.