Restore mdf file

Friday, March 30, 2012

restore db of 2000 to 2005 logins

I'm not sure why I can't find any info on this issue, because it is blaring.
When backup a 2000 db and restore it on Sql Server 2005, logins are not
created at the server level. And then when I try to create the logins
manually at the server level, I get "login already exists". Then as I've done
with db migrations in the past, I battle with deleting logins at the db level
because they are assigned to schemas.
What is the best way to get logins at the db level to the server level?
Thanks.HI Jamesm,
Thank you for using MSDN Managed Newsgroup Support.
From your description, my understanding of this issue is: After you backup
your SQL Server 2000 database to another SQL Server 2005 instance, the
logins didn't created in the Server side and you get the "User or role '%s'
already exists in the current database." error when you want to grant the
permission to the database. If I misunderstood your concern, please feel
free to let me know.
This is an orphaned users scenario. To resolve this issue, please use the
sp_change_users_login system stored procedure.
USE <database_name>;
GO
sp_change_users_login @.Action='update_one',
@.UserNamePattern='<database_user>', @.LoginName='<login_name>';
GO
You may refer the following KB article:
PRB: "Troubleshooting Orphaned Users" Topic in Books Online is Incomplete
http://support.microsoft.com/kb/274188/en-us
Also, here are some articles may be helpful:
Troubleshooting Orphaned Users
http://msdn2.microsoft.com/en-us/library/ms175475(SQL.90).aspx
sp_change_users_login (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms174378.aspx
How to transfer logins and passwords between instances of SQL Server
http://support.microsoft.com/kb/246133/en-us
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
http://blogs.msdn.com/lcris/archive/2006/04/03/567680.aspx
"Wei Lu" <weilu@.online.microsoft.com> wrote in message
news:fyTtY7NaGHA.932@.TK2MSFTNGXA01.phx.gbl...
> HI Jamesm,
> Thank you for using MSDN Managed Newsgroup Support.
> From your description, my understanding of this issue is: After you backup
> your SQL Server 2000 database to another SQL Server 2005 instance, the
> logins didn't created in the Server side and you get the "User or role
> '%s'
> already exists in the current database." error when you want to grant the
> permission to the database. If I misunderstood your concern, please feel
> free to let me know.
> This is an orphaned users scenario. To resolve this issue, please use the
> sp_change_users_login system stored procedure.
> USE <database_name>;
> GO
> sp_change_users_login @.Action='update_one',
> @.UserNamePattern='<database_user>', @.LoginName='<login_name>';
> GO
> You may refer the following KB article:
> PRB: "Troubleshooting Orphaned Users" Topic in Books Online is Incomplete
> http://support.microsoft.com/kb/274188/en-us
> Also, here are some articles may be helpful:
> Troubleshooting Orphaned Users
> http://msdn2.microsoft.com/en-us/library/ms175475(SQL.90).aspx
> sp_change_users_login (Transact-SQL)
> http://msdn2.microsoft.com/en-us/library/ms174378.aspx
> How to transfer logins and passwords between instances of SQL Server
> http://support.microsoft.com/kb/246133/en-us
> 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.
>|||Awesome! Thank you for help.
"Wei Lu" wrote:
> HI Jamesm,
> Thank you for using MSDN Managed Newsgroup Support.
> From your description, my understanding of this issue is: After you backup
> your SQL Server 2000 database to another SQL Server 2005 instance, the
> logins didn't created in the Server side and you get the "User or role '%s'
> already exists in the current database." error when you want to grant the
> permission to the database. If I misunderstood your concern, please feel
> free to let me know.
> This is an orphaned users scenario. To resolve this issue, please use the
> sp_change_users_login system stored procedure.
> USE <database_name>;
> GO
> sp_change_users_login @.Action='update_one',
> @.UserNamePattern='<database_user>', @.LoginName='<login_name>';
> GO
> You may refer the following KB article:
> PRB: "Troubleshooting Orphaned Users" Topic in Books Online is Incomplete
> http://support.microsoft.com/kb/274188/en-us
> Also, here are some articles may be helpful:
> Troubleshooting Orphaned Users
> http://msdn2.microsoft.com/en-us/library/ms175475(SQL.90).aspx
> sp_change_users_login (Transact-SQL)
> http://msdn2.microsoft.com/en-us/library/ms174378.aspx
> How to transfer logins and passwords between instances of SQL Server
> http://support.microsoft.com/kb/246133/en-us
> 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.
>|||That code is now in the KB as well at:
http://support.microsoft.com/?id=246133
Kind of a mess though. They updated the SQL 2000 article and
code for sp_help_revlogin with the version for 2005 - same
code on Laurentiu's blog.
I don't know where the 2000 version of sp_help_revlogin is
at anymore though.
-Sue
On Wed, 26 Apr 2006 11:57:18 +0300, "Uri Dimant"
<urid@.iscar.co.il> wrote:
>Hi
>http://blogs.msdn.com/lcris/archive/2006/04/03/567680.aspx
>
>
>"Wei Lu" <weilu@.online.microsoft.com> wrote in message
>news:fyTtY7NaGHA.932@.TK2MSFTNGXA01.phx.gbl...
>> HI Jamesm,
>> Thank you for using MSDN Managed Newsgroup Support.
>> From your description, my understanding of this issue is: After you backup
>> your SQL Server 2000 database to another SQL Server 2005 instance, the
>> logins didn't created in the Server side and you get the "User or role
>> '%s'
>> already exists in the current database." error when you want to grant the
>> permission to the database. If I misunderstood your concern, please feel
>> free to let me know.
>> This is an orphaned users scenario. To resolve this issue, please use the
>> sp_change_users_login system stored procedure.
>> USE <database_name>;
>> GO
>> sp_change_users_login @.Action='update_one',
>> @.UserNamePattern='<database_user>', @.LoginName='<login_name>';
>> GO
>> You may refer the following KB article:
>> PRB: "Troubleshooting Orphaned Users" Topic in Books Online is Incomplete
>> http://support.microsoft.com/kb/274188/en-us
>> Also, here are some articles may be helpful:
>> Troubleshooting Orphaned Users
>> http://msdn2.microsoft.com/en-us/library/ms175475(SQL.90).aspx
>> sp_change_users_login (Transact-SQL)
>> http://msdn2.microsoft.com/en-us/library/ms174378.aspx
>> How to transfer logins and passwords between instances of SQL Server
>> http://support.microsoft.com/kb/246133/en-us
>> 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,Sue
> I don't know where the 2000 version of sp_help_revlogin is
> at anymore though.
If you want I can send you
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:d9tv42hmgtd3m5mjpevthjv0fqk519c075@.4ax.com...
> That code is now in the KB as well at:
> http://support.microsoft.com/?id=246133
> Kind of a mess though. They updated the SQL 2000 article and
> code for sp_help_revlogin with the version for 2005 - same
> code on Laurentiu's blog.
> I don't know where the 2000 version of sp_help_revlogin is
> at anymore though.
> -Sue
> On Wed, 26 Apr 2006 11:57:18 +0300, "Uri Dimant"
> <urid@.iscar.co.il> wrote:
>>Hi
>>http://blogs.msdn.com/lcris/archive/2006/04/03/567680.aspx
>>
>>
>>"Wei Lu" <weilu@.online.microsoft.com> wrote in message
>>news:fyTtY7NaGHA.932@.TK2MSFTNGXA01.phx.gbl...
>> HI Jamesm,
>> Thank you for using MSDN Managed Newsgroup Support.
>> From your description, my understanding of this issue is: After you
>> backup
>> your SQL Server 2000 database to another SQL Server 2005 instance, the
>> logins didn't created in the Server side and you get the "User or role
>> '%s'
>> already exists in the current database." error when you want to grant
>> the
>> permission to the database. If I misunderstood your concern, please feel
>> free to let me know.
>> This is an orphaned users scenario. To resolve this issue, please use
>> the
>> sp_change_users_login system stored procedure.
>> USE <database_name>;
>> GO
>> sp_change_users_login @.Action='update_one',
>> @.UserNamePattern='<database_user>', @.LoginName='<login_name>';
>> GO
>> You may refer the following KB article:
>> PRB: "Troubleshooting Orphaned Users" Topic in Books Online is
>> Incomplete
>> http://support.microsoft.com/kb/274188/en-us
>> Also, here are some articles may be helpful:
>> Troubleshooting Orphaned Users
>> http://msdn2.microsoft.com/en-us/library/ms175475(SQL.90).aspx
>> sp_change_users_login (Transact-SQL)
>> http://msdn2.microsoft.com/en-us/library/ms174378.aspx
>> How to transfer logins and passwords between instances of SQL Server
>> http://support.microsoft.com/kb/246133/en-us
>> 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.
>>
>|||Thanks...I have both them.
Just seems very odd they wiped out the 2000 version and
replaced the 2005 code in the 2000 KB article.
Should be two different articles....
-Sue
On Thu, 27 Apr 2006 12:12:16 +0300, "Uri Dimant"
<urid@.iscar.co.il> wrote:
>Hi,Sue
>> I don't know where the 2000 version of sp_help_revlogin is
>> at anymore though.
>If you want I can send you
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:d9tv42hmgtd3m5mjpevthjv0fqk519c075@.4ax.com...
>> That code is now in the KB as well at:
>> http://support.microsoft.com/?id=246133
>> Kind of a mess though. They updated the SQL 2000 article and
>> code for sp_help_revlogin with the version for 2005 - same
>> code on Laurentiu's blog.
>> I don't know where the 2000 version of sp_help_revlogin is
>> at anymore though.
>> -Sue
>> On Wed, 26 Apr 2006 11:57:18 +0300, "Uri Dimant"
>> <urid@.iscar.co.il> wrote:
>>Hi
>>http://blogs.msdn.com/lcris/archive/2006/04/03/567680.aspx
>>
>>
>>"Wei Lu" <weilu@.online.microsoft.com> wrote in message
>>news:fyTtY7NaGHA.932@.TK2MSFTNGXA01.phx.gbl...
>> HI Jamesm,
>> Thank you for using MSDN Managed Newsgroup Support.
>> From your description, my understanding of this issue is: After you
>> backup
>> your SQL Server 2000 database to another SQL Server 2005 instance, the
>> logins didn't created in the Server side and you get the "User or role
>> '%s'
>> already exists in the current database." error when you want to grant
>> the
>> permission to the database. If I misunderstood your concern, please feel
>> free to let me know.
>> This is an orphaned users scenario. To resolve this issue, please use
>> the
>> sp_change_users_login system stored procedure.
>> USE <database_name>;
>> GO
>> sp_change_users_login @.Action='update_one',
>> @.UserNamePattern='<database_user>', @.LoginName='<login_name>';
>> GO
>> You may refer the following KB article:
>> PRB: "Troubleshooting Orphaned Users" Topic in Books Online is
>> Incomplete
>> http://support.microsoft.com/kb/274188/en-us
>> Also, here are some articles may be helpful:
>> Troubleshooting Orphaned Users
>> http://msdn2.microsoft.com/en-us/library/ms175475(SQL.90).aspx
>> sp_change_users_login (Transact-SQL)
>> http://msdn2.microsoft.com/en-us/library/ms174378.aspx
>> How to transfer logins and passwords between instances of SQL Server
>> http://support.microsoft.com/kb/246133/en-us
>> 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.
>>
>|||I am doing a SQL 2000 > 2005 migration. I plan to just restore
databases from backup, and then run the sp_help_revlogin stored
procedure.
Does anyone know if I need to restore the databases before I run this
script, or if its safe to run the script (which migrates all
logins/passwords from my SQL 2000 server), and then restore the
databases at a later date
Thanks in advance,
-Jsql

Restore db in cluster environment

Hi,
I got this error when I move the log file to a diff location in the cluster
environment.
Any ideas and solution?
Thanks
Msg 5184, Level 16, State 2, Line 1
Cannot use file 'T:\Logs\myrpdLog.ldf' for clustered server. Only
formatted files on which the cluster resource of the server has a dependency
can be used.
Your T: drive have not been made a dependency of SQL Server. You need to do
the following:
1) Make the SQL Server resource offline.
2) Move the T: drive resource into the SQL group if it is not already
there.
3) Bring up the properties of the SQL Server resource.
4) Make the T: drive a dependency of SQL Server.
5) Bring the SQL group online.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"mecn" <mecn2002@.yahoo.com> wrote in message
news:eybFs1luHHA.4916@.TK2MSFTNGP04.phx.gbl...
Hi,
I got this error when I move the log file to a diff location in the cluster
environment.
Any ideas and solution?
Thanks
Msg 5184, Level 16, State 2, Line 1
Cannot use file 'T:\Logs\myrpdLog.ldf' for clustered server. Only
formatted files on which the cluster resource of the server has a dependency
can be used.
|||Got it, Thanks
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23A3R85luHHA.536@.TK2MSFTNGP06.phx.gbl...
> Your T: drive have not been made a dependency of SQL Server. You need to
> do
> the following:
> 1) Make the SQL Server resource offline.
> 2) Move the T: drive resource into the SQL group if it is not already
> there.
> 3) Bring up the properties of the SQL Server resource.
> 4) Make the T: drive a dependency of SQL Server.
> 5) Bring the SQL group online.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:eybFs1luHHA.4916@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I got this error when I move the log file to a diff location in the
> cluster
> environment.
> Any ideas and solution?
> Thanks
>
> Msg 5184, Level 16, State 2, Line 1
> Cannot use file 'T:\Logs\myrpdLog.ldf' for clustered server. Only
> formatted files on which the cluster resource of the server has a
> dependency
> can be used.
>
>

