Converting problem: existing

Showing posts with label existing. Show all posts

Thursday, March 29, 2012

copy of existing table and data

Hello,
SELECT * INTO NEW_TABLE FROM OLD_TABLE
THis will copy the table structure and data into NEW_TABLE. You may need to
craete the Indexes manually to NEW_TABLE.
Thanks
Hari
"SANJAY PAWAR" <sanju@.nisiki.net> wrote in message
news:%23n7D6speHHA.3960@.TK2MSFTNGP02.phx.gbl...
> My query is very simple, I am new to SQL.
> I want to create copy of existing table and data.
> Pls suggest a command !
> Thanks in advance
> Sanjay
>
Also...
As well as indexes Primary Keys, Foreign Keys, CHECK constraints are not
transferred, but Identities are!!! E.g
CREATE TABLE MyMaster ( id int not null identity constraint PK_MyMaster
PRIMARY KEY,
Value int not null )
CREATE TABLE Mydetail (
id int not null identity constraint PK_Mydetail PRIMARY KEY,
master_id int not null constraint FK_MyMaster FOREIGN KEY REFERENCES
MyMaster ( id ),
Value int not null CONSTRAINT CK_value CHECK ( value > 10 ))
INSERT INTO MyMaster ( value )
SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
INSERT INTO Mydetail ( Master_id, value )
SELECT 1, 100
UNION ALL SELECT 2, 20
UNION ALL SELECT 3, 30
UNION ALL SELECT 4, 40
UNION ALL SELECT 4, 400
SELECT * INTO MyOtherMaster FROM MyMaster
EXEC sp_help MyMaster
EXEC sp_help MyOtherMaster
SELECT * INTO MyOtherDetail FROM MyDetail
EXEC sp_help MyDetail
EXEC sp_help MyOtherDetail
John
"Hari Prasad" wrote:

> Hello,
> SELECT * INTO NEW_TABLE FROM OLD_TABLE
> THis will copy the table structure and data into NEW_TABLE. You may need to
> craete the Indexes manually to NEW_TABLE.
> Thanks
> Hari
>
> "SANJAY PAWAR" <sanju@.nisiki.net> wrote in message
> news:%23n7D6speHHA.3960@.TK2MSFTNGP02.phx.gbl...
>
>

copy of existing table and data

My query is very simple, I am new to SQL.
I want to create copy of existing table and data.
Pls suggest a command !
Thanks in advance
SanjayEverything in the database? If so, I suggest backup and restore. If not, che
ck out some of the tools
at http://www.karaszi.com/SQLServer/in...rate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"SANJAY PAWAR" <sanju@.nisiki.net> wrote in message news:%23n7D6speHHA.3960@.TK2MSFTNGP02.phx.
gbl...
> My query is very simple, I am new to SQL.
> I want to create copy of existing table and data.
> Pls suggest a command !
> Thanks in advance
> Sanjay
>|||Hello,
SELECT * INTO NEW_TABLE FROM OLD_TABLE
THis will copy the table structure and data into NEW_TABLE. You may need to
craete the Indexes manually to NEW_TABLE.
Thanks
Hari
"SANJAY PAWAR" <sanju@.nisiki.net> wrote in message
news:%23n7D6speHHA.3960@.TK2MSFTNGP02.phx.gbl...
> My query is very simple, I am new to SQL.
> I want to create copy of existing table and data.
> Pls suggest a command !
> Thanks in advance
> Sanjay
>|||Thanks for the prompt response.
I think, i have failed to pass on my message.
I want to create a new table using existing table with its structure and
records.
Sanjay
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OYKfdypeHHA.4300@.TK2MSFTNGP02.phx.gbl...
> Everything in the database? If so, I suggest backup and restore. If not,
> check out some of the tools at
> http://www.karaszi.com/SQLServer/in...rate_script.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "SANJAY PAWAR" <sanju@.nisiki.net> wrote in message
> news:%23n7D6speHHA.3960@.TK2MSFTNGP02.phx.gbl...
>|||Also...
As well as indexes Primary Keys, Foreign Keys, CHECK constraints are not
transferred, but Identities are!!! E.g
CREATE TABLE MyMaster ( id int not null identity constraint PK_MyMaster
PRIMARY KEY,
Value int not null )
CREATE TABLE Mydetail (
id int not null identity constraint PK_Mydetail PRIMARY KEY,
master_id int not null constraint FK_MyMaster FOREIGN KEY REFERENCES
MyMaster ( id ),
Value int not null CONSTRAINT CK_value CHECK ( value > 10 ))
INSERT INTO MyMaster ( value )
SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
INSERT INTO Mydetail ( Master_id, value )
SELECT 1, 100
UNION ALL SELECT 2, 20
UNION ALL SELECT 3, 30
UNION ALL SELECT 4, 40
UNION ALL SELECT 4, 400
SELECT * INTO MyOtherMaster FROM MyMaster
EXEC sp_help MyMaster
EXEC sp_help MyOtherMaster
SELECT * INTO MyOtherDetail FROM MyDetail
EXEC sp_help MyDetail
EXEC sp_help MyOtherDetail
John
"Hari Prasad" wrote:

> Hello,
> SELECT * INTO NEW_TABLE FROM OLD_TABLE
> THis will copy the table structure and data into NEW_TABLE. You may need t
o
> craete the Indexes manually to NEW_TABLE.
> Thanks
> Hari
>
> "SANJAY PAWAR" <sanju@.nisiki.net> wrote in message
> news:%23n7D6speHHA.3960@.TK2MSFTNGP02.phx.gbl...
>
>

copy of existing table and data

My query is very simple, I am new to SQL.
I want to create copy of existing table and data.
Pls suggest a command !
Thanks in advance
SanjayEverything in the database? If so, I suggest backup and restore. If not, check out some of the tools
at http://www.karaszi.com/SQLServer/info_generate_script.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"SANJAY PAWAR" <sanju@.nisiki.net> wrote in message news:%23n7D6speHHA.3960@.TK2MSFTNGP02.phx.gbl...
> My query is very simple, I am new to SQL.
> I want to create copy of existing table and data.
> Pls suggest a command !
> Thanks in advance
> Sanjay
>|||Hello,
SELECT * INTO NEW_TABLE FROM OLD_TABLE
THis will copy the table structure and data into NEW_TABLE. You may need to
craete the Indexes manually to NEW_TABLE.
Thanks
Hari
"SANJAY PAWAR" <sanju@.nisiki.net> wrote in message
news:%23n7D6speHHA.3960@.TK2MSFTNGP02.phx.gbl...
> My query is very simple, I am new to SQL.
> I want to create copy of existing table and data.
> Pls suggest a command !
> Thanks in advance
> Sanjay
>|||Thanks for the prompt response.
I think, i have failed to pass on my message.
I want to create a new table using existing table with its structure and
records.
Sanjay
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OYKfdypeHHA.4300@.TK2MSFTNGP02.phx.gbl...
> Everything in the database? If so, I suggest backup and restore. If not,
> check out some of the tools at
> http://www.karaszi.com/SQLServer/info_generate_script.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "SANJAY PAWAR" <sanju@.nisiki.net> wrote in message
> news:%23n7D6speHHA.3960@.TK2MSFTNGP02.phx.gbl...
>> My query is very simple, I am new to SQL.
>> I want to create copy of existing table and data.
>> Pls suggest a command !
>> Thanks in advance
>> Sanjay
>|||Also...
As well as indexes Primary Keys, Foreign Keys, CHECK constraints are not
transferred, but Identities are!!! E.g
CREATE TABLE MyMaster ( id int not null identity constraint PK_MyMaster
PRIMARY KEY,
Value int not null )
CREATE TABLE Mydetail (
id int not null identity constraint PK_Mydetail PRIMARY KEY,
master_id int not null constraint FK_MyMaster FOREIGN KEY REFERENCES
MyMaster ( id ),
Value int not null CONSTRAINT CK_value CHECK ( value > 10 ))
INSERT INTO MyMaster ( value )
SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
INSERT INTO Mydetail ( Master_id, value )
SELECT 1, 100
UNION ALL SELECT 2, 20
UNION ALL SELECT 3, 30
UNION ALL SELECT 4, 40
UNION ALL SELECT 4, 400
SELECT * INTO MyOtherMaster FROM MyMaster
EXEC sp_help MyMaster
EXEC sp_help MyOtherMaster
SELECT * INTO MyOtherDetail FROM MyDetail
EXEC sp_help MyDetail
EXEC sp_help MyOtherDetail
John
"Hari Prasad" wrote:
> Hello,
> SELECT * INTO NEW_TABLE FROM OLD_TABLE
> THis will copy the table structure and data into NEW_TABLE. You may need to
> craete the Indexes manually to NEW_TABLE.
> Thanks
> Hari
>
> "SANJAY PAWAR" <sanju@.nisiki.net> wrote in message
> news:%23n7D6speHHA.3960@.TK2MSFTNGP02.phx.gbl...
> > My query is very simple, I am new to SQL.
> >
> > I want to create copy of existing table and data.
> > Pls suggest a command !
> >
> > Thanks in advance
> > Sanjay
> >
>
>

Copy MDF and LDF files Over Existing Database

I am not a DBA and it will be obvious to you soon.

