Copy databses from production server

Hi all:
I need to copy 2 relatively small databases from a SQL server, and then
bring those databases over to a test SQL server. I tried to do a restore on
the test serer from a backup copy of the production databases, but I think
the DBs have to exist on the test server before the restore can happen.
Can someone tell me the easiest way to copy the databases? I do not have a
direct connection to the test server so I need to make full copies of the DBs
and then copy them to the test server and then get them into SQL on the test
machine.
Can I select Tasks->Copy Databases and copy the databases to a local
directory? Can I do this while SQL Server is running without causing a
problem?
Thanks for any and all help.
John.
Do a backup and restore.
Backup the database.
Copy the database backup to the new server.
Create a database which will be the shell you will restore the database
backup into.
There is an option when you do the restore to replace existing database. Use
this option.
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John" <John@.discussions.microsoft.com> wrote in message
news:68305293-61F5-4C18-94E7-664148BB361D@.microsoft.com...
>
> Hi all:
> I need to copy 2 relatively small databases from a SQL server, and then
> bring those databases over to a test SQL server. I tried to do a restore
> on
> the test serer from a backup copy of the production databases, but I think
> the DBs have to exist on the test server before the restore can happen.
> Can someone tell me the easiest way to copy the databases? I do not have a
> direct connection to the test server so I need to make full copies of the
> DBs
> and then copy them to the test server and then get them into SQL on the
> test
> machine.
> Can I select Tasks->Copy Databases and copy the databases to a local
> directory? Can I do this while SQL Server is running without causing a
> problem?
> Thanks for any and all help.
> John.
>
|||Hi Hilary:
Thank you for your message. That seemed to work.
John.
"Hilary Cotter" wrote:

> Do a backup and restore.
> Backup the database.
> Copy the database backup to the new server.
> Create a database which will be the shell you will restore the database
> backup into.
> There is an option when you do the restore to replace existing database. Use
> this option.
> --
> http://www.zetainteractive.com - Shift Happens!
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "John" <John@.discussions.microsoft.com> wrote in message
> news:68305293-61F5-4C18-94E7-664148BB361D@.microsoft.com...
>
>

Copy data from 2000 to 2005 using Express Edition

Hi all

I am using Windows XP Prof, SP2 and having Sql Server 2000, SP4 and SQL Server 2005 Express Edition with Advanced Services SP1, and SQL Server 2005 Express Edition Toolkit SP1 installed. The toolkit is the one which created me the DTS folder in the root with this applicattion:

C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTSWizard.exe

I start now this Wizard and choose from Sql server - which is my 2000 version and to my sql express version, id/pw or windows authentication. I receive then the message:

Cannot continue for the following reason:

1) You have chosen a server that does not contain any packages.

2) The server you have chosen is neither a sql server 2000 nor sql server 7

The SSIS Migration Wizard can only load packages from those servers.

Nice. I am having Sql server 2000 and want to go to 2005.

What I am doing from ? Do I need to create some packages on Sql server 2000 to be able to proceed this task ?

Regards

Chrs

hi,

while SQL Server 2005 SSIS supports for sure older SQL Server versions, probably the DTS wizard is not supported for SQL Server 2005 scenarios, as lot of changes has occured in the metadata of SQL Server's handling...

you have to migrate your data to SQLExpress "the hard way", that's to say without designer support..

