Moving a Database

Hi

I want to transfer a database from one PC to another. (Both running SQL Server 2005 express)

I have copied the files (SQL Server Database Primary Data File and SQL Server Database Transaction Log File) from the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data folder of the source PC to the destination PC (Same folder).

I was under the impression that using Microsoft SQL Server 2005 - SQL Server Management Studio Express these files would be picked up and could be used, do I infact need to do something to attach the files/database so that they will appear in SQL Server management?

If you are moving the db;s you need to'detach' them first, then copy the files to the new location, then'attach' them back. check out BOL on how to attach/detach DB's.|||

Thanks

I have just tried doing this, I opened a sql command window on (local)SQLEXPRESS.master - SQLQuery1.sql and than this script:

EXEC

sp_attach_db @.dbname='pramsetc',

@.filename1

='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\blog.mdf',

@.filename2

='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\blog_log.ldf'

This seemed to run okay, but I cant now find my database anywhere in Object Explorer in Management Studio, however the database must of been created somwhere as when I try running the sql again I get:

Msg 1801, Level 16, State 3, Line 1

Database 'blog' already exists.

Any pointers as to where I am going wrong?FONT>

|||

Apologies correction:

Thanks

I have just tried doing this, I opened a sql command window on (local)SQLEXPRESS.master - SQLQuery1.sql and than this script:

EXEC

sp_attach_db @.dbname='blog',

@.filename1

='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\blog.mdf',

@.filename2

='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\blog_log.ldf'

This seemed to run okay, but I cant now find my database anywhere in Object Explorer in Management Studio, however the database must of been created somwhere as when I try running the sql again I get:

Msg 1801, Level 16, State 3, Line 1

Database 'blog' already exists.

Any pointers as to where I am going wrong?

|||Did you detach the DB before attaching it? If you did, refresh your databases list and you could probably see the new db in the list.|||

No I didnt know I needed to do that when I copied them across. I will do that now, just as a matter of interest why does that make a difference, what does the detach do that affects the file being attached locally?

Thanks

|||I am puzzled as how it let you copy the files without getting an "Access denied" or "File in use" error. (2) On the server where you are restoring, refresh the databases on the enterprise maanger. Do you see the 'Blob' db? If you already have one, you need to give a different name while restoring.|||I will try detaching it first, thanks

Move stored procs from 2000 to 2005

It's easy to move the tables, but how do you transfer stored procs and triggers?A few ways you can do this.

If you want to move a whole database, you can either backup the database from SQL 2000 and restore to 2005, or detach\reattach.

If you want to be more selective, you can use SQL Enterprise Manager on 2000 to script out the specific stored procedures\triggers to a script file, then execute the script on SQL 2005 using SQL Management Studio.

Jeff.

Move SQL Users id + pw to new box

I have everything transferred between our older stage server to it's
replacement. Is there a good way to transfer db users?
I have downloaded Sync SQL Logins from
http://www.dbmaint.com/SyncSqlLogins.asp
Is there an alternative someone has used that they like better?
From my first go with this tool, I think that I need to give every pw to
actually move the id?
TIA
"__Stephen" <srussell@.transactiongraphics.com> wrote in message
news:ezBk71TEGHA.1264@.TK2MSFTNGP09.phx.gbl...
>I have everything transferred between our older stage server to it's
>replacement. Is there a good way to transfer db users?
> I have downloaded Sync SQL Logins from
> http://www.dbmaint.com/SyncSqlLogins.asp
> Is there an alternative someone has used that they like better?
> From my first go with this tool, I think that I need to give every pw to
> actually move the id?
After looking around more on the net I realized that I had the RedGate
bundle and it did what I was interested in achieving. I live in the data
compare almost daily, and forgot all about the SQL compare.
__Stephen
|||Stephen
You can also use DTS as this article explains.
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
Regards
John
"__Stephen" wrote:

> "__Stephen" <srussell@.transactiongraphics.com> wrote in message
> news:ezBk71TEGHA.1264@.TK2MSFTNGP09.phx.gbl...
>
> After looking around more on the net I realized that I had the RedGate
> bundle and it did what I was interested in achieving. I live in the data
> compare almost daily, and forgot all about the SQL compare.
> __Stephen
>
>

