How to restore, transfer or copy a sql 2005 database to sql 2000 server

Because I'm stupid I assumed, it would be simple to move a sql server 2005 database to sql server 2000, if I don't use special 2005 features. But restore completely not work. No option in case of restore?

OK - there are some hints about using export etc. in

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=166196&SiteID=1

But it seems to be not so easy. Can someone please explain, how to do this move step by step? I need complete transfer for all objects, including fulltext, indexes, triggers and data. Can I use BCP :-( ?

Many thanks for help - I run into real trubble with this...

fd2000

The on-disk format of SQL databases has changed between SQL 2000 and SQL 2005. That is why you cannot restore a SQL 2005 backup onto a SQL 2000 instance. This is not going to change.

So, your options are to find a way to make SQL 2005 work for you, or to export your data, and re-import into a SQL 2000 instance. BCP should work for this. Use the BCP80 option which ensures that no SQL 2005 options are used. Note that if you have added anything which relies on a SQL2005 feature, it will not be fully transferred to your SQL 2000 instance. (UDTs come across as LOBs, etc.)

|||

You have to install a instance of sql server 2000 and export the data/object back to it else u have to make your sql server 2005 work for u.

sanjeevupd

How to restore x.bak.dap file

Hi,

I have a backup file say x.bak.dap

I want to restore this file which has the .dap extension

How to do it?

Please suggest me

Thanks in advance,

Senthil N

If it's a SQL Server backup, you restore the same as any other backup file. SQL Server doesn't really care what the extension is, the file just needs to be a SQL Server backup file. It's a pretty standard practice to use bak for the database backup file extension though.

If you aren't sure about the file itself, try to just view the files in the backup file with:

restore filelistonly

from disk = 'X:\PathToFile\x.bak.dap'

-Sue

|||

Hi,

Now I have the bakup file and I tried to restore 'Test' database from the Query Analyzer (master db).

I am getting the following error

Msg 3154, Level 16, State 4, Line 1

The backup set holds a backup of a database other than the existing 'Test' database.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

Please help me.

Thanks in advance,

Senthil N.

|||

Hi Sue,

Thank you for ur reply.

I tried the command and i am getting the logical & Physical file name.

But still i am getting error.

see my reply.

|||

It may be that your backup file contains more than one set of backups:

Try running:

restore headeronly

from disk = 'X:\PathToFile\x.bak.dap'

And if this is the case, you'll need to look at WITH FILE option in BOL for your restore.

Hope this helps!

|||

I haven't actually been able to replicate your error but there does seem to be a fair few people who have had this issue. It looks like adding the WITH REPLACE clause to the RESTORE statement which overwrites the existing database does the trick.

|||

You probably want to read up on the with move and replace options for restoring a database. To include the move and replace options in your restore statement, it would be something like:

Restore database YourDBName

From disk = 'X:\PathToFile\x.bak.dap'

with move 'LogicalDataFileName' to 'X:\NewLocation\YourDBName.mdf',

move 'LogicalLogFileName' to 'X:\NewLocation\YourDBName.ldf',

replace

-Sue

|||

Thanks Sue,

It's working after adding the replace option.

Regards,

Senthil.N

|||

Thank you Rich,

My problem sorted out after adding the restore options.

Regards,

Senthil.N

How to restore x.bak.dap file

Hi,

I have a backup file say x.bak.dap

I want to restore this file which has the .dap extension

How to do it?

Please suggest me

Thanks in advance,

Senthil N

If it's a SQL Server backup, you restore the same as any other backup file. SQL Server doesn't really care what the extension is, the file just needs to be a SQL Server backup file. It's a pretty standard practice to use bak for the database backup file extension though.

If you aren't sure about the file itself, try to just view the files in the backup file with:

restore filelistonly

from disk = 'X:\PathToFile\x.bak.dap'

-Sue

|||

Hi,

Now I have the bakup file and I tried to restore 'Test' database from the Query Analyzer (master db).

I am getting the following error

Msg 3154, Level 16, State 4, Line 1

The backup set holds a backup of a database other than the existing 'Test' database.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

Please help me.

Thanks in advance,

Senthil N.

|||

Hi Sue,

Thank you for ur reply.

I tried the command and i am getting the logical & Physical file name.

But still i am getting error.

see my reply.

|||

It may be that your backup file contains more than one set of backups:

Try running:

restore headeronly

from disk = 'X:\PathToFile\x.bak.dap'

And if this is the case, you'll need to look at WITH FILE option in BOL for your restore.

Hope this helps!

|||

I haven't actually been able to replicate your error but there does seem to be a fair few people who have had this issue. It looks like adding the WITH REPLACE clause to the RESTORE statement which overwrites the existing database does the trick.

|||

You probably want to read up on the with move and replace options for restoring a database. To include the move and replace options in your restore statement, it would be something like:

Restore database YourDBName

From disk = 'X:\PathToFile\x.bak.dap'

with move 'LogicalDataFileName' to 'X:\NewLocation\YourDBName.mdf',

move 'LogicalLogFileName' to 'X:\NewLocation\YourDBName.ldf',

replace

-Sue

|||

Thanks Sue,

It's working after adding the replace option.

Regards,

Senthil.N

|||

Thank you Rich,

My problem sorted out after adding the restore options.

Regards,

Senthil.N

How to restore Values to Table based on some Query

Hi,
I have a table called A and another Table Called B.
A table has the following Column and Values
ID One Two Three
---
10 test01 test02 test03
11 test11 test12 test13
12 test21 test22 test23
13 test31 test32 test33
B table has the following Column and Values
ID One Two Three
---
10 abcd01 abcd02 abcd03
11 abcd11 abcd12 abcd13
12 abcd21 abcd22
13 abcd31 abcd32 abcd33
I want to take a backup of Column Three where ID in(11,12,13). I know
using BCP utility i can take a backup of Table A of Column Three.
Following is the Query
"select Three,ID from [Test].dbo.A where ID in(11,12,13)" queryout
c:\try.txt -U sa -P ***
and then i want to restore it in to the Table B's Column Three by
specifying some Query. Meaning i want to restore the values to the
Table B's Column Three where ID is the ID which is obtained in
Backup[Table A]. How can i do this? If anybody knows the solution
Please Let me know to solve it.
Thanks,
Vinoth
Vinoth@.gsdindia.comHi
I am not totally sure what you require, as it sounds like a simple update
statement would do what you require e.g.
UPDATE B
SET [Three] = A.[Three]
FROM TABLEB B
JOIN TABLEA A ON A.id = B.id and B.id in ( 11, 12, 13 )
If you do choose to BCP the data out then your would probably need to load
the data into a staging table.
You may want to show what the final states of table a and b are.
John
"vinoth@.gsdindia.com" wrote:

> Hi,
>
> I have a table called A and another Table Called B.
>
> A table has the following Column and Values
>
> ID One Two Three
> ---
> 10 test01 test02 test03
> 11 test11 test12 test13
> 12 test21 test22 test23
> 13 test31 test32 test33
>
> B table has the following Column and Values
>
> ID One Two Three
> ---
> 10 abcd01 abcd02 abcd03
> 11 abcd11 abcd12 abcd13
> 12 abcd21 abcd22
> 13 abcd31 abcd32 abcd33
>
> I want to take a backup of Column Three where ID in(11,12,13). I know
> using BCP utility i can take a backup of Table A of Column Three.
> Following is the Query
>
> "select Three,ID from [Test].dbo.A where ID in(11,12,13)" queryout
> c:\try.txt -U sa -P ***
>
> and then i want to restore it in to the Table B's Column Three by
> specifying some Query. Meaning i want to restore the values to the
> Table B's Column Three where ID is the ID which is obtained in
> Backup[Table A]. How can i do this? If anybody knows the solution
> Please Let me know to solve it.
>
> Thanks,
> Vinoth
> Vinoth@.gsdindia.com
>

How to restore Values to Table based on some Query

Hi,
I have a table called A and another Table Called B.
A table has the following Column and Values
ID One Two Three
---
10 test01 test02 test03
11 test11 test12 test13
12 test21 test22 test23
13 test31 test32 test33
B table has the following Column and Values
ID One Two Three
---
10 abcd01 abcd02 abcd03
11 abcd11 abcd12 abcd13
12 abcd21 abcd22
13 abcd31 abcd32 abcd33
I want to take a backup of Column Three where ID in(11,12,13). I know
using BCP utility i can take a backup of Table A of Column Three.
Following is the Query
"select Three,ID from [Test].dbo.A where ID in(11,12,13)" queryout
c:\try.txt -U sa -P ***
and then i want to restore it in to the Table B's Column Three by
specifying some Query. Meaning i want to restore the values to the
Table B's Column Three where ID is the ID which is obtained in
Backup[Table A]. How can i do this? If anybody knows the solution
Please Let me know to solve it.
Thanks,
Vinoth
Vinoth@.gsdindia.comYou don't need to extract it to a file. If you want to update B from A,
just do:
update B
set
Three = (select A.Three from A where A.ID = B.ID)
where
ID in (11, 12, 13)
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
<vinoth@.gsdindia.com> wrote in message
news:1131797987.096680.189410@.g14g2000cwa.googlegroups.com...
> Hi,
>
> I have a table called A and another Table Called B.
>
> A table has the following Column and Values
>
> ID One Two Three
> ---
> 10 test01 test02 test03
> 11 test11 test12 test13
> 12 test21 test22 test23
> 13 test31 test32 test33
>
> B table has the following Column and Values
>
> ID One Two Three
> ---
> 10 abcd01 abcd02 abcd03
> 11 abcd11 abcd12 abcd13
> 12 abcd21 abcd22
> 13 abcd31 abcd32 abcd33
>
> I want to take a backup of Column Three where ID in(11,12,13). I know
> using BCP utility i can take a backup of Table A of Column Three.
> Following is the Query
>
> "select Three,ID from [Test].dbo.A where ID in(11,12,13)" queryout
> c:\try.txt -U sa -P ***
>
> and then i want to restore it in to the Table B's Column Three by
> specifying some Query. Meaning i want to restore the values to the
> Table B's Column Three where ID is the ID which is obtained in
> Backup[Table A]. How can i do this? If anybody knows the solution
> Please Let me know to solve it.
>
> Thanks,
> Vinoth
> Vinoth@.gsdindia.com
>|||Thanks for ur reply,
The table A will be in One database of Some Server and Table B will
be in Some other Database of Some other Server. How can i do it now?
Thanks,
Vinoth|||Use 4-part naming (after adding a linked server):
update B
set
Three = (select A.Three from OtherServer.OtherDB.dbo.A as A where A.ID =
B.ID)
where
ID in (11, 12, 13)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
<vinoth@.gsdindia.com> wrote in message
news:1131799665.556244.160530@.g44g2000cwa.googlegroups.com...
> Thanks for ur reply,
>
> The table A will be in One database of Some Server and Table B will
> be in Some other Database of Some other Server. How can i do it now?
> Thanks,
> Vinoth
>|||Hi,
I cant use the above query. Because i dont have the A and B table on
the same time. A table will be deleted. I want to restore the deleted A
table values[ This values wil be kept in a text file using BCP utility]
to the B table. Now could you please tell me how can i do this?
Thanks,
Vinoth
Vinoth@.gsdindia.com|||I still don't understand. You could simply create a new table, say CopyOfA
and put the old data there.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
<vinoth@.gsdindia.com> wrote in message
news:1131802194.908738.97710@.g14g2000cwa.googlegroups.com...
> Hi,
>
> I cant use the above query. Because i dont have the A and B table on
> the same time. A table will be deleted. I want to restore the deleted A
> table values[ This values wil be kept in a text file using BCP utility
]
> to the B table. Now could you please tell me how can i do this?
> Thanks,
> Vinoth
> Vinoth@.gsdindia.com
>

How to restore Values to Table based on some Query

Hi,
I have a table called A and another Table Called B.
A table has the following Column and Values
ID One Two Three
---
10 test01 test02 test03
11 test11 test12 test13
12 test21 test22 test23
13 test31 test32 test33
B table has the following Column and Values
ID One Two Three
---
10 abcd01 abcd02 abcd03
11 abcd11 abcd12 abcd13
12 abcd21 abcd22
13 abcd31 abcd32 abcd33
I want to take a backup of Column Three where ID in(11,12,13). I know
using BCP utility i can take a backup of Table A of Column Three.
Following is the Query
"select Three,ID from [Test].dbo.A where ID in(11,12,13)" queryout
c:\try.txt -U sa -P ***
and then i want to restore it in to the Table B's Column Three by
specifying some Query. Meaning i want to restore the values to the
Table B's Column Three where ID is the ID which is obtained in
Backup[Table A]. How can i do this? If anybody knows the solution
Please Let me know to solve it.
Thanks,
Vinoth
Vinoth@.gsdindia.comYou don't need to extract it to a file. If you want to update B from A,
just do:
update B
set
Three = (select A.Three from A where A.ID = B.ID)
where
ID in (11, 12, 13)
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
<vinoth@.gsdindia.com> wrote in message
news:1131797987.096680.189410@.g14g2000cwa.googlegroups.com...
> Hi,
>
> I have a table called A and another Table Called B.
>
> A table has the following Column and Values
>
> ID One Two Three
> ---
> 10 test01 test02 test03
> 11 test11 test12 test13
> 12 test21 test22 test23
> 13 test31 test32 test33
>
> B table has the following Column and Values
>
> ID One Two Three
> ---
> 10 abcd01 abcd02 abcd03
> 11 abcd11 abcd12 abcd13
> 12 abcd21 abcd22
> 13 abcd31 abcd32 abcd33
>
> I want to take a backup of Column Three where ID in(11,12,13). I know
> using BCP utility i can take a backup of Table A of Column Three.
> Following is the Query
>
> "select Three,ID from [Test].dbo.A where ID in(11,12,13)" queryout
> c:\try.txt -U sa -P ***
>
> and then i want to restore it in to the Table B's Column Three by
> specifying some Query. Meaning i want to restore the values to the
> Table B's Column Three where ID is the ID which is obtained in
> Backup[Table A]. How can i do this? If anybody knows the solution
> Please Let me know to solve it.
>
> Thanks,
> Vinoth
> Vinoth@.gsdindia.com
>|||Thanks for ur reply,
The table A will be in One database of Some Server and Table B will
be in Some other Database of Some other Server. How can i do it now?
Thanks,
Vinoth|||Use 4-part naming (after adding a linked server):
update B
set
Three = (select A.Three from OtherServer.OtherDB.dbo.A as A where A.ID =B.ID)
where
ID in (11, 12, 13)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
<vinoth@.gsdindia.com> wrote in message
news:1131799665.556244.160530@.g44g2000cwa.googlegroups.com...
> Thanks for ur reply,
>
> The table A will be in One database of Some Server and Table B will
> be in Some other Database of Some other Server. How can i do it now?
> Thanks,
> Vinoth
>|||Hi,
I cant use the above query. Because i dont have the A and B table on
the same time. A table will be deleted. I want to restore the deleted A
table values[ This values wil be kept in a text file using BCP utility]
to the B table. Now could you please tell me how can i do this?
Thanks,
Vinoth
Vinoth@.gsdindia.com|||I still don't understand. You could simply create a new table, say CopyOfA
and put the old data there.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
<vinoth@.gsdindia.com> wrote in message
news:1131802194.908738.97710@.g14g2000cwa.googlegroups.com...
> Hi,
>
> I cant use the above query. Because i dont have the A and B table on
> the same time. A table will be deleted. I want to restore the deleted A
> table values[ This values wil be kept in a text file using BCP utility]
> to the B table. Now could you please tell me how can i do this?
> Thanks,
> Vinoth
> Vinoth@.gsdindia.com
>

How to restore Values to Table based on some Query

Hi,
I have a table called A and another Table Called B.
A table has the following Column and Values
ID One Two Three
10 test01 test02 test03
11 test11 test12 test13
12 test21 test22 test23
13 test31 test32 test33
B table has the following Column and Values
ID One Two Three
10 abcd01 abcd02 abcd03
11 abcd11 abcd12 abcd13
12 abcd21 abcd22
13 abcd31 abcd32 abcd33
I want to take a backup of Column Three where ID in(11,12,13). I know
using BCP utility i can take a backup of Table A of Column Three.
Following is the Query
"select Three,ID from [Test].dbo.A where ID in(11,12,13)" queryout
c:\try.txt -U sa -P ***
and then i want to restore it in to the Table B's Column Three by
specifying some Query. Meaning i want to restore the values to the
Table B's Column Three where ID is the ID which is obtained in
Backup[Table A]. How can i do this? If anybody knows the solution
Please Let me know to solve it.
Thanks,
Vinoth
Vinoth@.gsdindia.com
You don't need to extract it to a file. If you want to update B from A,
just do:
update B
set
Three = (select A.Three from A where A.ID = B.ID)
where
ID in (11, 12, 13)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
<vinoth@.gsdindia.com> wrote in message
news:1131797987.096680.189410@.g14g2000cwa.googlegr oups.com...
> Hi,
>
> I have a table called A and another Table Called B.
>
> A table has the following Column and Values
>
> ID One Two Three
> 10 test01 test02 test03
> 11 test11 test12 test13
> 12 test21 test22 test23
> 13 test31 test32 test33
>
> B table has the following Column and Values
>
> ID One Two Three
> 10 abcd01 abcd02 abcd03
> 11 abcd11 abcd12 abcd13
> 12 abcd21 abcd22
> 13 abcd31 abcd32 abcd33
>
> I want to take a backup of Column Three where ID in(11,12,13). I know
> using BCP utility i can take a backup of Table A of Column Three.
> Following is the Query
>
> "select Three,ID from [Test].dbo.A where ID in(11,12,13)" queryout
> c:\try.txt -U sa -P ***
>
> and then i want to restore it in to the Table B's Column Three by
> specifying some Query. Meaning i want to restore the values to the
> Table B's Column Three where ID is the ID which is obtained in
> Backup[Table A]. How can i do this? If anybody knows the solution
> Please Let me know to solve it.
>
> Thanks,
> Vinoth
> Vinoth@.gsdindia.com
>
|||Thanks for ur reply,
The table A will be in One database of Some Server and Table B will
be in Some other Database of Some other Server. How can i do it now?
Thanks,
Vinoth
|||Use 4-part naming (after adding a linked server):
update B
set
Three = (select A.Three from OtherServer.OtherDB.dbo.A as A where A.ID =
B.ID)
where
ID in (11, 12, 13)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
<vinoth@.gsdindia.com> wrote in message
news:1131799665.556244.160530@.g44g2000cwa.googlegr oups.com...
> Thanks for ur reply,
>
> The table A will be in One database of Some Server and Table B will
> be in Some other Database of Some other Server. How can i do it now?
> Thanks,
> Vinoth
>
|||Hi,
I cant use the above query. Because i dont have the A and B table on
the same time. A table will be deleted. I want to restore the deleted A
table values[ This values wil be kept in a text file using BCP utility]
to the B table. Now could you please tell me how can i do this?
Thanks,
Vinoth
Vinoth@.gsdindia.com
|||I still don't understand. You could simply create a new table, say CopyOfA
and put the old data there.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
<vinoth@.gsdindia.com> wrote in message
news:1131802194.908738.97710@.g14g2000cwa.googlegro ups.com...
> Hi,
>
> I cant use the above query. Because i dont have the A and B table on
> the same time. A table will be deleted. I want to restore the deleted A
> table values[ This values wil be kept in a text file using BCP utility]
> to the B table. Now could you please tell me how can i do this?
> Thanks,
> Vinoth
> Vinoth@.gsdindia.com
>

How to RESTORE USERS PASSWORDS in MS SQL 2K when you restore db from a bkup?

How to RESTORE USERS PASSWORDS in MS SQL 2K when you restore db from a bkup?Please eead up on sp_change_users_login within Books Online (within the =
SQL Server program group).
--=20
Keith
"ktf" <anonymous@.discussions.microsoft.com> wrote in message =
news:6B76231F-ED2C-458B-88F9-ED10BF5BF5DD@.microsoft.com...
>
|||Hi,
Users password :
All the SQL Server logins and passwords are stored in MASTER database. SO if
you restore a database independently to a new server the
Login names will not be created. To syncronize you have to execute the
system procedure in your newly restored database,
sp_change_users_login (Refer books online for details and parameters)
Thanks
Hari
MCDBA
"ktf" <anonymous@.discussions.microsoft.com> wrote in message
news:6B76231F-ED2C-458B-88F9-ED10BF5BF5DD@.microsoft.com...
>
|||The passwords are encrypted. How am I be able to restore the password? How sp_change_users_login can help me? What steps should I take? Because of corruption caused by the replication software, last time I had to restore from backup on the same box and I
had to give a generic password to all users and that was a security issue. Will remapping logins do it for me? Does the Microsoft have any solution to restore passwords? I believe this is a very important issue. How can Microsoft help me (correct me if I
am wrong!) I know passwords are encrypted and the pattern is changing every time!
Thank you
|||From Books Online:
sp_change_users_login
New Information - SQL Server 2000 SP3.
Maps an existing user in a database to a Microsoft=C2=AE SQL =
Server=E2=84=A2 login.
Syntax
sp_change_users_login [ @.Action =3D ] 'action'=20
[ , [ @.UserNamePattern =3D ] 'user' ]=20
[ , [ @.LoginName =3D ] 'login' ]
[ , [ @.Password =3D ] 'password' ]
Arguments
[@.Action =3D] 'action'
Describes the action to be performed by the procedure. action is =
varchar(10), and can be one of these values.
Value Description=20
Auto_Fix Links a user entry in the sysusers table in the current =
database to a login of the same name in sysxlogins. You should check the =
result from the Auto_Fix statement to confirm that the correct link is =
in fact made. Avoid using Auto_Fix in security-sensitive situations.=20
When using Auto_Fix, you must specify user and password; login =
must be NULL. user must be a valid user in the current database.
=20
Report Lists the users and corresponding security identifiers =
(SID) in the current database that are not linked to any login.=20
user, login, and password must be NULL or not specified.
=20
Update_One Links the specified user in the current database to =
login. login must already exist. user and login must be specified. =
password must be NULL or not specified.=20
[@.UserNamePattern =3D] 'user'
Is the name of a SQL Server user in the current database. user is =
sysname, with a default of NULL.=20
[@.LoginName =3D] 'login'
Is the name of a SQL Server login. login is sysname, with a default of =
NULL.=20
[@.Password =3D] 'password'
Is the password assigned to a new SQL Server login created by Auto_Fix. =
If a matching login already exists, the user and login are mapped and =
password is ignored. If a matching login does not exist, =
sp_change_users_login creates a new SQL Server login and assigns =
password as the password for the new login. password is sysname, with a =
default of NULL.
Return Code Values
0 (success) or 1 (failure)
Result Sets
Column name Data type Description=20
UserName sysname Login name.=20
UserSID varbinary(85) Login security identifier.=20
Remarks
Use this procedure to link the security account for a user in the =
current database with a login. If the login for a user has changed, use =
sp_change_users_login to link the user to the new login without losing =
the user's permissions.
sp_change_users_login can be used only for SQL Server logins; it cannot =
be used with Windows logins.
login cannot be sa, and user cannot be the dbo, guest, or =
INFORMATION_SCHEMA users.
sp_change_users_login cannot be executed within a user-defined =
transaction.
Permissions
Any member of the public role can execute sp_change_users_login with the =
Report option. Only members of the sysadmin fixed server role can =
specify the Auto_Fix option. Only members of the sysadmin or db_owner =
roles can specify the Update_One option.
Examples
A. Show a report of the current user to login mappings
This example produces a report of the users in the current database and =
their security identifiers.
EXEC sp_change_users_login 'Report'
B. Change the login for a user
This example changes the link between user Mary in the pubs database and =
the existing login, to the new login NewMary (added with sp_addlogin).
--Add the new login.
USE master
go
EXEC sp_addlogin 'NewMary'
go
--Change the user account to link with the 'NewMary' login.
USE pubs
go
EXEC sp_change_users_login 'Update_One', 'Mary', 'NewMary'
C. Automatically map a user to a login, creating a new login if =
necessary
This example shows how to use the Auto_Fix option to map an existing =
user to a login with the same name, or create the SQL Server login Mary =
with the password B3r12-36 if the login Mary does not exist.
USE pubs
go
EXEC sp_change_users_login 'Auto_Fix', 'Mary', NULL, 'B3r12-36'
go
See Also
sp_addlogin
sp_adduser
sp_helplogins
System Stored Procedures
=C2=A91988-2004 Microsoft Corporation. All Rights Reserved.
--=20
Keith
"ktf" <anonymous@.discussions.microsoft.com> wrote in message =
news:2F5367FC-6951-416F-9132-FE145823FD55@.microsoft.com...
>=20
> The passwords are encrypted. How am I be able to restore the password? =
How sp_change_users_login can help me? What steps should I take? =
Because of corruption caused by the replication software, last time I =
had to restore from backup on the same box and I had to give a generic =
password to all users and that was a security issue. Will remapping =
logins do it for me? Does the Microsoft have any solution to restore =
passwords? I believe this is a very important issue. How can Microsoft =
help me (correct me if I am wrong!) I know passwords are encrypted and =
the pattern is changing every time! =20
> Thank you
>
|||I had the old one and just updated mine. Thank you
|||But we want to use the same passwords and do not create new ones (that is a security violation) this article applies for when I want to put new passwords not reuse the existing ones. (Correct me if I am wrong)
Thank you,
ktf
|||What do you mean by "that is a security violation?"
This example (from Books Online) allows you to fix Mary's login and set =
the password to 'B3r12-36'. Isn't that what you want to do?
C. Automatically map a user to a login, creating a new login if =
necessary
This example shows how to use the Auto_Fix option to map an existing =
user to a login with the same name, or create the SQL Server login Mary =
with the password B3r12-36 if the login Mary does not exist.
USE pubs
go
EXEC sp_change_users_login 'Auto_Fix', 'Mary', NULL, 'B3r12-36'
go
--=20
Keith
"ktf" <anonymous@.discussions.microsoft.com> wrote in message =
news:9289E8DF-FEA8-4B17-8DFD-05FB2E3DE6A8@.microsoft.com...
> But we want to use the same passwords and do not create new ones (that =
is a security violation) this article applies for when I want to put new =
passwords not reuse the existing ones. (Correct me if I am wrong)
> Thank you,
> ktf
>
|||I meant even me should not know about the users passwords, in event of restoring a database I am not allowed to create new passwords. All users should be able to use their original passwords to login.
|||Can you give them an arbitrary password that they will set the first =
time they log in?
You may also find this helpful:
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://www.support.microsoft.com/?id=3D246133
--=20
Keith
"ktf" <anonymous@.discussions.microsoft.com> wrote in message =
news:891BB281-C620-4CDE-B08A-A8C526FEFA7E@.microsoft.com...
>=20
> I meant even me should not know about the users passwords, in event of =
restoring a database I am not allowed to create new passwords. All users =
should be able to use their original passwords to login.
>

How to restore Transaction log

Hello everyone,
I have a problem which is driving me crazy. I can't figure it out how I'm
going to solve this problem but I appreciate any help in this regards.
I'm configuring a log shipping server and it's almost done. The only problem
I'm having is restoring logs from the primary server. the primary server is
appending logs in a log file. If I use the RESTORE HEADERONLY command to
read the file I can see lot's of backup sets like FILE 1, FILE 2, FILE
3....Now I know how to use the restore command but my problem is using TSQL
how can I restore this file in one go...I mean I don't have to restore
backup sets one by one...
Thanx.
Muneeb.You cannot make a single RESTORE command do several RESTORE operations. You
need to do several RESTORE executions.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Muneeb Ahmed" <muneeb.ahmed@.mhs.com> wrote in message
news:eBKYz4UDEHA.2564@.TK2MSFTNGP11.phx.gbl...
> Hello everyone,
> I have a problem which is driving me crazy. I can't figure it out how I'm
> going to solve this problem but I appreciate any help in this regards.
> I'm configuring a log shipping server and it's almost done. The only
problem
> I'm having is restoring logs from the primary server. the primary server
is
> appending logs in a log file. If I use the RESTORE HEADERONLY command to
> read the file I can see lot's of backup sets like FILE 1, FILE 2, FILE
> 3....Now I know how to use the restore command but my problem is using
TSQL
> how can I restore this file in one go...I mean I don't have to restore
> backup sets one by one...
> Thanx.
> Muneeb.
>

how to restore the sqlserver2000s mdb files to another sqlserver

Hi there,
My sqlserver 2000 was down(means failed to reload ) I just copied all the mdb files and log files from that server. now i wants to reload my databases from the mdb files. is it possible?. If so pls inform me how to do that.
Its very urgent. Pls help me:
confused:If you have all of the MDF and LDF files look into using SP_ATTACH_DB. After that you'll need to recreate the logins in MASTER from those in your databases.

How to Restore the msdb DatBase

Hi,

i have reinstalled the SQL Server before it i have taken backup of both master and
msdb database.Because there were many scduled jobs.
After Installation i want recreate those scduled job.
That i think i can get by restoring msdb.
But it is not restoring how can i restore it.

Or there is any way to get the all scduled job list so that i can recreate it manully.

Regards
Sanjay

Make sure the SQL Server Agent is stopped which uses the msdb database, then the restore should work fine. As a best practise I always get the scripts of the scheduled jobs in addition to the backup of the msdb database. This allows me to execute single script for job creation.

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de
|||

Hi jens,

i am tryning restore after stopping SQL Server Agent is stopped.

But it is giving the error massage that

"The backup of the system database on device E:\EcoSystemDatabasebackup\msdb_2006070200.BAK can not be restored because it was created by a different version of the server (134218488) than this server (134219767) .RESTORE DATABASE is terminating abnormally.

How can i restore it .while i am able to restore it in another server by different name.

Soisthere is any way to collect information of secduled job form there and create it in re quired server .

it is possible or not ?

if possible how can i do it.

Regards

Sanjay

|||

This isn't replication related, so moving to Engine forum.

By the way, what version of sql server did you take the backup from, and what version are you restoring it to (do select @.@.version)? As the error message says, if you're trying to restore to an earlier version of sql server, it won't work.

|||

Sorry, to come back that late. As greg already said, you have a problem concerning your different versions of your database (might have a service pack or hotfix installed meanwhile ?).

This is what a kn article is telling about the problem: http://support.microsoft.com/kb/264474/EN-US/

Another option would be to transfer the data via DTS, although I never tried that I would make sure to have a backup of the current (running msdb) first.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

Hi guys,

As i saw your goal it is to recreate the same jobs on another MS Sql Server.

This can be done very simple without using the backup/restore mechanism. Just start Enterprise Manager from the server where you have all the jobs.

Go in the tree in Management/SQL Server Agent/Jobs and from the context menu choose generate SQL Script. You will get a nice file with all your jobs.

This script file you will need to run it on the new SQL Server to create all the jobs there.

I hope will help.

How to restore the master db to server w/ new name

Not sure where to post this. How do I restore the master database to a brand new server that has a different name than the server the master currently resides on? I am using SQL Server 2005.

Try this article:

Moving system dbs 2005
http://msdn2.microsoft.com/en-us/library/ms345408(en-US,SQL.90).aspx

How to restore the entire replication topology in different boxe

We are migrating the entire sqlserver environment from Window2000 to Windows
2003 on different boxes with different NT servernames. I have installed the
sqlserver instances and I have restored the publisher, distribution db on the
primary side and the subscriber db on the replicated side. However, I don't
see any publications under the replication folder. Do I need to go through
the replication configuration first then restore the distribution?
Please advise. Thanks.
Wen,
for migrating to another server which has a different netbios name, the only
realistic solution is to script out the publications on the original setup,
install the new servers and then run the scripts on the new server, changing
the necessary parts eg servername and job owners.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

How to restore the deleted records?

Hi,
Plz help me to restore deleted records.Pl zsuggest the ways to do it.
Thanks!!There's no way within the SQL Server product itself. Rows aren't "flagged for deletion" like they are in some products (which isn't strictly true, but close enough for this discussion). Once SQL deletes them, they are truly gone.

There are some third party products that mitigate this, such as Lumigent's Log Explorer.

-PatP|||Or restore the data from last good known backup ;)

How to restore the database , Urgent!

I droped a table by mistake !!!!
How ?Restore the last backup to another dtabase and copy the table across.

How to restore tables dropped from logs in sql server?

How to restore tables dropped from logs in sql server 2000?
Peace in Christ
Marco Napoli
http://www.ourlovingmother.org> How to restore tables dropped from logs in sql server 2000?
http://www.aspfaq.com/2449

How to restore system messages to master.dbo.sysmessages

Hi,
I suspect that after a blackout here at the office (time enough to loose the
UPS's) I have lost some messages from the sysmessages table in the master
database.
I have discovered this when trying to construct a snapshot replication in
one server, and discovering, after diving into SP code for hours, that the
SP that configure the replication uses some messages to name the job step of
the agents that perform replication, and also discovering that the messages
are not in my database.
So, what can I do?. Do I have to perform a restore of the master database?.
What happen if I have the replication active in the server?.
Thanks
Flix DurnDo you happen to have another SQL Server with similar setup? If so, you
could copy the messages across. Look up sp_addmessage in Books Online too.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Felix D." <Felix.Duran@.segundamano.es> wrote in message
news:eRvxjbS2DHA.2032@.TK2MSFTNGP09.phx.gbl...
Hi,
I suspect that after a blackout here at the office (time enough to loose the
UPS's) I have lost some messages from the sysmessages table in the master
database.
I have discovered this when trying to construct a snapshot replication in
one server, and discovering, after diving into SP code for hours, that the
SP that configure the replication uses some messages to name the job step of
the agents that perform replication, and also discovering that the messages
are not in my database.
So, what can I do?. Do I have to perform a restore of the master database?.
What happen if I have the replication active in the server?.
Thanks
Flix Durn

How to restore system messages to master.dbo.sysmessages

Hi,
I suspect that after a blackout here at the office (time enough to loose the
UPS's) I have lost some messages from the sysmessages table in the master
database.
I have discovered this when trying to construct a snapshot replication in
one server, and discovering, after diving into SP code for hours, that the
SP that configure the replication uses some messages to name the job step of
the agents that perform replication, and also discovering that the messages
are not in my database.
So, what can I do?. Do I have to perform a restore of the master database?.
What happen if I have the replication active in the server?.
Thanks
Félix DuránDo you happen to have another SQL Server with similar setup? If so, you
could copy the messages across. Look up sp_addmessage in Books Online too.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Felix D." <Felix.Duran@.segundamano.es> wrote in message
news:eRvxjbS2DHA.2032@.TK2MSFTNGP09.phx.gbl...
Hi,
I suspect that after a blackout here at the office (time enough to loose the
UPS's) I have lost some messages from the sysmessages table in the master
database.
I have discovered this when trying to construct a snapshot replication in
one server, and discovering, after diving into SP code for hours, that the
SP that configure the replication uses some messages to name the job step of
the agents that perform replication, and also discovering that the messages
are not in my database.
So, what can I do?. Do I have to perform a restore of the master database?.
What happen if I have the replication active in the server?.
Thanks
Félix Durán

How to Restore SQL2K Full bkup then tran logs?

I am upgrading from SQL2000 to SQL2005 - I want to restore a full SQL2000
backup, then the transaction log backups to 2005. Am getting error below w/
full in standby - How to resolve? Thanks.
RESTORE DATABASE [MyDB] FROM DISK = N'\\srvimgdb2\bkData\MyDB.bak'
WITH FILE = 1, NOUNLOAD, STATS = 10, STANDBY = N'E:\dbData\standbyMyDB.txt',
REPLACE,
MOVE N'MyDB_Data' TO N'E:\dbData\MyDB_Data.mdf',
MOVE N'MyDB_Log' TO N'C:\dbLogs\MyDB_log.ldf'
Msg 3180, Level 16, State 1, Line 2
This backup cannot be restored using WITH STANDBY because a database upgrade
is needed. Reissue the RESTORE without WITH STANDBY.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.Chris wrote:
> I am upgrading from SQL2000 to SQL2005 - I want to restore a full SQL2000
> backup, then the transaction log backups to 2005. Am getting error below w/
> full in standby - How to resolve? Thanks.
> RESTORE DATABASE [MyDB] FROM DISK = N'\\srvimgdb2\bkData\MyDB.bak'
> WITH FILE = 1, NOUNLOAD, STATS = 10, STANDBY = N'E:\dbData\standbyMyDB.txt',
> REPLACE,
> MOVE N'MyDB_Data' TO N'E:\dbData\MyDB_Data.mdf',
> MOVE N'MyDB_Log' TO N'C:\dbLogs\MyDB_log.ldf'
>
> Msg 3180, Level 16, State 1, Line 2
> This backup cannot be restored using WITH STANDBY because a database upgrade
> is needed. Reissue the RESTORE without WITH STANDBY.
> Msg 3013, Level 16, State 1, Line 2
> RESTORE DATABASE is terminating abnormally.
Try WITH NORECOVERY instead of WITH STANDBY
Tracy McKibben
MCDBA
http://www.realsqlguy.com

How to Restore SQL2K Full bkup then tran logs?

I am upgrading from SQL2000 to SQL2005 - I want to restore a full SQL2000
backup, then the transaction log backups to 2005. Am getting error below w/
full in standby - How to resolve? Thanks.
RESTORE DATABASE [MyDB] FROM DISK = N'\\srvimgdb2\bkData\MyDB.bak'
WITH FILE = 1, NOUNLOAD, STATS = 10, STANDBY = N'E:\dbData\standbyMyDB.txt',
REPLACE,
MOVE N'MyDB_Data' TO N'E:\dbData\MyDB_Data.mdf',
MOVE N'MyDB_Log' TO N'C:\dbLogs\MyDB_log.ldf'
Msg 3180, Level 16, State 1, Line 2
This backup cannot be restored using WITH STANDBY because a database upgrade
is needed. Reissue the RESTORE without WITH STANDBY.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.Chris wrote:
> I am upgrading from SQL2000 to SQL2005 - I want to restore a full SQL2000
> backup, then the transaction log backups to 2005. Am getting error below w
/
> full in standby - How to resolve? Thanks.
> RESTORE DATABASE [MyDB] FROM DISK = N'\\srvimgdb2\bkData\MyDB.bak'
> WITH FILE = 1, NOUNLOAD, STATS = 10, STANDBY = N'E:\dbData\standbyMyDB.txt
',
> REPLACE,
> MOVE N'MyDB_Data' TO N'E:\dbData\MyDB_Data.mdf',
> MOVE N'MyDB_Log' TO N'C:\dbLogs\MyDB_log.ldf'
>
> Msg 3180, Level 16, State 1, Line 2
> This backup cannot be restored using WITH STANDBY because a database upgra
de
> is needed. Reissue the RESTORE without WITH STANDBY.
> Msg 3013, Level 16, State 1, Line 2
> RESTORE DATABASE is terminating abnormally.
Try WITH NORECOVERY instead of WITH STANDBY
Tracy McKibben
MCDBA
http://www.realsqlguy.com