Link to home
Start Free TrialLog in
Avatar of mrgordonz
mrgordonzFlag for Australia

asked on

SQL Server 2005: Restore database with different users/logins

In brief, I have a scenario where I want to restore a database backup as a different database, plus also change the owner of the objects and the logins that are associated with the new database.

In more detail:

I have a database called cyber.  I have two logins: cyber and cyber_report.  In addition to the normal users (eg: dbo, guest, etc) the cyber database has two users: cyber and cyber_report.  These two users are associated with the respective logins of the same name.  Also, there are two schemas: cyber and cyber_report.  The main difference between the users cyber and cyber_report is that cyber user owns all the tables, stored procs, etc.  The cyber_report owns a bunch of views which are used by the reporting engine (Crystal Reports).  The application for which this database is the back end connects using the cyber user.

I want to backup the cyber database, and restore it as cyber_dev.  Just like above, there will be two logins: cyber_dev and cyber_dev_report.  The cyber_dev database needs to have two users and two schemas: cyber_dev and cyber_dev_report.

The straight restore part I can do without too much drama.  But when I do, all the tables etc are still owned by cyber (not cyber_dev).  I want to be able to either some how "map" the old logins/users/schemas to the new ones; or alternatively run a script after the restore which "fixes" the new database.

This is somewhat urgent - please help!

Cheers,

Paul Hobbs
Avatar of dqmq
dqmq
Flag of United States of America image

That will be a lot of work.  You need to create the two new owners, create the two new schemas, rename each object from the old schema to the new schema, remap the new users to the old logins, drop the old schemas and drop the old users.  

And for what?  It adds no additional security and makes promoting programs out of dev an order of magnitude more difficult because the schema name changes between environments.  Push back on whoever came up with the idea.
Avatar of mrgordonz

ASKER

Hi dqmq,

What is a better solution then?  I need to be able to have the two databases in the same instance of SQL Server 2005.  Each database is being accessed by a separate instance of the application (which runs on a separate app server).

This was my idea, so unfortunately there isn't anyone to push back on. :-)

I am a bit rusty on SQL Server - the past few years I have worked mostly with Oracle.  How does one backup and restore a database as a new database (in the same instance of SQL Server) without running into the problem of mapping users, logins and schemas?
Just restore it with a different database name.   Then you will have objects named like this.

original database
----------------------
yourserver.cyber.cyber.yourtable
yourserver.cyber.cyber_report.yourview


new database
---------------------
yourserver.cyber_dev.cyber.yourtable
yourserver.cyber_dev.cyber_report.yourview


Everything within the database will be named the same and database security will all be the same.   But the cyber and cyber_report logins WILL NOT HAVE access to the new database.  You will need to grant access or create a different set of logins that have access.


But that is precisely the problem:

In the new database it needs to be:

yourserver.cyber_dev.cyber_dev.yourtable
yourserver.cyber_dev.cyber_dev_report.yourview

(I assume it works like this: server.schema.user.object)

I did as you suggested, but for all the tables the schema remained cyber; it didn't change to cyber_dev.  Same goes with stored procs, functions, views, etc.
Here is the script I used to restore the database:

RESTORE DATABASE cyber_dev
FROM  DISK = N'D:\mssql_data\backup\cyber.bak'
WITH  FILE = 1,  
MOVE N'cyber_data' TO N'D:\mssql_data\cyber_dev_data.mdf',  
MOVE N'cyber_log' TO N'D:\mssql_data\cyber_dev_log.ldf',  
MOVE N'sysft_cat_search' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData\cat_search_cyber_dev',  NOUNLOAD,  REPLACE,  STATS = 10
GO

I then manually created the new logins, schemas and users (cyber_dev and cyber_dev_report).  Thus far I have been running a variety of scripts to change the schema for tables, views, stored procs, etc.  Seems to be working, but it is tedious.  This is sooooo much easier in Oracle.
>I assume it works like this: server.schema.user.object

no, it's like this:  server.database.schema.object

>I did as you suggested, but for all the tables the schema remained cyber; it didn't change to cyber_dev.

That's correct. And that's how I would prefer it.

Consider one table, originally called:

    server.CIBER.ciber.customer

You connect to server, do a "USE CIBER" (the database) and reference ciber.customer (schema.table) in your code.

You have a copy in the CIBER_DEV database on the same server.  You connect to the server, do a "USE CIBER_DEV" and still reference ciber.customer in your code. See, this is very important: the same code works in both environments.

If the schema name changes between environments, then all the references to it also need to change when you migrate code.  I know some places do it that way, mostly by avoiding references to the schema name and there are other compromises, as well.

In other words, it's easier to maintain separate databases for each environment than it is to maintain separate enviroments within one database.  I've found the same to be true in Oracle.  





 








Hi dqmq

Thank you for all your help and advice - I will carve out some time to see if I can get the application to work by doing things as you have described.  

I think part of the issue for me is that I am used to schema being one thing in Oracle, but it seems to be slightly different in SQL Server 2005.  I have found that using the Oracle datapump I can import a schema, and use the remap_schema parameter to ensure that all the objects sit in the correct schema.  So in a single Oracle database I have lots of different schemas, each for separate instances of the application.  

Where I am getting a little confused is that in SQL Server you have lots of separate databases, and now in 2005 they have introduced schemas.  I don't quite see the point of schemas if you have separate databases anyway.  I last worked on SQL Server 2000 a few years ago so I think I have some catch-up reading to do.
ASKER CERTIFIED SOLUTION
Avatar of dqmq
dqmq
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial