Converting problem: 2012

Thursday, March 29, 2012

Copy Permissions

Hi,
I've copied tables and views from one SQL Server to another, but the
permissions for each user/role are not copied.
How come.
Thanks,
B.Bart
--
EXEC databasename..sp_helpuser 'username'
SELECT OBJECT_NAME(id),*
FROM databasename..syspermissions
WHERE grantee=USER_ID('username')
If you use BACKUP/RESTORE command to copy your tables/views so please
search on internet for "sp_help_revlogin" two stored procedures provided by
MS to copy logins beween SQL Servers
"Bart Steur" <solnews@.xs4all.nl> wrote in message
news:OtwLA6myFHA.460@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've copied tables and views from one SQL Server to another, but the
> permissions for each user/role are not copied.
> How come.
> Thanks,
> B.
>

Copy Permissions

Hi,
I've copied tables and views from one SQL Server to another, but the
permissions for each user/role are not copied.
How come.
Thanks,
B.
Bart
EXEC databasename..sp_helpuser 'username'
SELECT OBJECT_NAME(id),*
FROM databasename..syspermissions
WHERE grantee=USER_ID('username')
If you use BACKUP/RESTORE command to copy your tables/views so please
search on internet for "sp_help_revlogin" two stored procedures provided by
MS to copy logins beween SQL Servers
"Bart Steur" <solnews@.xs4all.nl> wrote in message
news:OtwLA6myFHA.460@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've copied tables and views from one SQL Server to another, but the
> permissions for each user/role are not copied.
> How come.
> Thanks,
> B.
>

Copy paste from Excel - should this work?

