Moving backup devices

Is there a way to copy backup devices between 2 servers with a script or
something else ? I actually need to move my current installation on a new
server and I don't want to recreate 200 backup devices manually.
Thanks in advance
script out the sysdevices table... eg
USE master
GO
SELECT *
FROM dbo.sysdevices

Moving backup devices

Is there a way to copy backup devices between 2 servers with a script or
something else ? I actually need to move my current installation on a new
server and I don't want to recreate 200 backup devices manually.
Thanks in advancescript out the sysdevices table... eg
USE master
GO
SELECT *
FROM dbo.sysdevices

Moving backup devices

Is there a way to copy backup devices between 2 servers with a script or
something else ? I actually need to move my current installation on a new
server and I don't want to recreate 200 backup devices manually.
Thanks in advancescript out the sysdevices table... eg
USE master
GO
SELECT *
FROM dbo.sysdevices

Moving a SQL7 DB to SQL2000

I have a backup of a SQL7 DB that i need to restore into SQL 2000. I only have the backup of the DB , i don't have access to the SQL7 server. When ever I try to restore the DB into SQL2000 nothing works, the tables and properties all restore properly and the data is there but i can't retrieve info from it. I'm trying to move a website DB from SQL7 to SQL 2000 for use with Cold Fusion. I know they're is issues with going from 7 to 2000 but i don't know what they are. If anyone could help me with this i would greatly appreciate it. I know I need to do some special steps or changes before it will work in 2000, does anyone know what these are?

Thanks in advance,
B"...I can't retrieve info from it..."

can you provide a few more details? What are you using to retrieve info (QA, development IDE, EM)? What error messages are you getting?

Recall that if you restore a database onto a new server, the logins and permissions don't convey (kind of like the furniture in my house when I go to sell it).

Permissions may be a part of your issue.

Regards,

hmscott|||I've got it working, I had to do a DTS Import and I checked Use Collation. This seemed to fix the problem, thanks to those who helped!!!

moving a database to a different drive

what is the reccomended method for moving a database to a different drive on
the same computer? Just a backup and restore?
Have a look at
INF: Moving SQL Server Databases to a New Location with Detach/Attach
http://support.microsoft.com/default...;EN-US;q224071
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"djc" <noone@.nowhere.com> wrote in message
news:elhbGfwUEHA.484@.TK2MSFTNGP10.phx.gbl...
> what is the reccomended method for moving a database to a different drive
on
> the same computer? Just a backup and restore?
>
|||Thanks. I will.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:eC3pXbxUEHA.2844@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Have a look at
> INF: Moving SQL Server Databases to a New Location with Detach/Attach
> http://support.microsoft.com/default...;EN-US;q224071
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "djc" <noone@.nowhere.com> wrote in message
> news:elhbGfwUEHA.484@.TK2MSFTNGP10.phx.gbl...
drive
> on
>

moving a database to a different drive

Hi,
You could do a backup and restore using the 'WITH MOVE'
OPTION or you could (better use) detach the database, copy
the datafile to new location and attach the database files.
You can look at sp_detach_db and sp_attach_db procedures
in BOL.
hth
DeeJay
>--Original Message--
>what is the reccomended method for moving a database to a
different drive on
>the same computer? Just a backup and restore?
>
>.
>
ok. I'll check out those procedures in Books Online. Thanks for the input.
"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
news:1cc9b01c45309$967fde70$a101280a@.phx.gbl...[vbcol=seagreen]
> Hi,
> You could do a backup and restore using the 'WITH MOVE'
> OPTION or you could (better use) detach the database, copy
> the datafile to new location and attach the database files.
> You can look at sp_detach_db and sp_attach_db procedures
> in BOL.
> hth
> DeeJay
> different drive on
|||Hi,
I will use the detatch/attatch method to move but your reply brings another
question to me. I did not see an option to restore 'WITH MOVE' in enterprise
manager GUI restore... is it only available when using TSQL? The reason I
ask is I had previously done a backup and restore to new database name and
new location in order to quickly create a 'staging' or 'testing' version of
a production database and want to be sure that was ok... meaning I wanted to
verify that I had NOT chosen the 'WITH MOVE' option... I didn't know if it
was a default option. Because, in that case, I wanted the database to be
different... not a move of the real database..
If I actually got a clear question out of that (doubtful ;) ) any info would
be appreciated... I think I'm just being overly paranoid.
"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
news:1cc9b01c45309$967fde70$a101280a@.phx.gbl...[vbcol=seagreen]
> Hi,
> You could do a backup and restore using the 'WITH MOVE'
> OPTION or you could (better use) detach the database, copy
> the datafile to new location and attach the database files.
> You can look at sp_detach_db and sp_attach_db procedures
> in BOL.
> hth
> DeeJay
> different drive on
|||The move option is exposed on the right-most tab, where EM specifies the physical file names. In the right
part, just type the desired file name.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"djc" <noone@.nowhere.com> wrote in message news:%23Cj8oj5UEHA.2668@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I will use the detatch/attatch method to move but your reply brings another
> question to me. I did not see an option to restore 'WITH MOVE' in enterprise
> manager GUI restore... is it only available when using TSQL? The reason I
> ask is I had previously done a backup and restore to new database name and
> new location in order to quickly create a 'staging' or 'testing' version of
> a production database and want to be sure that was ok... meaning I wanted to
> verify that I had NOT chosen the 'WITH MOVE' option... I didn't know if it
> was a default option. Because, in that case, I wanted the database to be
> different... not a move of the real database..
> If I actually got a clear question out of that (doubtful ;) ) any info would
> be appreciated... I think I'm just being overly paranoid.
>
> "DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
> news:1cc9b01c45309$967fde70$a101280a@.phx.gbl...
>

moving a database to a different drive

