database problem: copy

Showing posts with label copy. Show all posts

Wednesday, March 28, 2012

Restoring to get a single table

A user has managed to ruin a table on our production system, and I'd like to
offer them an older copy online so they can compare them. I think the best
way would be to restore the last backup (only a day old, phew) as a new
database, and then copy the data over.
Can anyone guide me on how to do the restore to a NEW database, leaving the
original intact and online?
MaurySee the Restore Database command, and simply use MyDatabase_New instead of
MyDatabase. Same thing in Enterprise Manager if you prefer to do it
there...just give it a new name
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:27178864-F2FE-44B9-8D10-7B3CB35B1303@.microsoft.com...
>A user has managed to ruin a table on our production system, and I'd like
>to
> offer them an older copy online so they can compare them. I think the best
> way would be to restore the last backup (only a day old, phew) as a new
> database, and then copy the data over.
> Can anyone guide me on how to do the restore to a NEW database, leaving
> the
> original intact and online?
> Maury|||"Kevin3NF" wrote:
> See the Restore Database command, and simply use MyDatabase_New instead of
> MyDatabase. Same thing in Enterprise Manager if you prefer to do it
> there...just give it a new name
Ok, thanks. Here goes nothing...
Maury|||www.red-gate.com makes a data Compare utility that may be useful in helping
you identify the ruined data, as well as writing scripts to resolve it.
"Maury Markowitz" wrote:
> "Kevin3NF" wrote:
> > See the Restore Database command, and simply use MyDatabase_New instead of
> > MyDatabase. Same thing in Enterprise Manager if you prefer to do it
> > there...just give it a new name
> Ok, thanks. Here goes nothing...
> Maurysql

Restoring to get a single table

A user has managed to ruin a table on our production system, and I'd like to
offer them an older copy online so they can compare them. I think the best
way would be to restore the last backup (only a day old, phew) as a new
database, and then copy the data over.
Can anyone guide me on how to do the restore to a NEW database, leaving the
original intact and online?
Maury
See the Restore Database command, and simply use MyDatabase_New instead of
MyDatabase. Same thing in Enterprise Manager if you prefer to do it
there...just give it a new name
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:27178864-F2FE-44B9-8D10-7B3CB35B1303@.microsoft.com...
>A user has managed to ruin a table on our production system, and I'd like
>to
> offer them an older copy online so they can compare them. I think the best
> way would be to restore the last backup (only a day old, phew) as a new
> database, and then copy the data over.
> Can anyone guide me on how to do the restore to a NEW database, leaving
> the
> original intact and online?
> Maury
|||"Kevin3NF" wrote:

> See the Restore Database command, and simply use MyDatabase_New instead of
> MyDatabase. Same thing in Enterprise Manager if you prefer to do it
> there...just give it a new name
Ok, thanks. Here goes nothing...
Maury
|||www.red-gate.com makes a data Compare utility that may be useful in helping
you identify the ruined data, as well as writing scripts to resolve it.
"Maury Markowitz" wrote:

> "Kevin3NF" wrote:
>
> Ok, thanks. Here goes nothing...
> Maury

Restoring to get a single table

A user has managed to ruin a table on our production system, and I'd like to
offer them an older copy online so they can compare them. I think the best
way would be to restore the last backup (only a day old, phew) as a new
database, and then copy the data over.
Can anyone guide me on how to do the restore to a NEW database, leaving the
original intact and online?
MaurySee the Restore Database command, and simply use MyDatabase_New instead of
MyDatabase. Same thing in Enterprise Manager if you prefer to do it
there...just give it a new name
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:27178864-F2FE-44B9-8D10-7B3CB35B1303@.microsoft.com...
>A user has managed to ruin a table on our production system, and I'd like
>to
> offer them an older copy online so they can compare them. I think the best
> way would be to restore the last backup (only a day old, phew) as a new
> database, and then copy the data over.
> Can anyone guide me on how to do the restore to a NEW database, leaving
> the
> original intact and online?
> Maury|||"Kevin3NF" wrote:

> See the Restore Database command, and simply use MyDatabase_New instead of
> MyDatabase. Same thing in Enterprise Manager if you prefer to do it
> there...just give it a new name
Ok, thanks. Here goes nothing...
Maury|||www.red-gate.com makes a data Compare utility that may be useful in helping
you identify the ruined data, as well as writing scripts to resolve it.
"Maury Markowitz" wrote:

> "Kevin3NF" wrote:
>
> Ok, thanks. Here goes nothing...
> Maury

Monday, March 26, 2012

Restoring stored procedure HELP!

Someone accidently deleted some of a stored procedure. Is it possible to
restore just that one whole procedure, or do we have to restore DB and copy
that one store procedure?You can't selectively restore objects. Restore the database elsewhere and
then copy the proc.
It's definitely good practice to keep a separate copy of your SQL source
code, just as you would for any other code in C# or VB. Use a source control
system such as Microsoft SourceSafe.
--
David Portas
SQL Server MVP
--|||Hi
Restore the DB as a different DB on the server and copy it over.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:4A8E8BD3-461B-4175-93B2-37F22B9F6B12@.microsoft.com...
> Someone accidently deleted some of a stored procedure. Is it possible to
> restore just that one whole procedure, or do we have to restore DB and
> copy
> that one store procedure?|||restore primary filegroup and copy sp
--
Aleksandar Grbic
MCDBA, Senior Database Administrator
"Mike" wrote:
> Someone accidently deleted some of a stored procedure. Is it possible to
> restore just that one whole procedure, or do we have to restore DB and copy
> that one store procedure?

Friday, March 23, 2012

Restoring SQL Server 2000 backup to Express

When I try to restore my SQL Server 2000 backup file to a newly installed copy of SQL Server 2005 Express using Studio Express, I get the following error:

The backup set holds a backup of a database other than the existing 'UpperBridge' database. (Microsoft.sqlservfer.Express.smo)

The backup is made from an SQL Server 2000 database called 'UpperBridge'

I am trying to restore to a database called 'UpperBridge' which I created under 'New databases' in Studio Express.

Any help very much appreciated.

hi,

withers wrote:

When I try to restore my SQL Server 2000 backup file to a newly installed copy of SQL Server 2005 Express using Studio Express, I get the following error:
The backup set holds a backup of a database other than the existing 'UpperBridge' database. (Microsoft.sqlservfer.Express.smo)
The backup is made from an SQL Server 2000 database called 'UpperBridge'
I am trying to restore to a database called 'UpperBridge' which I created under 'New databases' in Studio Express.
Any help very much appreciated.

try forcing the "overwrite" of the database already present in the SQLExpress instance,
RESTORE DATABASE ....
....
WITH REPLACE;

or just drop the database before restoring (obviously take a full local backup of it before dropping it, if required )..

regards

Monday, March 12, 2012

Restoring Master db after Reinstall

After reinstall of sql server and applying SP3a I can't restore a saved
copy of Master. Receiving massage " the backup of the system database
on device '*:\***\...bak' cannot be restored because it was created by a
different version of the server (134218646) than this server(134218488).
Restore database is terminating abnormally."
Which SP do I need to get to SoftwareVersionBuild = 818Hi Jim,
I believe the recipe for Build 8.00.818 is:
SP3 + Q821277/Q821337/Q818388/Q826161/Q821280
Steven Hutchinson,
IT Support Engineer,
Tayside Fire Brigade.
"Jim C." <Jim C.@.discussions.microsoft.com> wrote in message
news:32E29BC8-A586-496C-A191-D2CE5CC7E3F9@.microsoft.com...
> After reinstall of sql server and applying SP3a I can't restore a saved
> copy of Master. Receiving massage " the backup of the system database
> on device '*:\***\...bak' cannot be restored because it was created by a
> different version of the server (134218646) than this server(134218488).
> Restore database is terminating abnormally."
> Which SP do I need to get to SoftwareVersionBuild = 818|||Thanks Steve. I tried SP3A and that didn't do it.
"Steven Hutchinson" wrote:
> Hi Jim,
> I believe the recipe for Build 8.00.818 is:
> SP3 + Q821277/Q821337/Q818388/Q826161/Q821280
>
> --
> Steven Hutchinson,
> IT Support Engineer,
> Tayside Fire Brigade.
>
> "Jim C." <Jim C.@.discussions.microsoft.com> wrote in message
> news:32E29BC8-A586-496C-A191-D2CE5CC7E3F9@.microsoft.com...
> > After reinstall of sql server and applying SP3a I can't restore a saved
> > copy of Master. Receiving massage " the backup of the system database
> > on device '*:\***\...bak' cannot be restored because it was created by a
> > different version of the server (134218646) than this server(134218488).
> >
> > Restore database is terminating abnormally."
> >
> > Which SP do I need to get to SoftwareVersionBuild = 818
>
>