Hi,
Now I'm worried as I showed this bodge to a colleague and he is using
it a lot! I found that you can copy paste directly from Excel into the
Enterprise Manager GUI (we're on v8.0, sql server 2000).
The way I do it is say you had some Excel data in cols B-D, highlight A
as well (leaving this column blank), click the arrow on the new row at
the bottom of your table (a table with 3 columns matching B-D) in
Enterprise Manager and voila a paste option appears in the right hand
mouse button menu.
Why? What is this blank column doing? Is it causing any harm
internally to the db to insert nulls (or empty excel strings more
correctly?) in this column?
Any help / warnings much appreciated!
CTHi
I don't seem to be able to get this to happen, maybe I am missing something!
What version of SQL Server are you using and are your client tools at the
same version?
If you need to regularly import files into SQL Server you may want to look
at using DTS to do this, you can have jobs that regularly check any files
dropped into a directory and upload them check out
http://www.sqldts.com/default.aspx for lots of information on how to do this.
John
"cheesey_toastie" wrote:
> Hi,
> Now I'm worried as I showed this bodge to a colleague and he is using
> it a lot! I found that you can copy paste directly from Excel into the
> Enterprise Manager GUI (we're on v8.0, sql server 2000).
> The way I do it is say you had some Excel data in cols B-D, highlight A
> as well (leaving this column blank), click the arrow on the new row at
> the bottom of your table (a table with 3 columns matching B-D) in
> Enterprise Manager and voila a paste option appears in the right hand
> mouse button menu.
> Why? What is this blank column doing? Is it causing any harm
> internally to the db to insert nulls (or empty excel strings more
> correctly?) in this column?
> Any help / warnings much appreciated!
> CT
>|||Hi John,
Its SQL Server 2000 (sp4), the client tools are Enterprise Manager V8.0
I do use the DTS for files but sometimes this is quicker!! As I said
my main concern was accidently showing a user how to do this and now I
wonder what it is doing internally (if anything). I'm confused you cant
replicate this behaviour. We do have a later version of SQL server on
a test environment - I'll try it there and see if it still works.
CT
John Bell wrote:
> Hi
> I don't seem to be able to get this to happen, maybe I am missing something!
> What version of SQL Server are you using and are your client tools at the
> same version?
> If you need to regularly import files into SQL Server you may want to look
> at using DTS to do this, you can have jobs that regularly check any files
> dropped into a directory and upload them check out
> http://www.sqldts.com/default.aspx for lots of information on how to do this.
> John
> "cheesey_toastie" wrote:
> > Hi,
> >
> > Now I'm worried as I showed this bodge to a colleague and he is using
> > it a lot! I found that you can copy paste directly from Excel into the
> > Enterprise Manager GUI (we're on v8.0, sql server 2000).
> >
> > The way I do it is say you had some Excel data in cols B-D, highlight A
> > as well (leaving this column blank), click the arrow on the new row at
> > the bottom of your table (a table with 3 columns matching B-D) in
> > Enterprise Manager and voila a paste option appears in the right hand
> > mouse button menu.
> >
> > Why? What is this blank column doing? Is it causing any harm
> > internally to the db to insert nulls (or empty excel strings more
> > correctly?) in this column?
> >
> > Any help / warnings much appreciated!
> >
> > CT
> >
> >|||Hi
You can check to see if at least one of the file versions the exes for the
tools match the version returned by SELECT @.@.VERSION to see if the tools are
up to date. You may also want to post the ddl for your table.
From what you say an extra column is appearing when you choose paste from
the menus, if after you have pasted and saved the values the table
definition has not changed then I would expect things to be ok.
John
"cheesey_toastie" <bletchley_scum@.yahoo.co.uk> wrote in message
news:1160209997.605041.124600@.m73g2000cwd.googlegroups.com...
> Hi John,
> Its SQL Server 2000 (sp4), the client tools are Enterprise Manager V8.0
> I do use the DTS for files but sometimes this is quicker!! As I said
> my main concern was accidently showing a user how to do this and now I
> wonder what it is doing internally (if anything). I'm confused you cant
> replicate this behaviour. We do have a later version of SQL server on
> a test environment - I'll try it there and see if it still works.
> CT
> John Bell wrote:
>> Hi
>> I don't seem to be able to get this to happen, maybe I am missing
>> something!
>> What version of SQL Server are you using and are your client tools at the
>> same version?
>> If you need to regularly import files into SQL Server you may want to
>> look
>> at using DTS to do this, you can have jobs that regularly check any files
>> dropped into a directory and upload them check out
>> http://www.sqldts.com/default.aspx for lots of information on how to do
>> this.
>> John
>> "cheesey_toastie" wrote:
>> > Hi,
>> >
>> > Now I'm worried as I showed this bodge to a colleague and he is using
>> > it a lot! I found that you can copy paste directly from Excel into the
>> > Enterprise Manager GUI (we're on v8.0, sql server 2000).
>> >
>> > The way I do it is say you had some Excel data in cols B-D, highlight A
>> > as well (leaving this column blank), click the arrow on the new row at
>> > the bottom of your table (a table with 3 columns matching B-D) in
>> > Enterprise Manager and voila a paste option appears in the right hand
>> > mouse button menu.
>> >
>> > Why? What is this blank column doing? Is it causing any harm
>> > internally to the db to insert nulls (or empty excel strings more
>> > correctly?) in this column?
>> >
>> > Any help / warnings much appreciated!
>> >
>> > CT
>> >
>> >
>sql sql

Copy or insert Columns from a table in another Database, How?

Hallo,
I thought it is easily done, but no. I have 3 columns of hundreds of data i
n a table in another Database within thesame server that i want to move to m
y production Database. I have tried Select into, copy, insert method... wit
hout success.
Please help.
ThanksHi,
If the structure of the table in production database is identical then use
Insert into
Insert into productiondatabase..table_name(col1,col2,col3) select
col1,col2,col3 from devdb..table
Above command will fail incase the same table have any other column which
will not allow "NOT NULL"
I have tried Select into, copy, insert method... without success.
Tell the error you are getting.
Thanks
Hari
MCDBA
"Ototofioto" <anonymous@.discussions.microsoft.com> wrote in message
news:6ED03A97-26F9-483A-93B9-3BFB8F28C87D@.microsoft.com...
> Hallo,
> I thought it is easily done, but no. I have 3 columns of hundreds of data
in a table in another Database within thesame server that i want to move to
my production Database. I have tried Select into, copy, insert method...
without success.
> Please help.
> Thanks

Copy one row from tableA to tableB

I want to copy all columns from tblA into tblB where tblA.UniqueID =
parameter @.ID without having to list each column name in an insert
statement. Is this possible?
Thanks,
lqI forgot to mention all column names and data types are identical in
tblA and tblB except for the UniqueID which is PK in tblA and not PK in
tblB.
lq|||Sure,

INSERT INTO tblB
SELECT *
FROM tblA
WHERE UniqueID = @.ID

Stu|||laurenq uantrell (laurenquantrell@.hotmail.com) writes:
> I want to copy all columns from tblA into tblB where tblA.UniqueID =
> parameter @.ID without having to list each column name in an insert
> statement. Is this possible?

Under some circumstances, yes.

Then again, in application code, I think it is very bad practice to say:

INSERT tbl2 SELECT * FROM tbl1

SELECT * itself is bad practice, so are INSERT statements without listing
of target columns.

Why are this bad:

* You cannot see if a column is actually used.
* If the table defintion changes, the result of the SELECT changes.
* And for INSERT it is likely to result in the INSERT statment failing.

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

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

Copy one db with jobs to another hard drive

Hi all,
I'm replaced our current hard drive with another and I need to know the
best way to transfer all the sql data with jobs.
Going from:
NT4 Server running SQL Server 7
to:
W2K Server running SQL Server 7
I need to transfer all sql data including jobs to the new hard drive.
Can I just copy all the dbs (Master, User defined, etc) to the new hard
drive and be set?
What's the best way to handle this.
Thank you,
BillWilliam,
Go with Backup and Restore procedure.
--
Dejan Sarka, SQL Server MVP
Please reply only to the newsgroups.
"William Oliveri" <wuji@.bigvalley.net> wrote in message
news:eV2PpPFkDHA.2652@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I'm replaced our current hard drive with another and I need to know the
> best way to transfer all the sql data with jobs.
> Going from:
> NT4 Server running SQL Server 7
> to:
> W2K Server running SQL Server 7
> I need to transfer all sql data including jobs to the new hard drive.
> Can I just copy all the dbs (Master, User defined, etc) to the new hard
> drive and be set?
> What's the best way to handle this.
> Thank you,
> Bill
>|||> Can I just copy all the dbs (Master, User defined, etc) to the new hard
> drive and be set?
>
No there are two ways:
1)
detach/attach
take care that no user is connected (kill SPID´s or : alter database
set restricted_user with rollback immediate)
all files you have to move you find at sysfiles (master)
exec sp_detach_db DBName
copy or move the files
at the new Server:
exec sp_attach_db DBName, FileName1, FileName2...FileName16
e.g exec sp_attach_db 'MyDB',
'e:\SQLData\MyDB.mdf','e:\SQLLog\MyDB.ldf'
2)
Backup/Restore
Backup Database DBName To Disk='PathName'
then on the new machine:
Restore Database DBName
from Disk = 'PathName'
with move 'MyDB' TO 'g:\SQLData\MyDB.mdf',
move 'MyDb_log' TO 'g:\SQLLog\MyDB.ldf'
hope this was helpful
Peter

Copy one coulm to another

Hi Everyone.
I have one table that has CUST_NO, LICENSE_NO and LOGIN. CUST_NO is unique.
I need to copy whatever is in LICENSE_NO field to LOGIN field. How can I do
this?
Thanks for your help.If you want to update all values in the LOGIN column, then it should be as
simple as this:
UPDATE MyTable
SET LOGIN = LICENSE_NO
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||If I understand what you are trying to do...
update t1
set LOGIN=LICENSE_NO
--
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
"Shan" <Shan@.discussions.microsoft.com> wrote in message
news:13B60DE7-BE32-426B-9EFB-1FA8B8010871@.microsoft.com...
> Hi Everyone.
> I have one table that has CUST_NO, LICENSE_NO and LOGIN. CUST_NO is
> unique.
> I need to copy whatever is in LICENSE_NO field to LOGIN field. How can I
> do
> this?
> Thanks for your help.|||"Shan" <Shan@.discussions.microsoft.com> wrote in message
news:13B60DE7-BE32-426B-9EFB-1FA8B8010871@.microsoft.com...
> Hi Everyone.
> I have one table that has CUST_NO, LICENSE_NO and LOGIN. CUST_NO is
> unique.
> I need to copy whatever is in LICENSE_NO field to LOGIN field. How can I
> do
> this?
> Thanks for your help.
UPDATE FOO set LOGIN=LICENSE_NO
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Thanks very much. Works great.
"Plamen Ratchev" wrote:
> If you want to update all values in the LOGIN column, then it should be as
> simple as this:
> UPDATE MyTable
> SET LOGIN = LICENSE_NO
> HTH,
> Plamen Ratchev
> http://www.SQLStudio.com
>

Copy one coulm to another

Hi Everyone.
I have one table that has CUST_NO, LICENSE_NO and LOGIN. CUST_NO is unique.
I need to copy whatever is in LICENSE_NO field to LOGIN field. How can I do
this?
Thanks for your help.
If you want to update all values in the LOGIN column, then it should be as
simple as this:
UPDATE MyTable
SET LOGIN = LICENSE_NO
HTH,
Plamen Ratchev
http://www.SQLStudio.com
|||If I understand what you are trying to do...
update t1
set LOGIN=LICENSE_NO
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
"Shan" <Shan@.discussions.microsoft.com> wrote in message
news:13B60DE7-BE32-426B-9EFB-1FA8B8010871@.microsoft.com...
> Hi Everyone.
> I have one table that has CUST_NO, LICENSE_NO and LOGIN. CUST_NO is
> unique.
> I need to copy whatever is in LICENSE_NO field to LOGIN field. How can I
> do
> this?
> Thanks for your help.
|||"Shan" <Shan@.discussions.microsoft.com> wrote in message
news:13B60DE7-BE32-426B-9EFB-1FA8B8010871@.microsoft.com...
> Hi Everyone.
> I have one table that has CUST_NO, LICENSE_NO and LOGIN. CUST_NO is
> unique.
> I need to copy whatever is in LICENSE_NO field to LOGIN field. How can I
> do
> this?
> Thanks for your help.
UPDATE FOO set LOGIN=LICENSE_NO
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||Thanks very much. Works great.
"Plamen Ratchev" wrote:

> If you want to update all values in the LOGIN column, then it should be as
> simple as this:
> UPDATE MyTable
> SET LOGIN = LICENSE_NO
> HTH,
> Plamen Ratchev
> http://www.SQLStudio.com
>
sql sql

Copy one column to another

I have a table I need to make changes to. Dropping columns isn't my problem, but before I drop one of the columns I need to copy the values that aren't null to another existing column in the same table. So for psuedo-code it would be something like:

update Mapping Set SID = (select SMappingID from Mapping where SMappingID is not null)

I don't know if I should be doing this in a loop (which I'm not totally familiar with using) or if there is a better way to copy these values. If it helps my columns are -

ID (int, Not Null, PK)
SID (int, Not Null, FK)
PartID (int, Not Null, FK)
CompID (int, Not Null)
SMappingID (int, Null)

Quote:

Originally Posted by NamelessNumberheadMan

update Mapping Set SID = (select SMappingID from Mapping where SMappingID is not null)

I don't know if I should be doing this in a loop (which I'm not totally familiar with using) or if there is a better way to copy these values. If it helps my columns are -