Move SQL Users id + pw to new box

I have everything transferred between our older stage server to it's
replacement. Is there a good way to transfer db users?
I have downloaded Sync SQL Logins from
http://www.dbmaint.com/SyncSqlLogins.asp
Is there an alternative someone has used that they like better?
From my first go with this tool, I think that I need to give every pw to
actually move the id?
TIA"__Stephen" <srussell@.transactiongraphics.com> wrote in message
news:ezBk71TEGHA.1264@.TK2MSFTNGP09.phx.gbl...
>I have everything transferred between our older stage server to it's
>replacement. Is there a good way to transfer db users?
> I have downloaded Sync SQL Logins from
> http://www.dbmaint.com/SyncSqlLogins.asp
> Is there an alternative someone has used that they like better?
> From my first go with this tool, I think that I need to give every pw to
> actually move the id?
After looking around more on the net I realized that I had the RedGate
bundle and it did what I was interested in achieving. I live in the data
compare almost daily, and forgot all about the SQL compare.
__Stephen|||Stephen
You can also use DTS as this article explains.
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
Regards
John
"__Stephen" wrote:

> "__Stephen" <srussell@.transactiongraphics.com> wrote in message
> news:ezBk71TEGHA.1264@.TK2MSFTNGP09.phx.gbl...
>
> After looking around more on the net I realized that I had the RedGate
> bundle and it did what I was interested in achieving. I live in the data
> compare almost daily, and forgot all about the SQL compare.
> __Stephen
>
>

Move SQL Users id + pw to new box

I have everything transferred between our older stage server to it's
replacement. Is there a good way to transfer db users?
I have downloaded Sync SQL Logins from
http://www.dbmaint.com/SyncSqlLogins.asp
Is there an alternative someone has used that they like better?
From my first go with this tool, I think that I need to give every pw to
actually move the id?
TIA"__Stephen" <srussell@.transactiongraphics.com> wrote in message
news:ezBk71TEGHA.1264@.TK2MSFTNGP09.phx.gbl...
>I have everything transferred between our older stage server to it's
>replacement. Is there a good way to transfer db users?
> I have downloaded Sync SQL Logins from
> http://www.dbmaint.com/SyncSqlLogins.asp
> Is there an alternative someone has used that they like better?
> From my first go with this tool, I think that I need to give every pw to
> actually move the id?
After looking around more on the net I realized that I had the RedGate
bundle and it did what I was interested in achieving. I live in the data
compare almost daily, and forgot all about the SQL compare.
__Stephen|||Stephen
You can also use DTS as this article explains.
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
Regards
John
"__Stephen" wrote:
> "__Stephen" <srussell@.transactiongraphics.com> wrote in message
> news:ezBk71TEGHA.1264@.TK2MSFTNGP09.phx.gbl...
> >I have everything transferred between our older stage server to it's
> >replacement. Is there a good way to transfer db users?
> >
> > I have downloaded Sync SQL Logins from
> > http://www.dbmaint.com/SyncSqlLogins.asp
> >
> > Is there an alternative someone has used that they like better?
> >
> > From my first go with this tool, I think that I need to give every pw to
> > actually move the id?
>
> After looking around more on the net I realized that I had the RedGate
> bundle and it did what I was interested in achieving. I live in the data
> compare almost daily, and forgot all about the SQL compare.
> __Stephen
>
>

move sql database to different sql server language version

HI guys!

I have a Japanese sql server installed in one server..
I want to transfer everything to an English SQL Server version.
Will this be no problem?
Hope you can enlighten me on this one...

Thanks!

As far as the Sort order is matched you would be able to restore the database, and also the UNICODE data can be used to present the non-english data.|||

Thank you very much for your reply.

I'd just like to clarify below.

Satya SKJ wrote:

...and also the UNICODE data can be used to present the non-english data.

What did you mean by this?

Move DB users