Wednesday, March 7, 2012

Restoring from an copy of the offline database?

I have a question considering restores. If I take my database offline
the backup system will backup the .mdf and .ldf files making up the
database. Can these files be used to restore a SQL 2000 database.
The reason I ask is that we don't have enough space to create a full
back up and we cannot write directly to a tape device. I have no
problem detaching the database, letting that backup, then reattaching
it. However, if I am not here I'd rather leave instructions for my
boss to just take the database offline when he leaves for the weekend
and bring it back online Monday morning.This may work, but it is not guaranteed.
I would backup to a USB drive or a UNC share across the network. If you
don't have the space to handle a backup copy and a restore copy of the
database, you really shouldn't be running SQL Server.
HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?id=555128
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Randy K" <wawork@.hotmail.com> wrote in message
news:44491b1e.70492156@.msnews.microsoft.com...
>I have a question considering restores. If I take my database offline
> the backup system will backup the .mdf and .ldf files making up the
> database. Can these files be used to restore a SQL 2000 database.
> The reason I ask is that we don't have enough space to create a full
> back up and we cannot write directly to a tape device. I have no
> problem detaching the database, letting that backup, then reattaching
> it. However, if I am not here I'd rather leave instructions for my
> boss to just take the database offline when he leaves for the weekend
> and bring it back online Monday morning.
>|||"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:eHCiDCXZGHA.4248@.TK2MSFTNGP05.phx.gbl...
> This may work, but it is not guaranteed.
> I would backup to a USB drive or a UNC share across the network. If you
> don't have the space to handle a backup copy and a restore copy of the
> database, you really shouldn't be running SQL Server.
We use the UNC share method all the time and in fact just upgraded to a Snap
4200 server.
Works very nicely for us. Not the fastest solution out there, but almost 3
times faster than our Snap Server 4100 was, so I'm happy.
(100+ gig in about 3 hours).
> HowTo: Backup to UNC name using Database Maintenance Wizard
> http://support.microsoft.com/?id=555128
>
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Randy K" <wawork@.hotmail.com> wrote in message
> news:44491b1e.70492156@.msnews.microsoft.com...
> >I have a question considering restores. If I take my database offline
> > the backup system will backup the .mdf and .ldf files making up the
> > database. Can these files be used to restore a SQL 2000 database.
> >
> > The reason I ask is that we don't have enough space to create a full
> > back up and we cannot write directly to a tape device. I have no
> > problem detaching the database, letting that backup, then reattaching
> > it. However, if I am not here I'd rather leave instructions for my
> > boss to just take the database offline when he leaves for the weekend
> > and bring it back online Monday morning.
> >
> >
>|||Personally, I use the UNC share method for t-log backups and recent full
backups. I use tape drives for longer term backups and archiving. My NT
admins like me because I use the tape drives during daylight hours and the
network at night, both of which are low activity times for the respective
resources.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:elfo4aaZGHA.3652@.TK2MSFTNGP03.phx.gbl...
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> news:eHCiDCXZGHA.4248@.TK2MSFTNGP05.phx.gbl...
>> This may work, but it is not guaranteed.
>> I would backup to a USB drive or a UNC share across the network. If you
>> don't have the space to handle a backup copy and a restore copy of the
>> database, you really shouldn't be running SQL Server.
> We use the UNC share method all the time and in fact just upgraded to a
> Snap
> 4200 server.
> Works very nicely for us. Not the fastest solution out there, but almost
> 3
> times faster than our Snap Server 4100 was, so I'm happy.
> (100+ gig in about 3 hours).
>
>> HowTo: Backup to UNC name using Database Maintenance Wizard
>> http://support.microsoft.com/?id=555128
>>
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>>
>> "Randy K" <wawork@.hotmail.com> wrote in message
>> news:44491b1e.70492156@.msnews.microsoft.com...
>> >I have a question considering restores. If I take my database offline
>> > the backup system will backup the .mdf and .ldf files making up the
>> > database. Can these files be used to restore a SQL 2000 database.
>> >
>> > The reason I ask is that we don't have enough space to create a full
>> > back up and we cannot write directly to a tape device. I have no
>> > problem detaching the database, letting that backup, then reattaching
>> > it. However, if I am not here I'd rather leave instructions for my
>> > boss to just take the database offline when he leaves for the weekend
>> > and bring it back online Monday morning.
>> >
>> >
>>
>|||Thank you for your suggestions!
Another question we have concerns the maximum size for the physical
database files when building VLDBs. This database will start out
about 2TB growing to 4 over the next year and a half. I haven't
been able to find anything on this subject. With two 1TB LUNs is
250GB too large for individual database files?
On Sun, 23 Apr 2006 23:41:08 -0400, "Geoff N. Hiten"
<SQLCraftsman@.gmail.com> wrote:
>Personally, I use the UNC share method for t-log backups and recent full
>backups. I use tape drives for longer term backups and archiving. My NT
>admins like me because I use the tape drives during daylight hours and the
>network at night, both of which are low activity times for the respective
>resources.
>--
>Geoff N. Hiten
>Senior Database Administrator
>Microsoft SQL Server MVP
>
>"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
>news:elfo4aaZGHA.3652@.TK2MSFTNGP03.phx.gbl...
>> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
>> news:eHCiDCXZGHA.4248@.TK2MSFTNGP05.phx.gbl...
>> This may work, but it is not guaranteed.
>> I would backup to a USB drive or a UNC share across the network. If you
>> don't have the space to handle a backup copy and a restore copy of the
>> database, you really shouldn't be running SQL Server.
>> We use the UNC share method all the time and in fact just upgraded to a
>> Snap
>> 4200 server.
>> Works very nicely for us. Not the fastest solution out there, but almost
>> 3
>> times faster than our Snap Server 4100 was, so I'm happy.
>> (100+ gig in about 3 hours).
>>
>> HowTo: Backup to UNC name using Database Maintenance Wizard
>> http://support.microsoft.com/?id=555128
>>
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>>
>> "Randy K" <wawork@.hotmail.com> wrote in message
>> news:44491b1e.70492156@.msnews.microsoft.com...
>> >I have a question considering restores. If I take my database offline
>> > the backup system will backup the .mdf and .ldf files making up the
>> > database. Can these files be used to restore a SQL 2000 database.
>> >
>> > The reason I ask is that we don't have enough space to create a full
>> > back up and we cannot write directly to a tape device. I have no
>> > problem detaching the database, letting that backup, then reattaching
>> > it. However, if I am not here I'd rather leave instructions for my
>> > boss to just take the database offline when he leaves for the weekend
>> > and bring it back online Monday morning.
>> >
>> >
>>
>>
>