Hi,
You could do a backup and restore using the 'WITH MOVE'
OPTION or you could (better use) detach the database, copy
the datafile to new location and attach the database files.
You can look at sp_detach_db and sp_attach_db procedures
in BOL.
hth
DeeJay
>--Original Message--
>what is the reccomended method for moving a database to a
different drive on
>the same computer? Just a backup and restore?
>
>.
>ok. I'll check out those procedures in Books Online. Thanks for the input.
"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
news:1cc9b01c45309$967fde70$a101280a@.phx
.gbl...[vbcol=seagreen]
> Hi,
> You could do a backup and restore using the 'WITH MOVE'
> OPTION or you could (better use) detach the database, copy
> the datafile to new location and attach the database files.
> You can look at sp_detach_db and sp_attach_db procedures
> in BOL.
> hth
> DeeJay
> different drive on|||Hi,
I will use the detatch/attatch method to move but your reply brings another
question to me. I did not see an option to restore 'WITH MOVE' in enterprise
manager GUI restore... is it only available when using TSQL? The reason I
ask is I had previously done a backup and restore to new database name and
new location in order to quickly create a 'staging' or 'testing' version of
a production database and want to be sure that was ok... meaning I wanted to
verify that I had NOT chosen the 'WITH MOVE' option... I didn't know if it
was a default option. Because, in that case, I wanted the database to be
different... not a move of the real database..
If I actually got a clear question out of that (doubtful ;) ) any info would
be appreciated... I think I'm just being overly paranoid.
"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
news:1cc9b01c45309$967fde70$a101280a@.phx
.gbl...[vbcol=seagreen]
> Hi,
> You could do a backup and restore using the 'WITH MOVE'
> OPTION or you could (better use) detach the database, copy
> the datafile to new location and attach the database files.
> You can look at sp_detach_db and sp_attach_db procedures
> in BOL.
> hth
> DeeJay
> different drive on|||The move option is exposed on the right-most tab, where EM specifies the phy
sical file names. In the right
part, just type the desired file name.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"djc" <noone@.nowhere.com> wrote in message news:%23Cj8oj5UEHA.2668@.TK2MSFTNGP10.phx.gbl...[v
bcol=seagreen]
> Hi,
> I will use the detatch/attatch method to move but your reply brings anothe
r
> question to me. I did not see an option to restore 'WITH MOVE' in enterpri
se
> manager GUI restore... is it only available when using TSQL? The reason I
> ask is I had previously done a backup and restore to new database name and
> new location in order to quickly create a 'staging' or 'testing' version
of
> a production database and want to be sure that was ok... meaning I wanted
to
> verify that I had NOT chosen the 'WITH MOVE' option... I didn't know if it
> was a default option. Because, in that case, I wanted the database to be
> different... not a move of the real database..
> If I actually got a clear question out of that (doubtful ;) ) any info wou
ld
> be appreciated... I think I'm just being overly paranoid.
>
> "DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
> news:1cc9b01c45309$967fde70$a101280a@.phx
.gbl...
>[/vbcol]sql

moving a database to a different drive

what is the reccomended method for moving a database to a different drive on
the same computer? Just a backup and restore?Have a look at
INF: Moving SQL Server Databases to a New Location with Detach/Attach
http://support.microsoft.com/defaul...b;EN-US;q224071
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"djc" <noone@.nowhere.com> wrote in message
news:elhbGfwUEHA.484@.TK2MSFTNGP10.phx.gbl...
> what is the reccomended method for moving a database to a different drive
on
> the same computer? Just a backup and restore?
>|||Thanks. I will.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:eC3pXbxUEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Have a look at
> INF: Moving SQL Server Databases to a New Location with Detach/Attach
> http://support.microsoft.com/defaul...b;EN-US;q224071
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "djc" <noone@.nowhere.com> wrote in message
> news:elhbGfwUEHA.484@.TK2MSFTNGP10.phx.gbl...
drive[vbcol=seagreen]
> on
>

moving a database to a different drive

what is the reccomended method for moving a database to a different drive on
the same computer? Just a backup and restore?Hi,
You could do a backup and restore using the 'WITH MOVE'
OPTION or you could (better use) detach the database, copy
the datafile to new location and attach the database files.
You can look at sp_detach_db and sp_attach_db procedures
in BOL.
hth
DeeJay
>--Original Message--
>what is the reccomended method for moving a database to a
different drive on
>the same computer? Just a backup and restore?
>
>.
>|||ok. I'll check out those procedures in Books Online. Thanks for the input.
"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
news:1cc9b01c45309$967fde70$a101280a@.phx.gbl...
> Hi,
> You could do a backup and restore using the 'WITH MOVE'
> OPTION or you could (better use) detach the database, copy
> the datafile to new location and attach the database files.
> You can look at sp_detach_db and sp_attach_db procedures
> in BOL.
> hth
> DeeJay
> >--Original Message--
> >what is the reccomended method for moving a database to a
> different drive on
> >the same computer? Just a backup and restore?
> >
> >
> >.
> >|||Have a look at
INF: Moving SQL Server Databases to a New Location with Detach/Attach
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q224071
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"djc" <noone@.nowhere.com> wrote in message
news:elhbGfwUEHA.484@.TK2MSFTNGP10.phx.gbl...
> what is the reccomended method for moving a database to a different drive
on
> the same computer? Just a backup and restore?
>|||Thanks. I will.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:eC3pXbxUEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Have a look at
> INF: Moving SQL Server Databases to a New Location with Detach/Attach
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;q224071
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "djc" <noone@.nowhere.com> wrote in message
> news:elhbGfwUEHA.484@.TK2MSFTNGP10.phx.gbl...
> > what is the reccomended method for moving a database to a different
drive
> on
> > the same computer? Just a backup and restore?
> >
> >
>|||Hi,
I will use the detatch/attatch method to move but your reply brings another
question to me. I did not see an option to restore 'WITH MOVE' in enterprise
manager GUI restore... is it only available when using TSQL? The reason I
ask is I had previously done a backup and restore to new database name and
new location in order to quickly create a 'staging' or 'testing' version of
a production database and want to be sure that was ok... meaning I wanted to
verify that I had NOT chosen the 'WITH MOVE' option... I didn't know if it
was a default option. Because, in that case, I wanted the database to be
different... not a move of the real database..
If I actually got a clear question out of that (doubtful ;) ) any info would
be appreciated... I think I'm just being overly paranoid.
"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
news:1cc9b01c45309$967fde70$a101280a@.phx.gbl...
> Hi,
> You could do a backup and restore using the 'WITH MOVE'
> OPTION or you could (better use) detach the database, copy
> the datafile to new location and attach the database files.
> You can look at sp_detach_db and sp_attach_db procedures
> in BOL.
> hth
> DeeJay
> >--Original Message--
> >what is the reccomended method for moving a database to a
> different drive on
> >the same computer? Just a backup and restore?
> >
> >
> >.
> >|||The move option is exposed on the right-most tab, where EM specifies the physical file names. In the right
part, just type the desired file name.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"djc" <noone@.nowhere.com> wrote in message news:%23Cj8oj5UEHA.2668@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I will use the detatch/attatch method to move but your reply brings another
> question to me. I did not see an option to restore 'WITH MOVE' in enterprise
> manager GUI restore... is it only available when using TSQL? The reason I
> ask is I had previously done a backup and restore to new database name and
> new location in order to quickly create a 'staging' or 'testing' version of
> a production database and want to be sure that was ok... meaning I wanted to
> verify that I had NOT chosen the 'WITH MOVE' option... I didn't know if it
> was a default option. Because, in that case, I wanted the database to be
> different... not a move of the real database..
> If I actually got a clear question out of that (doubtful ;) ) any info would
> be appreciated... I think I'm just being overly paranoid.
>
> "DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
> news:1cc9b01c45309$967fde70$a101280a@.phx.gbl...
> > Hi,
> >
> > You could do a backup and restore using the 'WITH MOVE'
> > OPTION or you could (better use) detach the database, copy
> > the datafile to new location and attach the database files.
> >
> > You can look at sp_detach_db and sp_attach_db procedures
> > in BOL.
> >
> > hth
> >
> > DeeJay
> > >--Original Message--
> > >what is the reccomended method for moving a database to a
> > different drive on
> > >the same computer? Just a backup and restore?
> > >
> > >
> > >.
> > >
>