Restore db from SQL 2k5 beta 2 to SQL 2k5 final

I have a db I created in SQL 2005 Beta that I would like to keep. I have a
new SQL 2005 server built with the release version. I tried to restore the
DB and I get an error that it is an earlier version and it cannot restore. I
also tried to detach and attach to the new server. I got a similar error
message.
Is there any way to get this to work other than recreate the DB?You need to extract the data and import it into the released version. You
can probably script the schema from the old one and run that on the new one
but you can not restore them.
--
Andrew J. Kelly SQL MVP
"Aaronous" <Aaronous@.discussions.microsoft.com> wrote in message
news:EB2C59E6-9073-4271-BBFE-2A21320D8724@.microsoft.com...
>I have a db I created in SQL 2005 Beta that I would like to keep. I have a
> new SQL 2005 server built with the release version. I tried to restore
> the
> DB and I get an error that it is an earlier version and it cannot restore.
> I
> also tried to detach and attach to the new server. I got a similar error
> message.
> Is there any way to get this to work other than recreate the DB?
>|||Only certain CTP builds (documented at the time) have upgrade paths to RTM -
which CTP/Beta version is the database?
--
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OH%23FBlaEGHA.1088@.tk2msftngp13.phx.gbl...
> You need to extract the data and import it into the released version. You
> can probably script the schema from the old one and run that on the new
> one but you can not restore them.
> --
> Andrew J. Kelly SQL MVP
>
> "Aaronous" <Aaronous@.discussions.microsoft.com> wrote in message
> news:EB2C59E6-9073-4271-BBFE-2A21320D8724@.microsoft.com...
>>I have a db I created in SQL 2005 Beta that I would like to keep. I have
>>a
>> new SQL 2005 server built with the release version. I tried to restore
>> the
>> DB and I get an error that it is an earlier version and it cannot
>> restore. I
>> also tried to detach and attach to the new server. I got a similar error
>> message.
>> Is there any way to get this to work other than recreate the DB?
>|||I exported it. DTS wizard came up and walked me through. Very nice and
easy. Thanks.
"Paul S Randal [MS]" wrote:
> Only certain CTP builds (documented at the time) have upgrade paths to RTM -
> which CTP/Beta version is the database?
> --
> Paul Randal
> Lead Program Manager, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OH%23FBlaEGHA.1088@.tk2msftngp13.phx.gbl...
> > You need to extract the data and import it into the released version. You
> > can probably script the schema from the old one and run that on the new
> > one but you can not restore them.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Aaronous" <Aaronous@.discussions.microsoft.com> wrote in message
> > news:EB2C59E6-9073-4271-BBFE-2A21320D8724@.microsoft.com...
> >>I have a db I created in SQL 2005 Beta that I would like to keep. I have
> >>a
> >> new SQL 2005 server built with the release version. I tried to restore
> >> the
> >> DB and I get an error that it is an earlier version and it cannot
> >> restore. I
> >> also tried to detach and attach to the new server. I got a similar error
> >> message.
> >>
> >> Is there any way to get this to work other than recreate the DB?
> >>
> >
> >
>
>

