backing up/restoring a db

Hello,
I have a set of scripts, which create a database Test1,
create accounts/roles/users, and grant permissions for
those users to access the newly created database.
When I ran the scripts for the 1st time, everything worked
just fine. Test1 db was created and I could successfully
connect to it from my client application, add/remove
records, etc.
Then, I needed to move my db to a different server, so I
decided to backup the database, and then to restore on the
new server.
Here is the backup script I ran:
USE master
EXEC sp_adddumpdevice 'disk', 'Test1DB', 'Test1db.dat'
BACKUP DATABASE Test1 TO Test1DB
The database backup is created properly (when I restored,
I saw all the original records).
The problem I am experiencing, though, is the messed up
accounts/logins, once the database is restored.
If I just run a restore script, my database is restored,
but unders 'security' for the SQL registration (in
Enterprise Manager) the login associated with my DB is
missing.
If I first run my scripts to create a brand new database,
and then restore the database from a backup, the logins
are created, but after the restore script is done, the
association with DB seems to be broken: if I double-click
on my login under 'security' and look at the list of dbs
in 'database access', my Test1 db doesn't have a check
mark next to it (even though it was there before I ran
restore script), if I try to set the check, I get an error
21002, 'User already 'so-and-so' already exist'.
What I am doing wrong? Is there any way to backup a db in
such way so that logins could be also restored? Or is
there a way to restore a db, so that existing
accounts/permissions would not be affected, but only the
data be merged in?
Thanks a lot for any help/suggestions.
Sincerely,
VRHi
backing or restoring a user database does not create
logins ,the logins hv to pre-exist or u hv to create
logins before creating/restoring the user db. bcos logins
exists inside master db and not user db
but when u exec scripts which include the logins surely
they get created.
thanks
rahul
>--Original Message--
>Hello,
>I have a set of scripts, which create a database Test1,
>create accounts/roles/users, and grant permissions for
>those users to access the newly created database.
>When I ran the scripts for the 1st time, everything
worked
>just fine. Test1 db was created and I could successfully
>connect to it from my client application, add/remove
>records, etc.
>Then, I needed to move my db to a different server, so I
>decided to backup the database, and then to restore on
the
>new server.
>Here is the backup script I ran:
>USE master
>EXEC sp_adddumpdevice 'disk', 'Test1DB', 'Test1db.dat'
>BACKUP DATABASE Test1 TO Test1DB
>The database backup is created properly (when I
restored,
>I saw all the original records).
>The problem I am experiencing, though, is the messed up
>accounts/logins, once the database is restored.
>If I just run a restore script, my database is restored,
>but unders 'security' for the SQL registration (in
>Enterprise Manager) the login associated with my DB is
>missing.
>If I first run my scripts to create a brand new
database,
>and then restore the database from a backup, the logins
>are created, but after the restore script is done, the
>association with DB seems to be broken: if I double-
click
>on my login under 'security' and look at the list of dbs
>in 'database access', my Test1 db doesn't have a check
>mark next to it (even though it was there before I ran
>restore script), if I try to set the check, I get an
error
>21002, 'User already 'so-and-so' already exist'.
>What I am doing wrong? Is there any way to backup a db
in
>such way so that logins could be also restored? Or is
>there a way to restore a db, so that existing
>accounts/permissions would not be affected, but only the
>data be merged in?
>Thanks a lot for any help/suggestions.
>Sincerely,
>VR
>
>.
>|||Check out sp_change_users_login in the Books Online
<tsqlref.chm::/ts_sp_ca-cz_8qzy.htm>. This will report and/or correct
mismatches between users and logins due to a restore/attach.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"VR" <anonymous@.discussions.microsoft.com> wrote in message
news:033601c3cb79$a2806270$a101280a@.phx.gbl...
> Hello,
> I have a set of scripts, which create a database Test1,
> create accounts/roles/users, and grant permissions for
> those users to access the newly created database.
> When I ran the scripts for the 1st time, everything worked
> just fine. Test1 db was created and I could successfully
> connect to it from my client application, add/remove
> records, etc.
> Then, I needed to move my db to a different server, so I
> decided to backup the database, and then to restore on the
> new server.
> Here is the backup script I ran:
> USE master
> EXEC sp_adddumpdevice 'disk', 'Test1DB', 'Test1db.dat'
> BACKUP DATABASE Test1 TO Test1DB
> The database backup is created properly (when I restored,
> I saw all the original records).
> The problem I am experiencing, though, is the messed up
> accounts/logins, once the database is restored.
> If I just run a restore script, my database is restored,
> but unders 'security' for the SQL registration (in
> Enterprise Manager) the login associated with my DB is
> missing.
> If I first run my scripts to create a brand new database,
> and then restore the database from a backup, the logins
> are created, but after the restore script is done, the
> association with DB seems to be broken: if I double-click
> on my login under 'security' and look at the list of dbs
> in 'database access', my Test1 db doesn't have a check
> mark next to it (even though it was there before I ran
> restore script), if I try to set the check, I get an error
> 21002, 'User already 'so-and-so' already exist'.
> What I am doing wrong? Is there any way to backup a db in
> such way so that logins could be also restored? Or is
> there a way to restore a db, so that existing
> accounts/permissions would not be affected, but only the
> data be merged in?
> Thanks a lot for any help/suggestions.
> Sincerely,
> VR
>
>