Hi,
I have an already existing DB A, and I created new one B under the same SQL
Server registration. I want to transfer DB "Users" from a to B. How can I
do that? To make myself clear, I am not talking about "Logins"I assume that you are talking about moving logins from one server to
another. I have used this method many times and it works well.
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://www.support.microsoft.com/?id=246133
--
Keith
"SQLServerNewbee" <SQLServerNewbee@.discussions.microsoft.com> wrote in
message news:A4F45400-3FF6-4717-A9DE-D9E0266E20B2@.microsoft.com...
> Hi,
> I have an already existing DB A, and I created new one B under the same
SQL
> Server registration. I want to transfer DB "Users" from a to B. How can
I
> do that? To make myself clear, I am not talking about "Logins"|||No, Keith...I am trying to move Users from dB 'A' to dB 'B'. I know that we
can transfer logins...but how about Users
"Keith Kratochvil" wrote:
> I assume that you are talking about moving logins from one server to
> another. I have used this method many times and it works well.
> HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
> http://www.support.microsoft.com/?id=246133
> --
> Keith
>
> "SQLServerNewbee" <SQLServerNewbee@.discussions.microsoft.com> wrote in
> message news:A4F45400-3FF6-4717-A9DE-D9E0266E20B2@.microsoft.com...
> > Hi,
> > I have an already existing DB A, and I created new one B under the same
> SQL
> > Server registration. I want to transfer DB "Users" from a to B. How can
> I
> > do that? To make myself clear, I am not talking about "Logins"
>|||This should serve as a start:
SELECT 'EXEC sp_grantdbaccess "' + name + '"'
FROM sysusers
WHERE isntname = 1
SELECT 'EXEC sp_adduser "' + name + '"'
FROM sysusers
WHERE issqluser = 1
Run above in source db and execute result in destination db. Above assumes same user name as login
name. If that isn't the case, join sysusers to master..syslogins and pick the login name and use as
second parameter for the sp_ call. Also, this was a quickie, run tests etc and make sure that is
work as expected.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"SQLServerNewbee" <SQLServerNewbee@.discussions.microsoft.com> wrote in message
news:51D4315F-93CE-429C-A13B-5B450E33BEA6@.microsoft.com...
> No, Keith...I am trying to move Users from dB 'A' to dB 'B'. I know that we
> can transfer logins...but how about Users
> "Keith Kratochvil" wrote:
>> I assume that you are talking about moving logins from one server to
>> another. I have used this method many times and it works well.
>> HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
>> http://www.support.microsoft.com/?id=246133
>> --
>> Keith
>>
>> "SQLServerNewbee" <SQLServerNewbee@.discussions.microsoft.com> wrote in
>> message news:A4F45400-3FF6-4717-A9DE-D9E0266E20B2@.microsoft.com...
>> > Hi,
>> > I have an already existing DB A, and I created new one B under the same
>> SQL
>> > Server registration. I want to transfer DB "Users" from a to B. How can
>> I
>> > do that? To make myself clear, I am not talking about "Logins"
>>|||You aren't moving users -- they already exist. You have to grant database
access. Tibor's script should work for you.
--
Keith
"SQLServerNewbee" <SQLServerNewbee@.discussions.microsoft.com> wrote in
message news:51D4315F-93CE-429C-A13B-5B450E33BEA6@.microsoft.com...
> No, Keith...I am trying to move Users from dB 'A' to dB 'B'. I know that
we
> can transfer logins...but how about Users
> "Keith Kratochvil" wrote:
> > I assume that you are talking about moving logins from one server to
> > another. I have used this method many times and it works well.
> >
> > HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
> > http://www.support.microsoft.com/?id=246133
> >
> > --
> > Keith
> >
> >
> > "SQLServerNewbee" <SQLServerNewbee@.discussions.microsoft.com> wrote in
> > message news:A4F45400-3FF6-4717-A9DE-D9E0266E20B2@.microsoft.com...
> > > Hi,
> > > I have an already existing DB A, and I created new one B under the
same
> > SQL
> > > Server registration. I want to transfer DB "Users" from a to B. How
can
> > I
> > > do that? To make myself clear, I am not talking about "Logins"
> >
> >|||Hi Keith, Tibor,
I do not think permissions will be proper after this.. we need to reapply
permissions script... pls confirm
regards,
bharath
"Keith Kratochvil" wrote:
> You aren't moving users -- they already exist. You have to grant database
> access. Tibor's script should work for you.
> --
> Keith
>
> "SQLServerNewbee" <SQLServerNewbee@.discussions.microsoft.com> wrote in
> message news:51D4315F-93CE-429C-A13B-5B450E33BEA6@.microsoft.com...
> > No, Keith...I am trying to move Users from dB 'A' to dB 'B'. I know that
> we
> > can transfer logins...but how about Users
> >
> > "Keith Kratochvil" wrote:
> >
> > > I assume that you are talking about moving logins from one server to
> > > another. I have used this method many times and it works well.
> > >
> > > HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
> > > http://www.support.microsoft.com/?id=246133
> > >
> > > --
> > > Keith
> > >
> > >
> > > "SQLServerNewbee" <SQLServerNewbee@.discussions.microsoft.com> wrote in
> > > message news:A4F45400-3FF6-4717-A9DE-D9E0266E20B2@.microsoft.com...
> > > > Hi,
> > > > I have an already existing DB A, and I created new one B under the
> same
> > > SQL
> > > > Server registration. I want to transfer DB "Users" from a to B. How
> can
> > > I
> > > > do that? To make myself clear, I am not talking about "Logins"
> > >
> > >
>|||Correct. You need to bring over permissions as well. Perhaps it is better to let EM generate scripts
for this and extract the sp_adduser and GRANT commands from the generated scripts...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bharath" <Bharath@.discussions.microsoft.com> wrote in message
news:669D8883-F7FB-44CE-979B-9487FEEDCFCF@.microsoft.com...
> Hi Keith, Tibor,
> I do not think permissions will be proper after this.. we need to reapply
> permissions script... pls confirm
> regards,
> bharath
> "Keith Kratochvil" wrote:
>> You aren't moving users -- they already exist. You have to grant database
>> access. Tibor's script should work for you.
>> --
>> Keith
>>
>> "SQLServerNewbee" <SQLServerNewbee@.discussions.microsoft.com> wrote in
>> message news:51D4315F-93CE-429C-A13B-5B450E33BEA6@.microsoft.com...
>> > No, Keith...I am trying to move Users from dB 'A' to dB 'B'. I know that
>> we
>> > can transfer logins...but how about Users
>> >
>> > "Keith Kratochvil" wrote:
>> >
>> > > I assume that you are talking about moving logins from one server to
>> > > another. I have used this method many times and it works well.
>> > >
>> > > HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
>> > > http://www.support.microsoft.com/?id=246133
>> > >
>> > > --
>> > > Keith
>> > >
>> > >
>> > > "SQLServerNewbee" <SQLServerNewbee@.discussions.microsoft.com> wrote in
>> > > message news:A4F45400-3FF6-4717-A9DE-D9E0266E20B2@.microsoft.com...
>> > > > Hi,
>> > > > I have an already existing DB A, and I created new one B under the
>> same
>> > > SQL
>> > > > Server registration. I want to transfer DB "Users" from a to B. How
>> can
>> > > I
>> > > > do that? To make myself clear, I am not talking about "Logins"
>> > >
>> > >
>>