Saturday, February 25, 2012

Restoring DB for files...

Hi
In order to copy a database from a server to a server, I cant detach the database as there are many users connected to it. The transaction log is large file while the data is about 46 MB, I made a backup for the data file only, zipped it and copied it to the target server.
Ex: the name of the source DB on the source server is x, the file I took as a backup for is x-data
From the Enterprise Manager, right-click the x DB, All tasks, Backup database, in the backup option click the File and Filegroup option and chose the primary file group with x_data file.
Then, I created a new database name it y with y_data , y_log files on primary filegroup. I want to restore my backup to have the DB x on my target server,
I tried:
RESTORE DATABASE y
FILE = 'y_data',
FILEGROUP = 'primary'
FROM disk= 'E:\VMS\DBFiles\test'
GO

But it did not work. Any help?

Thanks,what is the error you are getting ?

Restoring DB

Hi,

I have created a DB on my local machine.
Now I would like to copy this on to another machine.
I stopped the service and then copied both the MDF file and the
log files into the same folder as in Data folder of the second machine.

When I run theStudio express I cannot find the MDF file.It does not show up in the
the DB folder or window.
When I try to create one it says that the DB exists.

What am I missing?

I tried doing a backup and restore but then the DB is not created on the second machine and
hence wont work.

Tnx