I have two
databases on the same server. I want to basically restore a db from an
existing db on the same server. The catch is that I don't want to
restore from a backup file and I have to enable this process to run by
the class instructor through an ASP page calling a stored proc. Due to
time limitations between training classes I would just like to copy the
.MDF and .LDF from the Source db to the destination db and attach it.
I was trying the code below:
RESTORE DATABASE train
FROM train_devo
WITH RECOVERY,
MOVE 'c:\train_devo_data.mdf' TO 'c:\train_data.mdf',
MOVE 'c:\train_devo_log.ldf' TO 'c:\train_log.ldf'
GO
I am getting the error:
Server: Msg 3206, Level 16, State 1, Line 1
No entry in sysdevices for backup device 'train_devo'. Update
sysdevices and rerun statement.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I know that I will need to kill active processes and that my record
doesn't exist in the sysdevices table but I don't have enough time
right now to study this completely.
Any help is greatly appreciated.
Thanks
BenBACKUP and RESTORE are fast operations on small databases, you might want to
try that method.
You could restore
FROM FILE = 'x:\train.bak'
or
from the "device" train_devo. You have to set up this device first, so it
might just be easier to to restore from file.
You will want to (need to) use REPLACE to replace the files if they already
exist.
Keith Kratochvil
<bcumminsr@.yahoo.com> wrote in message
news:1153259948.748817.240020@.m73g2000cwd.googlegroups.com...
>I am not a DBA and it will be obvious to you soon.

I have two
> databases on the same server. I want to basically restore a db from an
> existing db on the same server. The catch is that I don't want to
> restore from a backup file and I have to enable this process to run by
> the class instructor through an ASP page calling a stored proc. Due to
> time limitations between training classes I would just like to copy the
> .MDF and .LDF from the Source db to the destination db and attach it.
> I was trying the code below:
> RESTORE DATABASE train
> FROM train_devo
> WITH RECOVERY,
> MOVE 'c:\train_devo_data.mdf' TO 'c:\train_data.mdf',
> MOVE 'c:\train_devo_log.ldf' TO 'c:\train_log.ldf'
> GO
> I am getting the error:
> Server: Msg 3206, Level 16, State 1, Line 1
> No entry in sysdevices for backup device 'train_devo'. Update
> sysdevices and rerun statement.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> I know that I will need to kill active processes and that my record
> doesn't exist in the sysdevices table but I don't have enough time
> right now to study this completely.
> Any help is greatly appreciated.
> Thanks
> Ben
>
Hi
You can use BACKUP/RESTORE as suggested by Keith, or you can detach the
existing database, copy the files, and then reattach the db's again.
Try to look up RESTORE and sp_detach_db/sp_attach_db in Books On Line
for details.
Regards
Steen Schlter Persson
Databaseadministrator / Systemadministrator|||underprocessable|||Steen Persson (DK) wrote:
> bcumminsr@.yahoo.com wrote:
> Hi
> You can use BACKUP/RESTORE as suggested by Keith, or you can detach the
> existing database, copy the files, and then reattach the db's again.
> Try to look up RESTORE and sp_detach_db/sp_attach_db in Books On Line
> for details.
>
> --
> Regards
> Steen Schl=FCter Persson
> Databaseadministrator / Systemadministrator
> --030204050507050003020602
> Content-Type: text/html; charset=3DISO-8859-1
> X-Google-AttachSize: 2025
> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
> <html>
> <head>
> <meta content=3D"text/html;charset=3DISO-8859-1" http-equiv=3D"Content-=
Type">
> </head>
> <body bgcolor=3D"#ffffff" text=3D"#000000">
> <a class=3D"moz-txt-link-abbreviated" href=3D"mailto:bcumminsr@.yahoo.com"=
>bcumminsr@.yahoo.com</a> wrote:
> <blockquote
> cite=3D"mid1153259948.748817.240020@.m73g2000cwd.googlegroups.com"
> type=3D"cite">
> <pre wrap=3D"">I am not a DBA and it will be obvious to you soon.

I=
have two
> databases on the same server. I want to basically restore a db from an
> existing db on the same server. The catch is that I don't want to
> restore from a backup file and I have to enable this process to run by
> the class instructor through an ASP page calling a stored proc. Due to
> time limitations between training classes I would just like to copy the
> .MDF and .LDF from the Source db to the destination db and attach it.
> I was trying the code below:
> RESTORE DATABASE train
> FROM train_devo
> WITH RECOVERY,
> MOVE 'c:\train_devo_data.mdf' TO 'c:\train_data.mdf',
> MOVE 'c:\train_devo_log.ldf' TO 'c:\train_log.ldf'
> GO
> I am getting the error:
> Server: Msg 3206, Level 16, State 1, Line 1
> No entry in sysdevices for backup device 'train_devo'. Update
> sysdevices and rerun statement.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> I know that I will need to kill active processes and that my record
> doesn't exist in the sysdevices table but I don't have enough time
> right now to study this completely.
> Any help is greatly appreciated.
> Thanks
> Ben
> </pre>
> </blockquote>
> Hi 
> 
> You can use BACKUP/RESTORE as suggested by Keith, or you can detach the
> existing database, copy the files, and then reattach the db's again. 
> 
> Try to look up RESTORE and sp_detach_db/sp_attach_db in Books On Line
> for details. 
> 
> 
> -- 
> Regards 
> Steen Schlüter Persson 
> Databaseadministrator / Systemadministrator 
> 
> </body>
> </html>
> --030204050507050003020602--
Thanks for all of your help. I need to do this using T-SQL and I can't
get the RESTORE command to work. Is it possible to copy the MDF and LDF
using T-SQL to a different location? Thanks|||Why doesn't it work? What errors do you receive?
Keith Kratochvil
<bcumminsr@.yahoo.com> wrote in message
news:1153327247.825744.46880@.75g2000cwc.googlegroups.com...
Thanks for all of your help. I need to do this using T-SQL and I can't
get the RESTORE command to work. Is it possible to copy the MDF and LDF
using T-SQL to a different location? Thanks|||bcumminsr@.yahoo.com wrote:
> Steen Persson (DK) wrote:
>
> Thanks for all of your help. I need to do this using T-SQL and I can't
> get the RESTORE command to work. Is it possible to copy the MDF and LDF
> using T-SQL to a different location? Thanks
>
You'll have to give us some more than just "it doesn't work". If you
want to copy the files using T-sql, you can look up xp_cmdshell in Books
On Line.
Regards
Steen Schlter Persson
Databaseadministrator / Systemadministrator

Copy MDF and LDF files Over Existing Database

I am not a DBA and it will be obvious to you soon. :) I have two
databases on the same server. I want to basically restore a db from an
existing db on the same server. The catch is that I don't want to
restore from a backup file and I have to enable this process to run by
the class instructor through an ASP page calling a stored proc. Due to
time limitations between training classes I would just like to copy the
.MDF and .LDF from the Source db to the destination db and attach it.
I was trying the code below:
RESTORE DATABASE train
FROM train_devo
WITH RECOVERY,
MOVE 'c:\train_devo_data.mdf' TO 'c:\train_data.mdf',
MOVE 'c:\train_devo_log.ldf' TO 'c:\train_log.ldf'
GO
I am getting the error:
Server: Msg 3206, Level 16, State 1, Line 1
No entry in sysdevices for backup device 'train_devo'. Update
sysdevices and rerun statement.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I know that I will need to kill active processes and that my record
doesn't exist in the sysdevices table but I don't have enough time
right now to study this completely.
Any help is greatly appreciated.
Thanks
BenBACKUP and RESTORE are fast operations on small databases, you might want to
try that method.
You could restore
FROM FILE = 'x:\train.bak'
or
from the "device" train_devo. You have to set up this device first, so it
might just be easier to to restore from file.
You will want to (need to) use REPLACE to replace the files if they already
exist.
Keith Kratochvil
<bcumminsr@.yahoo.com> wrote in message
news:1153259948.748817.240020@.m73g2000cwd.googlegroups.com...
>I am not a DBA and it will be obvious to you soon. :) I have two
> databases on the same server. I want to basically restore a db from an
> existing db on the same server. The catch is that I don't want to
> restore from a backup file and I have to enable this process to run by
> the class instructor through an ASP page calling a stored proc. Due to
> time limitations between training classes I would just like to copy the
> .MDF and .LDF from the Source db to the destination db and attach it.
> I was trying the code below:
> RESTORE DATABASE train
> FROM train_devo
> WITH RECOVERY,
> MOVE 'c:\train_devo_data.mdf' TO 'c:\train_data.mdf',
> MOVE 'c:\train_devo_log.ldf' TO 'c:\train_log.ldf'
> GO
> I am getting the error:
> Server: Msg 3206, Level 16, State 1, Line 1
> No entry in sysdevices for backup device 'train_devo'. Update
> sysdevices and rerun statement.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> I know that I will need to kill active processes and that my record
> doesn't exist in the sysdevices table but I don't have enough time
> right now to study this completely.
> Any help is greatly appreciated.
> Thanks
> Ben
>|||This is a multi-part message in MIME format.
--030204050507050003020602
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
bcumminsr@.yahoo.com wrote:
> I am not a DBA and it will be obvious to you soon. :) I have two
> databases on the same server. I want to basically restore a db from an
> existing db on the same server. The catch is that I don't want to
> restore from a backup file and I have to enable this process to run by
> the class instructor through an ASP page calling a stored proc. Due to
> time limitations between training classes I would just like to copy the
> .MDF and .LDF from the Source db to the destination db and attach it.
> I was trying the code below:
> RESTORE DATABASE train
> FROM train_devo
> WITH RECOVERY,
> MOVE 'c:\train_devo_data.mdf' TO 'c:\train_data.mdf',
> MOVE 'c:\train_devo_log.ldf' TO 'c:\train_log.ldf'
> GO
> I am getting the error:
> Server: Msg 3206, Level 16, State 1, Line 1
> No entry in sysdevices for backup device 'train_devo'. Update
> sysdevices and rerun statement.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> I know that I will need to kill active processes and that my record
> doesn't exist in the sysdevices table but I don't have enough time
> right now to study this completely.
> Any help is greatly appreciated.
> Thanks
> Ben
>
Hi
You can use BACKUP/RESTORE as suggested by Keith, or you can detach the
existing database, copy the files, and then reattach the db's again.
Try to look up RESTORE and sp_detach_db/sp_attach_db in Books On Line
for details.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator
--030204050507050003020602
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:bcumminsr@.yahoo.com">bcumminsr@.yahoo.com</a> wrote:
<blockquote
cite="mid1153259948.748817.240020@.m73g2000cwd.googlegroups.com"
type="cite">
<pre wrap="">I am not a DBA and it will be obvious to you soon. :) I have two
databases on the same server. I want to basically restore a db from an
existing db on the same server. The catch is that I don't want to
restore from a backup file and I have to enable this process to run by
the class instructor through an ASP page calling a stored proc. Due to
time limitations between training classes I would just like to copy the
.MDF and .LDF from the Source db to the destination db and attach it.
I was trying the code below:
RESTORE DATABASE train
FROM train_devo
WITH RECOVERY,
MOVE 'c:\train_devo_data.mdf' TO 'c:\train_data.mdf',
MOVE 'c:\train_devo_log.ldf' TO 'c:\train_log.ldf'
GO
I am getting the error:
Server: Msg 3206, Level 16, State 1, Line 1
No entry in sysdevices for backup device 'train_devo'. Update
sysdevices and rerun statement.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I know that I will need to kill active processes and that my record
doesn't exist in the sysdevices table but I don't have enough time
right now to study this completely.
Any help is greatly appreciated.
Thanks
Ben
</pre>
</blockquote>
Hi 
 