Move DB users

Hi,
I have an already existing DB A, and I created new one B under the same SQL
Server registration. I want to transfer DB "Users" from a to B. How can I
do that? To make myself clear, I am not talking about "Logins"
I assume that you are talking about moving logins from one server to
another. I have used this method many times and it works well.
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://www.support.microsoft.com/?id=246133
Keith
"SQLServerNewbee" <SQLServerNewbee@.discussions.microsoft.com> wrote in
message news:A4F45400-3FF6-4717-A9DE-D9E0266E20B2@.microsoft.com...
> Hi,
> I have an already existing DB A, and I created new one B under the same
SQL
> Server registration. I want to transfer DB "Users" from a to B. How can
I
> do that? To make myself clear, I am not talking about "Logins"
|||No, Keith...I am trying to move Users from dB 'A' to dB 'B'. I know that we
can transfer logins...but how about Users
"Keith Kratochvil" wrote:

> I assume that you are talking about moving logins from one server to
> another. I have used this method many times and it works well.
> HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
> http://www.support.microsoft.com/?id=246133
> --
> Keith
>
> "SQLServerNewbee" <SQLServerNewbee@.discussions.microsoft.com> wrote in
> message news:A4F45400-3FF6-4717-A9DE-D9E0266E20B2@.microsoft.com...
> SQL
> I
>
|||This should serve as a start:
SELECT 'EXEC sp_grantdbaccess "' + name + '"'
FROM sysusers
WHERE isntname = 1
SELECT 'EXEC sp_adduser "' + name + '"'
FROM sysusers
WHERE issqluser = 1
Run above in source db and execute result in destination db. Above assumes same user name as login
name. If that isn't the case, join sysusers to master..syslogins and pick the login name and use as
second parameter for the sp_ call. Also, this was a quickie, run tests etc and make sure that is
work as expected.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"SQLServerNewbee" <SQLServerNewbee@.discussions.microsoft.com> wrote in message
news:51D4315F-93CE-429C-A13B-5B450E33BEA6@.microsoft.com...[vbcol=seagreen]
> No, Keith...I am trying to move Users from dB 'A' to dB 'B'. I know that we
> can transfer logins...but how about Users
> "Keith Kratochvil" wrote:
|||You aren't moving users -- they already exist. You have to grant database
access. Tibor's script should work for you.
Keith
"SQLServerNewbee" <SQLServerNewbee@.discussions.microsoft.com> wrote in
message news:51D4315F-93CE-429C-A13B-5B450E33BEA6@.microsoft.com...
> No, Keith...I am trying to move Users from dB 'A' to dB 'B'. I know that
we[vbcol=seagreen]
> can transfer logins...but how about Users
> "Keith Kratochvil" wrote:
same[vbcol=seagreen]
can[vbcol=seagreen]
|||Hi Keith, Tibor,
I do not think permissions will be proper after this.. we need to reapply
permissions script... pls confirm
regards,
bharath
"Keith Kratochvil" wrote:

> You aren't moving users -- they already exist. You have to grant database
> access. Tibor's script should work for you.
> --
> Keith
>
> "SQLServerNewbee" <SQLServerNewbee@.discussions.microsoft.com> wrote in
> message news:51D4315F-93CE-429C-A13B-5B450E33BEA6@.microsoft.com...
> we
> same
> can
>
|||Correct. You need to bring over permissions as well. Perhaps it is better to let EM generate scripts
for this and extract the sp_adduser and GRANT commands from the generated scripts...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bharath" <Bharath@.discussions.microsoft.com> wrote in message
news:669D8883-F7FB-44CE-979B-9487FEEDCFCF@.microsoft.com...[vbcol=seagreen]
> Hi Keith, Tibor,
> I do not think permissions will be proper after this.. we need to reapply
> permissions script... pls confirm
> regards,
> bharath
> "Keith Kratochvil" wrote:

Move DB users

Hi,
I have an already existing DB A, and I created new one B under the same SQL
Server registration. I want to transfer DB "Users" from a to B. How can I
do that? To make myself clear, I am not talking about "Logins"I assume that you are talking about moving logins from one server to
another. I have used this method many times and it works well.
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://www.support.microsoft.com/?id=246133
Keith
"SQLServerNewbee" <SQLServerNewbee@.discussions.microsoft.com> wrote in
message news:A4F45400-3FF6-4717-A9DE-D9E0266E20B2@.microsoft.com...
> Hi,
> I have an already existing DB A, and I created new one B under the same
SQL
> Server registration. I want to transfer DB "Users" from a to B. How can
I
> do that? To make myself clear, I am not talking about "Logins"|||No, Keith...I am trying to move Users from dB 'A' to dB 'B'. I know that we
can transfer logins...but how about Users
"Keith Kratochvil" wrote:

> I assume that you are talking about moving logins from one server to
> another. I have used this method many times and it works well.
> HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
> http://www.support.microsoft.com/?id=246133
> --
> Keith
>
> "SQLServerNewbee" <SQLServerNewbee@.discussions.microsoft.com> wrote in
> message news:A4F45400-3FF6-4717-A9DE-D9E0266E20B2@.microsoft.com...
> SQL
> I
>|||This should serve as a start:
SELECT 'EXEC sp_grantdbaccess "' + name + '"'
FROM sysusers
WHERE isntname = 1
SELECT 'EXEC sp_adduser "' + name + '"'
FROM sysusers
WHERE issqluser = 1
Run above in source db and execute result in destination db. Above assumes s
ame user name as login
name. If that isn't the case, join sysusers to master..syslogins and pick th
e login name and use as
second parameter for the sp_ call. Also, this was a quickie, run tests etc a
nd make sure that is
work as expected.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"SQLServerNewbee" <SQLServerNewbee@.discussions.microsoft.com> wrote in messa
ge
news:51D4315F-93CE-429C-A13B-5B450E33BEA6@.microsoft.com...[vbcol=seagreen]
> No, Keith...I am trying to move Users from dB 'A' to dB 'B'. I know that
we
> can transfer logins...but how about Users
> "Keith Kratochvil" wrote:
>|||You aren't moving users -- they already exist. You have to grant database
access. Tibor's script should work for you.
Keith
"SQLServerNewbee" <SQLServerNewbee@.discussions.microsoft.com> wrote in
message news:51D4315F-93CE-429C-A13B-5B450E33BEA6@.microsoft.com...
> No, Keith...I am trying to move Users from dB 'A' to dB 'B'. I know that
we[vbcol=seagreen]
> can transfer logins...but how about Users
> "Keith Kratochvil" wrote:
>
same[vbcol=seagreen]
can[vbcol=seagreen]|||Hi Keith, Tibor,
I do not think permissions will be proper after this.. we need to reapply
permissions script... pls confirm
regards,
bharath
"Keith Kratochvil" wrote:

> You aren't moving users -- they already exist. You have to grant database
> access. Tibor's script should work for you.
> --
> Keith
>
> "SQLServerNewbee" <SQLServerNewbee@.discussions.microsoft.com> wrote in
> message news:51D4315F-93CE-429C-A13B-5B450E33BEA6@.microsoft.com...
> we
> same
> can
>|||Correct. You need to bring over permissions as well. Perhaps it is better to
let EM generate scripts
for this and extract the sp_adduser and GRANT commands from the generated sc
ripts...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bharath" <Bharath@.discussions.microsoft.com> wrote in message
news:669D8883-F7FB-44CE-979B-9487FEEDCFCF@.microsoft.com...[vbcol=seagreen]
> Hi Keith, Tibor,
> I do not think permissions will be proper after this.. we need to reapply
> permissions script... pls confirm
> regards,
> bharath
> "Keith Kratochvil" wrote:
>

Move DB to new server

