mrgordonz
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
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
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?
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.you rtable
yourserver.cyber.cyber_rep ort.yourvi ew
new database
---------------------
yourserver.cyber_dev.cyber .yourtable
yourserver.cyber_dev.cyber _report.yo urview
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.
original database
----------------------
yourserver.cyber.cyber.you
yourserver.cyber.cyber_rep
new database
---------------------
yourserver.cyber_dev.cyber
yourserver.cyber_dev.cyber
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.
ASKER
But that is precisely the problem:
In the new database it needs to be:
yourserver.cyber_dev.cyber _dev.yourt able
yourserver.cyber_dev.cyber _dev_repor t.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.
In the new database it needs to be:
yourserver.cyber_dev.cyber
yourserver.cyber_dev.cyber
(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.
ASKER
Here is the script I used to restore the database:
RESTORE DATABASE cyber_dev
FROM DISK = N'D:\mssql_data\backup\cyb er.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\FTDat a\cat_sear ch_cyber_d ev', 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.
RESTORE DATABASE cyber_dev
FROM DISK = N'D:\mssql_data\backup\cyb
WITH FILE = 1,
MOVE N'cyber_data' TO N'D:\mssql_data\cyber_dev_
MOVE N'cyber_log' TO N'D:\mssql_data\cyber_dev_
MOVE N'sysft_cat_search' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTDat
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.obj ect
>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.custome r
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.
no, it's like this: server.database.schema.obj
>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.custome
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.
ASKER
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.