ID (int, Not Null, PK)
SID (int, Not Null, FK)
PartID (int, Not Null, FK)
CompID (int, Not Null)
SMappingID (int, Null)

try:
update Mapping Set SID = SMappingID
where SMappingID IS NOT NULL|||

Quote:

Originally Posted by NamelessNumberheadMan

update Mapping Set SID = (select SMappingID from Mapping where SMappingID is not null)

I don't know if I should be doing this in a loop (which I'm not totally familiar with using) or if there is a better way to copy these values. If it helps my columns are -

ID (int, Not Null, PK)
SID (int, Not Null, FK)
PartID (int, Not Null, FK)
CompID (int, Not Null)
SMappingID (int, Null)

The query is simple:

UPDATE TABLENAME
SET NEWCOLUMN = SOURCECOLUMN WHERE SOURCECOLUMN IS NOT NULL.

Thanks.

copy on condition

i have two tables A and B with the same fields,

If the id field of table B equals id field in Table A i need to update th edata for that id row.
If the id field doesn;t match then i need to insert a new record in tale A for that id

that is i need to perform insertion or updation into table A depending on table B data
Can anyone give me some idea how to start?

You need two statements.
UPDATE A SET col1 = b.col1, col2 = b.col2, ... FROM A INNER JOIN B on A.pkcol = B.pkcol
INERT INTO A (pkcol, col1, col2, col3)
SELECT pkcol, col1, col2, col3
FROM B
WHERE pkcol NOT IN (SELECT pkcol FROM A)|||