We just bought a new server for our 23 DB. We are running SQL 2000.
What is the best way to transfer our DB form our old server to our new one.
DTS?
Thanks for any documentation that will help me acheive my goal.
Dan
Hi,
2 approaches:-
I.
1. SP_DETACH_DB DBNAME
2. Move the .mdf and .ldf files to new server
3. SP_ATTACH_DB dbanme,@.filename1='',@.filename2=''
or else you can move a database using below steps,
II.
1. Do a Backup of database
2. Copy the .BAK file to new server
3. Restore the database
see below link for details:-
http://support.microsoft.com/default...b;en-us;314546
Another undocumented method:-
If you can maintain the same directory structure in the new server, Stop the
MSSQL server service in both servers and copy all the .MDF and .LDF files
to the identical folder in new server and then start the MSSQL service.
Once the service come up , you can use the below system stored procedure to
change the server name.
sp_dropserver 'oldservername'
go
sp_addserver 'newservername','local'
After this restart the SQL server service.
Thanks
Hari
MCDBA
"Dan M" <Dan.Morrow@.nodoby.com> wrote in message
news:ecAlcZOZEHA.3476@.tk2msftngp13.phx.gbl...
> We just bought a new server for our 23 DB. We are running SQL 2000.
> What is the best way to transfer our DB form our old server to our new
one.
> DTS?
> Thanks for any documentation that will help me acheive my goal.
> Dan
>
|||If you want to transfer users, jobs, as well:
Use the simple Copy Database Wizard to copy all databases, logins. Ths is
less admin hassle and imo this is a great tool. You may need to reset the
users default databases once completed.
The below method does not migrate the users from the master database or the
Jobs from the MSDB etc..
Regards
James
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uFKSHgOZEHA.3988@.tk2msftngp13.phx.gbl...
> Hi,
> 2 approaches:-
> I.
> 1. SP_DETACH_DB DBNAME
> 2. Move the .mdf and .ldf files to new server
> 3. SP_ATTACH_DB dbanme,@.filename1='',@.filename2=''
>
> or else you can move a database using below steps,
> II.
> 1. Do a Backup of database
> 2. Copy the .BAK file to new server
> 3. Restore the database
> see below link for details:-
> http://support.microsoft.com/default...b;en-us;314546
> Another undocumented method:-
> --
> If you can maintain the same directory structure in the new server, Stop
the
> MSSQL server service in both servers and copy all the .MDF and .LDF files
> to the identical folder in new server and then start the MSSQL service.
> Once the service come up , you can use the below system stored procedure
to
> change the server name.
> sp_dropserver 'oldservername'
> go
> sp_addserver 'newservername','local'
> After this restart the SQL server service.
>
> --
> Thanks
> Hari
> MCDBA
> "Dan M" <Dan.Morrow@.nodoby.com> wrote in message
> news:ecAlcZOZEHA.3476@.tk2msftngp13.phx.gbl...
> one.
>

Move DB to new server

We just bought a new server for our 23 DB. We are running SQL 2000.
What is the best way to transfer our DB form our old server to our new one.
DTS?
Thanks for any documentation that will help me acheive my goal.
DanHi,
2 approaches:-
I.
1. SP_DETACH_DB DBNAME
2. Move the .mdf and .ldf files to new server
3. SP_ATTACH_DB dbanme,@.filename1='',@.filename2=''
or else you can move a database using below steps,
II.
1. Do a Backup of database
2. Copy the .BAK file to new server
3. Restore the database
see below link for details:-
http://support.microsoft.com/defaul...kb;en-us;314546
Another undocumented method:-
--
If you can maintain the same directory structure in the new server, Stop the
MSSQL server service in both servers and copy all the .MDF and .LDF files
to the identical folder in new server and then start the MSSQL service.
Once the service come up , you can use the below system stored procedure to
change the server name.
sp_dropserver 'oldservername'
go
sp_addserver 'newservername','local'
After this restart the SQL server service.
Thanks
Hari
MCDBA
"Dan M" <Dan.Morrow@.nodoby.com> wrote in message
news:ecAlcZOZEHA.3476@.tk2msftngp13.phx.gbl...
> We just bought a new server for our 23 DB. We are running SQL 2000.
> What is the best way to transfer our DB form our old server to our new
one.
> DTS?
> Thanks for any documentation that will help me acheive my goal.
> Dan
>|||If you want to transfer users, jobs, as well:
Use the simple Copy Database Wizard to copy all databases, logins. Ths is
less admin hassle and imo this is a great tool. You may need to reset the
users default databases once completed.
The below method does not migrate the users from the master database or the
Jobs from the MSDB etc..
Regards
James
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uFKSHgOZEHA.3988@.tk2msftngp13.phx.gbl...
> Hi,
> 2 approaches:-
> I.
> 1. SP_DETACH_DB DBNAME
> 2. Move the .mdf and .ldf files to new server
> 3. SP_ATTACH_DB dbanme,@.filename1='',@.filename2=''
>
> or else you can move a database using below steps,
> II.
> 1. Do a Backup of database
> 2. Copy the .BAK file to new server
> 3. Restore the database
> see below link for details:-
> http://support.microsoft.com/defaul...kb;en-us;314546
> Another undocumented method:-
> --
> If you can maintain the same directory structure in the new server, Stop
the
> MSSQL server service in both servers and copy all the .MDF and .LDF files
> to the identical folder in new server and then start the MSSQL service.
> Once the service come up , you can use the below system stored procedure
to
> change the server name.
> sp_dropserver 'oldservername'
> go
> sp_addserver 'newservername','local'
> After this restart the SQL server service.
>
> --
> Thanks
> Hari
> MCDBA
> "Dan M" <Dan.Morrow@.nodoby.com> wrote in message
> news:ecAlcZOZEHA.3476@.tk2msftngp13.phx.gbl...
> one.
>