Moving a database between two SQL Server with different patch levels

Hi,

I need to move a database from an instance running SQL Server 2000 SP3
to another running SQL Server 2000 SP2. Can I just use backup/restore
or detach/reattach and let SQL server take care of any downgrading (if
any).

Many thanks

GiovanniHi

I doubt if you can downgrade like this, and you may get spurious results. A
safer option may be to use the transfer wizard.

John

"Giovanni" <giovanni.clayden@.lstrillium.com> wrote in message
news:366e1de1.0410260757.7e48c961@.posting.google.c om...
> Hi,
> I need to move a database from an instance running SQL Server 2000 SP3
> to another running SQL Server 2000 SP2. Can I just use backup/restore
> or detach/reattach and let SQL server take care of any downgrading (if
> any).
> Many thanks
>
> Giovanni|||Giovanni (giovanni.clayden@.lstrillium.com) writes:
> I need to move a database from an instance running SQL Server 2000 SP3
> to another running SQL Server 2000 SP2. Can I just use backup/restore
> or detach/reattach and let SQL server take care of any downgrading (if
> any).

No. You will have to rebuild the database from scripts and bulk data in
or out. Or use the Transfer Wizard that John mentioned.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns958EEC2E2A9B3Yazorman@.127.0.0.1...
> Giovanni (giovanni.clayden@.lstrillium.com) writes:
> > I need to move a database from an instance running SQL Server 2000 SP3
> > to another running SQL Server 2000 SP2. Can I just use backup/restore
> > or detach/reattach and let SQL server take care of any downgrading (if
> > any).
> No. You will have to rebuild the database from scripts and bulk data in
> or out. Or use the Transfer Wizard that John mentioned.

You positive? Generally SP's only modify system databases. This is one of
those cases I'd suggest "try it, expect it to fail, but you may get lucky."

> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Greg D. Moore (Strider) (mooregr_deleteth1s@.greenms.com) writes:
> You positive? Generally SP's only modify system databases. This is one
> of those cases I'd suggest "try it, expect it to fail, but you may get
> lucky."

And they you later run into problems, and then you find out that is not
supported. Better safe than sorry, as they say.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Moving a database

I would like to move my arc serve backup from the original datastore to
an SQL server. is there a procedure anywhere for this ?Are you saying that you have backup of a SQL Server database performed with
ArcServe and you want to
restore that to a SQL server? If so, check the ArcServe documentation. Or do
you want to create a
SQL server backup file of that ArcServe backup? If so, I would imagine that
ArcServe should have
such a utility (if they are a serious player).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"scuba19" <scuba19@.discussions.microsoft.com> wrote in message
news:90EE8394-F9AD-4B2B-951F-4931905F66A1@.microsoft.com...
>I would like to move my arc serve backup from the original datastore to
> an SQL server. is there a procedure anywhere for this ?

Moving a database

I would like to move my arc serve backup from the original datastore to
an SQL server. is there a procedure anywhere for this ?More information needed than this.|||Are you saying that you have backup of a SQL Server database performed with ArcServe and you want to
restore that to a SQL server? If so, check the ArcServe documentation. Or do you want to create a
SQL server backup file of that ArcServe backup? If so, I would imagine that ArcServe should have
such a utility (if they are a serious player).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"scuba19" <scuba19@.discussions.microsoft.com> wrote in message
news:90EE8394-F9AD-4B2B-951F-4931905F66A1@.microsoft.com...
>I would like to move my arc serve backup from the original datastore to
> an SQL server. is there a procedure anywhere for this ?|||Arc Serve has a database containing the information of the datastore
including indexes references etc. This is needed if a restore is going to
be done. I would like this database that arcserve creates to be put into
an SQL database, instead of residing on my local drive.
"unc27932@.yahoo.com" wrote:
> More information needed than this.
>

Moving a database

I would like to move my arc serve backup from the original datastore to
an SQL server. is there a procedure anywhere for this ?
Are you saying that you have backup of a SQL Server database performed with ArcServe and you want to
restore that to a SQL server? If so, check the ArcServe documentation. Or do you want to create a
SQL server backup file of that ArcServe backup? If so, I would imagine that ArcServe should have
such a utility (if they are a serious player).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"scuba19" <scuba19@.discussions.microsoft.com> wrote in message
news:90EE8394-F9AD-4B2B-951F-4931905F66A1@.microsoft.com...
>I would like to move my arc serve backup from the original datastore to
> an SQL server. is there a procedure anywhere for this ?

Moving a big database

What is the best way to move a 15gb database. Copy the
files from 1 server to the other (the network connection
is pretty quick) or to backup then restore using a DAT
tape?Probably sp_detach_db and sp_attach_db.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"mat" <anonymous@.discussions.microsoft.com> wrote in message
news:11c3001c410c4$f7b18fe0$a301280a@.phx
.gbl...
> What is the best way to move a 15gb database. Copy the
> files from 1 server to the other (the network connection
> is pretty quick) or to backup then restore using a DAT
> tape?|||Hi,
I too agree Tibers comments. Because the backup of 15 GB database will take
20 Minutes and then Restore will take another 30 minutes. But if you go with
Tibers comment it will just take only copy time from source to destination.
Thanks
Hari
MCDBA
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OcXNXZMEEHA.2424@.TK2MSFTNGP09.phx.gbl...
> Probably sp_detach_db and sp_attach_db.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "mat" <anonymous@.discussions.microsoft.com> wrote in message
> news:11c3001c410c4$f7b18fe0$a301280a@.phx
.gbl...
>|||And if you need to do this 'move' on a regular basis you should really check
out SQL Lite Speed which will compress the backup as it's happening. This
will save a LOT of space, but more importantly will make the file copy a lot
faster since the file is a lot smaller...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:eA7XnzMEEHA.1128@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I too agree Tibers comments. Because the backup of 15 GB database will
take
> 20 Minutes and then Restore will take another 30 minutes. But if you go
with
> Tibers comment it will just take only copy time from source to
destination.
> Thanks
> Hari
> MCDBA
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:OcXNXZMEEHA.2424@.TK2MSFTNGP09.phx.gbl...
>