How are you looking for the file in Management Studio? Are you using the Attach functionality? If there is more than one SQL Server running on the computer, there will be more than one Data directory, so it's possible that you've put the files in the wrong directory.

Let us know the exact steps you're using to attach the database on the new machine.

Regards,

Mike Wachal
SQL Express team

-
Mark the best posts as Answers!

|||Hi Mike,

>>How are you looking for the file in Management Studio? Are you using the Attach >>functionality?

I stoped the service on the first machine and then went into the folder:
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\
and copied both the DB and the Log files on to my CD.

I stoped the service on the second machine and then went into the folder.
copied them both into the second machine's :
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

There is only one SQL server installed on both the machines'.

Tnx

|||

Hi,

great you found the button for making fonts real big ! :-)

As Mike said you have to attach the database for before you can see it, use the Sp_attach procedure for this or use the graphical UI (SQL Server Management Studio Express) for that, I don′t know if there is a possibility to use this function for attaching otherwise sp_attachdb is quite easy to code in TSQL, there are samples on the BOL for that.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Tuesday, February 21, 2012

Restoring database problem in SQL Server 7

I have an updated version of a database which was not created by a
backup on my server but on another server. I put the updated copy in a
directory, and attempt to restore it to a database on my system. No
matter what I do, even though I am pointing directly to the backup, SQL
Server is showing me a backup with the size and date/time of my last
backup of that same database and not the updated copy. The amazing thing
is that the backup file which I am trying to use to restore is pointed
to directly, while no previous backup which I have done is anywhere in
that same directory, and yet SQL Server is somehow restoring that
previous backup. Does anybody know what is going on here ? It just can
not be that hard to use another backup from the database on another
server to restore to my server.
Have you tried:
restore database MyDB
from disk = 'C:\MyDB.bak'
with replace
, move 'MyDataFile' to 'C:\MyDB.mdf'
, move 'MyLogFile' to 'C:\MyDB.ldf'
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Edward Diener" <eddielee_no_spam_here@.tropicsoft.com> wrote in message
news:u%231yYZNXFHA.2468@.TK2MSFTNGP10.phx.gbl...
I have an updated version of a database which was not created by a
backup on my server but on another server. I put the updated copy in a
directory, and attempt to restore it to a database on my system. No
matter what I do, even though I am pointing directly to the backup, SQL
Server is showing me a backup with the size and date/time of my last
backup of that same database and not the updated copy. The amazing thing
is that the backup file which I am trying to use to restore is pointed
to directly, while no previous backup which I have done is anywhere in
that same directory, and yet SQL Server is somehow restoring that
previous backup. Does anybody know what is going on here ? It just can
not be that hard to use another backup from the database on another
server to restore to my server.

restoring database onto a different machine