Move DB to new server

We just bought a new server for our 23 DB. We are running SQL 2000.
What is the best way to transfer our DB form our old server to our new one.
DTS?
Thanks for any documentation that will help me acheive my goal.
DanHi,
2 approaches:-
I.
1. SP_DETACH_DB DBNAME
2. Move the .mdf and .ldf files to new server
3. SP_ATTACH_DB dbanme,@.filename1='',@.filename2=''
or else you can move a database using below steps,
II.
1. Do a Backup of database
2. Copy the .BAK file to new server
3. Restore the database
see below link for details:-
http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
Another undocumented method:-
--
If you can maintain the same directory structure in the new server, Stop the
MSSQL server service in both servers and copy all the .MDF and .LDF files
to the identical folder in new server and then start the MSSQL service.
Once the service come up , you can use the below system stored procedure to
change the server name.
sp_dropserver 'oldservername'
go
sp_addserver 'newservername','local'
After this restart the SQL server service.
Thanks
Hari
MCDBA
"Dan M" <Dan.Morrow@.nodoby.com> wrote in message
news:ecAlcZOZEHA.3476@.tk2msftngp13.phx.gbl...
> We just bought a new server for our 23 DB. We are running SQL 2000.
> What is the best way to transfer our DB form our old server to our new
one.
> DTS?
> Thanks for any documentation that will help me acheive my goal.
> Dan
>|||If you want to transfer users, jobs, as well:
Use the simple Copy Database Wizard to copy all databases, logins. Ths is
less admin hassle and imo this is a great tool. You may need to reset the
users default databases once completed.
The below method does not migrate the users from the master database or the
Jobs from the MSDB etc..
Regards
James
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uFKSHgOZEHA.3988@.tk2msftngp13.phx.gbl...
> Hi,
> 2 approaches:-
> I.
> 1. SP_DETACH_DB DBNAME
> 2. Move the .mdf and .ldf files to new server
> 3. SP_ATTACH_DB dbanme,@.filename1='',@.filename2=''
>
> or else you can move a database using below steps,
> II.
> 1. Do a Backup of database
> 2. Copy the .BAK file to new server
> 3. Restore the database
> see below link for details:-
> http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
> Another undocumented method:-
> --
> If you can maintain the same directory structure in the new server, Stop
the
> MSSQL server service in both servers and copy all the .MDF and .LDF files
> to the identical folder in new server and then start the MSSQL service.
> Once the service come up , you can use the below system stored procedure
to
> change the server name.
> sp_dropserver 'oldservername'
> go
> sp_addserver 'newservername','local'
> After this restart the SQL server service.
>
> --
> Thanks
> Hari
> MCDBA
> "Dan M" <Dan.Morrow@.nodoby.com> wrote in message
> news:ecAlcZOZEHA.3476@.tk2msftngp13.phx.gbl...
> > We just bought a new server for our 23 DB. We are running SQL 2000.
> >
> > What is the best way to transfer our DB form our old server to our new
> one.
> > DTS?
> >
> > Thanks for any documentation that will help me acheive my goal.
> >
> > Dan
> >
> >
>

move db from sql server 2000 to sql server 2005

how can we manage the db transfer when we are upgrading from 2000 to 2005? what is the right way to do that? where can we find something to read about it?

Hi,

you can simply backup and restore database in SQL 2005 but do remember to update you SQL 2005 Server for Logins. Refer below KB for mroe.

http://www.support.microsoft.com/kb/314546

Hemantgiri S. Goswami