Windows Forum

Ask something
Discussion Locked

SQL Server restore

0 helpful
6 replies

by dean

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I have a SQL 7 server at my office and 1 far far away. I want to take a database from my office and restore it to the server far away. The far away server has the same database, but the structure is not the same. We have added tables, etc. I have done this many times before, but not gracefully. I did it by backing up 1 database and then restoring it onto the 2nd server. The problem is users and permissions. I end up creating all new users, and changing our asp include statements. Help Me, Help Me.

6 replies

by jmiller1

Before running the restore on your 2nd server, run this command:

select 'sp_adduser '+name+', '+name+', convert(char,0xa),'go'
from sysusers

This will create a script for readding the users to the database after the restore. As for the asp include statements, I don't understand what the problem is...
HTH
jm

by zlatin zlatev

If you can establish a dial-up (or some other kind of remote) connection between the two servers, why not create a Replication database.
Another way is to create a script from the server "far far away" that recreates the data in the database. So with proper settings (not to recreate tables, just to insert/update data) you'll probably get the desired result.

Hope this helps,
Zlatin Zlatev,
Software Engineer & Mentor Programmer,
JEI-Bulgaria

by zlatin zlatev

btw. the suggestion above (1st answer) will not work, because after the restore you'll loose again your user accounts.

To do the things so, you must first create a temporary table/cursor with your user data.
And after the restore make the dinamic-SQL execution that readds your user accounts.

If you do not understand something of that I'm writing now, maybe you need to read SQL Server Books-Online and some articles from the MSDN library.

This discussion is closed to new replies.