Moving 6.5 database

HI

I have to move a production 6.5 database to another server (also 6.5) in wich other dbs already exist.
1. Can I just backup and restore on the other server ?
2. Do I have to check for the same service packs on both?
3. Are there other ways to do it?

Any help will be apreciated, since I can't find much info about this version.

Thank youIt's been awhile since I've used 6.5, however I know in the pass I've moved a dump over from one machine to another, a few notes:[list=a]
Make sure both machines where setup with the same CodePage
You'll have to resync any UserIDs in the database sysusers, to those in Master..syslogins, I believe Microsoft have a stored proc that does this
Recompile all of your procs, we use to have a problem with this. Use a simple cursor to loop through user tables issuing a 'SP_RECOMPILE' on each table
[/list=a]

Move DTS packages

Is there anyway to move the DTS packages to a new server. Can I backup and
restore the master database?
Any ideas?
ThanksGreg,
DTS packages are stored in MSDB not in master. The easist way to move a
small number of packages is to design the package, and click on the package
drop down menu, and click save as, and select the name of the new server.
--
Denny Cherry
DBA
GameSpy Industries
"Greg Richards" <grichards@.matrixwebs.com> wrote in message
news:eM2Yy$iVDHA.1948@.TK2MSFTNGP11.phx.gbl...
> Is there anyway to move the DTS packages to a new server. Can I backup
and
> restore the master database?
> Any ideas?
> Thanks
>|||Greg,
Follow this link for good tips on moving DTS packages.
http://www.sqldts.com/default.aspx?6,105,204,0,1
Hope this helps
John

Move DB by backup/restore or SP_Detach?

Question: Which method should I use to do the simultaneous change from SQL
2000 to 2005, and the change from an old PC (server) to a new one?
Current: SQL 2000 + Old Hardware
New (Future): SQL 2005 + New Hardware
What's the best way to get a single live database from the current to the
new? I expect to add users manually and point the client applications to
the new server. I've done the Upgrade Advisor and resolved a couple minor
things. For testing, I've taken a BAK file from the old server (sql 2000)
and imported it onto the new server (sql 2005) using RESTORE FILELISTONLY
and then RESTORE DATABASE -- FROM DISK. It imported fine, upgrading to
2005 in the process, and a few test queries execute properly. So I'm
inclined to just do a backup and restore at that magic moment when we do the
upgrade. And then I'd add logins and jobs manually. Is there some
reason why SP_Detach & Attach is better? At least with a backup/restore
method, I can easily switch back to the old database if something doesn't
work. Plus, I can test this method many times before the big day, but I
can't easily test a detach of this database in advance. Thoughts?
Hi HK
You can use both forms obviously, the detach and attach would be quicker and
you can just take a copy of your data and log files where a backup and
restore will take a bit longer, which ever method you choose i would suggest
you rebuild all your indexes, stored procs, functions and views and then last
run update stats.
Regards
Charl
http://www.sqlserver.co.za
"HK" wrote:

> Question: Which method should I use to do the simultaneous change from SQL
> 2000 to 2005, and the change from an old PC (server) to a new one?
> Current: SQL 2000 + Old Hardware
> New (Future): SQL 2005 + New Hardware
> What's the best way to get a single live database from the current to the
> new? I expect to add users manually and point the client applications to
> the new server. I've done the Upgrade Advisor and resolved a couple minor
> things. For testing, I've taken a BAK file from the old server (sql 2000)
> and imported it onto the new server (sql 2005) using RESTORE FILELISTONLY
> and then RESTORE DATABASE -- FROM DISK. It imported fine, upgrading to
> 2005 in the process, and a few test queries execute properly. So I'm
> inclined to just do a backup and restore at that magic moment when we do the
> upgrade. And then I'd add logins and jobs manually. Is there some
> reason why SP_Detach & Attach is better? At least with a backup/restore
> method, I can easily switch back to the old database if something doesn't
> work. Plus, I can test this method many times before the big day, but I
> can't easily test a detach of this database in advance. Thoughts?
>
>
|||How to move databases between computers that are running SQL Server
http://support.microsoft.com/default...b;en-us;314546
AMB
"HK" wrote:

> Question: Which method should I use to do the simultaneous change from SQL
> 2000 to 2005, and the change from an old PC (server) to a new one?
> Current: SQL 2000 + Old Hardware
> New (Future): SQL 2005 + New Hardware
> What's the best way to get a single live database from the current to the
> new? I expect to add users manually and point the client applications to
> the new server. I've done the Upgrade Advisor and resolved a couple minor
> things. For testing, I've taken a BAK file from the old server (sql 2000)
> and imported it onto the new server (sql 2005) using RESTORE FILELISTONLY
> and then RESTORE DATABASE -- FROM DISK. It imported fine, upgrading to
> 2005 in the process, and a few test queries execute properly. So I'm
> inclined to just do a backup and restore at that magic moment when we do the
> upgrade. And then I'd add logins and jobs manually. Is there some
> reason why SP_Detach & Attach is better? At least with a backup/restore
> method, I can easily switch back to the old database if something doesn't
> work. Plus, I can test this method many times before the big day, but I
> can't easily test a detach of this database in advance. Thoughts?
>
>
|||Why would I have to do anything with stored procs?
Re-indexing doesn't seem required, but I can easily do that, and it is a
good idea anyway while I have the servers down. I don't have any functions
or views.
Can you elaborate on "update stats"?
"Charl" <Charl@.discussions.microsoft.com> wrote in message
news:FE5CFB19-0B91-48E4-A9C5-97BC0D419D03@.microsoft.com...
> Hi HK
> You can use both forms obviously, the detach and attach would be quicker
and
> you can just take a copy of your data and log files where a backup and
> restore will take a bit longer, which ever method you choose i would
suggest
> you rebuild all your indexes, stored procs, functions and views and then
last[vbcol=seagreen]
> run update stats.
> Regards
> Charl
> --
> http://www.sqlserver.co.za
>
> "HK" wrote:
SQL[vbcol=seagreen]
the[vbcol=seagreen]
to[vbcol=seagreen]
minor[vbcol=seagreen]
2000)[vbcol=seagreen]
FILELISTONLY[vbcol=seagreen]
the[vbcol=seagreen]
backup/restore[vbcol=seagreen]
doesn't[vbcol=seagreen]
|||Hi HK
The reasoning behind this is based on personal experience when we moved a
SQL 2000 DB to SQL 2005, we ran into some performance issues when migrating,
we rebuilt all the objects and ran sp_updatestats to update the table
statistics again and it resolved our problems. MS have made changes to the
query optimizer and to ensure that your procs use the best possible execution
plan i would suggest it. SQL should be intelligent enough to do it but we did
it as a precausionary and it helped our implementation.
Regards
charl
http://www.sqlserver.co.za
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> How to move databases between computers that are running SQL Server
> http://support.microsoft.com/default...b;en-us;314546
>
> AMB
> "HK" wrote:
|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.server:427477
You wrote that you rebuild all the objects. Which types of objects were
rebuilt besides indexes? Thanks.
"Charl" <Charl@.discussions.microsoft.com> wrote in message
news:7736D8DD-EEFC-4470-8EF8-D2F08BE8D96A@.microsoft.com...
> Hi HK
> The reasoning behind this is based on personal experience when we moved a
> SQL 2000 DB to SQL 2005, we ran into some performance issues when
migrating,
> we rebuilt all the objects and ran sp_updatestats to update the table
> statistics again and it resolved our problems. MS have made changes to the
> query optimizer and to ensure that your procs use the best possible
execution
> plan i would suggest it. SQL should be intelligent enough to do it but we
did[vbcol=seagreen]
> it as a precausionary and it helped our implementation.
> Regards
> charl
>
> --
> http://www.sqlserver.co.za
>
> "Alejandro Mesa" wrote:
from SQL[vbcol=seagreen]
the[vbcol=seagreen]
applications to[vbcol=seagreen]
minor[vbcol=seagreen]
2000)[vbcol=seagreen]
FILELISTONLY[vbcol=seagreen]
to[vbcol=seagreen]
I'm[vbcol=seagreen]
do the[vbcol=seagreen]
backup/restore[vbcol=seagreen]
doesn't[vbcol=seagreen]
I[vbcol=seagreen]

Move DB by backup/restore or SP_Detach?

Question: Which method should I use to do the simultaneous change from SQL
2000 to 2005, and the change from an old PC (server) to a new one?
Current: SQL 2000 + Old Hardware
New (Future): SQL 2005 + New Hardware
What's the best way to get a single live database from the current to the
new? I expect to add users manually and point the client applications to
the new server. I've done the Upgrade Advisor and resolved a couple minor
things. For testing, I've taken a BAK file from the old server (sql 2000)
and imported it onto the new server (sql 2005) using RESTORE FILELISTONLY
and then RESTORE DATABASE -- FROM DISK. It imported fine, upgrading to
2005 in the process, and a few test queries execute properly. So I'm
inclined to just do a backup and restore at that magic moment when we do the
upgrade. And then I'd add logins and jobs manually. Is there some
reason why SP_Detach & Attach is better? At least with a backup/restore
method, I can easily switch back to the old database if something doesn't
work. Plus, I can test this method many times before the big day, but I
can't easily test a detach of this database in advance. Thoughts?Hi HK
You can use both forms obviously, the detach and attach would be quicker and
you can just take a copy of your data and log files where a backup and
restore will take a bit longer, which ever method you choose i would sugges
t
you rebuild all your indexes, stored procs, functions and views and then las
t
run update stats.
Regards
Charl
http://www.sqlserver.co.za
"HK" wrote:

> Question: Which method should I use to do the simultaneous change from SQ
L
> 2000 to 2005, and the change from an old PC (server) to a new one?
> Current: SQL 2000 + Old Hardware
> New (Future): SQL 2005 + New Hardware
> What's the best way to get a single live database from the current to the
> new? I expect to add users manually and point the client applications to
> the new server. I've done the Upgrade Advisor and resolved a couple mino
r
> things. For testing, I've taken a BAK file from the old server (sql 2000)
> and imported it onto the new server (sql 2005) using RESTORE FILELISTONLY
> and then RESTORE DATABASE -- FROM DISK. It imported fine, upgrading to
> 2005 in the process, and a few test queries execute properly. So I'm
> inclined to just do a backup and restore at that magic moment when we do t
he
> upgrade. And then I'd add logins and jobs manually. Is there some
> reason why SP_Detach & Attach is better? At least with a backup/restore
> method, I can easily switch back to the old database if something doesn't
> work. Plus, I can test this method many times before the big day, but I
> can't easily test a detach of this database in advance. Thoughts?
>
>|||How to move databases between computers that are running SQL Server
http://support.microsoft.com/defaul...kb;en-us;314546
AMB
"HK" wrote:

> Question: Which method should I use to do the simultaneous change from SQ
L
> 2000 to 2005, and the change from an old PC (server) to a new one?
> Current: SQL 2000 + Old Hardware
> New (Future): SQL 2005 + New Hardware
> What's the best way to get a single live database from the current to the
> new? I expect to add users manually and point the client applications to
> the new server. I've done the Upgrade Advisor and resolved a couple mino
r
> things. For testing, I've taken a BAK file from the old server (sql 2000)
> and imported it onto the new server (sql 2005) using RESTORE FILELISTONLY
> and then RESTORE DATABASE -- FROM DISK. It imported fine, upgrading to
> 2005 in the process, and a few test queries execute properly. So I'm
> inclined to just do a backup and restore at that magic moment when we do t
he
> upgrade. And then I'd add logins and jobs manually. Is there some
> reason why SP_Detach & Attach is better? At least with a backup/restore
> method, I can easily switch back to the old database if something doesn't
> work. Plus, I can test this method many times before the big day, but I
> can't easily test a detach of this database in advance. Thoughts?
>
>|||Why would I have to do anything with stored procs?
Re-indexing doesn't seem required, but I can easily do that, and it is a
good idea anyway while I have the servers down. I don't have any functions
or views.
Can you elaborate on "update stats"?
"Charl" <Charl@.discussions.microsoft.com> wrote in message
news:FE5CFB19-0B91-48E4-A9C5-97BC0D419D03@.microsoft.com...
> Hi HK
> You can use both forms obviously, the detach and attach would be quicker
and
> you can just take a copy of your data and log files where a backup and
> restore will take a bit longer, which ever method you choose i would
suggest
> you rebuild all your indexes, stored procs, functions and views and then
last[vbcol=seagreen]
> run update stats.
> Regards
> Charl
> --
> http://www.sqlserver.co.za
>
> "HK" wrote:
>
SQL[vbcol=seagreen]
the[vbcol=seagreen]
to[vbcol=seagreen]
minor[vbcol=seagreen]
2000)[vbcol=seagreen]
FILELISTONLY[vbcol=seagreen]
the[vbcol=seagreen]
backup/restore[vbcol=seagreen]
doesn't[vbcol=seagreen]|||Hi HK
The reasoning behind this is based on personal experience when we moved a
SQL 2000 DB to SQL 2005, we ran into some performance issues when migrating,
we rebuilt all the objects and ran sp_updatestats to update the table
statistics again and it resolved our problems. MS have made changes to the
query optimizer and to ensure that your procs use the best possible executio
n
plan i would suggest it. SQL should be intelligent enough to do it but we di
d
it as a precausionary and it helped our implementation.
Regards
charl
http://www.sqlserver.co.za
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> How to move databases between computers that are running SQL Server
> http://support.microsoft.com/defaul...kb;en-us;314546
>
> AMB
> "HK" wrote:
>|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.server:427477
You wrote that you rebuild all the objects. Which types of objects were
rebuilt besides indexes? Thanks.
"Charl" <Charl@.discussions.microsoft.com> wrote in message
news:7736D8DD-EEFC-4470-8EF8-D2F08BE8D96A@.microsoft.com...
> Hi HK
> The reasoning behind this is based on personal experience when we moved a
> SQL 2000 DB to SQL 2005, we ran into some performance issues when
migrating,
> we rebuilt all the objects and ran sp_updatestats to update the table
> statistics again and it resolved our problems. MS have made changes to the
> query optimizer and to ensure that your procs use the best possible
execution
> plan i would suggest it. SQL should be intelligent enough to do it but we
did[vbcol=seagreen]
> it as a precausionary and it helped our implementation.
> Regards
> charl
>
> --
> http://www.sqlserver.co.za
>
> "Alejandro Mesa" wrote:
>
from SQL[vbcol=seagreen]
the[vbcol=seagreen]
applications to[vbcol=seagreen]
minor[vbcol=seagreen]
2000)[vbcol=seagreen]
FILELISTONLY[vbcol=seagreen]
to[vbcol=seagreen]
I'm[vbcol=seagreen]
do the[vbcol=seagreen]
backup/restore[vbcol=seagreen]
doesn't[vbcol=seagreen]
I[vbcol=seagreen]

Move DB by backup/restore or SP_Detach?