Thank you very much Alex!
One more question, I have millions of data in the table, will the 2 queries work well for such huge data
.I mean performance wise. I have created indexes .

|||You'll just have to try it to find out. No way of guessing that without knowing what the tables are, how the indexes are, hardware, load, etc ...

copy of table from another database into your database

We copy a table from a progress database into our sql server database that we use as a read-only table. We perform this action through a job.

Is there a way to have a read only table from another database that is put into your database that will always be insinc with the original copy to use as a read only copy, that as the original table is updated, it will proprogate to your copied table?

Would setting up a package to import the data from the other database on a schedule work? In SQL Server, you can do an import and you have the option to save the import as a package and schedule it to run at your discretion.

|||

If you require the tables be kept in sync, then the easiest way would be to use a link from the source to the destination server, and have triggers on the source populate the destination. This does carry a performance penalty, of course.

A more performant solution would be to have the triggers on the source write their updates to an update table on the same database, and poll it for changes every 5 minutes or so, but you're not guaranteed to have the DBs in sync then.

Copy of SQLServer software

Does anybody know where a copy of the MS-SQLServer software is posted?"Curt" <dkdkdk@.dkdkdk.dkd> wrote in message
news:Xns93C7596DBAD52curtsympaticoca@.207.35.177.13 4...
> Does anybody know where a copy of the MS-SQLServer software is posted?

Umm, you can go to http://www.microsoft.com/sql/evalua...ial/default.asp
and get a trial version.

Other than that, you have to purchase it.sql sql

Copy of RS??

Hi there

We are running SQL server 2000 on a windows server 2000 OS, we are also using the Evaluation edition of RS, can you tell me if the full version is on the SQL server 2000 cd, or the Windows Server 2000 cd?

thanks

tim

SQL RS is a part of SQL Server, so you'll find the install bits on the SQL Server CD. If you bought the CD before Reporting Services was released - you'll have to order a CD from Microsoft.|||

Hi there, thanks for the reply

How do I order from Microsoft and will there be a charge?

Thanks

Tim

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 of a group on same page

hi everyone, i was just wondering if there is a solution to a couple of problems i am having. I am designing a report for checks and have a group header with the details at the top of the page and want to duplicate the exact group with details in the middle of the page, is this possible? everytime i add another group, it embeds it in the first group.

also, i want to add an underlay with "VOID" on the checks at the page footer after the fist page. any ideas?

JosoCan you show us the expected report format?sql sql

Copy of a database with new filenames