You can use BACKUP/RESTORE as suggested by Keith, or you can detach the
existing database, copy the files, and then reattach the db's again. 
 
Try to look up RESTORE and sp_detach_db/sp_attach_db in Books On Line
for details. 
 
 
-- 
Regards 
Steen Schlüter Persson 
Databaseadministrator / Systemadministrator 

</body>
</html>
--030204050507050003020602--|||By the way: I mentioned that you should use backup and restore because of
this blurb related to sp_attach_db within the SQL Server 2005 Books Online:
Important:
This feature will be removed in a future version of Microsoft SQL
Server. Avoid using this feature in new development work, and plan to modify
applications that currently use this feature. We recommend that you use
CREATE DATABASE database_name FOR ATTACH instead. For more information, see
CREATE DATABASE (Transact-SQL).
Keith Kratochvil
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:OLkoterqGHA.4912@.TK2MSFTNGP05.phx.gbl...
> BACKUP and RESTORE are fast operations on small databases, you might want
> to
> try that method.
> You could restore
> FROM FILE = 'x:\train.bak'
> or
> from the "device" train_devo. You have to set up this device first, so it
> might just be easier to to restore from file.
>
> You will want to (need to) use REPLACE to replace the files if they
> already
> exist.
>
> --
> Keith Kratochvil
>
> <bcumminsr@.yahoo.com> wrote in message
> news:1153259948.748817.240020@.m73g2000cwd.googlegroups.com...
>>I am not a DBA and it will be obvious to you soon. :) I have two
>> databases on the same server. I want to basically restore a db from an
>> existing db on the same server. The catch is that I don't want to
>> restore from a backup file and I have to enable this process to run by
>> the class instructor through an ASP page calling a stored proc. Due to
>> time limitations between training classes I would just like to copy the
>> .MDF and .LDF from the Source db to the destination db and attach it.
>> I was trying the code below:
>> RESTORE DATABASE train
>> FROM train_devo
>> WITH RECOVERY,
>> MOVE 'c:\train_devo_data.mdf' TO 'c:\train_data.mdf',
>> MOVE 'c:\train_devo_log.ldf' TO 'c:\train_log.ldf'
>> GO
>> I am getting the error:
>> Server: Msg 3206, Level 16, State 1, Line 1
>> No entry in sysdevices for backup device 'train_devo'. Update
>> sysdevices and rerun statement.
>> Server: Msg 3013, Level 16, State 1, Line 1
>> RESTORE DATABASE is terminating abnormally.
>> I know that I will need to kill active processes and that my record
>> doesn't exist in the sysdevices table but I don't have enough time
>> right now to study this completely.
>> Any help is greatly appreciated.
>> Thanks
>> Ben
>
begin 666 note.gif
M1TE&.#EA"@.`*`+,)`/\$`/]=6L;#QM;3UO_/`(2&A(R.C ```/__SO___P``
M`````````````````````"'Y! $```D`+ `````*``H```0HT,@.Y1 `&Z7T(
AWEIW)!E'C.16G$.2(L-1((5;%D-H@._H+HH>@.,.B*```[
`
end|||Steen Persson (DK) wrote:
> bcumminsr@.yahoo.com wrote:
> > I am not a DBA and it will be obvious to you soon. :) I have two
> > databases on the same server. I want to basically restore a db from an
> > existing db on the same server. The catch is that I don't want to
> > restore from a backup file and I have to enable this process to run by
> > the class instructor through an ASP page calling a stored proc. Due to
> > time limitations between training classes I would just like to copy the
> > .MDF and .LDF from the Source db to the destination db and attach it.
> >
> > I was trying the code below:
> >
> > RESTORE DATABASE train
> > FROM train_devo
> > WITH RECOVERY,
> > MOVE 'c:\train_devo_data.mdf' TO 'c:\train_data.mdf',
> > MOVE 'c:\train_devo_log.ldf' TO 'c:\train_log.ldf'
> > GO
> >
> > I am getting the error:
> > Server: Msg 3206, Level 16, State 1, Line 1
> > No entry in sysdevices for backup device 'train_devo'. Update
> > sysdevices and rerun statement.
> > Server: Msg 3013, Level 16, State 1, Line 1
> > RESTORE DATABASE is terminating abnormally.
> >
> > I know that I will need to kill active processes and that my record
> > doesn't exist in the sysdevices table but I don't have enough time
> > right now to study this completely.
> >
> > Any help is greatly appreciated.
> > Thanks
> > Ben
> >
> >
> Hi
> You can use BACKUP/RESTORE as suggested by Keith, or you can detach the
> existing database, copy the files, and then reattach the db's again.
> Try to look up RESTORE and sp_detach_db/sp_attach_db in Books On Line
> for details.
>
> --
> Regards
> Steen Schl=FCter Persson
> Databaseadministrator / Systemadministrator
> --030204050507050003020602
> Content-Type: text/html; charset=3DISO-8859-1
> X-Google-AttachSize: 2025
> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
> <html>
> <head>
> <meta content=3D"text/html;charset=3DISO-8859-1" http-equiv=3D"Content-=Type">
> </head>
> <body bgcolor=3D"#ffffff" text=3D"#000000">
> <a class=3D"moz-txt-link-abbreviated" href=3D"mailto:bcumminsr@.yahoo.com"=>bcumminsr@.yahoo.com</a> wrote:
> <blockquote
> cite=3D"mid1153259948.748817.240020@.m73g2000cwd.googlegroups.com"
> type=3D"cite">
> <pre wrap=3D"">I am not a DBA and it will be obvious to you soon. :) I= have two
> databases on the same server. I want to basically restore a db from an
> existing db on the same server. The catch is that I don't want to
> restore from a backup file and I have to enable this process to run by
> the class instructor through an ASP page calling a stored proc. Due to
> time limitations between training classes I would just like to copy the
> .MDF and .LDF from the Source db to the destination db and attach it.
> I was trying the code below:
> RESTORE DATABASE train
> FROM train_devo
> WITH RECOVERY,
> MOVE 'c:\train_devo_data.mdf' TO 'c:\train_data.mdf',
> MOVE 'c:\train_devo_log.ldf' TO 'c:\train_log.ldf'
> GO
> I am getting the error:
> Server: Msg 3206, Level 16, State 1, Line 1
> No entry in sysdevices for backup device 'train_devo'. Update
> sysdevices and rerun statement.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> I know that I will need to kill active processes and that my record
> doesn't exist in the sysdevices table but I don't have enough time
> right now to study this completely.
> Any help is greatly appreciated.
> Thanks
> Ben
> </pre>
> </blockquote>
> Hi 
> 
> You can use BACKUP/RESTORE as suggested by Keith, or you can detach the
> existing database, copy the files, and then reattach the db's again. 
> 
> Try to look up RESTORE and sp_detach_db/sp_attach_db in Books On Line
> for details. 
> 
> 
> -- 
> Regards 
> Steen Schlüter Persson 
> Databaseadministrator / Systemadministrator 
> 
> </body>
> </html>
> --030204050507050003020602--
Thanks for all of your help. I need to do this using T-SQL and I can't
get the RESTORE command to work. Is it possible to copy the MDF and LDF
using T-SQL to a different location? Thanks|||Why doesn't it work? What errors do you receive?
--
Keith Kratochvil
<bcumminsr@.yahoo.com> wrote in message
news:1153327247.825744.46880@.75g2000cwc.googlegroups.com...
Thanks for all of your help. I need to do this using T-SQL and I can't
get the RESTORE command to work. Is it possible to copy the MDF and LDF
using T-SQL to a different location? Thanks|||This is a multi-part message in MIME format.
--050402070308080300020405
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
bcumminsr@.yahoo.com wrote:
> Steen Persson (DK) wrote:
>> bcumminsr@.yahoo.com wrote:
>> I am not a DBA and it will be obvious to you soon. :) I have two
>> databases on the same server. I want to basically restore a db from an
>> existing db on the same server. The catch is that I don't want to
>> restore from a backup file and I have to enable this process to run by
>> the class instructor through an ASP page calling a stored proc. Due to
>> time limitations between training classes I would just like to copy the
>> .MDF and .LDF from the Source db to the destination db and attach it.
>> I was trying the code below:
>> RESTORE DATABASE train
>> FROM train_devo
>> WITH RECOVERY,
>> MOVE 'c:\train_devo_data.mdf' TO 'c:\train_data.mdf',
>> MOVE 'c:\train_devo_log.ldf' TO 'c:\train_log.ldf'
>> GO
>> I am getting the error:
>> Server: Msg 3206, Level 16, State 1, Line 1
>> No entry in sysdevices for backup device 'train_devo'. Update
>> sysdevices and rerun statement.
>> Server: Msg 3013, Level 16, State 1, Line 1
>> RESTORE DATABASE is terminating abnormally.
>> I know that I will need to kill active processes and that my record
>> doesn't exist in the sysdevices table but I don't have enough time
>> right now to study this completely.
>> Any help is greatly appreciated.
>> Thanks
>> Ben
>>
>> Hi
>> You can use BACKUP/RESTORE as suggested by Keith, or you can detach the
>> existing database, copy the files, and then reattach the db's again.
>> Try to look up RESTORE and sp_detach_db/sp_attach_db in Books On Line
>> for details.
>>
>> --
>> Regards
>> Steen Schlüter Persson
>> Databaseadministrator / Systemadministrator
>> --030204050507050003020602
>> Content-Type: text/html; charset=ISO-8859-1
>> X-Google-AttachSize: 2025
>> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
>> <html>
>> <head>
>> <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
>> </head>
>> <body bgcolor="#ffffff" text="#000000">
>> <a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:bcumminsr@.yahoo.com">bcumminsr@.yahoo.com</a> wrote:
>> <blockquote
>> cite="mid1153259948.748817.240020@.m73g2000cwd.googlegroups.com"
>> type="cite">
>> <pre wrap="">I am not a DBA and it will be obvious to you soon. :) I have two
>> databases on the same server. I want to basically restore a db from an
>> existing db on the same server. The catch is that I don't want to
>> restore from a backup file and I have to enable this process to run by
>> the class instructor through an ASP page calling a stored proc. Due to
>> time limitations between training classes I would just like to copy the
>> .MDF and .LDF from the Source db to the destination db and attach it.
>> I was trying the code below:
>> RESTORE DATABASE train
>> FROM train_devo
>> WITH RECOVERY,
>> MOVE 'c:\train_devo_data.mdf' TO 'c:\train_data.mdf',
>> MOVE 'c:\train_devo_log.ldf' TO 'c:\train_log.ldf'
>> GO
>> I am getting the error:
>> Server: Msg 3206, Level 16, State 1, Line 1
>> No entry in sysdevices for backup device 'train_devo'. Update
>> sysdevices and rerun statement.
>> Server: Msg 3013, Level 16, State 1, Line 1
>> RESTORE DATABASE is terminating abnormally.
>> I know that I will need to kill active processes and that my record
>> doesn't exist in the sysdevices table but I don't have enough time
>> right now to study this completely.
>> Any help is greatly appreciated.
>> Thanks
>> Ben
>> </pre>
>> </blockquote>
>> Hi 
>> 
>> You can use BACKUP/RESTORE as suggested by Keith, or you can detach the
>> existing database, copy the files, and then reattach the db's again. 
>> 
>> Try to look up RESTORE and sp_detach_db/sp_attach_db in Books On Line
>> for details. 
>> 
>> 
>> -- 
>> Regards 
>> Steen Schlüter Persson 
>> Databaseadministrator / Systemadministrator 
>> 
>> </body>
>> </html>
>> --030204050507050003020602--
> Thanks for all of your help. I need to do this using T-SQL and I can't
> get the RESTORE command to work. Is it possible to copy the MDF and LDF
> using T-SQL to a different location? Thanks
>
You'll have to give us some more than just "it doesn't work". If you
want to copy the files using T-sql, you can look up xp_cmdshell in Books
On Line.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator
--050402070308080300020405
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:bcumminsr@.yahoo.com">bcumminsr@.yahoo.com</a> wrote:
<blockquote
cite="mid1153327247.825744.46880@.75g2000cwc.googlegroups.com"
type="cite">
<pre wrap="">Steen Persson (DK) wrote:
</pre>
<blockquote type="cite">
<pre wrap=""><a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:bcumminsr@.yahoo.com">bcumminsr@.yahoo.com</a> wrote:
</pre>
<blockquote type="cite">
<pre wrap="">I am not a DBA and it will be obvious to you soon. :) I have two
databases on the same server. I want to basically restore a db from an
existing db on the same server. The catch is that I don't want to
restore from a backup file and I have to enable this process to run by
the class instructor through an ASP page calling a stored proc. Due to
time limitations between training classes I would just like to copy the
.MDF and .LDF from the Source db to the destination db and attach it.
I was trying the code below:
RESTORE DATABASE train
FROM train_devo
WITH RECOVERY,
MOVE 'c:\train_devo_data.mdf' TO 'c:\train_data.mdf',
MOVE 'c:\train_devo_log.ldf' TO 'c:\train_log.ldf'
GO
I am getting the error:
Server: Msg 3206, Level 16, State 1, Line 1
No entry in sysdevices for backup device 'train_devo'. Update
sysdevices and rerun statement.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I know that I will need to kill active processes and that my record
doesn't exist in the sysdevices table but I don't have enough time
right now to study this completely.
Any help is greatly appreciated.
Thanks
Ben
</pre>
</blockquote>
<pre wrap="">Hi
You can use BACKUP/RESTORE as suggested by Keith, or you can detach the
existing database, copy the files, and then reattach the db's again.
Try to look up RESTORE and sp_detach_db/sp_attach_db in Books On Line
for details.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator
--030204050507050003020602
Content-Type: text/html; charset=ISO-8859-1
X-Google-AttachSize: 2025
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<a class="moz-txt-link-abbreviated" href=<a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:bcumminsr@.yahoo.com">"mailto:bcumminsr@.yahoo.com"</a>><a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:bcumminsr@.yahoo.com">bcumminsr@.yahoo.com</a></a> wrote:
<blockquote
cite=<a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:mid1153259948.748817.240020@.m73g2000cwd.googlegroups.com">"mid1153259948.748817.240020@.m73g2000cwd.googlegroups.com"</a>
type="cite">
<pre wrap="">I am not a DBA and it will be obvious to you soon. :) I have two
databases on the same server. I want to basically restore a db from an
existing db on the same server. The catch is that I don't want to
restore from a backup file and I have to enable this process to run by
the class instructor through an ASP page calling a stored proc. Due to
time limitations between training classes I would just like to copy the
.MDF and .LDF from the Source db to the destination db and attach it.
I was trying the code below:
RESTORE DATABASE train
FROM train_devo
WITH RECOVERY,
MOVE 'c:\train_devo_data.mdf' TO 'c:\train_data.mdf',
MOVE 'c:\train_devo_log.ldf' TO 'c:\train_log.ldf'
GO
I am getting the error:
Server: Msg 3206, Level 16, State 1, Line 1
No entry in sysdevices for backup device 'train_devo'. Update
sysdevices and rerun statement.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I know that I will need to kill active processes and that my record
doesn't exist in the sysdevices table but I don't have enough time
right now to study this completely.
Any help is greatly appreciated.
Thanks
Ben
</pre>
</blockquote>
Hi 
 
You can use BACKUP/RESTORE as suggested by Keith, or you can detach the
existing database, copy the files, and then reattach the db's again. 
 
Try to look up RESTORE and sp_detach_db/sp_attach_db in Books On Line
for details. 
 
 
-- 
Regards 
Steen Schlüter Persson 
Databaseadministrator / Systemadministrator 

</body>
</html>
--030204050507050003020602--
</pre>
</blockquote>
<pre wrap=""><!-->
Thanks for all of your help. I need to do this using T-SQL and I can't
get the RESTORE command to work. Is it possible to copy the MDF and LDF
using T-SQL to a different location? Thanks
</pre>
</blockquote>
You'll have to give us some more
than just "it doesn't work". If you want to copy the files using T-sql,
you can look up xp_cmdshell in Books On Line. 
 
 
-- 
Regards 
Steen Schlüter Persson 
Databaseadministrator / Systemadministrator 

</body>
</html>
--050402070308080300020405--

Thursday, March 22, 2012

Copy Database Wizard on Vista x64 machine

I've got a new Vista x64 build with SQL2005 (+SP2) freshly installed. I'm
now trying to copy existing databased from other services but I can't get it
to work. I get the following error logged in the Event Viewer Application
Log:
"Log Name: Application
Source: Application Error
Date: 29/11/2007 16:16:56
Event ID: 1000
Task Category: (100)
Level: Error
Keywords: Classic
User: N/A
Computer: mycomputer
Description:
The description for Event ID 1000 from source Application Error cannot be
found. Either the component that raises this event is not installed on your
local computer or the installation is corrupted. You can install or repair
the component on the local computer.
If the event originated on another computer, the display information had to
be saved with the event.
The following information was included with the event:
DTExec.exe
2005.90.3042.0
45cd7539
kernel32.dll
6.0.6000.16386
4549d328
e053534f
00000000000233ac
The substitution string for insert index (%1) could not be found"
I cant find anything online that enlightens me - I know that Vista needs SP
installed to work, so I've installed it:
@.@.Version=
Microsoft SQL Server 2005 - 9.00.3054.00 (X64)
Mar 23 2007 18:41:50
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.0 (Build 6000: )
All the services are running under a domain user that has access to the
areas where the data is stored, both source and destination, so I'm hoping
it's not a permissions issue - if it is, I wouldnt know where to look.
I *could* copy a local database to the same server (renamed of course), so
it suggests that the server is largely OK - does this suggest it *is* a
permissions issue?
The domain user that the services are running under are not in the local
Administrators group, but they are in the
SQLServer2005MSSQLUser$MyComputer$MSSQLS
ERVER group - which I assume is the
group that was set up by the SQL Server Provisioning Tool for Vista.
This is a development machine so I frequently want to copy different
versions of different databases backwards and forwards.
Any suggestions? Any other Vista x64-ers out there?
Thanks in advance...
ChrisSQL Server uses Active Directory accounts and groups for its
permissions.
Active Directory accounts and groups are represented by GUIDs that are
unique to a particular Active Directory Domain which is run by a
Primary Domain Contoroller. If your new Vista machine is not part of
a network, or is on a different domain than the old one, then it
probably uses different GUIDs for the active directory accounts than
the source machine did. If you copied over the SQL Server database
using a backup, then you probably also copied all the old GUIDs for
the SQL Server accounts that point to the GUIDs on the old machine,
and not the ones on new machine.
This means that your SQL Server accounts are out of sync with active
directory. you may have to recreate your SQL Server accounts on the
new machine.
Permissions are assigned through SQL Server Management Studio in two
steps. First, you assign the active directory accounts that are
allowed to connect to a SQL Server instance. This is done by right
clicking the Logins folder under the Security folder of the SQL Server
instance the user is to connect to and selecting New Login.
Then, you assign what SQL Server instance users are allowed to connect
to a particular database within an instance. This is done by right
clicking the Security folder under the database you want to give
permission for and selecting New->user.
In both cases, an extensive permissions dialog will open. For the
Login folder, you only have to specify an active directory account.
For the Security folder, you have to click securables in the left
sidebar, add the entities you want to view (tables), and then set
select, reference, update, delete, and insert permissions. There are
more steps, but this should point you in the right direction.|||Andy, thanks for your reply...
"Andy" <anedza@.infotek-consulting.com> wrote in message
news:1ee305c9-338f-4a4f-9a42-198b42c597b5@.w40g2000hsb.googlegroups.com...
> If your new Vista machine is not part of
> a network, or is on a different domain than the old one,
>
My new machine is part of the same Domain and is recognised within Active
Directory.
> Permissions are assigned through SQL Server Management Studio in two
> steps. First, you assign the active directory accounts that are
> allowed to connect to a SQL Server instance. This is done by right
> clicking the Logins folder under the Security folder of the SQL Server
> instance the user is to connect to and selecting New Login.
Each human administrator is per of an SQL Admins group as are each of the
domain users that the different servers run under. On each server, the SQL
Admins group is added with a login and has sufficiend permissions (AFAIK).

> Then, you assign what SQL Server instance users are allowed to connect
> to a particular database within an instance. This is done by right
> clicking the Security folder under the database you want to give
> permission for and selecting New->user.
>
Done

> In both cases, an extensive permissions dialog will open. For the
> Login folder, you only have to specify an active directory account.
> For the Security folder, you have to click securables in the left
> sidebar, add the entities you want to view (tables), and then set
> select, reference, update, delete, and insert permissions. There are
> more steps, but this should point you in the right direction.
>
The login is set as a sysdamin so surely this shouldnt be necessary?
Somewhere in my tweaking, I've changed something. I *think* it's for the
better - I'm still getting errors but I'm getting further along.
I'm now getting Error 12550: Access to the path
'\\MyServer\MyShare\MyDB.mdf' is denied. The SQL Admins group has read/write
permissions to this area so I'm puzzled.
This is immediately followed by another Error 12550: Object reference not
set to an instance of an object. There are other errors further on, but I
think they are just as a consequence of these earlier errors.
I'm actually going to start copying backups because I need to crack on with
things, but I'm determined to get to the bottom of this. In my work, this
wizard is very useful so I'd rather not have to work around it.
Any further ideas?
Cheers
Chris

Copy Database Wizard on Vista x64 machine

I've got a new Vista x64 build with SQL2005 (+SP2) freshly installed. I'm
now trying to copy existing databased from other services but I can't get it
to work. I get the following error logged in the Event Viewer Application
Log:
"Log Name: Application
Source: Application Error
Date: 29/11/2007 16:16:56
Event ID: 1000
Task Category: (100)
Level: Error
Keywords: Classic
User: N/A
Computer: mycomputer
Description:
The description for Event ID 1000 from source Application Error cannot be
found. Either the component that raises this event is not installed on your
local computer or the installation is corrupted. You can install or repair
the component on the local computer.
If the event originated on another computer, the display information had to
be saved with the event.
The following information was included with the event:
DTExec.exe
2005.90.3042.0
45cd7539
kernel32.dll
6.0.6000.16386
4549d328
e053534f
00000000000233ac
The substitution string for insert index (%1) could not be found"
I cant find anything online that enlightens me - I know that Vista needs SP
installed to work, so I've installed it:
@.@.Version= Microsoft SQL Server 2005 - 9.00.3054.00 (X64)
Mar 23 2007 18:41:50
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.0 (Build 6000: )
All the services are running under a domain user that has access to the
areas where the data is stored, both source and destination, so I'm hoping
it's not a permissions issue - if it is, I wouldnt know where to look.
I *could* copy a local database to the same server (renamed of course), so
it suggests that the server is largely OK - does this suggest it *is* a
permissions issue?
The domain user that the services are running under are not in the local
Administrators group, but they are in the
SQLServer2005MSSQLUser$MyComputer$MSSQLSERVER group - which I assume is the
group that was set up by the SQL Server Provisioning Tool for Vista.
This is a development machine so I frequently want to copy different
versions of different databases backwards and forwards.
Any suggestions? Any other Vista x64-ers out there?
Thanks in advance...
ChrisSQL Server uses Active Directory accounts and groups for its
permissions.
Active Directory accounts and groups are represented by GUIDs that are
unique to a particular Active Directory Domain which is run by a
Primary Domain Contoroller. If your new Vista machine is not part of
a network, or is on a different domain than the old one, then it
probably uses different GUIDs for the active directory accounts than
the source machine did. If you copied over the SQL Server database
using a backup, then you probably also copied all the old GUIDs for
the SQL Server accounts that point to the GUIDs on the old machine,
and not the ones on new machine.
This means that your SQL Server accounts are out of sync with active
directory. you may have to recreate your SQL Server accounts on the
new machine.
Permissions are assigned through SQL Server Management Studio in two
steps. First, you assign the active directory accounts that are
allowed to connect to a SQL Server instance. This is done by right
clicking the Logins folder under the Security folder of the SQL Server
instance the user is to connect to and selecting New Login.
Then, you assign what SQL Server instance users are allowed to connect
to a particular database within an instance. This is done by right
clicking the Security folder under the database you want to give
permission for and selecting New->user.
In both cases, an extensive permissions dialog will open. For the
Login folder, you only have to specify an active directory account.
For the Security folder, you have to click securables in the left
sidebar, add the entities you want to view (tables), and then set
select, reference, update, delete, and insert permissions. There are
more steps, but this should point you in the right direction.|||Andy, thanks for your reply...
"Andy" <anedza@.infotek-consulting.com> wrote in message
news:1ee305c9-338f-4a4f-9a42-198b42c597b5@.w40g2000hsb.googlegroups.com...
> If your new Vista machine is not part of
> a network, or is on a different domain than the old one,
>
My new machine is part of the same Domain and is recognised within Active
Directory.
> Permissions are assigned through SQL Server Management Studio in two
> steps. First, you assign the active directory accounts that are
> allowed to connect to a SQL Server instance. This is done by right
> clicking the Logins folder under the Security folder of the SQL Server
> instance the user is to connect to and selecting New Login.
Each human administrator is per of an SQL Admins group as are each of the
domain users that the different servers run under. On each server, the SQL
Admins group is added with a login and has sufficiend permissions (AFAIK).
> Then, you assign what SQL Server instance users are allowed to connect
> to a particular database within an instance. This is done by right
> clicking the Security folder under the database you want to give
> permission for and selecting New->user.
>
Done
> In both cases, an extensive permissions dialog will open. For the
> Login folder, you only have to specify an active directory account.
> For the Security folder, you have to click securables in the left
> sidebar, add the entities you want to view (tables), and then set
> select, reference, update, delete, and insert permissions. There are
> more steps, but this should point you in the right direction.
>
The login is set as a sysdamin so surely this shouldnt be necessary?
Somewhere in my tweaking, I've changed something. I *think* it's for the
better - I'm still getting errors but I'm getting further along.
I'm now getting Error 12550: Access to the path
'\\MyServer\MyShare\MyDB.mdf' is denied. The SQL Admins group has read/write
permissions to this area so I'm puzzled.
This is immediately followed by another Error 12550: Object reference not
set to an instance of an object. There are other errors further on, but I
think they are just as a consequence of these earlier errors.
I'm actually going to start copying backups because I need to crack on with
things, but I'm determined to get to the bottom of this. In my work, this
wizard is very useful so I'd rather not have to work around it.
Any further ideas?
Cheers
Chrissql sql

Tuesday, March 20, 2012

Copy Database Wizard : where is it?

I need to copy an existing database within my SQL server 2005 to a new database within the same SQL Server instance. I've read a lot about the Copy Database Wizard but can't see this option within any menu of SQL Server 2005 Management Studio. As far as I know this option should be shown after right clicking at a database and selecting of 'Tasks'. It shows other options as Detach, Back-up, Restore but not he Copy function. I also don't see any wizard option when selecting 'Management'. I've already updated to SQL Server 2005 SP2 but that didn't help.

I've installed MS SQL Server 2005 from my MSDN-Professional subscription. After installation, while SQL Server Management Studio is shown without an 'Express' addition, it has named the SQL Server instance as Servername\SQLExpress which I find confusing. The full name of SQL Server Management Studio is shown with the version number 9.0.3042 which ensures that it is SQL Server 2005 SP2 version and not the Express edition.

Do I need to install something additionaly to display the Copy Database Wizard? Very frustrating that this option is not shown and there is no documentation how to toget it. Is this maybe a known bug?

regards

Oscar

Copy Database should be the last item on the Tasks menu.

On my machine with developer edition:

SELECT @.@.VERSION;

SELECT SERVERPROPERTY('Edition ');

Returns: Developer Edition

|||

In my case:

SELECT @.@.VERSION;

Returns : Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Express Edition with Advanced Services on Windows NT 5.1 (Build 2600: Service Pack 2)

SELECT SERVERPROPERTY('Edition ');

Returns : Express Edition with Advanced Services

So it looks like I've installed the Express edition. This is hard to believe since I used my Official MSDN Subscription to install SQL Server 2005. Important to inform, I even can't update the Manager Studio with the SQL Server Studio Express SP2 since it reports that the Express edition has not been installed.

Besides, As I've noticed, The SQL Server 2005 Express users have this Copy Database option and in my case it doesn't show this menu option.

Where did you got your Developer edition from?

|||

I have an MSDN subscription I can't remember the new name...it replaced universal.

You can download the trial edition from:

http://www.microsoft.com/sql/downloads/trial-software.mspx

|||

As I already own an MSDN subscription why should I download a trial edition?

Can anyone reply whether the Express edition lacks the Copy Database option like my case?

|||

Oscar99 wrote:
Can anyone reply whether the Express edition lacks the Copy Database option like my case?

Correct, the Express edition does not have the Copy Database Wizard. If you have an MSDN subscription you can download SQL Server Developer Edition. If you don't, you can buy it for $50.

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

|||

i don't think so

In my case:

SELECT @.@.VERSION;

SELECT SERVERPROPERTY('Edition ');

Returns: Developer Edition

but i ca'nt see this option

|||

Thanks Paul,

Finally everything clear now.

Meanwhile I've reinstalled my MSDN july 2006 version and now the option for CDW shows up! As to MS I don't understand why MS removes such basic and simple functions within the Express edition.

regards,

Oscar

|||I upgraded a SQLExpress instance to Standard and the copy command did not come with it. It was enough of a reason for me to uninstall Express and reinstall Standard and restore the pertinent dbs. What a hassle. I'd call that a bug.

Thursday, March 8, 2012

Copy Database

Is there a way to copy a database using TSQL?

When we set up a new customer we would like to make a copy of the existing database and set up a new database for them. Is there a way to programmatically do this?

There are different ways you could approach this depending on your needs. If one day of data latency isn't an issue, you could just restore a nightly backup of the database in question as a new database name. You could setup a stored procedure to receive variables for database name, physical file name, and any other specific options. Then parse and execute your restore statement.

http://msdn2.microsoft.com/en-us/library/ms186858(d=ide).aspx

|||I was looking for a more direct approach. I know there is the Copy Database wizard. Are there commands that I can use that use the SMO version of that wizard?|||You can use the "VS2003 setup project" to deploy the database to the client. VS 2005 has better support for these kind of scenario.|||

One easy way to set it up (if you have two machines runing SQL Server) is to use the Copy Database Wizard and save either the DTS in the database or save to a script. Then I think you might be able to tweak the script to modify the target database name and filenames.

I haven't done this before so no guarantees. Once you had it setup though as a DTS, you could use Global variables to pass in the databasename and and filenames. You could then execute the DTS from T-Sql and pass in the variables.

Otherwise, I think you can use SMO although it will take looking closely to see the best path. Even using SMO, the easiest way may end up being scripting a backup and restore of the database. It seems to me that it is the cleanest option.

|||We dont' want to "deploy" a database. It need to be a SQL SP that we can run that will copy an existing "base" database in our server to a new database.|||

The copy database wizard has no facility to create a script.

This needs to be something that can be run through the application by an administrator.

|||At the end of the wizard, you can set it to run on a schedule instead of immediately. In the schedule, just set it for a previous date so it won't ever run. Then it will create the copy package for you.|||

That was a great idea....BUT...the script button is disabled.

|||

1) just back up the original database and make a copy

2) Create a new database

3) Then use attach and dettach command

EXEC sp_attach_db @.dbname = N'AdventureWorks',
@.filename1 = N'c:\Program Files\Microsoft SQL Server
\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf',
@.filename2 = N'c:\Program Files\Microsoft SQL Server
\MSSQL.1\MSSQL\Data\AdventureWorks_log.ldf' ;

just set the parameters..all these you can do in one SP.

Copy database

Hello,
hope this is the correct group.
I am looking for an easy and safe way to create a copy of an existing live
database and put it on the same SQL Server in a test database.
Has anyone done this before?
I am not an experienced SQL admin and will really appreciate your help!
TIA!
other than what Paul mentioned you can use DTS to push it over. DTS allows
you to select a subset of the objects or data.
With a backup restore you will get an entire copy. Sometimes for space
issues this is not ideal.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Jane" <Jane@.discussions.microsoft.com> wrote in message
news:FB20DAC9-0F80-4AF6-AA6C-BADB0CAFB9B7@.microsoft.com...
> Hello,
> hope this is the correct group.
> I am looking for an easy and safe way to create a copy of an existing live
> database and put it on the same SQL Server in a test database.
> Has anyone done this before?
> I am not an experienced SQL admin and will really appreciate your help!
> TIA!
|||Thank you, Paul and Hilary.
It happened I dropped 3 tables from test db, then I copied them from
production to test db, but of course, having all data in place, they miss the
dependancies.. Is there a way besides recreating(copying) the whole db, to
roll back all these transactions?
"Paul Ibison" wrote:

> The simplest way is backup and restore (details in books
> on line). You can restore the database to another name
> and location than the original. If the database has to
> have the same name (due to eg 3 part naming convention
> being used in queries), you can restore to another
> instance of sql server on the same computer.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||It depends on the recovery model. If you are in full or
bulk logged, then backup your current transaction log
then do a point-in-time restore back to just before you
dropped the tables. Of course any other work done since
then will also be removed.
If this is not viable, you could create a script of the
dependencies (I assume you mean FKs) from thje live
system and apply it to the test system. Such a script can
be generated by Enterprise Manager by right-clicking on
the relevant tables and selecting all tasks, generate sql
script, with the PK/FK option selected (final tab).
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
I have full recovery model. How would I start a point-in-time restore?
"Paul Ibison" wrote:

> It depends on the recovery model. If you are in full or
> bulk logged, then backup your current transaction log
> then do a point-in-time restore back to just before you
> dropped the tables. Of course any other work done since
> then will also be removed.
> If this is not viable, you could create a script of the
> dependencies (I assume you mean FKs) from thje live
> system and apply it to the test system. Such a script can
> be generated by Enterprise Manager by right-clicking on
> the relevant tables and selecting all tasks, generate sql
> script, with the PK/FK option selected (final tab).
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Jane,
I'm assuming you have a full database backup, as this won't work without it.
You need to restore this backup and any backups you have of logs. If you
haven't done any, then it'll just be the backup you make of your live log.
Each restore uses a similar syntax and is done with NORECOVERY, apart from
the last which is done with RECOVERY. For the log which has the 3 table
deletes in it, you'll be using the syntax STOP AT datetime
eg this restores the database and 2 logs, with the senond stopped at a
certain time:
RESTORE DATABASE MyNwind FROM MyNwind_1 WITH NORECOVERYRESTORE LOG
MyNwind FROM MyNwindLog1 WITH NORECOVERYRESTORE LOG MyNwind FROM
MyNwindLog2 WITH RECOVERY, STOPAT = 'Apr 15, 1998 12:00 AM'See this
reference for more
info:http://msdn.microsoft.com/library/de...y/en-us/tsqlre
f/ts_ra-rz_25rm.asp HTH, Paul Ibison SQL Server MVP,
www.replicationanswers.com (recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thank you, Paul.
It turned out we do not backup a test database, so I went on and created a
script of the dependencies from the live system table with all "Table
Scripting Options". How would I apply the script to the table int test db?
"Paul Ibison" wrote:

> Jane,
> I'm assuming you have a full database backup, as this won't work without it.
> You need to restore this backup and any backups you have of logs. If you
> haven't done any, then it'll just be the backup you make of your live log.
> Each restore uses a similar syntax and is done with NORECOVERY, apart from
> the last which is done with RECOVERY. For the log which has the 3 table
> deletes in it, you'll be using the syntax STOP AT datetime
> eg this restores the database and 2 logs, with the senond stopped at a
> certain time:
> RESTORE DATABASE MyNwind FROM MyNwind_1 WITH NORECOVERYRESTORE LOG
> MyNwind FROM MyNwindLog1 WITH NORECOVERYRESTORE LOG MyNwind FROM
> MyNwindLog2 WITH RECOVERY, STOPAT = 'Apr 15, 1998 12:00 AM'See this
> reference for more
> info:http://msdn.microsoft.com/library/de...y/en-us/tsqlre
> f/ts_ra-rz_25rm.asp HTH, Paul Ibison SQL Server MVP,
> www.replicationanswers.com (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Just open up query analyser, log onto the test system,
move to the test database, paste the script and hit F5.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
I am uncertain about Formatting options when generating script:
- Do I really need the Generate CREATE/DROP commands? The table already
exists (I imported it from production db). Or should I drop and recreate the
table through the script - maybe this way it will have all the missing
dependancies?
Then I will import the data into table?
Thank you so much for hanging in there with me!
- I probably do need "scripts for all dependent objects"
Do you think it
"Paul Ibison" wrote:

> Just open up query analyser, log onto the test system,
> move to the test database, paste the script and hit F5.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Jane,
as you have the (3) empty tables on the test db, running
the script will just drop and recreate them, but along
with the extra constraints you need. Check that you only
have the (3) table scripts you need for this and not any
others. If in any doubt, post up the scripts and please
explain what I'm missing and I'll edit them and post them
back.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

copy database

I am trying to create a copy of an existing sql database. I want to run a
live version and a test version so that when I am coding the changes to my
application I can use the test version without impacting the live version.
I have tried scripting and I can reproduce the database structure; however,
when I try to import data it gives errors that are very cryptic. I would
think that since the data is from the database that was scripted it would be
ok.
Any thoughts or suggestions to accomplish this seemingly easy task.
WBI would start with a full backup of the original and restore it under a
different name but here are your choices:
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=274463 Copy DB
Wizard issues
Andrew J. Kelly
SQL Server MVP
"WB" <none> wrote in message news:Ojb4lFX0DHA.3196@.TK2MSFTNGP11.phx.gbl...
> I am trying to create a copy of an existing sql database. I want to run a
> live version and a test version so that when I am coding the changes to my
> application I can use the test version without impacting the live version.
> I have tried scripting and I can reproduce the database structure;
however,
> when I try to import data it gives errors that are very cryptic. I would
> think that since the data is from the database that was scripted it would
be
> ok.
> Any thoughts or suggestions to accomplish this seemingly easy task.
> WB
>

Copy Data Issue

I need to create a new field and copy over an existing field from the same table. Since this table is about 3 million rows. It will take about 1 hour 30 minutes to complete.

Any good way around?

Thanks!

-J8

-------------
UPDATE Customer SET ColB = ColA WHERE ColA IS NOT NULLWhat (if any) point is there to this exercise?

-PatP|||Check this (http://www.dbforums.com/showthread.php?&p=3687532) thread out.|||The New column ColB will become a key column for sorting and also accept new kind of data while orig. ColA column continually receive data as it was. the historical data need to be copy over for sorting ...

Saturday, February 25, 2012

Copy a Table

In SQL Server 2000, what is the easiest way to make an exact copy of an existing table?
Copy is in the popup menu if you right click on a table but there's no PasteOriginally posted by wey97
In SQL Server 2000, what is the easiest way to make an exact copy of an existing table?

Copy is in the popup menu if you right click on a table but there's no Paste|||[QUOTE][SIZE=1]Originally posted by wey97
In SQL Server 2000, what is the easiest way to make an exact copy of an existing table?

You can do this:

SELECT *
INTO B
FROM A

Notes:
B = TABLE NAME of COPY
A = TABLE NAME of ORIGINAL|||Originally posted by wey97
In SQL Server 2000, what is the easiest way to make an exact copy of an existing table?

Copy is in the popup menu if you right click on a table but there's no Paste

You can do paste in QA (shift+Insert or Cntrl+V)|||To copy only the structure with no data (minimally logged)

select * into newtable from oldtable where 0=1

To copy only the structure with data (minimally logged)

select * into newtable from oldtable

To copy data into an existing table (Fully Logged)

insert into newtable select * from oldtable

To Copy data into an existing table where it doesn't already exist (logged)

insert into newtable select * from oldtable where PK not in (select * from newtable)

HTH|||rhigdon: don't do NOT IN, you'll kill everybody else and possibly the server as well. do left outer join on key_field where key_field is null|||I've heard that before, I'll have to test the IO difference. Have you tested it?|||Right click the database then alltasks->export data
A wizard will appear...
Select the table, put the correct source and destination and at the end choose to drop the existing destination object and not append the data.

It's easy and simple. You can also select to mantain permissions, indexes, etc

Paulo

Originally posted by wey97
In SQL Server 2000, what is the easiest way to make an exact copy of an existing table?

Copy is in the popup menu if you right click on a table but there's no Paste|||yup, many times, plus evidence presented by others, plus ms white papers.|||I'm pretty sure you are right, going to do some testing but if you could share any of those white papers I would appreciate it.|||Well, you can't use:

where PK not in (select * from newtable)

anyway.

I'm sure you meant either:
where PK not in (select PK from newtable)
or:
where not exists (select * from oldtable where old.PK = new.PK)

...both of which generate the same optimizer plan, and oddly enough they ran slightly (insignificantly even) faster than the left outer join method, which threw in an extra step for filtering. Maybe because my test table only had about 20,000 rows.

I've use all three methods and never killed a database with them, but I would be interested in reading any MS white papers as well.

blindman|||I believe you are right, Blindman. MS has been tinkering with this part of the query optimizer, and I think the query plans are largely the same. Imagine my surprise when I was about to storm into a developer's cube to have him re-write such a query, when my own re-write did nothing to help.|||Well, the left join method did use a slightly different plan than the other two, so it could potentially be more (or less) efficient for larger tables. If anybody feels the urge to experiment please post the results.

blindman|||Yes BM, I meant

where PK not in (select PK from newtable)

Well, I am a little confused as I rewrote a query that did a NOT IN query to a 1.4 million row table from a 1000 row table and it cost me a total of 3014 logical reads, when I rewrote it in a left outer join it cost me 3126 logical reads so either I am doing something wrong or the difference is insignificant.

I have searched pretty hard but have been unable to find any MS whitepapers discussing this (although I did find an interesting book in my search that I have now ordered!)|||white papers, articles...this issue goes all the way to 6.0 times where non-clustered field was challenged with IN/NOT IN and the optimizer would revert to table scan in both outer and inner queries. it is possible that in the case of the PK, unless it is non-clustered, not in and let outer join would yield the same result, or close to be the same. this is not the point. the point is that IN clause is less preferrable than exists or left outer join. any more requests for articles?|||I agree with Mcrowley in that I beleive this used to be the case but is no longer true. Not sure what you mean by "any more requests for articles" but I take it to mean you don't want to continue this discussion so guess we'll leave it at that...|||I agree with Mcrowley in that I beleive this used to be the case but is no longer true. Not sure what you mean by "any more requests for articles" but I take it to mean you don't want to continue this discussion so guess we'll leave it at that...

Copy a row in SQL SERVER 2000

Hello all,
I'd like to INSERT a new row based upon a SELECT of an existing row,
I've tried the below but there's a syntax error:
INSERT INTO TableA VALUES (SELECT * FROM TableA WHERE TableA.PK = 13185)
Also, would this work as it will try to INSERT a field with a
duplicate PK. Any suggesting on how this could be best achived?
Thanks,
JYJon
You almost got it
INSERT INTO TableA SELECT * FROM TableA WHERE TableA.PK = 13185
> Also, would this work as it will try to INSERT a field with a
> duplicate PK. Any suggesting on how this could be best achived?
No it would not. Add an IDENTITY property as a surrogate key
"Jon" <JonMYates@.gmail.com> wrote in message
news:1178702349.126759.189880@.l77g2000hsb.googlegroups.com...
> Hello all,
> I'd like to INSERT a new row based upon a SELECT of an existing row,
> I've tried the below but there's a syntax error:
> INSERT INTO TableA VALUES (SELECT * FROM TableA WHERE TableA.PK => 13185)
> Also, would this work as it will try to INSERT a field with a
> duplicate PK. Any suggesting on how this could be best achived?
> Thanks,
> JY
>|||On 9 May, 10:19, Jon <JonMYa...@.gmail.com> wrote:
> Hello all,
> I'd like to INSERT a new row based upon a SELECT of an existing row,
> I've tried the below but there's a syntax error:
> INSERT INTO TableA VALUES (SELECT * FROM TableA WHERE TableA.PK => 13185)
> Also, would this work as it will try to INSERT a field with a
> duplicate PK. Any suggesting on how this could be best achived?
> Thanks,
> JY
Just leave out VALUES and the brackets:
INSERT INTO TableA (col1, col2, col3)
SELECT col1, col2, col3 FROM ...
It makes no sense to duplicate an entire row. You need to supply new
values for at least the key column(s) of the table in question.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||INSERT INTO tabelename (the table you want to copy into)
SELECT * from tablename (the table you want to copy from)
WHERE Field10 = (some condition)

Copy a row in SQL SERVER 2000

Hello all,
I'd like to INSERT a new row based upon a SELECT of an existing row,
I've tried the below but there's a syntax error:
INSERT INTO TableA VALUES (SELECT * FROM TableA WHERE TableA.PK =
13185)
Also, would this work as it will try to INSERT a field with a
duplicate PK. Any suggesting on how this could be best achived?
Thanks,
JY
Jon
You almost got it
INSERT INTO TableA SELECT * FROM TableA WHERE TableA.PK = 13185

> Also, would this work as it will try to INSERT a field with a
> duplicate PK. Any suggesting on how this could be best achived?
No it would not. Add an IDENTITY property as a surrogate key
"Jon" <JonMYates@.gmail.com> wrote in message
news:1178702349.126759.189880@.l77g2000hsb.googlegr oups.com...
> Hello all,
> I'd like to INSERT a new row based upon a SELECT of an existing row,
> I've tried the below but there's a syntax error:
> INSERT INTO TableA VALUES (SELECT * FROM TableA WHERE TableA.PK =
> 13185)
> Also, would this work as it will try to INSERT a field with a
> duplicate PK. Any suggesting on how this could be best achived?
> Thanks,
> JY
>
|||On 9 May, 10:19, Jon <JonMYa...@.gmail.com> wrote:
> Hello all,
> I'd like to INSERT a new row based upon a SELECT of an existing row,
> I've tried the below but there's a syntax error:
> INSERT INTO TableA VALUES (SELECT * FROM TableA WHERE TableA.PK =
> 13185)
> Also, would this work as it will try to INSERT a field with a
> duplicate PK. Any suggesting on how this could be best achived?
> Thanks,
> JY
Just leave out VALUES and the brackets:
INSERT INTO TableA (col1, col2, col3)
SELECT col1, col2, col3 FROM ...
It makes no sense to duplicate an entire row. You need to supply new
values for at least the key column(s) of the table in question.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||INSERT INTO tabelename (the table you want to copy into)
SELECT * from tablename (the table you want to copy from)
WHERE Field10 = (some condition)

Copy a row in SQL SERVER 2000

Hello all,
I'd like to INSERT a new row based upon a SELECT of an existing row,
I've tried the below but there's a syntax error:
INSERT INTO TableA VALUES (SELECT * FROM TableA WHERE TableA.PK =
13185)
Also, would this work as it will try to INSERT a field with a
duplicate PK. Any suggesting on how this could be best achived?
Thanks,
JYJon
You almost got it
INSERT INTO TableA SELECT * FROM TableA WHERE TableA.PK = 13185

> Also, would this work as it will try to INSERT a field with a
> duplicate PK. Any suggesting on how this could be best achived?
No it would not. Add an IDENTITY property as a surrogate key
"Jon" <JonMYates@.gmail.com> wrote in message
news:1178702349.126759.189880@.l77g2000hsb.googlegroups.com...
> Hello all,
> I'd like to INSERT a new row based upon a SELECT of an existing row,
> I've tried the below but there's a syntax error:
> INSERT INTO TableA VALUES (SELECT * FROM TableA WHERE TableA.PK =
> 13185)
> Also, would this work as it will try to INSERT a field with a
> duplicate PK. Any suggesting on how this could be best achived?
> Thanks,
> JY
>|||On 9 May, 10:19, Jon <JonMYa...@.gmail.com> wrote:
> Hello all,
> I'd like to INSERT a new row based upon a SELECT of an existing row,
> I've tried the below but there's a syntax error:
> INSERT INTO TableA VALUES (SELECT * FROM TableA WHERE TableA.PK =
> 13185)
> Also, would this work as it will try to INSERT a field with a
> duplicate PK. Any suggesting on how this could be best achived?
> Thanks,
> JY
Just leave out VALUES and the brackets:
INSERT INTO TableA (col1, col2, col3)
SELECT col1, col2, col3 FROM ...
It makes no sense to duplicate an entire row. You need to supply new
values for at least the key column(s) of the table in question.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||INSERT INTO tabelename (the table you want to copy into)
SELECT * from tablename (the table you want to copy from)
WHERE Field10 = (some condition)

Sunday, February 19, 2012

Converting varchar to date

Hi
Not being a coder I have no idea how to do this and existing posts dont
really help.
I have a table called 'Incoming' and a column called dateofbirth. The
format of the column is varchar. The values in the dateof birth column
are 01012000. When searching for particular dates I'm getting crap
results (because the query is crap too). I reckon I need to convert the
values in the column to dates so that my query can work.
All help gratefully accepted.
TIAHi
declare @.dt varchar(20)
set @.dt='01012000'
select
convert(datetime,substring(@.dt,5,4)+substring(@.dt,1,2)+substring(@.dt,3,2),112)
"jjaggii" <richardwsmit@.gmail.com> wrote in message
news:1152864481.073991.8460@.75g2000cwc.googlegroups.com...
> Hi
> Not being a coder I have no idea how to do this and existing posts dont
> really help.
> I have a table called 'Incoming' and a column called dateofbirth. The
> format of the column is varchar. The values in the dateof birth column
> are 01012000. When searching for particular dates I'm getting crap
> results (because the query is crap too). I reckon I need to convert the
> values in the column to dates so that my query can work.
> All help gratefully accepted.
> TIA
>|||Many thanks for that Uri :)
Uri Dimant wrote:
> Hi
> declare @.dt varchar(20)
> set @.dt='01012000'
> select
> convert(datetime,substring(@.dt,5,4)+substring(@.dt,1,2)+substring(@.dt,3,2),112)
>
>
> "jjaggii" <richardwsmit@.gmail.com> wrote in message
> news:1152864481.073991.8460@.75g2000cwc.googlegroups.com...
> > Hi
> > Not being a coder I have no idea how to do this and existing posts dont
> > really help.
> >
> > I have a table called 'Incoming' and a column called dateofbirth. The
> > format of the column is varchar. The values in the dateof birth column
> > are 01012000. When searching for particular dates I'm getting crap
> > results (because the query is crap too). I reckon I need to convert the
> > values in the column to dates so that my query can work.
> > All help gratefully accepted.
> > TIA
> >|||SELECT DATEOFBIRTH = CAST( SUBSTRING(dateofbirth,5,4) + '-' +
SUBSTRING(dateofbirth,1,2) + '-' +
SUBSTRING(dateofbirth,3,2)
AS DATETIME
)
FROM Incoming
M A Srinivas
jjaggii wrote:
> Hi
> Not being a coder I have no idea how to do this and existing posts dont
> really help.
> I have a table called 'Incoming' and a column called dateofbirth. The
> format of the column is varchar. The values in the dateof birth column
> are 01012000. When searching for particular dates I'm getting crap
> results (because the query is crap too). I reckon I need to convert the
> values in the column to dates so that my query can work.
> All help gratefully accepted.
> TIA

Converting varchar to date

Uri Dimant wrote:
[vbcol=seagreen]
> Hi
> declare @.dt varchar(20)
> set @.dt='01012000'
> select
> convert(datetime,substring(@.dt,5,4)+subs
tring(@.dt,1,2)+substring(@.dt,3,2),
112)
>
>
> "jjaggii" <richardwsmit@.gmail.com> wrote in message
> news:1152864481.073991.8460@.75g2000cwc.googlegroups.com...|||SELECT DATEOFBIRTH = CAST( SUBSTRING(dateofbirth,5,4) + '-' +
SUBSTRING(dateofbirth,1,2) + '-' +
SUBSTRING(dateofbirth,3,2)
AS DATETIME
)
FROM Incoming
M A Srinivas
jjaggii wrote:
> Hi
> Not being a coder I have no idea how to do this and existing posts dont
> really help.
> I have a table called 'Incoming' and a column called dateofbirth. The
> format of the column is varchar. The values in the dateof birth column
> are 01012000. When searching for particular dates I'm getting crap
> results (because the query is crap too). I reckon I need to convert the
> values in the column to dates so that my query can work.
> All help gratefully accepted.
> TIA

Converting To ADP, Reference Recommendations

I have been creating Access applications since version 2.0 and may soon
become involved with a project that will convert an existing database
into an ADP. I would consider myself verbose in VBA, but inexperienced
using a SQL server back end. Typically when faced with a new challenge
like this one, I will read technical articles & books plus start
experimenting with code. I am sure there are many of you out there
that understand the issues I will have to confront (it worked in
'just' Access, but now...). I would appreciate recommendations
regarding books, web page links, etc., that will be helpful in my next
project.
Access version 2000 - possibly moving to 2003 next year.
Regards,
Bob<boborta@.hotmail.com> wrote in message
news:1162223068.352845.97040@.i42g2000cwa.googlegroups.com...
> I have been creating Access applications since version 2.0 and may soon
> become involved with a project that will convert an existing database
> into an ADP. I would consider myself verbose in VBA, but inexperienced
> using a SQL server back end. Typically when faced with a new challenge
> like this one, I will read technical articles & books plus start
> experimenting with code. I am sure there are many of you out there
> that understand the issues I will have to confront (it worked in
> 'just' Access, but now...). I would appreciate recommendations
> regarding books, web page links, etc., that will be helpful in my next
> project.
> Access version 2000 - possibly moving to 2003 next year.
> Regards,
Keep in mind that you can use SQL Server as a back-end and continue to use
an access mdb as the front-end. Many developers use this model. You can
still use SQL Server functionality like views, stored procedures, etc.. I
have had difficulties with ADPs and no longer attempt to use them. I also
have questions about the future of ADPs. Others have different views. Google
groups for "access adp pros cons" and you'll see there has been a lot of
discussion on this. Perhaps it has already been decided that this project
will be ADP. If so then good luck.
If the requirement is to move the data to SQL Server but the format of the
front-end is still open I would generally recommend using mdb with linked
tables to SQL Server. A lot would depend on why you are upgrading, the
application itself, the experience of the developers, etc. This is all of
course in my opinion.|||Try the book:
Microsoft Access Developers Guide to SQL Server
by Mary Chipman and Andy Baron. They address a lot of the
issues you will face in the conversion from an Access
developers perspective.
-Sue
On 30 Oct 2006 07:44:28 -0800, "boborta@.hotmail.com"
<boborta@.hotmail.com> wrote:

>I have been creating Access applications since version 2.0 and may soon
>become involved with a project that will convert an existing database
>into an ADP. I would consider myself verbose in VBA, but inexperienced
>using a SQL server back end. Typically when faced with a new challenge
>like this one, I will read technical articles & books plus start
>experimenting with code. I am sure there are many of you out there
>that understand the issues I will have to confront (it worked in
>'just' Access, but now...). I would appreciate recommendations
>regarding books, web page links, etc., that will be helpful in my next
>project.
>Access version 2000 - possibly moving to 2003 next year.
>Regards,
>Bob

Thursday, March 29, 2012

copy of existing table and data

copy of existing table and data

copy of existing table and data

Copy MDF and LDF files Over Existing Database

Copy MDF and LDF files Over Existing Database

Thursday, March 22, 2012

Copy Database Wizard on Vista x64 machine

Copy Database Wizard on Vista x64 machine

Tuesday, March 20, 2012

Copy Database Wizard : where is it?

Thursday, March 8, 2012

Copy Database

Copy database

copy database

Copy Data Issue

Saturday, February 25, 2012

Copy a Table

Copy a row in SQL SERVER 2000

Copy a row in SQL SERVER 2000

Copy a row in SQL SERVER 2000

Sunday, February 19, 2012

Converting varchar to date

Converting varchar to date

Converting To ADP, Reference Recommendations

Converting problem

Blog Archive

About Me