Question: Which method should I use to do the simultaneous change from SQL
2000 to 2005, and the change from an old PC (server) to a new one?
Current: SQL 2000 + Old Hardware
New (Future): SQL 2005 + New Hardware
What's the best way to get a single live database from the current to the
new? I expect to add users manually and point the client applications to
the new server. I've done the Upgrade Advisor and resolved a couple minor
things. For testing, I've taken a BAK file from the old server (sql 2000)
and imported it onto the new server (sql 2005) using RESTORE FILELISTONLY
and then RESTORE DATABASE -- FROM DISK. It imported fine, upgrading to
2005 in the process, and a few test queries execute properly. So I'm
inclined to just do a backup and restore at that magic moment when we do the
upgrade. And then I'd add logins and jobs manually. Is there some
reason why SP_Detach & Attach is better? At least with a backup/restore
method, I can easily switch back to the old database if something doesn't
work. Plus, I can test this method many times before the big day, but I
can't easily test a detach of this database in advance. Thoughts?Hi HK
You can use both forms obviously, the detach and attach would be quicker and
you can just take a copy of your data and log files where a backup and
restore will take a bit longer, which ever method you choose i would suggest
you rebuild all your indexes, stored procs, functions and views and then last
run update stats.
Regards
Charl
--
http://www.sqlserver.co.za
"HK" wrote:
> Question: Which method should I use to do the simultaneous change from SQL
> 2000 to 2005, and the change from an old PC (server) to a new one?
> Current: SQL 2000 + Old Hardware
> New (Future): SQL 2005 + New Hardware
> What's the best way to get a single live database from the current to the
> new? I expect to add users manually and point the client applications to
> the new server. I've done the Upgrade Advisor and resolved a couple minor
> things. For testing, I've taken a BAK file from the old server (sql 2000)
> and imported it onto the new server (sql 2005) using RESTORE FILELISTONLY
> and then RESTORE DATABASE -- FROM DISK. It imported fine, upgrading to
> 2005 in the process, and a few test queries execute properly. So I'm
> inclined to just do a backup and restore at that magic moment when we do the
> upgrade. And then I'd add logins and jobs manually. Is there some
> reason why SP_Detach & Attach is better? At least with a backup/restore
> method, I can easily switch back to the old database if something doesn't
> work. Plus, I can test this method many times before the big day, but I
> can't easily test a detach of this database in advance. Thoughts?
>
>|||How to move databases between computers that are running SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
AMB
"HK" wrote:
> Question: Which method should I use to do the simultaneous change from SQL
> 2000 to 2005, and the change from an old PC (server) to a new one?
> Current: SQL 2000 + Old Hardware
> New (Future): SQL 2005 + New Hardware
> What's the best way to get a single live database from the current to the
> new? I expect to add users manually and point the client applications to
> the new server. I've done the Upgrade Advisor and resolved a couple minor
> things. For testing, I've taken a BAK file from the old server (sql 2000)
> and imported it onto the new server (sql 2005) using RESTORE FILELISTONLY
> and then RESTORE DATABASE -- FROM DISK. It imported fine, upgrading to
> 2005 in the process, and a few test queries execute properly. So I'm
> inclined to just do a backup and restore at that magic moment when we do the
> upgrade. And then I'd add logins and jobs manually. Is there some
> reason why SP_Detach & Attach is better? At least with a backup/restore
> method, I can easily switch back to the old database if something doesn't
> work. Plus, I can test this method many times before the big day, but I
> can't easily test a detach of this database in advance. Thoughts?
>
>|||Why would I have to do anything with stored procs?
Re-indexing doesn't seem required, but I can easily do that, and it is a
good idea anyway while I have the servers down. I don't have any functions
or views.
Can you elaborate on "update stats"?
"Charl" <Charl@.discussions.microsoft.com> wrote in message
news:FE5CFB19-0B91-48E4-A9C5-97BC0D419D03@.microsoft.com...
> Hi HK
> You can use both forms obviously, the detach and attach would be quicker
and
> you can just take a copy of your data and log files where a backup and
> restore will take a bit longer, which ever method you choose i would
suggest
> you rebuild all your indexes, stored procs, functions and views and then
last
> run update stats.
> Regards
> Charl
> --
> http://www.sqlserver.co.za
>
> "HK" wrote:
> > Question: Which method should I use to do the simultaneous change from
SQL
> > 2000 to 2005, and the change from an old PC (server) to a new one?
> >
> > Current: SQL 2000 + Old Hardware
> > New (Future): SQL 2005 + New Hardware
> >
> > What's the best way to get a single live database from the current to
the
> > new? I expect to add users manually and point the client applications
to
> > the new server. I've done the Upgrade Advisor and resolved a couple
minor
> > things. For testing, I've taken a BAK file from the old server (sql
2000)
> > and imported it onto the new server (sql 2005) using RESTORE
FILELISTONLY
> > and then RESTORE DATABASE -- FROM DISK. It imported fine, upgrading to
> > 2005 in the process, and a few test queries execute properly. So I'm
> > inclined to just do a backup and restore at that magic moment when we do
the
> > upgrade. And then I'd add logins and jobs manually. Is there some
> > reason why SP_Detach & Attach is better? At least with a
backup/restore
> > method, I can easily switch back to the old database if something
doesn't
> > work. Plus, I can test this method many times before the big day, but I
> > can't easily test a detach of this database in advance. Thoughts?
> >
> >
> >|||Hi HK
The reasoning behind this is based on personal experience when we moved a
SQL 2000 DB to SQL 2005, we ran into some performance issues when migrating,
we rebuilt all the objects and ran sp_updatestats to update the table
statistics again and it resolved our problems. MS have made changes to the
query optimizer and to ensure that your procs use the best possible execution
plan i would suggest it. SQL should be intelligent enough to do it but we did
it as a precausionary and it helped our implementation.
Regards
charl
http://www.sqlserver.co.za
"Alejandro Mesa" wrote:
> How to move databases between computers that are running SQL Server
> http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
>
> AMB
> "HK" wrote:
> > Question: Which method should I use to do the simultaneous change from SQL
> > 2000 to 2005, and the change from an old PC (server) to a new one?
> >
> > Current: SQL 2000 + Old Hardware
> > New (Future): SQL 2005 + New Hardware
> >
> > What's the best way to get a single live database from the current to the
> > new? I expect to add users manually and point the client applications to
> > the new server. I've done the Upgrade Advisor and resolved a couple minor
> > things. For testing, I've taken a BAK file from the old server (sql 2000)
> > and imported it onto the new server (sql 2005) using RESTORE FILELISTONLY
> > and then RESTORE DATABASE -- FROM DISK. It imported fine, upgrading to
> > 2005 in the process, and a few test queries execute properly. So I'm
> > inclined to just do a backup and restore at that magic moment when we do the
> > upgrade. And then I'd add logins and jobs manually. Is there some
> > reason why SP_Detach & Attach is better? At least with a backup/restore
> > method, I can easily switch back to the old database if something doesn't
> > work. Plus, I can test this method many times before the big day, but I
> > can't easily test a detach of this database in advance. Thoughts?
> >
> >
> >|||You wrote that you rebuild all the objects. Which types of objects were
rebuilt besides indexes? Thanks.
"Charl" <Charl@.discussions.microsoft.com> wrote in message
news:7736D8DD-EEFC-4470-8EF8-D2F08BE8D96A@.microsoft.com...
> Hi HK
> The reasoning behind this is based on personal experience when we moved a
> SQL 2000 DB to SQL 2005, we ran into some performance issues when
migrating,
> we rebuilt all the objects and ran sp_updatestats to update the table
> statistics again and it resolved our problems. MS have made changes to the
> query optimizer and to ensure that your procs use the best possible
execution
> plan i would suggest it. SQL should be intelligent enough to do it but we
did
> it as a precausionary and it helped our implementation.
> Regards
> charl
>
> --
> http://www.sqlserver.co.za
>
> "Alejandro Mesa" wrote:
> > How to move databases between computers that are running SQL Server
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
> >
> >
> > AMB
> >
> > "HK" wrote:
> >
> > > Question: Which method should I use to do the simultaneous change
from SQL
> > > 2000 to 2005, and the change from an old PC (server) to a new one?
> > >
> > > Current: SQL 2000 + Old Hardware
> > > New (Future): SQL 2005 + New Hardware
> > >
> > > What's the best way to get a single live database from the current to
the
> > > new? I expect to add users manually and point the client
applications to
> > > the new server. I've done the Upgrade Advisor and resolved a couple
minor
> > > things. For testing, I've taken a BAK file from the old server (sql
2000)
> > > and imported it onto the new server (sql 2005) using RESTORE
FILELISTONLY
> > > and then RESTORE DATABASE -- FROM DISK. It imported fine, upgrading
to
> > > 2005 in the process, and a few test queries execute properly. So
I'm
> > > inclined to just do a backup and restore at that magic moment when we
do the
> > > upgrade. And then I'd add logins and jobs manually. Is there some
> > > reason why SP_Detach & Attach is better? At least with a
backup/restore
> > > method, I can easily switch back to the old database if something
doesn't
> > > work. Plus, I can test this method many times before the big day, but
I
> > > can't easily test a detach of this database in advance. Thoughts?
> > >
> > >
> > >

Move Database to client