I have database A on files A.mdf and A.ldf
Using sp_detach_db and sp_attach_db I can get
database B on files A.mdf and A.ldf
Is there a way to get database B on B.mdf and B.ldf
not going through the copying of individual objects.
Thanks in advance
SergeiHi,
1. Backup the database A (backup database A to disk='c:\a_db.bak' with init
2. Restore database B from disk='C:\a_db.bak' with move 'logfilename' to
'path\b.mdf',
move 'loglogname' to 'path\b.ldf'
Replace path with your actual path name.
Thanks
Hari
MCDBA
"Sergei" <sergei@.kbotd.ru> wrote in message
news:#y#LzBA3DHA.560@.TK2MSFTNGP11.phx.gbl...
> I have database A on files A.mdf and A.ldf
> Using sp_detach_db and sp_attach_db I can get
> database B on files A.mdf and A.ldf
> Is there a way to get database B on B.mdf and B.ldf
> not going through the copying of individual objects.
> Thanks in advance
> Sergei|||Check books on line for the backup with MOVE to statement, it's straight
forward and useful
--
Regards,
Dandy Weyn
MCSE, MCSA, MCDBA, MCT
www.dandyman.net
"Sergei" <sergei@.kbotd.ru> wrote in message
news:%23y%23LzBA3DHA.560@.TK2MSFTNGP11.phx.gbl...
> I have database A on files A.mdf and A.ldf
> Using sp_detach_db and sp_attach_db I can get
> database B on files A.mdf and A.ldf
> Is there a way to get database B on B.mdf and B.ldf
> not going through the copying of individual objects.
> Thanks in advance
> Sergei|||To add to the other responses, you can also rename the file to the desired
names before the attach.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Sergei" <sergei@.kbotd.ru> wrote in message
news:%23y%23LzBA3DHA.560@.TK2MSFTNGP11.phx.gbl...
> I have database A on files A.mdf and A.ldf
> Using sp_detach_db and sp_attach_db I can get
> database B on files A.mdf and A.ldf
> Is there a way to get database B on B.mdf and B.ldf
> not going through the copying of individual objects.
> Thanks in advance
> Sergei

Copy of a database with new filenames

quote:

> I have database A on files A.mdf and A.ldf
> Using sp_detach_db and sp_attach_db I can get
> database B on files A.mdf and A.ldf
> Is there a way to get database B on B.mdf and B.ldf
> not going through the copying of individual objects.
> Thanks in advance
> Sergei

|||Check books on line for the backup with MOVE to statement, it's straight
forward and useful
Regards,
Dandy Weyn
MCSE, MCSA, MCDBA, MCT
www.dandyman.net
"Sergei" <sergei@.kbotd.ru> wrote in message
news:%23y%23LzBA3DHA.560@.TK2MSFTNGP11.phx.gbl...

quote:

> I have database A on files A.mdf and A.ldf
> Using sp_detach_db and sp_attach_db I can get
> database B on files A.mdf and A.ldf
> Is there a way to get database B on B.mdf and B.ldf
> not going through the copying of individual objects.
> Thanks in advance
> Sergei

|||To add to the other responses, you can also rename the file to the desired
names before the attach.
Hope this helps.
Dan Guzman
SQL Server MVP
"Sergei" <sergei@.kbotd.ru> wrote in message
news:%23y%23LzBA3DHA.560@.TK2MSFTNGP11.phx.gbl...

quote:

> I have database A on files A.mdf and A.ldf
> Using sp_detach_db and sp_attach_db I can get
> database B on files A.mdf and A.ldf
> Is there a way to get database B on B.mdf and B.ldf
> not going through the copying of individual objects.
> Thanks in advance
> Sergei

Copy objects Wizard - deleted data??

I have not used the copy objects wizard that much. I used it today to copy 4
views from my dev box to production. It copied the views, but also wiped out
my data in all the tables that the views are built around!!! In
production!!!!

Can someone provide me some insight into why this happened?

Thanks,
ChadChad Richardson (chad@.NIXSPAM_chadrichardson.com) writes:
> I have not used the copy objects wizard that much. I used it today to
> copy 4 views from my dev box to production. It copied the views, but
> also wiped out my data in all the tables that the views are built
> around!!! In production!!!!
> Can someone provide me some insight into why this happened?

Extremely nasty. I have not used the wizard in question myself, and I
think you understand why after this experience. It's a bit ironic: the
wizards are there to help, but you can only use them, if you know
exactly what they do, and in such case you may not need them.

Anyway my guess is that the wizard saw reason to recreate the underlying
tables as well; possibly because the defintion in production was different
from your dev box.

The correct way to deploy things in production is through change scripts
that are created from information in the version-control system.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Yes, very nasty. Luckily my hosting company (ReadyHosting) was able to
restore everything from their backup and the transaction logs.

Normally, whenever I make DB table changes I save the scripts and put them
in a "To Promote to Prod" directory, then use SQL Analyzer to apply those
changes to prod. But changes to views don't prompt for you to save these
changes as a script.

What specifically do you mean by "the version control system"? (As you can
tell by my question, I know just enough of SQL Server to be dangerous, so
any insight on how to handle version contol is appreciated.)

Thanks,
Chad

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9682EF2F57F5DYazorman@.127.0.0.1...
> Chad Richardson (chad@.NIXSPAM_chadrichardson.com) writes:
>> I have not used the copy objects wizard that much. I used it today to
>> copy 4 views from my dev box to production. It copied the views, but
>> also wiped out my data in all the tables that the views are built
>> around!!! In production!!!!
>>
>> Can someone provide me some insight into why this happened?
> Extremely nasty. I have not used the wizard in question myself, and I
> think you understand why after this experience. It's a bit ironic: the
> wizards are there to help, but you can only use them, if you know
> exactly what they do, and in such case you may not need them.
> Anyway my guess is that the wizard saw reason to recreate the underlying
> tables as well; possibly because the defintion in production was different
> from your dev box.
> The correct way to deploy things in production is through change scripts
> that are created from information in the version-control system.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Chad Richardson (chad@.NIXSPAM_chadrichardson.com) writes:
> Yes, very nasty. Luckily my hosting company (ReadyHosting) was able to
> restore everything from their backup and the transaction logs.
> Normally, whenever I make DB table changes I save the scripts and put them
> in a "To Promote to Prod" directory, then use SQL Analyzer to apply those
> changes to prod. But changes to views don't prompt for you to save these
> changes as a script.
> What specifically do you mean by "the version control system"? (As you can
> tell by my question, I know just enough of SQL Server to be dangerous, so
> any insight on how to handle version contol is appreciated.)

"version control system" or "source code control" is nothing specific
to SQL Server, but fundamentals of software engineering. In a version
control system, developers adds their files. Later a file may be
checked out, maybe by the same developer, maybe by someone else. The
person who checked out the file, performs some changes to it, and
then checks back in again, after proper testing.

When it's getting time to make a build for an integration test, someone
who is a "build master", "configuration manager" or similar puts some
label on all the most recent versions of files, to create a baseline.
During tests, bugs may be uncovered and fixed. The fixes can be inserted
into that baseline, or a new baseline be created.

Eventually, the thing is put into production and a baseline is created for
this. Now, development of 2.0 starts. However, there may be need to
fix bugs in production as well. Say that version 12 of file foo.cs
was in the shipment baseline. By the time a critical bug in production
is discovered, the file at version 14 for 2.0 development. But you
check out version 12, and fix that, and check it in as 12.1 - you
have now created a branch.

The exact terminology for these various actions are different from
product to product. The most commonly used version-control system
in the Microsoft world is Visual SourceSafe. It performs branching
different that about any other product. VSS has a lot of short-comings
as a version-control system, but it's easy to start with, and it's OK
for smaller teams.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||For a tool that links the drop/create scripts (for ALL database
objects) in any source control system to your development, test and
production databases have a look at DB Ghost (www.dbghost.com).

The approach is to regard the drop/create scripts as the only true
representation of the desired schema i.e. your 'source database'. Once
you have modifed the drop/create scripts DB Ghost will build a brand
new database from them in an extremely fast manner whilst taking care
of any dependencies. This a) verifies that no syntax or dependency
errors have been introduced and b) gives you a real source database to
use as the basis for a compare and upgrade of your actual target i.e.
the test or production database. DB Ghost does this also and creates a
rock solid delta script of the differences that is guaranteed to work
with no hand coded modifications.

