I am looking for specific documentation on how to restore a mission critical
Report Services environment in the event of a catastrophic failure.
Thanx in advance,
GregOn Sep 8, 12:25 pm, "SurferJoe" <Surfer...@.newsgroup.nospam> wrote:
> I am looking for specific documentation on how to restore a mission critical
> Report Services environment in the event of a catastrophic failure.
> Thanx in advance,
> Greg
This article should help.
http://msdn2.microsoft.com/en-us/library/ms155814.aspx
Regards,
Enrique Martinez
Sr. Software Consultant|||> http://msdn2.microsoft.com/en-us/library/ms155814.aspx
This article and subsequent topics contained therein do not give specific
restore step-by-step instructions.
I am attempting to restore/migrate a report services installation in a
testing environment to see if it will work, so far no success:(
Thanx,
Greg|||Hello Greg,
In the article Martinez provided, you could see that we need to backup the
reporting services databases. From your description, it seems that you want
to move the reporting services to another server. This more like migrate
the reporting services.
There is a step by step instruction for migrating SQL 2000 reporting
services to SQL Server 2005 reporting services. You could also follow the
steps to migrate Reporting Services 2005 to 2005.
Migrating Reporting Services
http://msdn2.microsoft.com/en-us/library/ms143724.aspx
Hope this helps.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||This article speaks better to what I am trying to do;
http://support.microsoft.com/kb/842425
These articles are weak on exact restore procedures;
http://msdn2.microsoft.com/en-us/library/ms155814.aspx
http://msdn2.microsoft.com/en-us/library/ms143724.aspx
I have the databases and symmetric key successfully "restored/migrated" to
the new computer running under a different named instance of SQL server.
The remaining problem is how to get the browser or ASP folder structure
"restored/migrated" to the new computer. It not real clear just where this
information is stored for retrieval by the browser.|||Hello Greg,
I am not sure what ASP folder structure you mean. If you mean the report
manager and report server virtual directory, you don't have to move them
because you need to install reporting services in the new server which will
create those 2 virtual directories.
All the report information is stored in the ReportServer and
ReportServerTemp databases. In the KB article you found, it indicate that
you need to move the database to the new server. So all the information
will move to the new server.
Hope this is clear and if you have any question, please feel free to let me
know.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||In nutshell;
The report services databases symmetric key and the virtual directory
information were all backed up from machine (E).
The report services databases and symmetric key were then restored to
machine (M).
After this "Report Services Configuration Manager" indicated that both
machine (E) & (M) were initialized and no changes could be effected using
"Report Services Configuration Manager".
The instructions contained in this article
http://support.microsoft.com/kb/842425 were followed eliminating machine
name (E) from initialized instances.
Report Services seems to be running correctly now on machine (M) with the
data and symmetric key restored from machine (E).
The Report Services Folder Structure available via IE / Report Manager does
not reflect that of the source server machine (E).|||Hello Joe,
Thanks for the update and sharing. If you have any questions, please feel
free to let me know.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.
Showing posts with label environment. Show all posts
Showing posts with label environment. Show all posts
Tuesday, March 20, 2012
Restore Replicated Database
I seem to have run into a problem within an environment I have set up with
replication.
There is an environment called STAGING where releases and changes are tested
before being moved into production. This database is an exact copy of
production. We also have replication set up in this environment. It should be
known they are different servers.
Here's the problem: STAGING is updated by taking a backup of production and
doing a restore in the environment. I know that restoring a non replicated
database over a replicated database will break replication because of the
loss of the metadata.
Is it possible to restore both the distribution AND the replicated database
in order to preseerver replication in STAGING? Or does the fact there are two
different server names preclude us from being a ble to do a restore at all.
I've also considered generating SQL Scripts of our staging environment's
replication topology and just running those scripts after each restore.
Thank You!
Is
This is correct - the different server names preclude a successful restore
of the replication settings. The easiest solution is to use the replication
script generated on the origional server and search and replace on the
servername. You might also have to change the job owners if they have not
been set to sa.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I'm going to create a job that will run after the restore has completed to
recreate the publicaion and subscriptions.
Will the fact that the distribution database is "different" than the
database in production, will recreating the publication on the other server
still work? The articles and everything will still be thae same.
"Paul Ibison" wrote:
> This is correct - the different server names preclude a successful restore
> of the replication settings. The easiest solution is to use the replication
> script generated on the origional server and search and replace on the
> servername. You might also have to change the job owners if they have not
> been set to sa.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||As long as you change references to the server (eg @.publisher, @.subscriber),
job owners if necessary, the repldata path if you are setting up the
distributor, the distribution database path also if it is to be on a
different disk etc then the rest of the script is constant. This sounds a
lot but it is pretty straightforward really. In some circumstances you might
want different agent profiles on each server and this is also something to
be aware of.
HTH,
Paul Ibison, SQL MVP
"A. Robinson" <ARobinson@.discussions.microsoft.com> wrote in message
news:B09551B0-3147-470C-9A8C-4A157D1D3A98@.microsoft.com...[vbcol=seagreen]
> I'm going to create a job that will run after the restore has completed to
> recreate the publicaion and subscriptions.
> Will the fact that the distribution database is "different" than the
> database in production, will recreating the publication on the other
> server
> still work? The articles and everything will still be thae same.
> "Paul Ibison" wrote:
|||Paul:
Here's kind of what I've come up with - it stinks but it seems to be the
only way to guarantee everything will come back up with as little fuss as
possible.
I'm going to generate the DROP / CREATE SQL for both the publications and
the subscription. For the subscription script, I don't think I'll need to
generte the SQL for the jobs - or will I?
Once the restore of the database has taken place, I'll then run a script to
DROP the publication and subscription. I'll then run the script to CREATE the
publication, create the subscription, then start the snapshot agent.
Essentially I'm recreating the entire replication topology from scratch,
which kinda sucks.
Have I missed anything?
Thanks!
"Paul Ibison" wrote:
> As long as you change references to the server (eg @.publisher, @.subscriber),
> job owners if necessary, the repldata path if you are setting up the
> distributor, the distribution database path also if it is to be on a
> different disk etc then the rest of the script is constant. This sounds a
> lot but it is pretty straightforward really. In some circumstances you might
> want different agent profiles on each server and this is also something to
> be aware of.
> HTH,
> Paul Ibison, SQL MVP
>
> "A. Robinson" <ARobinson@.discussions.microsoft.com> wrote in message
> news:B09551B0-3147-470C-9A8C-4A157D1D3A98@.microsoft.com...
>
>
|||Hi there,
We do this backup/restore process all the time from our production
enviroment to our test enviroment. We are running SQL server 2000 with
merge replication with 3 puplications...
each time we restore the database and use the scripts from the
production envrionment to set up the publications again.
The problem we are having is with identity ranges. When we republish the
restored database in the test environment and try doing any inserts on
the server we get the identity ranges full message and run the
sp_adjustpublisheridentityrange sp to fix the problem. When we do this
sometimes it works in that we can do inserts at the server and other
times it doesnt. I've come across references to restarting the merge
agent.. does this mean after i run the stored procedure we need to
stop/re start the SQL agent on the server to get the identity ranges
back in sync or does it mean syncing one of the subscriber databases?
i guess what we're looking for is a consistent way of checking the
identity ranges - and then a consistent way of setting them... on re
published databases. This is causing us serious grief as one of our
app's in test is to merge duplicate entries on our system and it keeps
falling over in test due to the indentity ranges filling up...
Any help would be greatly appreciated -
Cheers,
Michael
*** Sent via Developersdex http://www.codecomments.com ***
|||Michael:
Would it be an imposition if I were to ask you to outline your procedure for
doing your restore from a production environment?
I'm just kind of looking for a template to work from. I've tried a couple
different ways and still run into the occasional issue.
What we need to implement is a "sure fire" way to perform the refresh. The
process will need to be automated, so it has to be fairly bullet proof.
Any insight would be greatly appreciated!!
Thanks in advance...
"Michael McGoldrick" wrote:
> Hi there,
> We do this backup/restore process all the time from our production
> enviroment to our test enviroment. We are running SQL server 2000 with
> merge replication with 3 puplications...
> each time we restore the database and use the scripts from the
> production envrionment to set up the publications again.
> The problem we are having is with identity ranges. When we republish the
> restored database in the test environment and try doing any inserts on
> the server we get the identity ranges full message and run the
> sp_adjustpublisheridentityrange sp to fix the problem. When we do this
> sometimes it works in that we can do inserts at the server and other
> times it doesnt. I've come across references to restarting the merge
> agent.. does this mean after i run the stored procedure we need to
> stop/re start the SQL agent on the server to get the identity ranges
> back in sync or does it mean syncing one of the subscriber databases?
> i guess what we're looking for is a consistent way of checking the
> identity ranges - and then a consistent way of setting them... on re
> published databases. This is causing us serious grief as one of our
> app's in test is to merge duplicate entries on our system and it keeps
> falling over in test due to the indentity ranges filling up...
> Any help would be greatly appreciated -
> Cheers,
> Michael
>
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
>
replication.
There is an environment called STAGING where releases and changes are tested
before being moved into production. This database is an exact copy of
production. We also have replication set up in this environment. It should be
known they are different servers.
Here's the problem: STAGING is updated by taking a backup of production and
doing a restore in the environment. I know that restoring a non replicated
database over a replicated database will break replication because of the
loss of the metadata.
Is it possible to restore both the distribution AND the replicated database
in order to preseerver replication in STAGING? Or does the fact there are two
different server names preclude us from being a ble to do a restore at all.
I've also considered generating SQL Scripts of our staging environment's
replication topology and just running those scripts after each restore.
Thank You!
Is
This is correct - the different server names preclude a successful restore
of the replication settings. The easiest solution is to use the replication
script generated on the origional server and search and replace on the
servername. You might also have to change the job owners if they have not
been set to sa.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I'm going to create a job that will run after the restore has completed to
recreate the publicaion and subscriptions.
Will the fact that the distribution database is "different" than the
database in production, will recreating the publication on the other server
still work? The articles and everything will still be thae same.
"Paul Ibison" wrote:
> This is correct - the different server names preclude a successful restore
> of the replication settings. The easiest solution is to use the replication
> script generated on the origional server and search and replace on the
> servername. You might also have to change the job owners if they have not
> been set to sa.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||As long as you change references to the server (eg @.publisher, @.subscriber),
job owners if necessary, the repldata path if you are setting up the
distributor, the distribution database path also if it is to be on a
different disk etc then the rest of the script is constant. This sounds a
lot but it is pretty straightforward really. In some circumstances you might
want different agent profiles on each server and this is also something to
be aware of.
HTH,
Paul Ibison, SQL MVP
"A. Robinson" <ARobinson@.discussions.microsoft.com> wrote in message
news:B09551B0-3147-470C-9A8C-4A157D1D3A98@.microsoft.com...[vbcol=seagreen]
> I'm going to create a job that will run after the restore has completed to
> recreate the publicaion and subscriptions.
> Will the fact that the distribution database is "different" than the
> database in production, will recreating the publication on the other
> server
> still work? The articles and everything will still be thae same.
> "Paul Ibison" wrote:
|||Paul:
Here's kind of what I've come up with - it stinks but it seems to be the
only way to guarantee everything will come back up with as little fuss as
possible.
I'm going to generate the DROP / CREATE SQL for both the publications and
the subscription. For the subscription script, I don't think I'll need to
generte the SQL for the jobs - or will I?
Once the restore of the database has taken place, I'll then run a script to
DROP the publication and subscription. I'll then run the script to CREATE the
publication, create the subscription, then start the snapshot agent.
Essentially I'm recreating the entire replication topology from scratch,
which kinda sucks.
Have I missed anything?
Thanks!
"Paul Ibison" wrote:
> As long as you change references to the server (eg @.publisher, @.subscriber),
> job owners if necessary, the repldata path if you are setting up the
> distributor, the distribution database path also if it is to be on a
> different disk etc then the rest of the script is constant. This sounds a
> lot but it is pretty straightforward really. In some circumstances you might
> want different agent profiles on each server and this is also something to
> be aware of.
> HTH,
> Paul Ibison, SQL MVP
>
> "A. Robinson" <ARobinson@.discussions.microsoft.com> wrote in message
> news:B09551B0-3147-470C-9A8C-4A157D1D3A98@.microsoft.com...
>
>
|||Hi there,
We do this backup/restore process all the time from our production
enviroment to our test enviroment. We are running SQL server 2000 with
merge replication with 3 puplications...
each time we restore the database and use the scripts from the
production envrionment to set up the publications again.
The problem we are having is with identity ranges. When we republish the
restored database in the test environment and try doing any inserts on
the server we get the identity ranges full message and run the
sp_adjustpublisheridentityrange sp to fix the problem. When we do this
sometimes it works in that we can do inserts at the server and other
times it doesnt. I've come across references to restarting the merge
agent.. does this mean after i run the stored procedure we need to
stop/re start the SQL agent on the server to get the identity ranges
back in sync or does it mean syncing one of the subscriber databases?
i guess what we're looking for is a consistent way of checking the
identity ranges - and then a consistent way of setting them... on re
published databases. This is causing us serious grief as one of our
app's in test is to merge duplicate entries on our system and it keeps
falling over in test due to the indentity ranges filling up...
Any help would be greatly appreciated -
Cheers,
Michael
*** Sent via Developersdex http://www.codecomments.com ***
|||Michael:
Would it be an imposition if I were to ask you to outline your procedure for
doing your restore from a production environment?
I'm just kind of looking for a template to work from. I've tried a couple
different ways and still run into the occasional issue.
What we need to implement is a "sure fire" way to perform the refresh. The
process will need to be automated, so it has to be fairly bullet proof.
Any insight would be greatly appreciated!!
Thanks in advance...
"Michael McGoldrick" wrote:
> Hi there,
> We do this backup/restore process all the time from our production
> enviroment to our test enviroment. We are running SQL server 2000 with
> merge replication with 3 puplications...
> each time we restore the database and use the scripts from the
> production envrionment to set up the publications again.
> The problem we are having is with identity ranges. When we republish the
> restored database in the test environment and try doing any inserts on
> the server we get the identity ranges full message and run the
> sp_adjustpublisheridentityrange sp to fix the problem. When we do this
> sometimes it works in that we can do inserts at the server and other
> times it doesnt. I've come across references to restarting the merge
> agent.. does this mean after i run the stored procedure we need to
> stop/re start the SQL agent on the server to get the identity ranges
> back in sync or does it mean syncing one of the subscriber databases?
> i guess what we're looking for is a consistent way of checking the
> identity ranges - and then a consistent way of setting them... on re
> published databases. This is causing us serious grief as one of our
> app's in test is to merge duplicate entries on our system and it keeps
> falling over in test due to the indentity ranges filling up...
> Any help would be greatly appreciated -
> Cheers,
> Michael
>
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
>
Restore puts copy of user tables in master
Environment is SQL Server 2000 64 bit.
I restore from a script 'my' database, this works fine. However, all
the tables are also found in master, no data though.
Anyone experienced this?"Emille378" <dishonty@.seidata.com> wrote in message
news:d25b2692.0405050650.854b85c@.posting.google.co m...
> Environment is SQL Server 2000 64 bit.
> I restore from a script 'my' database, this works fine. However, all
> the tables are also found in master, no data though.
> Anyone experienced this?
I restore from a script 'my' database, this works fine. However, all
the tables are also found in master, no data though.
Anyone experienced this?"Emille378" <dishonty@.seidata.com> wrote in message
news:d25b2692.0405050650.854b85c@.posting.google.co m...
> Environment is SQL Server 2000 64 bit.
> I restore from a script 'my' database, this works fine. However, all
> the tables are also found in master, no data though.
> Anyone experienced this?
Can you show us the script?
Monday, March 12, 2012
Restore puts copy of user tables in master
Environment is SQL Server 2000 64 bit.
I restore from a script 'my' database, this works fine. However, all
the tables are also found in master, no data though.
Anyone experienced this?"Emille378" <dishonty@.seidata.com> wrote in message
news:d25b2692.0405050650.854b85c@.posting.google.co m...
> Environment is SQL Server 2000 64 bit.
> I restore from a script 'my' database, this works fine. However, all
> the tables are also found in master, no data though.
> Anyone experienced this?
I restore from a script 'my' database, this works fine. However, all
the tables are also found in master, no data though.
Anyone experienced this?"Emille378" <dishonty@.seidata.com> wrote in message
news:d25b2692.0405050650.854b85c@.posting.google.co m...
> Environment is SQL Server 2000 64 bit.
> I restore from a script 'my' database, this works fine. However, all
> the tables are also found in master, no data though.
> Anyone experienced this?
Can you show us the script?|||"Greg D. Moore \(Strider\)" <mooregr_deleteth1s@.greenms.com> wrote in message news:<%fgmc.160405$M3.149305@.twister.nyroc.rr.com>...
> "Emille378" <dishonty@.seidata.com> wrote in message
> news:d25b2692.0405050650.854b85c@.posting.google.co m...
> > Environment is SQL Server 2000 64 bit.
> > I restore from a script 'my' database, this works fine. However, all
> > the tables are also found in master, no data though.
> > Anyone experienced this?
> Can you show us the script?
RESTORE DATABASE XX
FROM DISK = 'g:\XX_db.BAK'
WITH STATS = 10, REPLACE,
MOVE 'XX_data' TO 'h:\Program Files\Microsoft SQL
Server\MSSQL\Data\XX_Data.mdf',
MOVE 'XX_log' TO 'g:\logs\XX_Log.ldf',
MOVE 'XX_Indx' TO 'h:\Program Files\Microsoft SQL
Server\MSSQL\Data\XX_Indx_Data.NDF'
Restore prod to dev with different users
Does anyone have a solution to the restore problem from a production
environment to a dev/stage environment where different users exist?
For our enterprise ETL and reporting tools we use business process
(BP) IDs that differ between environments, and of course, developers
exist in dev/stage where they don't in production. Mapsid works if the
same logins exist. I have resorted to manually reviewing users and
their permissions in the database before restore, and then putting
everything back afterwards.
ThxIf you have some additional users in your dev environments, then when you
restore your production db to dev, those users will not have access the
restored database.
The way I'd go about it, is to script all the users and their permissions,
save it a file and run it after the restore. Your file will typically
contain call to sp_grantdbaccess and GRANT, DENY and REVOKE commands.
Or simply have a script to create a database role, grant access to all
users, add the users to this group, and apply permissions on this group
Do it once, save the script, and reuse it when needed.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Shawn" <coloradocamper@.hotmail.com> wrote in message
news:9ff983ee.0408180619.26ec1ac8@.posting.google.com...
Does anyone have a solution to the restore problem from a production
environment to a dev/stage environment where different users exist?
For our enterprise ETL and reporting tools we use business process
(BP) IDs that differ between environments, and of course, developers
exist in dev/stage where they don't in production. Mapsid works if the
same logins exist. I have resorted to manually reviewing users and
their permissions in the database before restore, and then putting
everything back afterwards.
Thx
environment to a dev/stage environment where different users exist?
For our enterprise ETL and reporting tools we use business process
(BP) IDs that differ between environments, and of course, developers
exist in dev/stage where they don't in production. Mapsid works if the
same logins exist. I have resorted to manually reviewing users and
their permissions in the database before restore, and then putting
everything back afterwards.
ThxIf you have some additional users in your dev environments, then when you
restore your production db to dev, those users will not have access the
restored database.
The way I'd go about it, is to script all the users and their permissions,
save it a file and run it after the restore. Your file will typically
contain call to sp_grantdbaccess and GRANT, DENY and REVOKE commands.
Or simply have a script to create a database role, grant access to all
users, add the users to this group, and apply permissions on this group
Do it once, save the script, and reuse it when needed.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Shawn" <coloradocamper@.hotmail.com> wrote in message
news:9ff983ee.0408180619.26ec1ac8@.posting.google.com...
Does anyone have a solution to the restore problem from a production
environment to a dev/stage environment where different users exist?
For our enterprise ETL and reporting tools we use business process
(BP) IDs that differ between environments, and of course, developers
exist in dev/stage where they don't in production. Mapsid works if the
same logins exist. I have resorted to manually reviewing users and
their permissions in the database before restore, and then putting
everything back afterwards.
Thx
Restore prod to dev with different users
Does anyone have a solution to the restore problem from a production
environment to a dev/stage environment where different users exist?
For our enterprise ETL and reporting tools we use business process
(BP) IDs that differ between environments, and of course, developers
exist in dev/stage where they don't in production. Mapsid works if the
same logins exist. I have resorted to manually reviewing users and
their permissions in the database before restore, and then putting
everything back afterwards.
Thx
If you have some additional users in your dev environments, then when you
restore your production db to dev, those users will not have access the
restored database.
The way I'd go about it, is to script all the users and their permissions,
save it a file and run it after the restore. Your file will typically
contain call to sp_grantdbaccess and GRANT, DENY and REVOKE commands.
Or simply have a script to create a database role, grant access to all
users, add the users to this group, and apply permissions on this group
Do it once, save the script, and reuse it when needed.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Shawn" <coloradocamper@.hotmail.com> wrote in message
news:9ff983ee.0408180619.26ec1ac8@.posting.google.c om...
Does anyone have a solution to the restore problem from a production
environment to a dev/stage environment where different users exist?
For our enterprise ETL and reporting tools we use business process
(BP) IDs that differ between environments, and of course, developers
exist in dev/stage where they don't in production. Mapsid works if the
same logins exist. I have resorted to manually reviewing users and
their permissions in the database before restore, and then putting
everything back afterwards.
Thx
environment to a dev/stage environment where different users exist?
For our enterprise ETL and reporting tools we use business process
(BP) IDs that differ between environments, and of course, developers
exist in dev/stage where they don't in production. Mapsid works if the
same logins exist. I have resorted to manually reviewing users and
their permissions in the database before restore, and then putting
everything back afterwards.
Thx
If you have some additional users in your dev environments, then when you
restore your production db to dev, those users will not have access the
restored database.
The way I'd go about it, is to script all the users and their permissions,
save it a file and run it after the restore. Your file will typically
contain call to sp_grantdbaccess and GRANT, DENY and REVOKE commands.
Or simply have a script to create a database role, grant access to all
users, add the users to this group, and apply permissions on this group
Do it once, save the script, and reuse it when needed.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Shawn" <coloradocamper@.hotmail.com> wrote in message
news:9ff983ee.0408180619.26ec1ac8@.posting.google.c om...
Does anyone have a solution to the restore problem from a production
environment to a dev/stage environment where different users exist?
For our enterprise ETL and reporting tools we use business process
(BP) IDs that differ between environments, and of course, developers
exist in dev/stage where they don't in production. Mapsid works if the
same logins exist. I have resorted to manually reviewing users and
their permissions in the database before restore, and then putting
everything back afterwards.
Thx
Restore prod to dev with different users
Does anyone have a solution to the restore problem from a production
environment to a dev/stage environment where different users exist?
For our enterprise ETL and reporting tools we use business process
(BP) IDs that differ between environments, and of course, developers
exist in dev/stage where they don't in production. Mapsid works if the
same logins exist. I have resorted to manually reviewing users and
their permissions in the database before restore, and then putting
everything back afterwards.
ThxIf you have some additional users in your dev environments, then when you
restore your production db to dev, those users will not have access the
restored database.
The way I'd go about it, is to script all the users and their permissions,
save it a file and run it after the restore. Your file will typically
contain call to sp_grantdbaccess and GRANT, DENY and REVOKE commands.
Or simply have a script to create a database role, grant access to all
users, add the users to this group, and apply permissions on this group
Do it once, save the script, and reuse it when needed.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Shawn" <coloradocamper@.hotmail.com> wrote in message
news:9ff983ee.0408180619.26ec1ac8@.posting.google.com...
Does anyone have a solution to the restore problem from a production
environment to a dev/stage environment where different users exist?
For our enterprise ETL and reporting tools we use business process
(BP) IDs that differ between environments, and of course, developers
exist in dev/stage where they don't in production. Mapsid works if the
same logins exist. I have resorted to manually reviewing users and
their permissions in the database before restore, and then putting
everything back afterwards.
Thx
environment to a dev/stage environment where different users exist?
For our enterprise ETL and reporting tools we use business process
(BP) IDs that differ between environments, and of course, developers
exist in dev/stage where they don't in production. Mapsid works if the
same logins exist. I have resorted to manually reviewing users and
their permissions in the database before restore, and then putting
everything back afterwards.
ThxIf you have some additional users in your dev environments, then when you
restore your production db to dev, those users will not have access the
restored database.
The way I'd go about it, is to script all the users and their permissions,
save it a file and run it after the restore. Your file will typically
contain call to sp_grantdbaccess and GRANT, DENY and REVOKE commands.
Or simply have a script to create a database role, grant access to all
users, add the users to this group, and apply permissions on this group
Do it once, save the script, and reuse it when needed.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Shawn" <coloradocamper@.hotmail.com> wrote in message
news:9ff983ee.0408180619.26ec1ac8@.posting.google.com...
Does anyone have a solution to the restore problem from a production
environment to a dev/stage environment where different users exist?
For our enterprise ETL and reporting tools we use business process
(BP) IDs that differ between environments, and of course, developers
exist in dev/stage where they don't in production. Mapsid works if the
same logins exist. I have resorted to manually reviewing users and
their permissions in the database before restore, and then putting
everything back afterwards.
Thx
Monday, February 20, 2012
Restore master database from 64bit sql server to 32 bit server
Hi,
We are planning to create a prod environment on our Dev server, by
creating a new named instance on DEV. Our prod server is running on
sql enterprise edition 64bit and dev on SQl enterprise 32bit.
Could we copy all the databases from prod server including
Master,msdb,model onto our dev server, and bring up all the databases?
Does Master store information about the version of SQl server?
We are planning to create a prod environment on our Dev server, by
creating a new named instance on DEV. Our prod server is running on
sql enterprise edition 64bit and dev on SQl enterprise 32bit.
Could we copy all the databases from prod server including
Master,msdb,model onto our dev server, and bring up all the databases?
Does Master store information about the version of SQl server?
Thanks for your help
GGIt would probably be best not to copy the system databases (except
perhaps model) - master and msdb have lots of information about the
instance, including name, backup history etc which could create
problems if you restore onto another instance. Typically, those
databases are only restored if they become corrupted, or as part of a
complete server recovery.
Simon
Subscribe to:
Posts (Atom)