SQL Server 2005: Restore database with different users/logins

Posted on 2009-05-26
Medium Priority
Last Modified: 2012-06-21
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!


Paul Hobbs
Question by:mrgordonz
  • 4
  • 4
LVL 42

Expert Comment

ID: 24480170
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.

Author Comment

ID: 24480200
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?
LVL 42

Expert Comment

ID: 24480265
Just restore it with a different database name.   Then you will have objects named like this.

original database

new database

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.

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.


Author Comment

ID: 24480360
But that is precisely the problem:

In the new database it needs to be:


(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.

Author Comment

ID: 24480773
Here is the script I used to restore the database:

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

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.
LVL 42

Expert Comment

ID: 24486779
>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:


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.  



Author Comment

ID: 24488730
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.
LVL 42

Accepted Solution

dqmq earned 2000 total points
ID: 24489216
Indeed, schemas have changed dramatically for the better with SQL 2005.  They provide a level of object ownership that was not possible before and really do simplify administration if used properly.  Consider that you cannot  drop a user if that user owns a single object.  With 2000, that involved changing ownership of a myriad of tables, views, procedures and other objects.  With 2005, it's simply a matter of changing schema ownership.

Consider a database with these schemas:

Then, you want a development environment and do like this:

See what has happenend? You do NOT have isolated environments, as the dbo and sys schemas are now common to both.   How do you test a change in the dbo schema? Add dbo-sys, as well?   No, it is far easier to create a second, fully parallel database which starts out as a copy of the first, including exactly the same schemas.

In Oracle, it's common for each developer to have their own schema in the development environment.  You can do that in SQL 2005, as well, but then (and just like Oracle), you need a tool to migrate object-by-object to a common schema for integration tests.  At the end of the day, only the common schemas migrate back to production.


Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question