What you end up with is a target database that matches a (labelled) set
of scripts under source control. If this approach is used for all
releases then a full audit trail of who changed what, why and when is
maintained in the source control system so it is easy to do reports
such as 'what changed between release X and release Y' or 'who first
changed sproc Z after release X'.

Relying on comments in sprocs etc. is a recipe for disaster in all but
the most disciplined of IT shops. Let your source control system do
the hard work for you and let DB Ghost handle all the SQL Server code.
It's called the DB Ghost Process and it can bring an amazing level of
quality to your deployments and code control in general.|||Erland,

Thanks for the explanation. I do have experience with source control tools
such as VSS and PVCS, but all have been for file/directory based source,
like VB. How do DB developers apply these principles (check in, check out,
etc.) to SQL Server? This is something I've curious about for a while now.

Thanks,
Chad

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96833C7C3BFCYazorman@.127.0.0.1...
> Chad Richardson (chad@.NIXSPAM_chadrichardson.com) writes:
>> Yes, very nasty. Luckily my hosting company (ReadyHosting) was able to
>> restore everything from their backup and the transaction logs.
>>
>> Normally, whenever I make DB table changes I save the scripts and put
>> them
>> in a "To Promote to Prod" directory, then use SQL Analyzer to apply those
>> changes to prod. But changes to views don't prompt for you to save these
>> changes as a script.
>>
>> What specifically do you mean by "the version control system"? (As you
>> can
>> tell by my question, I know just enough of SQL Server to be dangerous, so
>> any insight on how to handle version contol is appreciated.)
> "version control system" or "source code control" is nothing specific
> to SQL Server, but fundamentals of software engineering. In a version
> control system, developers adds their files. Later a file may be
> checked out, maybe by the same developer, maybe by someone else. The
> person who checked out the file, performs some changes to it, and
> then checks back in again, after proper testing.
> When it's getting time to make a build for an integration test, someone
> who is a "build master", "configuration manager" or similar puts some
> label on all the most recent versions of files, to create a baseline.
> During tests, bugs may be uncovered and fixed. The fixes can be inserted
> into that baseline, or a new baseline be created.
> Eventually, the thing is put into production and a baseline is created for
> this. Now, development of 2.0 starts. However, there may be need to
> fix bugs in production as well. Say that version 12 of file foo.cs
> was in the shipment baseline. By the time a critical bug in production
> is discovered, the file at version 14 for 2.0 development. But you
> check out version 12, and fix that, and check it in as 12.1 - you
> have now created a branch.
> The exact terminology for these various actions are different from
> product to product. The most commonly used version-control system
> in the Microsoft world is Visual SourceSafe. It performs branching
> different that about any other product. VSS has a lot of short-comings
> as a version-control system, but it's easy to start with, and it's OK
> for smaller teams.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Chad Richardson (chad@.NIXSPAM_chadrichardson.com) writes:
> Thanks for the explanation. I do have experience with source control tools
> such as VSS and PVCS, but all have been for file/directory based source,
> like VB. How do DB developers apply these principles (check in, check out,
> etc.) to SQL Server? This is something I've curious about for a while now.

They use - or at least should use - files. I've seen a whole lot of
questions on version control of SQL objects as this should be something
difficult or special. It isn't. Source code is source code, and should be
handled as such.

I guess people are tricked by tools that permit you to store things in the
database directly, point-and-click GUI:n for creating tables etc. But all
of that is really files.

The one gotcha there is if you use a tool like Query Analyzer for editing
your SQL objects, is that you disrupt the normal procedure which is
1) check out 2) edit 3) save 4) compile 5) test 6) back to 2 until it
works 7) check in. With a tool like QA, 3 is taken out of the chain, which
can lead to that what you check is not what you tested.

In our shop, we avoid this problem by using a third-party editor, Textpad.
It has no special features for SQL - but it is a better editor than QA.
From Textpad 3 and 4 is one key-click, as we can activate a command-line
from Textpad. We have our own load tool for quite a few bells and whistles,
but the tool could be command-line OSQL.