Restore db from SQL 2k5 beta 2 to SQL 2k5 final

restore db from network drive

All of our database servers are doing backups and saving the .bak files out
on a network share. With SQL 2005, how can I point to that share and restore
the database in SQL 2005 environment?
When I do 'restore database --> From Device --> File; when I click the 'ADD;
button, I only see my local drives on the computer. How can I map to my
network share and point to my restore file on the network?Use UNC names: \\servername\sharename\foldername\file.ext. The
account doing the backups - which is to say the account under which
SQL Server is running - needs appropriate rights to the share.
Roy Harvey
Beacon Falls, CT
On Tue, 5 Jun 2007 12:20:10 -0400, "Mike" <Mike@.community.nospam>
wrote:

>All of our database servers are doing backups and saving the .bak files out
>on a network share. With SQL 2005, how can I point to that share and restor
e
>the database in SQL 2005 environment?
>When I do 'restore database --> From Device --> File; when I click the 'ADD
;
>button, I only see my local drives on the computer. How can I map to my
>network share and point to my restore file on the network?
>|||I've tried the UNC path and I get 'network path not found' message
Beacon falls, ct, your in my nekc of the woods.
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:km4b63p0q6hco968a82ngn955tgse4eg47@.
4ax.com...[vbcol=seagreen]
> Use UNC names: \\servername\sharename\foldername\file.ext. The
> account doing the backups - which is to say the account under which
> SQL Server is running - needs appropriate rights to the share.
> Roy Harvey
> Beacon Falls, CT
> On Tue, 5 Jun 2007 12:20:10 -0400, "Mike" <Mike@.community.nospam>
> wrote:
>|||Hello,
In the management studio; go to query window and use RESTORE DATABASE
command. Make sure that you start sql server service using
a domain account which has access to remote share.
RESTORE DATABASE <DBNAME> FROM DISK='\\Servername\Share\filename.bak' with
stats=5
Thanks
Hari
"Mike" <Mike@.community.nospam> wrote in message
news:%23xEIwJ5pHHA.1244@.TK2MSFTNGP04.phx.gbl...
> I've tried the UNC path and I get 'network path not found' message
> Beacon falls, ct, your in my nekc of the woods.
>
> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:km4b63p0q6hco968a82ngn955tgse4eg47@.
4ax.com...
>|||On Tue, 5 Jun 2007 12:56:18 -0400, "Mike" <Mike@.community.nospam>
wrote:

>I've tried the UNC path and I get 'network path not found' message
Perhaps it is an access rights problem. A partial test can be done
using xp_cmdshell:
EXEC master..xp_cmdshell 'dir \\servername\sharename\folder\*.*'

>Beacon falls, ct, your in my nekc of the woods.
Cool! If you ever need more hands-on help remember I'm around!
Hari's advice to use a query window is excellent, by the way.
Roy Harvey
Beacon Falls, CT|||Ok, I think I got it, but now I'm getting this message:
'the media set has 2 media families but only 1 are provided. All members
must be provided.'
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:89eb631lpobifjd281tdpma4q57sct745h@.
4ax.com...
> On Tue, 5 Jun 2007 12:56:18 -0400, "Mike" <Mike@.community.nospam>
> wrote:
>
> Perhaps it is an access rights problem. A partial test can be done
> using xp_cmdshell:
> EXEC master..xp_cmdshell 'dir \\servername\sharename\folder\*.*'
>
> Cool! If you ever need more hands-on help remember I'm around!
> Hari's advice to use a query window is excellent, by the way.
> Roy Harvey
> Beacon Falls, CT|||Hello,
Did you strip the backup into multiple files? Can you execute RESTORE
LABELONLY (see books online) and see how many files are associated with the
backup.
Thanks
Hari
"Mike" <Mike@.community.nospam> wrote in message
news:u8sG$y6pHHA.2156@.TK2MSFTNGP03.phx.gbl...
> Ok, I think I got it, but now I'm getting this message:
> 'the media set has 2 media families but only 1 are provided. All members
> must be provided.'
>
> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:89eb631lpobifjd281tdpma4q57sct745h@.
4ax.com...
>|||No, one file. and now I can't even backup the SQL 2000 database version to
do a restore from.
This is driving me nuts.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23Ov2b29pHHA.3264@.TK2MSFTNGP04.phx.gbl...
> Hello,
> Did you strip the backup into multiple files? Can you execute RESTORE
> LABELONLY (see books online) and see how many files are associated with
> the backup.
> Thanks
> Hari
> "Mike" <Mike@.community.nospam> wrote in message
> news:u8sG$y6pHHA.2156@.TK2MSFTNGP03.phx.gbl...
>sql

Friday, March 30, 2012

restore db of 2000 to 2005 logins

restore db of 2000 to 2005 logins

Restore db in cluster environment

Restore db from SQL 2k5 beta 2 to SQL 2k5 final

Restore db from SQL 2k5 beta 2 to SQL 2k5 final

Restore db from SQL 2k5 beta 2 to SQL 2k5 final

restore db from network drive

Restore mdf file

Blog Archive

About Me