Hi,
We have a database that we built for a client and we would like to make a
backup of the database and restore the database on our clients Sql Server
box. Both environments are running SQL Server 2000 SP3a.
We make a backup of the database on our server and when we go to the clients
site and run the restore it complains and gives an error message that states
SQL-DMO (ODBC SQLState: 42000) and states we should use the WITH MOVE. We
tried the WITH MOVE but aree not experienced DBA so we are not sure if we
configured it right.
RESTORE DATABASE TEST FROM DISK='C:\TEMP\CDR-Prod.BAK'
WITH MOVE 'TEST_Data' TO 'c:\program files\microsoft sql
server\mssql\data\CDR-Prod_Data.MDF',
MOVE 'TEST_Log' TO ' c:\program files\microsoft sql
server\mssql\data\CDR-Prod_Log.LDF'
We were able to restore the database some how and then it gives us an
SQL-DMO error 21776.
Any ideas on how we should best approach this situation so that we can get
the full database (structure and data)?
Thanks for the assistance.Please post the entire error message. Error 21776 is the
generic error used for an object not found in a collection.
The specifics of what object in what collection would be in
the entire error message.
-Sue
On Sun, 13 Mar 2005 18:55:02 -0800, "Rod S" <Rod
S@.discussions.microsoft.com> wrote:
>Hi,
>We have a database that we built for a client and we would like to make a
>backup of the database and restore the database on our clients Sql Server
>box. Both environments are running SQL Server 2000 SP3a.
>We make a backup of the database on our server and when we go to the clients
>site and run the restore it complains and gives an error message that states
>SQL-DMO (ODBC SQLState: 42000) and states we should use the WITH MOVE. We
>tried the WITH MOVE but aree not experienced DBA so we are not sure if we
>configured it right.
>RESTORE DATABASE TEST FROM DISK='C:\TEMP\CDR-Prod.BAK'
>WITH MOVE 'TEST_Data' TO 'c:\program files\microsoft sql
>server\mssql\data\CDR-Prod_Data.MDF',
>MOVE 'TEST_Log' TO ' c:\program files\microsoft sql
>server\mssql\data\CDR-Prod_Log.LDF'
>
>We were able to restore the database some how and then it gives us an
>SQL-DMO error 21776.
>Any ideas on how we should best approach this situation so that we can get
>the full database (structure and data)?
>Thanks for the assistance.|||Hi,
Sorry Sue, I should have elaborated more:
We have two problems:
1. we areunable to restore the database properly using either the GUI based
restore tool in the Enterprise Manager or usign the Query Analyzer with the
script included.
2. We some how managed to restore the database on the client's server and
when we try to access any component of the "restored" database, we receive
the SQL-DMO 21776 error. Which states the following
"Error 21776: [SQL-DMO} The name 'CDR-Prod' was not found in the Databases
colelction. If the name is a qualified name, use [ ] to separate various
parts of the name and try again."
So what i am looking for is what is the best mechanism to move our database
from out SQL Server 2000 environment to the clients SQL Server 2000
environment where we can preserve the table structure and data contained
within the database.
As well, how can we delete the database that we were able to create but now
is not recognized by the Enterprise manager due to the 21776 error.
I hope this provides a more detailed explanation of the situation we are
facing.
Thanks for the assistance,
Rod
"Sue Hoegemeier" wrote:
> Please post the entire error message. Error 21776 is the
> generic error used for an object not found in a collection.
> The specifics of what object in what collection would be in
> the entire error message.
> -Sue
> On Sun, 13 Mar 2005 18:55:02 -0800, "Rod S" <Rod
> S@.discussions.microsoft.com> wrote:
> >Hi,
> >
> >We have a database that we built for a client and we would like to make a
> >backup of the database and restore the database on our clients Sql Server
> >box. Both environments are running SQL Server 2000 SP3a.
> >
> >We make a backup of the database on our server and when we go to the clients
> >site and run the restore it complains and gives an error message that states
> >SQL-DMO (ODBC SQLState: 42000) and states we should use the WITH MOVE. We
> >tried the WITH MOVE but aree not experienced DBA so we are not sure if we
> >configured it right.
> >
> >RESTORE DATABASE TEST FROM DISK='C:\TEMP\CDR-Prod.BAK'
> >WITH MOVE 'TEST_Data' TO 'c:\program files\microsoft sql
> >server\mssql\data\CDR-Prod_Data.MDF',
> >MOVE 'TEST_Log' TO ' c:\program files\microsoft sql
> >server\mssql\data\CDR-Prod_Log.LDF'
> >
> >
> >We were able to restore the database some how and then it gives us an
> >SQL-DMO error 21776.
> >
> >Any ideas on how we should best approach this situation so that we can get
> >the full database (structure and data)?
> >
> >Thanks for the assistance.
>|||The best mechanism is a backup and restore. You can also use
sp_detach_db, copy the data and log files and have the
client attach the files with sp_attach_db.
I would guess many of the problems are from having a
non-standard database name. The rules for identifier and
what characters are allowed in what positions is in the
books online topic: Using Identifiers
In terms of how to delete the database now, try dropping the
database using query analyzer and putting brackets around
the database name.
drop database [CDR-Prod]
-Sue
On Mon, 14 Mar 2005 08:11:05 -0800, "Rod S"
<RodS@.discussions.microsoft.com> wrote:
>Hi,
>Sorry Sue, I should have elaborated more:
>We have two problems:
>1. we areunable to restore the database properly using either the GUI based
>restore tool in the Enterprise Manager or usign the Query Analyzer with the
>script included.
>2. We some how managed to restore the database on the client's server and
>when we try to access any component of the "restored" database, we receive
>the SQL-DMO 21776 error. Which states the following
>"Error 21776: [SQL-DMO} The name 'CDR-Prod' was not found in the Databases
>colelction. If the name is a qualified name, use [ ] to separate various
>parts of the name and try again."
>So what i am looking for is what is the best mechanism to move our database
>from out SQL Server 2000 environment to the clients SQL Server 2000
>environment where we can preserve the table structure and data contained
>within the database.
>As well, how can we delete the database that we were able to create but now
>is not recognized by the Enterprise manager due to the 21776 error.
>I hope this provides a more detailed explanation of the situation we are
>facing.
>Thanks for the assistance,
>Rod
>"Sue Hoegemeier" wrote:
>> Please post the entire error message. Error 21776 is the
>> generic error used for an object not found in a collection.
>> The specifics of what object in what collection would be in
>> the entire error message.
>> -Sue
>> On Sun, 13 Mar 2005 18:55:02 -0800, "Rod S" <Rod
>> S@.discussions.microsoft.com> wrote:
>> >Hi,
>> >
>> >We have a database that we built for a client and we would like to make a
>> >backup of the database and restore the database on our clients Sql Server
>> >box. Both environments are running SQL Server 2000 SP3a.
>> >
>> >We make a backup of the database on our server and when we go to the clients
>> >site and run the restore it complains and gives an error message that states
>> >SQL-DMO (ODBC SQLState: 42000) and states we should use the WITH MOVE. We
>> >tried the WITH MOVE but aree not experienced DBA so we are not sure if we
>> >configured it right.
>> >
>> >RESTORE DATABASE TEST FROM DISK='C:\TEMP\CDR-Prod.BAK'
>> >WITH MOVE 'TEST_Data' TO 'c:\program files\microsoft sql
>> >server\mssql\data\CDR-Prod_Data.MDF',
>> >MOVE 'TEST_Log' TO ' c:\program files\microsoft sql
>> >server\mssql\data\CDR-Prod_Log.LDF'
>> >
>> >
>> >We were able to restore the database some how and then it gives us an
>> >SQL-DMO error 21776.
>> >
>> >Any ideas on how we should best approach this situation so that we can get
>> >the full database (structure and data)?
>> >
>> >Thanks for the assistance.
>>