The thing people seem to want to do, is to take the SQL objects from
the database, but this is actually really wrong when you think of it.
If you work in VB, would get the input for the version-control system
by disassembling the object modules?

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

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

Copy objects with DTS Import/Export Wizard?

Has the functionality of copying objects using the DTS Import/Export Wizard been removed in SQL Server 2005?

MSDN says this about the wizard:

"With the DTS Import/Export Wizard, you can transfer database objects such as indexes, views, roles, stored procedures, and referential integrity constraints. For more information, see Copy SQL Server Objects Task."

But the "for more imformation" link describes adding a task to a DTS package to copy an object, not how to use the wizard to copy objects.

It's been awhile since I've used SQL Server 2000, but as I recall it was possible to copy objects as well as data using the DTS Import/Export Wizard.

Thanks,

Ron

DTS has been enhanced to SSIS(Integration Services) in SQL 2005, so in this case you can use SSIS package to perform that copy SQL Server objects tasks which is similar to the copying the database objects.|||

You have a few options:

Copy Database Wizard:
Use this if you want to copy an entire database.

Right-click on a database in Management Studio, go to "Tasks", and choose "Copy Database...".|||

I also share in their frustrations... When you only have a few objects, in this case tables to transfer; you have very little options in the SQL2005 Import/Export wizard as compared to the SQL2000 DTS (Import/Export) wizard. Copy Database is out of the question for this scenario.

My problem lies in the fact that the SQL2005 Import/Export wizard does not create the indexes and keys when the table needs to be created in the target database, even if the source schema exists on the target database. Is there any way around this without pre-creating the target tables from scripts generated on the source DB? Seems that this manual step should be built into the wizard? Ay suggestions? We are running SP2 for SQL 2005.

There are suggestions that SSIS can be used, thats fine, but given the entirely different interface as compared to DTS, I don't have the time to deal with that when the wizard should be able to do this in a few minutes.

|||That option is fine for SQL2000/2005 to SQL2005, but is there any option to copy to SQL2000?|||I am getting very frustrated, for two days now I've been trying to work out how to copy a database (SQL express version) to my ISP which has the full MS 2005 server. I keep losing keys, stored procedures etc. When you right click on the database as you say and click on tasks there's no copy database there.|||Yey! now I get to spend 2 hours with ssis doing what used to take 2 minutes with dts import wizard.|||

I now use MS Database Publishing wizard which attaches to Database Explorer in Microsoft Developer Express. It works better than DTS Import/Export as I do not lose keys and can copy all object in the database. Great for making backups too.

Copy objects with DTS Import/Export Wizard?

Has the functionality of copying objects using the DTS Import/Export Wizard been removed in SQL Server 2005?

MSDN says this about the wizard:

But the "for more imformation" link describes adding a task to a DTS package to copy an object, not how to use the wizard to copy objects.

It's been awhile since I've used SQL Server 2000, but as I recall it was possible to copy objects as well as data using the DTS Import/Export Wizard.

Thanks,

Ron

You have a few options:

Copy Database Wizard:
Use this if you want to copy an entire database.

Right-click on a database in Management Studio, go to "Tasks", and choose "Copy Database...".|||

sql sql

Copy objects with DTS Import/Export Wizard?

Has the functionality of copying objects using the DTS Import/Export Wizard been removed in SQL Server 2005?

MSDN says this about the wizard:

But the "for more imformation" link describes adding a task to a DTS package to copy an object, not how to use the wizard to copy objects.

It's been awhile since I've used SQL Server 2000, but as I recall it was possible to copy objects as well as data using the DTS Import/Export Wizard.

Thanks,

Ron

You have a few options:

Copy Database Wizard:
Use this if you want to copy an entire database.

Right-click on a database in Management Studio, go to "Tasks", and choose "Copy Database...".|||

Copy objects with DTS Import/Export Wizard?

Has the functionality of copying objects using the DTS Import/Export Wizard been removed in SQL Server 2005?

MSDN says this about the wizard:

But the "for more imformation" link describes adding a task to a DTS package to copy an object, not how to use the wizard to copy objects.

It's been awhile since I've used SQL Server 2000, but as I recall it was possible to copy objects as well as data using the DTS Import/Export Wizard.

Thanks,

Ron

You have a few options:

Copy Database Wizard:
Use this if you want to copy an entire database.

Right-click on a database in Management Studio, go to "Tasks", and choose "Copy Database...".|||

copy objects and data from server to server

I want to copy all objects and data from other SQL 2000 server std. to my
local server(SQL 2000 personal) to test something.
when I do copy wizard I got a message
"SQL Server service is running under localsystem account. To get the
privilege to copy through the network you should change SQL Service
account."(FYI,this may not what you see in Eng version because I translated
it.)
Why?
This time I used "copy wizard", is it the best solution?If you load EM, & select the server in question. Right click & select
properties. Goto the Security tab. At the bottom, you have the option to
start & run SQL server in either the local system account, or another
account...
Cheers,
James Goodman MCSE, MCDBA
http://www.angelfire.com/sports/f1pictures|||HI,
To perform this operation start the SQL server service using a Domain OS
user, which has got access in both servers.
Thanks
Hari
MCDBA
"James Goodman" <j a m e s@.norton-associates.co.u k> wrote in message
news:bvd77v$o53$1@.sparta.btinternet.com...
> If you load EM, & select the server in question. Right click & select
> properties. Goto the Security tab. At the bottom, you have the option to
> start & run SQL server in either the local system account, or another
> account...
>
>
> --
> Cheers,
> James Goodman MCSE, MCDBA
> http://www.angelfire.com/sports/f1pictures
>

