I have several databases in a loading state, I have tried using the detach
option (via EM) but I get the following error: Microsoft SQL-DMO (ODBC
SQLState: 42000)
Error 15010: The database 'name' does not exist. Use sp_helpdb to show
available databases.
When using the sp_helpdb command, of course it only shows the databases that
are not in the loading state.
I need to get the databases that in loading state, detached or removed
completely. My nightly backups are failing since they can not attach to the
databases (loading)
All assistance in this matter is greatly appreciated and thanks in advanceForgot to advise. Using SQL 2000 with SP 3 on Win2K server
"scuba79" wrote:
> I have several databases in a loading state, I have tried using the detach
> option (via EM) but I get the following error: Microsoft SQL-DMO (ODBC
> SQLState: 42000)
> Error 15010: The database 'name' does not exist. Use sp_helpdb to show
> available databases.
> When using the sp_helpdb command, of course it only shows the databases that
> are not in the loading state.
> I need to get the databases that in loading state, detached or removed
> completely. My nightly backups are failing since they can not attach to the
> databases (loading)
> All assistance in this matter is greatly appreciated and thanks in advance|||Never mind... I found the solution... Forgot that I needed to use the
RECOVERY DATABASE WITH RECOVERY command
"scuba79" wrote:
> I have several databases in a loading state, I have tried using the detach
> option (via EM) but I get the following error: Microsoft SQL-DMO (ODBC
> SQLState: 42000)
> Error 15010: The database 'name' does not exist. Use sp_helpdb to show
> available databases.
> When using the sp_helpdb command, of course it only shows the databases that
> are not in the loading state.
> I need to get the databases that in loading state, detached or removed
> completely. My nightly backups are failing since they can not attach to the
> databases (loading)
> All assistance in this matter is greatly appreciated and thanks in advance
Showing posts with label detach. Show all posts
Showing posts with label detach. Show all posts
Thursday, March 29, 2012
Wednesday, March 7, 2012
database detach/attach
An original database (eg A.mdf) is imported/exported to a different server as named B.mdf. The detach of A.mdf was unitentionally not done. It seems that both the database were updated.
A.mdf - 123000KB 25/12/2003
A_log.ldf - 14000KB 25/12/2003
B.mdf - 67000KB 25/12/2003
B_log.ldf - 1024KB 25/12/2003
In this case, which mdf & ldf should be the correct database?
If I I remove A.mdf database by "detach", will B.mdf database work?
SQL server 2000 and SP3 installed.
Assistance is appreciatedyou are exporting a database file and getting different sizes .. seems strange
sp_helpdb 'database_name'
on both servers
Copy and paste results over here
That might help|||The original database (eg A.mdf) is export/imported into a different server and the database is named as B.mdf. The 'detach' command from of the original database (A.mdf) was unintentionally
not done. As I have unintentionally did not do a 'detach' of the old database, both the database seems to be updated (as indicated from the date stamp) AFTER I use the application (VB6 business application) a day or two.
Once an import/export is done, is the detach of the old database compulsory?. In SQL Server Enterprise Manager->(Select say, A database)->All Task-> Detach database
A.mdf - 123,000KB, 2/1/2004 1142am
A_log.ldf - 10,000 KB, 2/1/2004 0133pm
B.mdf - 67,000KB, 2/1/2004 0902am
B_log.ldf - 1024KB, 2/1/2004 0902am
Exporting of the database is ok before the I started using the application.
How can I correct this? Advise is appreciated.
Regards,
Brian
Originally posted by Enigma
you are exporting a database file and getting different sizes .. seems strange
sp_helpdb 'database_name'
on both servers
Copy and paste results over here
That might help|||Are both databases supposed to be active - please describe more as to why you have 2 ? Where are you retrieving the database sizes ? Use sp_spaceused and post the results. Is the real question, what has changed between the 2 databases and how to find those records ?|||Originally posted by rnealejr
Are both databases supposed to be active - please describe more as to why you have 2 ? Where are you retrieving the database sizes ? Use sp_spaceused and post the results. Is the real question, what has changed between the 2 databases and how to find those records ?
the A.mdf and its log is the active database. 2 databases are active as 1 is live/production environment and the other is test/QA environment. the database sizes is seen from the window explorer.|||What needs to be corrected ? You keep mentioning detaching the A database - but why ? Is A production or test ? You need to run the stored procedure sp_spaceused to determine actual space used. Are you concerned that there might be activity on both databases - and you are not sure why ? Are you exporting the data from the production to the test periodically ? Since your log file for B does not appear to have grown, it appears that either minimal or no user activity is occuring on the B database (unless you are backing up the database/transaction log for B).|||Originally posted by rnealejr
What needs to be corrected ? You keep mentioning detaching the A database - but why ? Is A production or test ? You need to run the stored procedure sp_spaceused to determine actual space used. Are you concerned that there might be activity on both databases - and you are not sure why ? Are you exporting the data from the production to the test periodically ? Since your log file for B does not appear to have grown, it appears that either minimal or no user activity is occuring on the B database (unless you are backing up the database/transaction log for B).
if I were to remove (detach) the test/QA data (denotes by B.mdf and its log), will this cause any problem with production (denotes by A.mdf and its log) database?. I noted that the date/time has been updated on the same day even though my ODBC (live application) uses production database which is A.mdf.
What happen if a detach is not done after import/export from production to test/QA environment?. Do the production database get updated (by right it should since ODBC points to production) along with the test database?. I am concern that activities may be updated in the test environment - yes, I am not sure and I would like some feedback.|||There should be no connection between your A database and your B database (unless you have triggers/replication between the 2) - other than the fact that you exported the data from A to B. What method did you use to export the data from A to B ? Several activities can cause the date/time to change on B, but since the log is only 1 meg (the minimum) I would suspect that nothing has really changed on B. But if you are really concerned - dump the test database and copy the database from prod back to test.|||Originally posted by rnealejr
There should be no connection between your A database and your B database (unless you have triggers/replication between the 2) - other than the fact that you exported the data from A to B. What method did you use to export the data from A to B ? Several activities can cause the date/time to change on B, but since the log is only 1 meg (the minimum) I would suspect that nothing has really changed on B. But if you are really concerned - dump the test database and copy the database from prod back to test.
I agree with you. All I did was to simply import/export. no DTS used.
The strange thing I noted was that after the import/export from production to test (and no "detach" was done on test database), I ran the application and did the update. I found was that the test database time/date was updated AND the data went into production and the mdf and log of production did not change (seen from explorer).
Thanks for your feedback.|||It was probably just coincidence ... or a poltergeist (oooohhhhhh - supposed to be a spooky sound)
A.mdf - 123000KB 25/12/2003
A_log.ldf - 14000KB 25/12/2003
B.mdf - 67000KB 25/12/2003
B_log.ldf - 1024KB 25/12/2003
In this case, which mdf & ldf should be the correct database?
If I I remove A.mdf database by "detach", will B.mdf database work?
SQL server 2000 and SP3 installed.
Assistance is appreciatedyou are exporting a database file and getting different sizes .. seems strange
sp_helpdb 'database_name'
on both servers
Copy and paste results over here
That might help|||The original database (eg A.mdf) is export/imported into a different server and the database is named as B.mdf. The 'detach' command from of the original database (A.mdf) was unintentionally
not done. As I have unintentionally did not do a 'detach' of the old database, both the database seems to be updated (as indicated from the date stamp) AFTER I use the application (VB6 business application) a day or two.
Once an import/export is done, is the detach of the old database compulsory?. In SQL Server Enterprise Manager->(Select say, A database)->All Task-> Detach database
A.mdf - 123,000KB, 2/1/2004 1142am
A_log.ldf - 10,000 KB, 2/1/2004 0133pm
B.mdf - 67,000KB, 2/1/2004 0902am
B_log.ldf - 1024KB, 2/1/2004 0902am
Exporting of the database is ok before the I started using the application.
How can I correct this? Advise is appreciated.
Regards,
Brian
Originally posted by Enigma
you are exporting a database file and getting different sizes .. seems strange
sp_helpdb 'database_name'
on both servers
Copy and paste results over here
That might help|||Are both databases supposed to be active - please describe more as to why you have 2 ? Where are you retrieving the database sizes ? Use sp_spaceused and post the results. Is the real question, what has changed between the 2 databases and how to find those records ?|||Originally posted by rnealejr
Are both databases supposed to be active - please describe more as to why you have 2 ? Where are you retrieving the database sizes ? Use sp_spaceused and post the results. Is the real question, what has changed between the 2 databases and how to find those records ?
the A.mdf and its log is the active database. 2 databases are active as 1 is live/production environment and the other is test/QA environment. the database sizes is seen from the window explorer.|||What needs to be corrected ? You keep mentioning detaching the A database - but why ? Is A production or test ? You need to run the stored procedure sp_spaceused to determine actual space used. Are you concerned that there might be activity on both databases - and you are not sure why ? Are you exporting the data from the production to the test periodically ? Since your log file for B does not appear to have grown, it appears that either minimal or no user activity is occuring on the B database (unless you are backing up the database/transaction log for B).|||Originally posted by rnealejr
What needs to be corrected ? You keep mentioning detaching the A database - but why ? Is A production or test ? You need to run the stored procedure sp_spaceused to determine actual space used. Are you concerned that there might be activity on both databases - and you are not sure why ? Are you exporting the data from the production to the test periodically ? Since your log file for B does not appear to have grown, it appears that either minimal or no user activity is occuring on the B database (unless you are backing up the database/transaction log for B).
if I were to remove (detach) the test/QA data (denotes by B.mdf and its log), will this cause any problem with production (denotes by A.mdf and its log) database?. I noted that the date/time has been updated on the same day even though my ODBC (live application) uses production database which is A.mdf.
What happen if a detach is not done after import/export from production to test/QA environment?. Do the production database get updated (by right it should since ODBC points to production) along with the test database?. I am concern that activities may be updated in the test environment - yes, I am not sure and I would like some feedback.|||There should be no connection between your A database and your B database (unless you have triggers/replication between the 2) - other than the fact that you exported the data from A to B. What method did you use to export the data from A to B ? Several activities can cause the date/time to change on B, but since the log is only 1 meg (the minimum) I would suspect that nothing has really changed on B. But if you are really concerned - dump the test database and copy the database from prod back to test.|||Originally posted by rnealejr
There should be no connection between your A database and your B database (unless you have triggers/replication between the 2) - other than the fact that you exported the data from A to B. What method did you use to export the data from A to B ? Several activities can cause the date/time to change on B, but since the log is only 1 meg (the minimum) I would suspect that nothing has really changed on B. But if you are really concerned - dump the test database and copy the database from prod back to test.
I agree with you. All I did was to simply import/export. no DTS used.
The strange thing I noted was that after the import/export from production to test (and no "detach" was done on test database), I ran the application and did the update. I found was that the test database time/date was updated AND the data went into production and the mdf and log of production did not change (seen from explorer).
Thanks for your feedback.|||It was probably just coincidence ... or a poltergeist (oooohhhhhh - supposed to be a spooky sound)
Database detach problem
Hi all,
I'm trying to detach a database and after archive it for deliver to other server.
The detach wents fine and the database is removed from the database tree list in Management Studio. The problem is that the mdf file is being locked by some process (maybe SQL) and I can't imagine why. Here is the code for this operation:
USE master;
GO
ALTER DATABASE IMS_MCK_MIS SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE IMS_MCK_MIS SET EMERGENCY
GO
DECLARE @.strDate NVARCHAR(8)
DECLARE @.strCmd NVARCHAR(255)
SELECT @.strDate = CONVERT(nvarchar(8), GETDATE(),112)
EXEC sp_detach_db 'IMS_MCK_MIS'SELECT @.strCmd = 'wzzip -ex -m C:\Delivery\archive_' + @.strDate + '.zip E:\DB\SQL\IMS_MCK_MIS.mdf'
exec master..xp_cmdshell @.strCmd
Anyone have any idea how to quickly free the mdf file?
Thanks and regards
CG
Setting emergency mode is not same as detach. If you simply want to copy the database files then set the database state to OFFLINE, copy the files and then make it ONLINE. This gives the benefit of retaining the database definition during the copy process. You can do detach and attach but that will remove the database definition from instance upon detach. Emergency mode is meant for disaster recovery scenarios.|||The emergency state was a desasperated code line. Even putting the database offline, it stills being in use by SQL Server. The idea is to move that DB to an external server in client's house.
This code works fine in SQL Server 2000 but in 2005 even after being removed from available databases in Studio, the file is being in use by operating system.
Do you have any idea, besides a backup operation?
Regards,
|||
What error are you getting from the OS? Were you able to set the database offline. I don't use SSMS (GUI part) that much so I don't know if that does something behind the scenes. But below script works fine for me:
use master
go
create database det_test
go
use det_test
go
select * from sys.database_files
go
use master
go
alter database det_test set offline
go
-- do copy here:
-- cleanup:
drop database det_test
go
Once the database was offline, I was able to copy the files to a different folder/location successfully. So can you post a repro script like this? And also any error message that you are getting now.
|||There's a GUI part of SSMS? :)
Just a wild guess. Any kind of virus scanning going on? Some other process that might have the file busy? I made it work on my laptop server using the following code using detach and with offline too, even with the detach and setting it offline in the same batch:
USE master;
GO
begin try
alter database testZip set single_user with rollback immediate
drop database testZip
end try
begin catch
end catch
go
create database testZip
ON
( NAME = testZip,
FILENAME = 'c:\testZip.mdf',
SIZE = 2mb )
LOG ON
( NAME = testZip_log,
FILENAME = 'c:\testZip.ldf',
SIZE = 1MB
)
go
ALTER DATABASE testZip SET AUTO_UPDATE_STATISTICS ON
GO
--use detach
exec sp_detach_db 'testZip'
DECLARE @.strDate NVARCHAR(8)
DECLARE @.strCmd NVARCHAR(255)
SELECT @.strDate = CONVERT(nvarchar(8), GETDATE(),112)
SELECT @.strCmd = '"C:\Program Files\Self Installed Files\info-zip\zip" C:\archive_' + @.strDate + '.zip c:\testZip.mdf'
exec master..xp_cmdshell @.strCmd
exec sp_attach_db 'testZip','c:\testZip.mdf'
go
--now try offline mode
ALTER DATABASE testZIp SET OFFLINE
DECLARE @.strDate NVARCHAR(8)
DECLARE @.strCmd NVARCHAR(255)
SELECT @.strDate = CONVERT(nvarchar(8), GETDATE(),112)
SELECT @.strCmd = '"C:\Program Files\Self Installed Files\info-zip\zip" C:\archive_' + @.strDate + '.zip c:\testZip.mdf'
exec master..xp_cmdshell @.strCmd
ALTER DATABASE testZIp SET ONLINE
go
Hi guys,
Thanks for the samples. I've tried both and here are the results:
1. the detach method - Error message
output
WinZip(R) Command Line Support Add-On Version 2.0 (Build 7041)
Copyright (c) WinZip International LLC 1991-2005 - All Rights Reserved
Searching... ... ..
Adding testZip.mdf
Warning: could not open for reading: c:\testZip.mdf. .
creating Zip file C:\archive_20061104.zip
NULL
(7 row(s) affected)
The mdf is still being in use.
2. The offline method works fine, the mdf is released.
output
WinZip(R) Command Line Support Add-On Version 2.0 (Build 7041)
Copyright (c) WinZip International LLC 1991-2005 - All Rights Reserved
Searching...
creating Zip file C:\archive_20061104.zip
Moving Files...
NULL
(6 row(s) affected)
No matter how strange this behaviour is, I'll use the second choice.
Thanks for your help.
See you.
Database Detach - orphan users
I've used the sp_'s from this article:
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://support.microsoft.com/default.aspx?scid=kb;[LN];Q246133
And still got orphaned users.
Here's what I did:
1. Script users on master server with sp_'s in KB article.
2. Detach
3. FTP
4. Attach
5. Load users from sp_'s output
user databases still had no link to sql server logins
(not using windows auth by the way, only sql server logins)
I had to use sp_change_users_login but I should not have had to.
What went wrong ?
Possibly logins already existed with the same name on the destination database. Did you get any
errors from step 5? That would explain it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ben" <null@.void.com> wrote in message news:%23yKtezCqFHA.3424@.TK2MSFTNGP14.phx.gbl...
> I've used the sp_'s from this article:
> HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
> http://support.microsoft.com/default.aspx?scid=kb;[LN];Q246133
> And still got orphaned users.
> Here's what I did:
> 1. Script users on master server with sp_'s in KB article.
> 2. Detach
> 3. FTP
> 4. Attach
> 5. Load users from sp_'s output
> user databases still had no link to sql server logins
> (not using windows auth by the way, only sql server logins)
> I had to use sp_change_users_login but I should not have had to.
> What went wrong ?
>
>
|||There were no errors and the users did not exist.
The second server was a fresh install
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:unZSCBIqFHA.544@.TK2MSFTNGP11.phx.gbl...
> Possibly logins already existed with the same name on the destination
database. Did you get any
> errors from step 5? That would explain it.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ben" <null@.void.com> wrote in message
news:%23yKtezCqFHA.3424@.TK2MSFTNGP14.phx.gbl...
>
|||Then I have no ideas I'm afraid. It should not happen. I guess you could compare the file produced
by sp_help_revlogins with the logins on the originating system and with sysusers on both originating
server as well as the restored database. That should give you the whole picture.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ben" <null@.void.com> wrote in message news:%23qdaZrNqFHA.364@.TK2MSFTNGP11.phx.gbl...
> There were no errors and the users did not exist.
> The second server was a fresh install
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:unZSCBIqFHA.544@.TK2MSFTNGP11.phx.gbl...
> database. Did you get any
> news:%23yKtezCqFHA.3424@.TK2MSFTNGP14.phx.gbl...
>
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://support.microsoft.com/default.aspx?scid=kb;[LN];Q246133
And still got orphaned users.
Here's what I did:
1. Script users on master server with sp_'s in KB article.
2. Detach
3. FTP
4. Attach
5. Load users from sp_'s output
user databases still had no link to sql server logins
(not using windows auth by the way, only sql server logins)
I had to use sp_change_users_login but I should not have had to.
What went wrong ?
Possibly logins already existed with the same name on the destination database. Did you get any
errors from step 5? That would explain it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ben" <null@.void.com> wrote in message news:%23yKtezCqFHA.3424@.TK2MSFTNGP14.phx.gbl...
> I've used the sp_'s from this article:
> HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
> http://support.microsoft.com/default.aspx?scid=kb;[LN];Q246133
> And still got orphaned users.
> Here's what I did:
> 1. Script users on master server with sp_'s in KB article.
> 2. Detach
> 3. FTP
> 4. Attach
> 5. Load users from sp_'s output
> user databases still had no link to sql server logins
> (not using windows auth by the way, only sql server logins)
> I had to use sp_change_users_login but I should not have had to.
> What went wrong ?
>
>
|||There were no errors and the users did not exist.
The second server was a fresh install
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:unZSCBIqFHA.544@.TK2MSFTNGP11.phx.gbl...
> Possibly logins already existed with the same name on the destination
database. Did you get any
> errors from step 5? That would explain it.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ben" <null@.void.com> wrote in message
news:%23yKtezCqFHA.3424@.TK2MSFTNGP14.phx.gbl...
>
|||Then I have no ideas I'm afraid. It should not happen. I guess you could compare the file produced
by sp_help_revlogins with the logins on the originating system and with sysusers on both originating
server as well as the restored database. That should give you the whole picture.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ben" <null@.void.com> wrote in message news:%23qdaZrNqFHA.364@.TK2MSFTNGP11.phx.gbl...
> There were no errors and the users did not exist.
> The second server was a fresh install
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:unZSCBIqFHA.544@.TK2MSFTNGP11.phx.gbl...
> database. Did you get any
> news:%23yKtezCqFHA.3424@.TK2MSFTNGP14.phx.gbl...
>
Database Detach - orphan users
I've used the sp_'s from this article:
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://support.microsoft.com/defaul...scid=kb;[LN];Q246133
And still got orphaned users.
Here's what I did:
1. Script users on master server with sp_'s in KB article.
2. Detach
3. FTP
4. Attach
5. Load users from sp_'s output
user databases still had no link to sql server logins
(not using windows auth by the way, only sql server logins)
I had to use sp_change_users_login but I should not have had to.
What went wrong ?Possibly logins already existed with the same name on the destination databa
se. Did you get any
errors from step 5? That would explain it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ben" <null@.void.com> wrote in message news:%23yKtezCqFHA.3424@.TK2MSFTNGP14.phx.gbl...[vbcol
=seagreen]
> I've used the sp_'s from this article:
> HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
> http://support.microsoft.com/defaul...scid=kb;[LN];Q246133
> And still got orphaned users.
> Here's what I did:
> 1. Script users on master server with sp_'s in KB article.
> 2. Detach
> 3. FTP
> 4. Attach
> 5. Load users from sp_'s output
> user databases still had no link to sql server logins
> (not using windows auth by the way, only sql server logins)
> I had to use sp_change_users_login but I should not have had to.
> What went wrong ?
>
>[/vbcol]|||There were no errors and the users did not exist.
The second server was a fresh install
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:unZSCBIqFHA.544@.TK2MSFTNGP11.phx.gbl...
> Possibly logins already existed with the same name on the destination
database. Did you get any
> errors from step 5? That would explain it.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ben" <null@.void.com> wrote in message
news:%23yKtezCqFHA.3424@.TK2MSFTNGP14.phx.gbl...
>|||Then I have no ideas I'm afraid. It should not happen. I guess you could com
pare the file produced
by sp_help_revlogins with the logins on the originating system and with sysu
sers on both originating
server as well as the restored database. That should give you the whole pict
ure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ben" <null@.void.com> wrote in message news:%23qdaZrNqFHA.364@.TK2MSFTNGP11.phx.gbl...seagreen">
> There were no errors and the users did not exist.
> The second server was a fresh install
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:unZSCBIqFHA.544@.TK2MSFTNGP11.phx.gbl...
> database. Did you get any
> news:%23yKtezCqFHA.3424@.TK2MSFTNGP14.phx.gbl...
>
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://support.microsoft.com/defaul...scid=kb;[LN];Q246133
And still got orphaned users.
Here's what I did:
1. Script users on master server with sp_'s in KB article.
2. Detach
3. FTP
4. Attach
5. Load users from sp_'s output
user databases still had no link to sql server logins
(not using windows auth by the way, only sql server logins)
I had to use sp_change_users_login but I should not have had to.
What went wrong ?Possibly logins already existed with the same name on the destination databa
se. Did you get any
errors from step 5? That would explain it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ben" <null@.void.com> wrote in message news:%23yKtezCqFHA.3424@.TK2MSFTNGP14.phx.gbl...[vbcol
=seagreen]
> I've used the sp_'s from this article:
> HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
> http://support.microsoft.com/defaul...scid=kb;[LN];Q246133
> And still got orphaned users.
> Here's what I did:
> 1. Script users on master server with sp_'s in KB article.
> 2. Detach
> 3. FTP
> 4. Attach
> 5. Load users from sp_'s output
> user databases still had no link to sql server logins
> (not using windows auth by the way, only sql server logins)
> I had to use sp_change_users_login but I should not have had to.
> What went wrong ?
>
>[/vbcol]|||There were no errors and the users did not exist.
The second server was a fresh install
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:unZSCBIqFHA.544@.TK2MSFTNGP11.phx.gbl...
> Possibly logins already existed with the same name on the destination
database. Did you get any
> errors from step 5? That would explain it.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ben" <null@.void.com> wrote in message
news:%23yKtezCqFHA.3424@.TK2MSFTNGP14.phx.gbl...
>|||Then I have no ideas I'm afraid. It should not happen. I guess you could com
pare the file produced
by sp_help_revlogins with the logins on the originating system and with sysu
sers on both originating
server as well as the restored database. That should give you the whole pict
ure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ben" <null@.void.com> wrote in message news:%23qdaZrNqFHA.364@.TK2MSFTNGP11.phx.gbl...seagreen">
> There were no errors and the users did not exist.
> The second server was a fresh install
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:unZSCBIqFHA.544@.TK2MSFTNGP11.phx.gbl...
> database. Did you get any
> news:%23yKtezCqFHA.3424@.TK2MSFTNGP14.phx.gbl...
>
Database Detach - orphan users
I've used the sp_'s from this article:
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://support.microsoft.com/default.aspx?scid=kb;[LN];Q246133
And still got orphaned users.
Here's what I did:
1. Script users on master server with sp_'s in KB article.
2. Detach
3. FTP
4. Attach
5. Load users from sp_'s output
user databases still had no link to sql server logins
(not using windows auth by the way, only sql server logins)
I had to use sp_change_users_login but I should not have had to.
What went wrong ?Possibly logins already existed with the same name on the destination database. Did you get any
errors from step 5? That would explain it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ben" <null@.void.com> wrote in message news:%23yKtezCqFHA.3424@.TK2MSFTNGP14.phx.gbl...
> I've used the sp_'s from this article:
> HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
> http://support.microsoft.com/default.aspx?scid=kb;[LN];Q246133
> And still got orphaned users.
> Here's what I did:
> 1. Script users on master server with sp_'s in KB article.
> 2. Detach
> 3. FTP
> 4. Attach
> 5. Load users from sp_'s output
> user databases still had no link to sql server logins
> (not using windows auth by the way, only sql server logins)
> I had to use sp_change_users_login but I should not have had to.
> What went wrong ?
>
>|||There were no errors and the users did not exist.
The second server was a fresh install
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:unZSCBIqFHA.544@.TK2MSFTNGP11.phx.gbl...
> Possibly logins already existed with the same name on the destination
database. Did you get any
> errors from step 5? That would explain it.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ben" <null@.void.com> wrote in message
news:%23yKtezCqFHA.3424@.TK2MSFTNGP14.phx.gbl...
> > I've used the sp_'s from this article:
> > HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
> > http://support.microsoft.com/default.aspx?scid=kb;[LN];Q246133
> > And still got orphaned users.
> >
> > Here's what I did:
> >
> > 1. Script users on master server with sp_'s in KB article.
> > 2. Detach
> > 3. FTP
> > 4. Attach
> > 5. Load users from sp_'s output
> >
> > user databases still had no link to sql server logins
> > (not using windows auth by the way, only sql server logins)
> >
> > I had to use sp_change_users_login but I should not have had to.
> > What went wrong ?
> >
> >
> >
>|||Then I have no ideas I'm afraid. It should not happen. I guess you could compare the file produced
by sp_help_revlogins with the logins on the originating system and with sysusers on both originating
server as well as the restored database. That should give you the whole picture.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ben" <null@.void.com> wrote in message news:%23qdaZrNqFHA.364@.TK2MSFTNGP11.phx.gbl...
> There were no errors and the users did not exist.
> The second server was a fresh install
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:unZSCBIqFHA.544@.TK2MSFTNGP11.phx.gbl...
>> Possibly logins already existed with the same name on the destination
> database. Did you get any
>> errors from step 5? That would explain it.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Ben" <null@.void.com> wrote in message
> news:%23yKtezCqFHA.3424@.TK2MSFTNGP14.phx.gbl...
>> > I've used the sp_'s from this article:
>> > HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
>> > http://support.microsoft.com/default.aspx?scid=kb;[LN];Q246133
>> > And still got orphaned users.
>> >
>> > Here's what I did:
>> >
>> > 1. Script users on master server with sp_'s in KB article.
>> > 2. Detach
>> > 3. FTP
>> > 4. Attach
>> > 5. Load users from sp_'s output
>> >
>> > user databases still had no link to sql server logins
>> > (not using windows auth by the way, only sql server logins)
>> >
>> > I had to use sp_change_users_login but I should not have had to.
>> > What went wrong ?
>> >
>> >
>> >
>
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://support.microsoft.com/default.aspx?scid=kb;[LN];Q246133
And still got orphaned users.
Here's what I did:
1. Script users on master server with sp_'s in KB article.
2. Detach
3. FTP
4. Attach
5. Load users from sp_'s output
user databases still had no link to sql server logins
(not using windows auth by the way, only sql server logins)
I had to use sp_change_users_login but I should not have had to.
What went wrong ?Possibly logins already existed with the same name on the destination database. Did you get any
errors from step 5? That would explain it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ben" <null@.void.com> wrote in message news:%23yKtezCqFHA.3424@.TK2MSFTNGP14.phx.gbl...
> I've used the sp_'s from this article:
> HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
> http://support.microsoft.com/default.aspx?scid=kb;[LN];Q246133
> And still got orphaned users.
> Here's what I did:
> 1. Script users on master server with sp_'s in KB article.
> 2. Detach
> 3. FTP
> 4. Attach
> 5. Load users from sp_'s output
> user databases still had no link to sql server logins
> (not using windows auth by the way, only sql server logins)
> I had to use sp_change_users_login but I should not have had to.
> What went wrong ?
>
>|||There were no errors and the users did not exist.
The second server was a fresh install
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:unZSCBIqFHA.544@.TK2MSFTNGP11.phx.gbl...
> Possibly logins already existed with the same name on the destination
database. Did you get any
> errors from step 5? That would explain it.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ben" <null@.void.com> wrote in message
news:%23yKtezCqFHA.3424@.TK2MSFTNGP14.phx.gbl...
> > I've used the sp_'s from this article:
> > HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
> > http://support.microsoft.com/default.aspx?scid=kb;[LN];Q246133
> > And still got orphaned users.
> >
> > Here's what I did:
> >
> > 1. Script users on master server with sp_'s in KB article.
> > 2. Detach
> > 3. FTP
> > 4. Attach
> > 5. Load users from sp_'s output
> >
> > user databases still had no link to sql server logins
> > (not using windows auth by the way, only sql server logins)
> >
> > I had to use sp_change_users_login but I should not have had to.
> > What went wrong ?
> >
> >
> >
>|||Then I have no ideas I'm afraid. It should not happen. I guess you could compare the file produced
by sp_help_revlogins with the logins on the originating system and with sysusers on both originating
server as well as the restored database. That should give you the whole picture.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ben" <null@.void.com> wrote in message news:%23qdaZrNqFHA.364@.TK2MSFTNGP11.phx.gbl...
> There were no errors and the users did not exist.
> The second server was a fresh install
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:unZSCBIqFHA.544@.TK2MSFTNGP11.phx.gbl...
>> Possibly logins already existed with the same name on the destination
> database. Did you get any
>> errors from step 5? That would explain it.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Ben" <null@.void.com> wrote in message
> news:%23yKtezCqFHA.3424@.TK2MSFTNGP14.phx.gbl...
>> > I've used the sp_'s from this article:
>> > HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
>> > http://support.microsoft.com/default.aspx?scid=kb;[LN];Q246133
>> > And still got orphaned users.
>> >
>> > Here's what I did:
>> >
>> > 1. Script users on master server with sp_'s in KB article.
>> > 2. Detach
>> > 3. FTP
>> > 4. Attach
>> > 5. Load users from sp_'s output
>> >
>> > user databases still had no link to sql server logins
>> > (not using windows auth by the way, only sql server logins)
>> >
>> > I had to use sp_change_users_login but I should not have had to.
>> > What went wrong ?
>> >
>> >
>> >
>
Subscribe to:
Posts (Atom)