Hi,
Every time I have to copy a database from one machine to another I get stuck
in a problem with login IDs. Here's what happens:
On machine A I have a database called D. there is a user called U that maps
to a login called U (no NTLM). U owns all the tables in database D, and D is
the default database for U. On machine B there is also a login called U.
The machines are not on the same network so I backup the database to a flat
file and restore it onto machine B
Now if I log onto machine B as user U, I do not have permission to change
into the newly restored database D. I presume this is because user U on
machine B is different to user U on machine A, and the database is owned by
user U on machine A.
So I log into machine B as 'sa' and I try to add user U to the database D.
This fails because there is already a user called U on the database. I
cannot remove the existing user called U because it owns all the tables. So
it seems I am in a catch 22 situation.
I have tried sp_fix_users_login which looks like it should do the right
thing but to no avail.
The only way I can get working is create a new user called something other
than U, add this to the database, and then use sp_changeobjectowner to
change ownership of all the tables to the new user, which is a right PITA.
I have tried to keep it as simple as possible (only 1 user per database,
only 1 database per user, login name same as user name, etc etc) but I am
still stuck. Any ideas
TIA
AndyI suspect that you mean sp_change_users_login not sp_fix_users_login. I
recommend that you have dbo own the database objects. You can grant the
appropriate rights on the tables (or stored procedures) to the other
database users.
--
Keith
"Andy Fish" <ajfish@.blueyonder.co.uk> wrote in message
news:uZx%237PJKFHA.3788@.tk2msftngp13.phx.gbl...
> Hi,
> Every time I have to copy a database from one machine to another I get
stuck
> in a problem with login IDs. Here's what happens:
> On machine A I have a database called D. there is a user called U that
maps
> to a login called U (no NTLM). U owns all the tables in database D, and D
is
> the default database for U. On machine B there is also a login called U.
> The machines are not on the same network so I backup the database to a
flat
> file and restore it onto machine B
> Now if I log onto machine B as user U, I do not have permission to change
> into the newly restored database D. I presume this is because user U on
> machine B is different to user U on machine A, and the database is owned
by
> user U on machine A.
> So I log into machine B as 'sa' and I try to add user U to the database D.
> This fails because there is already a user called U on the database. I
> cannot remove the existing user called U because it owns all the tables.
So
> it seems I am in a catch 22 situation.
> I have tried sp_fix_users_login which looks like it should do the right
> thing but to no avail.
> The only way I can get working is create a new user called something other
> than U, add this to the database, and then use sp_changeobjectowner to
> change ownership of all the tables to the new user, which is a right PITA.
> I have tried to keep it as simple as possible (only 1 user per database,
> only 1 database per user, login name same as user name, etc etc) but I am
> still stuck. Any ideas
> TIA
> Andy
>|||There is a way to create a Login on one server with the GUID of another
login on another server.
This should get rid of the need to recreate the login everytime a restore is
done.
I cannot recollect how I used to do it as it is a year since I last worked
on DB administration.
You might want to check with BOL.
Gopi
"Andy Fish" <ajfish@.blueyonder.co.uk> wrote in message
news:uZx%237PJKFHA.3788@.tk2msftngp13.phx.gbl...
> Hi,
> Every time I have to copy a database from one machine to another I get
> stuck in a problem with login IDs. Here's what happens:
> On machine A I have a database called D. there is a user called U that
> maps to a login called U (no NTLM). U owns all the tables in database D,
> and D is the default database for U. On machine B there is also a login
> called U.
> The machines are not on the same network so I backup the database to a
> flat file and restore it onto machine B
> Now if I log onto machine B as user U, I do not have permission to change
> into the newly restored database D. I presume this is because user U on
> machine B is different to user U on machine A, and the database is owned
> by user U on machine A.
> So I log into machine B as 'sa' and I try to add user U to the database D.
> This fails because there is already a user called U on the database. I
> cannot remove the existing user called U because it owns all the tables.
> So it seems I am in a catch 22 situation.
> I have tried sp_fix_users_login which looks like it should do the right
> thing but to no avail.
> The only way I can get working is create a new user called something other
> than U, add this to the database, and then use sp_changeobjectowner to
> change ownership of all the tables to the new user, which is a right PITA.
> I have tried to keep it as simple as possible (only 1 user per database,
> only 1 database per user, login name same as user name, etc etc) but I am
> still stuck. Any ideas
> TIA
> Andy
>|||HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://www.support.microsoft.com/?id=246133
--
Keith
"rgn" <gopinathr@.healthasyst.com> wrote in message
news:O5emurJKFHA.1096@.tk2msftngp13.phx.gbl...
> There is a way to create a Login on one server with the GUID of another
> login on another server.
> This should get rid of the need to recreate the login everytime a restore
is
> done.
> I cannot recollect how I used to do it as it is a year since I last worked
> on DB administration.
> You might want to check with BOL.
> Gopi
>
> "Andy Fish" <ajfish@.blueyonder.co.uk> wrote in message
> news:uZx%237PJKFHA.3788@.tk2msftngp13.phx.gbl...
> > Hi,
> >
> > Every time I have to copy a database from one machine to another I get
> > stuck in a problem with login IDs. Here's what happens:
> >
> > On machine A I have a database called D. there is a user called U that
> > maps to a login called U (no NTLM). U owns all the tables in database D,
> > and D is the default database for U. On machine B there is also a login
> > called U.
> >
> > The machines are not on the same network so I backup the database to a
> > flat file and restore it onto machine B
> >
> > Now if I log onto machine B as user U, I do not have permission to
change
> > into the newly restored database D. I presume this is because user U on
> > machine B is different to user U on machine A, and the database is owned
> > by user U on machine A.
> >
> > So I log into machine B as 'sa' and I try to add user U to the database
D.
> > This fails because there is already a user called U on the database. I
> > cannot remove the existing user called U because it owns all the tables.
> > So it seems I am in a catch 22 situation.
> >
> > I have tried sp_fix_users_login which looks like it should do the right
> > thing but to no avail.
> >
> > The only way I can get working is create a new user called something
other
> > than U, add this to the database, and then use sp_changeobjectowner to
> > change ownership of all the tables to the new user, which is a right
PITA.
> >
> > I have tried to keep it as simple as possible (only 1 user per database,
> > only 1 database per user, login name same as user name, etc etc) but I
am
> > still stuck. Any ideas
> >
> > TIA
> >
> > Andy
> >
> >
>|||Thanks for that link Keith, it had links to some other very useful articles.
It turned out to be fairly simple. When I was running sp_change_users_login
as sa, I was still in the master database. Changing to the new database and
then running:
exec sp_change_users_login 'Auto_Fix', 'U'
worked a treat !!
Andy
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:eLHBNzJKFHA.484@.TK2MSFTNGP15.phx.gbl...
> HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
> http://www.support.microsoft.com/?id=246133
> --
> Keith
>
> "rgn" <gopinathr@.healthasyst.com> wrote in message
> news:O5emurJKFHA.1096@.tk2msftngp13.phx.gbl...
>> There is a way to create a Login on one server with the GUID of another
>> login on another server.
>> This should get rid of the need to recreate the login everytime a restore
> is
>> done.
>> I cannot recollect how I used to do it as it is a year since I last
>> worked
>> on DB administration.
>> You might want to check with BOL.
>> Gopi
>>
>> "Andy Fish" <ajfish@.blueyonder.co.uk> wrote in message
>> news:uZx%237PJKFHA.3788@.tk2msftngp13.phx.gbl...
>> > Hi,
>> >
>> > Every time I have to copy a database from one machine to another I get
>> > stuck in a problem with login IDs. Here's what happens:
>> >
>> > On machine A I have a database called D. there is a user called U that
>> > maps to a login called U (no NTLM). U owns all the tables in database
>> > D,
>> > and D is the default database for U. On machine B there is also a login
>> > called U.
>> >
>> > The machines are not on the same network so I backup the database to a
>> > flat file and restore it onto machine B
>> >
>> > Now if I log onto machine B as user U, I do not have permission to
> change
>> > into the newly restored database D. I presume this is because user U on
>> > machine B is different to user U on machine A, and the database is
>> > owned
>> > by user U on machine A.
>> >
>> > So I log into machine B as 'sa' and I try to add user U to the database
> D.
>> > This fails because there is already a user called U on the database. I
>> > cannot remove the existing user called U because it owns all the
>> > tables.
>> > So it seems I am in a catch 22 situation.
>> >
>> > I have tried sp_fix_users_login which looks like it should do the right
>> > thing but to no avail.
>> >
>> > The only way I can get working is create a new user called something
> other
>> > than U, add this to the database, and then use sp_changeobjectowner to
>> > change ownership of all the tables to the new user, which is a right
> PITA.
>> >
>> > I have tried to keep it as simple as possible (only 1 user per
>> > database,
>> > only 1 database per user, login name same as user name, etc etc) but I
> am
>> > still stuck. Any ideas
>> >
>> > TIA
>> >
>> > Andy
>> >
>> >
>>
>|||Excellent. I am glad that you figured out how to fix the issue. Going
forward if you create your logins based on the script of one server the SID
will match across all of your servers and that should allow you to restore
across servers and not have to execute sp_change_users_login at all.
--
Keith
"Andy Fish" <ajfish@.blueyonder.co.uk> wrote in message
news:O%23K2jgKKFHA.2920@.TK2MSFTNGP10.phx.gbl...
> Thanks for that link Keith, it had links to some other very useful
articles.
> It turned out to be fairly simple. When I was running
sp_change_users_login
> as sa, I was still in the master database. Changing to the new database
and
> then running:
> exec sp_change_users_login 'Auto_Fix', 'U'
> worked a treat !!
> Andy
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:eLHBNzJKFHA.484@.TK2MSFTNGP15.phx.gbl...
> > HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
> > http://www.support.microsoft.com/?id=246133
> >
> > --
> > Keith
> >
> >
> > "rgn" <gopinathr@.healthasyst.com> wrote in message
> > news:O5emurJKFHA.1096@.tk2msftngp13.phx.gbl...
> >> There is a way to create a Login on one server with the GUID of another
> >> login on another server.
> >> This should get rid of the need to recreate the login everytime a
restore
> > is
> >> done.
> >>
> >> I cannot recollect how I used to do it as it is a year since I last
> >> worked
> >> on DB administration.
> >>
> >> You might want to check with BOL.
> >>
> >> Gopi
> >>
> >>
> >> "Andy Fish" <ajfish@.blueyonder.co.uk> wrote in message
> >> news:uZx%237PJKFHA.3788@.tk2msftngp13.phx.gbl...
> >> > Hi,
> >> >
> >> > Every time I have to copy a database from one machine to another I
get
> >> > stuck in a problem with login IDs. Here's what happens:
> >> >
> >> > On machine A I have a database called D. there is a user called U
that
> >> > maps to a login called U (no NTLM). U owns all the tables in database
> >> > D,
> >> > and D is the default database for U. On machine B there is also a
login
> >> > called U.
> >> >
> >> > The machines are not on the same network so I backup the database to
a
> >> > flat file and restore it onto machine B
> >> >
> >> > Now if I log onto machine B as user U, I do not have permission to
> > change
> >> > into the newly restored database D. I presume this is because user U
on
> >> > machine B is different to user U on machine A, and the database is
> >> > owned
> >> > by user U on machine A.
> >> >
> >> > So I log into machine B as 'sa' and I try to add user U to the
database
> > D.
> >> > This fails because there is already a user called U on the database.
I
> >> > cannot remove the existing user called U because it owns all the
> >> > tables.
> >> > So it seems I am in a catch 22 situation.
> >> >
> >> > I have tried sp_fix_users_login which looks like it should do the
right
> >> > thing but to no avail.
> >> >
> >> > The only way I can get working is create a new user called something
> > other
> >> > than U, add this to the database, and then use sp_changeobjectowner
to
> >> > change ownership of all the tables to the new user, which is a right
> > PITA.
> >> >
> >> > I have tried to keep it as simple as possible (only 1 user per
> >> > database,
> >> > only 1 database per user, login name same as user name, etc etc) but
I
> > am
> >> > still stuck. Any ideas
> >> >
> >> > TIA
> >> >
> >> > Andy
> >> >
> >> >
> >>
> >>
> >
>

Wednesday, March 28, 2012

Restoring to get a single table

Restoring to get a single table

Restoring to get a single table

Monday, March 26, 2012

Restoring stored procedure HELP!

Friday, March 23, 2012

Restoring SQL Server 2000 backup to Express

Monday, March 12, 2012

Restoring Master db after Reinstall

Wednesday, March 7, 2012

Restoring from an copy of the offline database?

Restoring from an copy of the offline database?

Saturday, February 25, 2012

Restoring DB for files...

Restoring DB

Tuesday, February 21, 2012

Restoring database problem in SQL Server 7

restoring database onto a different machine

restoring database onto a different machine

restoring database onto a different machine

database problem

Blog Archive

About Me