copy objects and data from server to server

quote:

> If you load EM, & select the server in question. Right click & select
> properties. Goto the Security tab. At the bottom, you have the option to
> start & run SQL server in either the local system account, or another
> account...
>
>
> --
> Cheers,
> James Goodman MCSE, MCDBA
> http://www.angelfire.com/sports/f1pictures
>

Copy member data to another member

Hi,

I need to copy all data from one member to another member from the same dimension.

To be more specific, I have a scenario dimension with these members:

->Budget
->Forecast

And I want to start my Forecast with the Budget data from last year * 1.05. Because I will change the values from forecast I can't just put a formula (forecast = budget *1.05, if I do that the cube will always show the formula values and not some new values that I want to enter).

Is there a easy way to do that? Any tip will be great.

Thanks,
Handerson

You should be able to achieve what you are trying to do using SCOPE and FREEZE in the cube MDX script. There are actually some good examples in the Adventure Works cube script. Open the script and do a find on "quota". This will take you to a section in the script where there are several examples that are used to populate a measure called "Sales Amount Quota" in the Adventure Works cube.

HTH,

- Steve

|||

Steve,

I have installed the Adventure Works cube but I can't find the scripts that you sad, where I can find then? I'm using SQL Server 2005.

I looked the Scope and Freeze command, you sugest that I do that:

Forecast01 = Budget * 1.05

Freeze(Forecast01)

After that the users can change de Budget values without change the Forecast, ok. But the users can change the Forecast value using write-back?

And every month I will generate a new Forecast, like this:

Forecast02 = Forecast01

Freeze(Forecast02)

Next month I will do:

Forecast03 = Forecast02

Freeze(Forecast03)

And so on... It'll be performatic? Because of that I was trying to find a way to copy the data instead of build a formule.

Thanks,

Handerson

|||

Handerson,

Here is the script segment that I was referring to. With regard to your question, I thought that you were starting from a base period and then trying to leverage the data that was there into future periods. If you truly need to copy data then you would probably be better off doing this at the source. You can still leverage MDX to show future periods that do not have data using the Scope and Freeze functions. You can also check to see if there is an existing value before you compute a projected value.

/*--

| Sales Quota Allocation |

--*/

/*-- Allocate equally to quarters in H2 FY 2005 */

Scope

(

[Date].[Fiscal Year].&[2005],

[Date].[Fiscal].[Fiscal Quarter].Members,

[Measures].[Sales Amount Quota]

) ;

This = ParallelPeriod

(

[Date].[Fiscal].[Fiscal Year], 1,

[Date].[Fiscal].CurrentMember

) * 1.35 ;

/*-- Allocate equally to months in FY 2002 --*/

Scope

(

[Date].[Fiscal Year].&[2002],

[Date].[Fiscal].[Month].Members

) ;

This = [Date].[Fiscal].CurrentMember.Parent / 3 ;

End Scope ;

/*-- Weighted allocation to remaining months */

// Pin quarterly values prior to assigning weights for months

// This is done in order to avoid overwriting the quarterly values

// once weights are entered for monthly values.

Freeze

(

[Date].[Fiscal].[Fiscal Quarter].Members,

[Measures].[Sales Amount Quota]

) ;

// Scope on month level in FY 2003 and onwards

Scope

(

[Date].[Fiscal Year].&[2003] : Null,

[Date].[Fiscal].[Month].Members

) ;

// Compute weights based on reseller sales ratio in previous year

This =

(

ParallelPeriod // Fetch reseller sales amount in previous year

(

[Date].[Fiscal].[Fiscal Year], 1,

[Date].[Fiscal].CurrentMember

[Measures].[Reseller Sales Amount]

)

(

ParallelPeriod // Divide monthly value by quarterly value to obtain ratio

(

[Date].[Fiscal].[Fiscal Year], 1,

[Date].[Fiscal].CurrentMember.Parent

[Measures].[Reseller Sales Amount]

) ;

// Allocate quarterly values to months according to weight

This = [Measures].CurrentMember * [Date].[Fiscal].Parent ;

End Scope ;

sql sql

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 29, 2012

Copy Permissions

Copy Permissions

Copy paste from Excel - should this work?

Copy paste from Excel - should this work?

Copy paste from Excel - should this work?

Copy or insert Columns from a table in another Database, How?

Copy one row from tableA to tableB

Copy one db with jobs to another hard drive

Copy one coulm to another

Copy one coulm to another

Copy one column to another

copy on condition

copy of table from another database into your database

Copy of SQLServer software

Copy of RS??

copy of existing table and data

copy of existing table and data

copy of existing table and data

copy of a group on same page

Copy of a database with new filenames

Copy of a database with new filenames

Copy objects Wizard - deleted data??

Copy objects with DTS Import/Export Wizard?

Copy objects with DTS Import/Export Wizard?

Copy objects with DTS Import/Export Wizard?

Copy objects with DTS Import/Export Wizard?

copy objects and data from server to server

copy objects and data from server to server

Copy member data to another member

Copy MDF and LDF files Over Existing Database

Copy MDF and LDF files Over Existing Database

Converting problem

Blog Archive

About Me