you can so use INSERT ... SELECT statements to move data from a linked SQL Server 2000 server (http://msdn2.microsoft.com/en-us/library/ms190479.aspx) or the like...

regards

|||

I finaly removed the express edition and downloaded the trial version. Here all is working fine. I will get the developer edition to test my issues and to learn more about the server. I think the express edition is really only for users with a small db and only connecting with a application, but nothing to learn or really work with.

Regards

|||

You can also just simply detach your file from SQL 2000 and then attach it to SQL 2005. The file will automatically be upgraded and you can start using it.

Mike

Copy a table in SQL Server 2005

Hello all
I want to make a copy of a table in SQL Server 2005, a back-up of my table
if you will. I don't see any way of doing this, I am I right in assuming
that one can't copy a table in an SQL Sever 2005 database?
ie: MyTable -> MyTableCOPY = 2 Tables in the same database
Thank you for your time.
Regards
David
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/
"David" <someone@.someisp.com> wrote in message
news:op.t4y5ugvcrasovn@.pavilion...
> I want to make a copy of a table in SQL Server 2005, a back-up of my table
> if you will. I don't see any way of doing this, I am I right in assuming
> that one can't copy a table in an SQL Sever 2005 database?
Just use
select * into newtable from oldtable
|||David
You can script out (with the data) the table and save it on the disk.
"David" <someone@.someisp.com> wrote in message
news:op.t4y5ugvcrasovn@.pavilion...
Hello all
I want to make a copy of a table in SQL Server 2005, a back-up of my table
if you will. I don't see any way of doing this, I am I right in assuming
that one can't copy a table in an SQL Sever 2005 database?
ie: MyTable -> MyTableCOPY = 2 Tables in the same database
Thank you for your time.
Regards
David
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/
|||Leon
If the database got corrupted you are still not be able to get back the
table. I think the way to save the datai is having proper backup of the
database or if the database is big, just script out into the text file the
table and keep it on the disk.
"Leon Mayne" <leon@.rmv_me.mvps.org> wrote in message
news:ubieII4VIHA.1164@.TK2MSFTNGP02.phx.gbl...
> "David" <someone@.someisp.com> wrote in message
> news:op.t4y5ugvcrasovn@.pavilion...
> Just use
> select * into newtable from oldtable
|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:u3D5IM4VIHA.4532@.TK2MSFTNGP02.phx.gbl...
> Leon
> If the database got corrupted you are still not be able to get back the
> table. I think the way to save the datai is having proper backup of the
> database or if the database is big, just script out into the text file
> the table and keep it on the disk.
Read the original post. They arn't talking about proper backups, they just
want a copy of the table in the same database.
|||Leon
The OP does not say that he wants the copy in the same db. He just said that
both tables are located in the same db
"Leon Mayne" <leon@.rmv_me.mvps.org> wrote in message
news:22CAA140-7D18-44BE-A9C4-A997937E5CF3@.microsoft.com...
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:u3D5IM4VIHA.4532@.TK2MSFTNGP02.phx.gbl...
> Read the original post. They arn't talking about proper backups, they just
> want a copy of the table in the same database.
|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23OsMlY4VIHA.2268@.TK2MSFTNGP02.phx.gbl...
> Leon
> The OP does not say that he wants the copy in the same db. He just said
> that both tables are located in the same db
"ie: MyTable -> MyTableCOPY = 2 Tables in the same database"
But you could be right. Whatever.
|||Hi David and Leon
This simple SELECT INTO will copy the table structure and all the data to a
new table.
It will not copy indexes, constraints, or triggers.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Leon Mayne" <leon@.rmv_me.mvps.org> wrote in message
news:ubieII4VIHA.1164@.TK2MSFTNGP02.phx.gbl...
> "David" <someone@.someisp.com> wrote in message
> news:op.t4y5ugvcrasovn@.pavilion...
> Just use
> select * into newtable from oldtable
|||"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:u1DrLC5VIHA.1208@.TK2MSFTNGP05.phx.gbl...
> Hi David and Leon
> This simple SELECT INTO will copy the table structure and all the data to
> a new table.
> It will not copy indexes, constraints, or triggers.
Correct. These schema objects would have to be copied manually, but if you
just want a quick backup copy of a table to play around with the data then
select into works well enough.
|||I agree, the data is the most important thing and the indexes, etc can
always be recreated. The OP should make note of what triggers, indexes and
constraints there were so that he will know to recreate them if he has to
revert to the copied table because it get accidentally dropped. (Of course,
it's all hypothetical, right, really nobody accidentally drops a table.
;-) )
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Leon Mayne" <leon@.rmv_me.mvps.org> wrote in message
news:C3367EE5-E728-46E5-8C83-3782ADF0141C@.microsoft.com...
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:u1DrLC5VIHA.1208@.TK2MSFTNGP05.phx.gbl...
> Correct. These schema objects would have to be copied manually, but if you
> just want a quick backup copy of a table to play around with the data then
> select into works well enough.

Converting negative numbers to positive

Hi All
I have a table with column Col2 with negative and positive numbers. I would
like to query the col2 and make sure all the negative numbers are converted
to positive in the resultset.
eg
Tb1:
Col1 Col2
-- --
a -1
b -2
c 3
select col2 from tb1 where col1 ='a'
Resultset:
Col2
--
1
How can i do this? Thank you in advance.

5 years experience with SQL Server 2000 and SAP BW/SEM> I would
> like to query the col2 and make sure all the negative numbers are
> converted
> to positive in the resultset.
Try:
SELECT ABS(col2)
FROM tb1
WHERE col1 = 'a'
Hope this helps.
Dan Guzman
SQL Server MVP
"MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
news:ACE007EE-E918-4CB6-A59C-8318CAC4B059@.microsoft.com...
> Hi All
> I have a table with column Col2 with negative and positive numbers. I
> would
> like to query the col2 and make sure all the negative numbers are
> converted
> to positive in the resultset.
> eg
> Tb1:
> Col1 Col2
> -- --
> a -1
> b -2
> c 3
> select col2 from tb1 where col1 ='a'
> Resultset:
> Col2
> --
> 1
> How can i do this? Thank you in advance.
>
>
>
>
>
>
>
>
>
> 5 years experience with SQL Server 2000 and SAP BW/SEM|||On Wed, 24 May 2006 16:41:02 -0700, MittyKom wrote:

>Hi All
>I have a table with column Col2 with negative and positive numbers. I woul
d
>like to query the col2 and make sure all the negative numbers are converte
d
>to positive in the resultset.
Hi MittyKom,
SELECT ABS(col2)
FROM tbl
Hugo Kornelis, SQL Server MVP|||MittyKom wrote:
> 5 years experience with SQL Server 2000 and SAP BW/SEM
5 years of experience and you don't know how to convert a negative
number into a positive one?|||Perhaps the OP was mostly on the administration side and/or specialized in
some other SQL Server non-development role. One could guess that there
would be a specialized function for this task but it's sometimes difficult
to find answers in the Books Online when you don't know what to look for.
Hope this helps.
Dan Guzman
SQL Server MVP
"Chris Lim" <blackcap80@.hotmail.com> wrote in message
news:1148517832.138537.174900@.i40g2000cwc.googlegroups.com...
> MittyKom wrote:
> 5 years of experience and you don't know how to convert a negative
> number into a positive one?
>|||To Chris Lim
I am new to sql programming my friend. If you cant help keep your mouth
shhhhhhh. OK.... I have been working on sql server admin only. Enjoy your
day.
"Chris Lim" wrote:

> MittyKom wrote:
> 5 years of experience and you don't know how to convert a negative
> number into a positive one?
>|||Thank you Hugo and Dan. It worked.
"Dan Guzman" wrote:

> Try:
> SELECT ABS(col2)
> FROM tb1
> WHERE col1 = 'a'
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
> news:ACE007EE-E918-4CB6-A59C-8318CAC4B059@.microsoft.com...
>
>