Showing posts with label reference. Show all posts
Showing posts with label reference. Show all posts

Friday, March 9, 2012

Problem Creating View from Management Studio

Hi:

I have a parent table and 5 child tables with a PK,FK reference. I designed a view in Management Studio by dropping the master and Child tables, so that I could see the parent data, and all details from child rows.

My view does not contain any data though my tables do. What have I done wrong?

To describe what I want:

I have Parent table with PK, ParentFields.

I have Child1 with FK, Details1, Child2 with FK, Details2 and so on.

I want a view that shows PK, ParentFields, Details1, Details2,..Details5.

Seems simple enough, I dont know what is wrong. I am a beginner and only use the Designer to do this kind of stuff.

Appreciate any help.

TIA

Kar

You can create a simple query such as

CREATE VIEW [dbo].[vVesselsCargo]
AS
SELECT
VC.VesselCargoID,
CMP.CompanyName Shipper
FROM
VesselCargo VC,
Companies CMP
WHERE
CMP.CompanieID = VC.ShipperID

Then you can see how Management Studio was create that View in design mode.

|||

Your problem needs to be described better including (1) the definition of the parent table and the 5 child tables and (2) the definition of the view as it presently exists. My knee-jerk guess would be that you have used an inner join in a situation that merits a left join. Examine this sequence:

Code Snippet

declare @.parent table (pk int, pkInfo varchar(20))
insert into @.parent
select 1, '1st Parent' union all select 2, '2nd Parent'
--select * from @.parent

declare @.child1 table (pk int, detail_1 varchar(30))
insert into @.child1
select 1, 'Pk #1: Detail Rec #1' union all
select 2, 'Pk #2: Detail Rec #2'

declare @.child2 table (pk int, detail_2 varchar(30))
insert into @.child2
select 2, 'Pk #2: Detail Rec #2'

declare @.child3 table (pk int, detail_3 varchar(30))
insert into @.child3
select 1, 'Pk #1: Detail Rec #3'

declare @.child4 table(pk int, detail_4 varchar(30))
insert into @.child4
select 1, 'Pk #1: detail Rec #4'

declare @.child5 table(pk int, detail_5 varchar(30))
insert into @.child5
select 1, 'Pk #1: detail Rec #5'

select p.pk,
p.pkInfo,
detail_1,
detail_2,
detail_3,
detail_4,
detail_5
from @.parent p
join @.child1 c1
on p.pk = c1.pk
join @.child2 c2
on p.pk = c2.pk
join @.child3 c3
on p.pk = c3.pk
join @.child4 c4
on p.pk = c4.pk
join @.child5 c5
on p.pk = c5.pk

/*
pk pkInfo detail_1 detail_2 detail_3 detail_4 detail_5
- -

(0 row(s) affected)

*/


Because there are cases in which one of the detail records is missing, no rows are returned; however, if you change the JOINs into LEFT JOINS, you get results for both PKs:

Code Snippet

select p.pk,
p.pkInfo,
detail_1,
detail_2,
detail_3,
detail_4,
detail_5
from @.parent p
left join @.child1 c1
on p.pk = c1.pk
left join @.child2 c2
on p.pk = c2.pk
left join @.child3 c3
on p.pk = c3.pk
left join @.child4 c4
on p.pk = c4.pk
left join @.child5 c5
on p.pk = c5.pk

/*
pk pkInfo detail_1 detail_2 detail_3 detail_4 detail_5
- - - - - -
1 1st Parent Pk #1: Detail Rec #1 NULL Pk #1: Detail Rec #3 Pk #1: detail Rec #4 Pk #1: detail Rec #5
2 2nd Parent Pk #2: Detail Rec #2 Pk #2: Detail Rec #2 NULL NULL NULL
*/

Perhaps you need to use a left join to return your data; however, you really need to give a better description of your problem.

|||

Hi:

Thanks for the replies. I am a beginner, and I did not use or create any SQL. I just used Management Studio, drag-dropped tables and selected the fields that I want.

The tables already have PK and FK relationships defined, and the Designer automatically created SQL that roughly looks like:

Select Primary.PK, Primary.<Other_Fields>, Child1.<Details>, Child2.<Details> ,Child3.<Details> etc

from primary inner join Child1 on Primary.PK=Secondary.FK etc.

I tried modifying it to Left join as you suggested, but Management Studio automatically changes it to Left Outer Join when I save the view.

My data is as follows:

Primary contains all the Transactions that I need, and the Txn_ID.

Each child could contain details of a particular type. It is not mandatory that a row in Primary will have a row in a child.

So if I have 100 rows in Primary, each Child table could contain 0-100 rows.

I want my output to have 100 rows with the details from the child rows added. So am trying to make my data horizontal.

How do I do that?

Thanks a lot again.

Kar

|||

I would suggest that you learn to create your views and queries from text and not use the GUI tool.

|||

Outer Left Join is actually same as Left join. So either you can use TSQL to create the view or if you want to use the UI then u can right click on the link joining the two tables and chose to select all rows from the parent table - this will automatically modify the join from INNER JOIN to the corresponding OUTER JOIN.

Hope that helps,

Kuntal

|||

Thanks for your replies, I tried left outer joins too, but result isnt what I want.

What I want is like a lookup. Please see example below:

Parent table has 1000 rows.

Child 1 has optional details1 for 50 rows.

Child 2 has optional details2 for 50 rows etc.

My view should contian only 1000 rows, with Details1 lookup from Child 1 and so on.

Left Outer join gives me 1100 rows in this example.

TIA

Kar

|||

Your query should look like -

select p.parentColPrimaryKey, c1.child1optkey1, c2.child2optkey2...

from parentTable as p

left outer join child1 as c1 on c1.primaryKey = p.parentColPrimaryKey

left outer join child2 as c2 on c2.primaryKey = p.parentColPrimaryKey

...

This should return you 1000 rows if your parentTable has 1000 rows and if there is no value in c2 corresponds to some p.parentColPrimaryKey, c2.child2optkey2 will be null for that row.

Hope that helps,

Kuntal

|||

Nope, this is what Management Studio generates, but this query does not give me 1000 rows, it gives me 1000 rows, plus rows in c1, plus rows in c2 etc.

Problem Creating View from Management Studio

Hi:

I have a parent table and 5 child tables with a PK,FK reference. I designed a view in Management Studio by dropping the master and Child tables, so that I could see the parent data, and all details from child rows.

My view does not contain any data though my tables do. What have I done wrong?

To describe what I want:

I have Parent table with PK, ParentFields.

I have Child1 with FK, Details1, Child2 with FK, Details2 and so on.

I want a view that shows PK, ParentFields, Details1, Details2,..Details5.

Seems simple enough, I dont know what is wrong. I am a beginner and only use the Designer to do this kind of stuff.

Appreciate any help.

TIA

Kar

You can create a simple query such as

CREATE VIEW [dbo].[vVesselsCargo]
AS
SELECT
VC.VesselCargoID,
CMP.CompanyName Shipper
FROM
VesselCargo VC,
Companies CMP
WHERE
CMP.CompanieID = VC.ShipperID

Then you can see how Management Studio was create that View in design mode.

|||

Your problem needs to be described better including (1) the definition of the parent table and the 5 child tables and (2) the definition of the view as it presently exists. My knee-jerk guess would be that you have used an inner join in a situation that merits a left join. Examine this sequence:

Code Snippet

declare @.parent table (pk int, pkInfo varchar(20))
insert into @.parent
select 1, '1st Parent' union all select 2, '2nd Parent'
--select * from @.parent

declare @.child1 table (pk int, detail_1 varchar(30))
insert into @.child1
select 1, 'Pk #1: Detail Rec #1' union all
select 2, 'Pk #2: Detail Rec #2'

declare @.child2 table (pk int, detail_2 varchar(30))
insert into @.child2
select 2, 'Pk #2: Detail Rec #2'

declare @.child3 table (pk int, detail_3 varchar(30))
insert into @.child3
select 1, 'Pk #1: Detail Rec #3'

declare @.child4 table(pk int, detail_4 varchar(30))
insert into @.child4
select 1, 'Pk #1: detail Rec #4'

declare @.child5 table(pk int, detail_5 varchar(30))
insert into @.child5
select 1, 'Pk #1: detail Rec #5'

select p.pk,
p.pkInfo,
detail_1,
detail_2,
detail_3,
detail_4,
detail_5
from @.parent p
join @.child1 c1
on p.pk = c1.pk
join @.child2 c2
on p.pk = c2.pk
join @.child3 c3
on p.pk = c3.pk
join @.child4 c4
on p.pk = c4.pk
join @.child5 c5
on p.pk = c5.pk

/*
pk pkInfo detail_1 detail_2 detail_3 detail_4 detail_5
- -

(0 row(s) affected)

*/


Because there are cases in which one of the detail records is missing, no rows are returned; however, if you change the JOINs into LEFT JOINS, you get results for both PKs:

Code Snippet

select p.pk,
p.pkInfo,
detail_1,
detail_2,
detail_3,
detail_4,
detail_5
from @.parent p
left join @.child1 c1
on p.pk = c1.pk
left join @.child2 c2
on p.pk = c2.pk
left join @.child3 c3
on p.pk = c3.pk
left join @.child4 c4
on p.pk = c4.pk
left join @.child5 c5
on p.pk = c5.pk

/*
pk pkInfo detail_1 detail_2 detail_3 detail_4 detail_5
- - - - - -
1 1st Parent Pk #1: Detail Rec #1 NULL Pk #1: Detail Rec #3 Pk #1: detail Rec #4 Pk #1: detail Rec #5
2 2nd Parent Pk #2: Detail Rec #2 Pk #2: Detail Rec #2 NULL NULL NULL
*/

Perhaps you need to use a left join to return your data; however, you really need to give a better description of your problem.

|||

Hi:

Thanks for the replies. I am a beginner, and I did not use or create any SQL. I just used Management Studio, drag-dropped tables and selected the fields that I want.

The tables already have PK and FK relationships defined, and the Designer automatically created SQL that roughly looks like:

Select Primary.PK, Primary.<Other_Fields>, Child1.<Details>, Child2.<Details> ,Child3.<Details> etc

from primary inner join Child1 on Primary.PK=Secondary.FK etc.

I tried modifying it to Left join as you suggested, but Management Studio automatically changes it to Left Outer Join when I save the view.

My data is as follows:

Primary contains all the Transactions that I need, and the Txn_ID.

Each child could contain details of a particular type. It is not mandatory that a row in Primary will have a row in a child.

So if I have 100 rows in Primary, each Child table could contain 0-100 rows.

I want my output to have 100 rows with the details from the child rows added. So am trying to make my data horizontal.

How do I do that?

Thanks a lot again.

Kar

|||

I would suggest that you learn to create your views and queries from text and not use the GUI tool.

|||

Outer Left Join is actually same as Left join. So either you can use TSQL to create the view or if you want to use the UI then u can right click on the link joining the two tables and chose to select all rows from the parent table - this will automatically modify the join from INNER JOIN to the corresponding OUTER JOIN.

Hope that helps,

Kuntal

|||

Thanks for your replies, I tried left outer joins too, but result isnt what I want.

What I want is like a lookup. Please see example below:

Parent table has 1000 rows.

Child 1 has optional details1 for 50 rows.

Child 2 has optional details2 for 50 rows etc.

My view should contian only 1000 rows, with Details1 lookup from Child 1 and so on.

Left Outer join gives me 1100 rows in this example.

TIA

Kar

|||

Your query should look like -

select p.parentColPrimaryKey, c1.child1optkey1, c2.child2optkey2...

from parentTable as p

left outer join child1 as c1 on c1.primaryKey = p.parentColPrimaryKey

left outer join child2 as c2 on c2.primaryKey = p.parentColPrimaryKey

...

This should return you 1000 rows if your parentTable has 1000 rows and if there is no value in c2 corresponds to some p.parentColPrimaryKey, c2.child2optkey2 will be null for that row.

Hope that helps,

Kuntal

|||

Nope, this is what Management Studio generates, but this query does not give me 1000 rows, it gives me 1000 rows, plus rows in c1, plus rows in c2 etc.

Problem Creating View from Management Studio

Hi:

I have a parent table and 5 child tables with a PK,FK reference. I designed a view in Management Studio by dropping the master and Child tables, so that I could see the parent data, and all details from child rows.

My view does not contain any data though my tables do. What have I done wrong?

To describe what I want:

I have Parent table with PK, ParentFields.

I have Child1 with FK, Details1, Child2 with FK, Details2 and so on.

I want a view that shows PK, ParentFields, Details1, Details2,..Details5.

Seems simple enough, I dont know what is wrong. I am a beginner and only use the Designer to do this kind of stuff.

Appreciate any help.

TIA

Kar

You can create a simple query such as

CREATE VIEW [dbo].[vVesselsCargo]
AS
SELECT
VC.VesselCargoID,
CMP.CompanyName Shipper
FROM
VesselCargo VC,
Companies CMP
WHERE
CMP.CompanieID = VC.ShipperID

Then you can see how Management Studio was create that View in design mode.

|||

Your problem needs to be described better including (1) the definition of the parent table and the 5 child tables and (2) the definition of the view as it presently exists. My knee-jerk guess would be that you have used an inner join in a situation that merits a left join. Examine this sequence:

Code Snippet

declare @.parent table (pk int, pkInfo varchar(20))
insert into @.parent
select 1, '1st Parent' union all select 2, '2nd Parent'
--select * from @.parent

declare @.child1 table (pk int, detail_1 varchar(30))
insert into @.child1
select 1, 'Pk #1: Detail Rec #1' union all
select 2, 'Pk #2: Detail Rec #2'

declare @.child2 table (pk int, detail_2 varchar(30))
insert into @.child2
select 2, 'Pk #2: Detail Rec #2'

declare @.child3 table (pk int, detail_3 varchar(30))
insert into @.child3
select 1, 'Pk #1: Detail Rec #3'

declare @.child4 table(pk int, detail_4 varchar(30))
insert into @.child4
select 1, 'Pk #1: detail Rec #4'

declare @.child5 table(pk int, detail_5 varchar(30))
insert into @.child5
select 1, 'Pk #1: detail Rec #5'

select p.pk,
p.pkInfo,
detail_1,
detail_2,
detail_3,
detail_4,
detail_5
from @.parent p
join @.child1 c1
on p.pk = c1.pk
join @.child2 c2
on p.pk = c2.pk
join @.child3 c3
on p.pk = c3.pk
join @.child4 c4
on p.pk = c4.pk
join @.child5 c5
on p.pk = c5.pk

/*
pk pkInfo detail_1 detail_2 detail_3 detail_4 detail_5
- -

(0 row(s) affected)

*/


Because there are cases in which one of the detail records is missing, no rows are returned; however, if you change the JOINs into LEFT JOINS, you get results for both PKs:

Code Snippet

select p.pk,
p.pkInfo,
detail_1,
detail_2,
detail_3,
detail_4,
detail_5
from @.parent p
left join @.child1 c1
on p.pk = c1.pk
left join @.child2 c2
on p.pk = c2.pk
left join @.child3 c3
on p.pk = c3.pk
left join @.child4 c4
on p.pk = c4.pk
left join @.child5 c5
on p.pk = c5.pk

/*
pk pkInfo detail_1 detail_2 detail_3 detail_4 detail_5
- - - - - -
1 1st Parent Pk #1: Detail Rec #1 NULL Pk #1: Detail Rec #3 Pk #1: detail Rec #4 Pk #1: detail Rec #5
2 2nd Parent Pk #2: Detail Rec #2 Pk #2: Detail Rec #2 NULL NULL NULL
*/

Perhaps you need to use a left join to return your data; however, you really need to give a better description of your problem.

|||

Hi:

Thanks for the replies. I am a beginner, and I did not use or create any SQL. I just used Management Studio, drag-dropped tables and selected the fields that I want.

The tables already have PK and FK relationships defined, and the Designer automatically created SQL that roughly looks like:

Select Primary.PK, Primary.<Other_Fields>, Child1.<Details>, Child2.<Details> ,Child3.<Details> etc

from primary inner join Child1 on Primary.PK=Secondary.FK etc.

I tried modifying it to Left join as you suggested, but Management Studio automatically changes it to Left Outer Join when I save the view.

My data is as follows:

Primary contains all the Transactions that I need, and the Txn_ID.

Each child could contain details of a particular type. It is not mandatory that a row in Primary will have a row in a child.

So if I have 100 rows in Primary, each Child table could contain 0-100 rows.

I want my output to have 100 rows with the details from the child rows added. So am trying to make my data horizontal.

How do I do that?

Thanks a lot again.

Kar

|||

I would suggest that you learn to create your views and queries from text and not use the GUI tool.

|||

Outer Left Join is actually same as Left join. So either you can use TSQL to create the view or if you want to use the UI then u can right click on the link joining the two tables and chose to select all rows from the parent table - this will automatically modify the join from INNER JOIN to the corresponding OUTER JOIN.

Hope that helps,

Kuntal

|||

Thanks for your replies, I tried left outer joins too, but result isnt what I want.

What I want is like a lookup. Please see example below:

Parent table has 1000 rows.

Child 1 has optional details1 for 50 rows.

Child 2 has optional details2 for 50 rows etc.

My view should contian only 1000 rows, with Details1 lookup from Child 1 and so on.

Left Outer join gives me 1100 rows in this example.

TIA

Kar

|||

Your query should look like -

select p.parentColPrimaryKey, c1.child1optkey1, c2.child2optkey2...

from parentTable as p

left outer join child1 as c1 on c1.primaryKey = p.parentColPrimaryKey

left outer join child2 as c2 on c2.primaryKey = p.parentColPrimaryKey

...

This should return you 1000 rows if your parentTable has 1000 rows and if there is no value in c2 corresponds to some p.parentColPrimaryKey, c2.child2optkey2 will be null for that row.

Hope that helps,

Kuntal

|||

Nope, this is what Management Studio generates, but this query does not give me 1000 rows, it gives me 1000 rows, plus rows in c1, plus rows in c2 etc.

Saturday, February 25, 2012

Problem copying a database for testing purposes

I want to make a database copy on the same computer for testing purposes.
In BOL I found in the Transact-SQL Reference:
It may be necessary to create a copy of an existing database on the same
computer for testing purposes. In this case, the database files for the
original database already exist, so different file names need to be
specified when the database copy is created during the restore operation.
Make a copy of a database using BACKUP and RESTORE:
This example uses both the BACKUP and RESTORE statements to make a copy of
the Northwind database (I changed the destination path)
BACKUP DATABASE Northwind
TO DISK = 'c:\Northwind.bak'
RESTORE FILELISTONLY FROM DISK = 'c:\Northwind.bak'
RESTORE DATABASE TestDB
FROM DISK = 'c:\Northwind.bak'
WITH MOVE 'Northwind' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\testdb.mdf',
MOVE 'Northwind_log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\testdb.ldf'
GO
This works fine, using SQLserver 2000 Developer with the Query analyzer.
I deleted the testdb database (in the Enterprise manager, testdb.mdf and
.ldf also disapeared)
Then I tried the production database:
BACKUP DATABASE Vineadb
TO DISK = 'C:\Vineadb.bak'
RESTORE FILELISTONLY
FROM DISK = 'C:\Vineadb.bak'
RESTORE DATABASE TestDB
FROM DISK = 'C:\Vineadb.bak'
WITH MOVE 'Vineadb' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\testdb.mdf',
MOVE 'Vineadb_log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\testdb.ldf'
GO
This didn't work, giving the message:
Processed 1848 pages for database 'Vineadb', file 'VineaDb_Data' on file 1.
Processed 1 pages for database 'Vineadb', file 'VineaDb_Log' on file 1.
BACKUP DATABASE successfully processed 1849 pages in 9.388 seconds (1.612
MB/sec).
(2 row(s) affected)
Server: Msg 3234, Level 16, State 2, Line 5 Logical file 'Vineadb' is not
part of database 'TestDB'. Use RESTORE FILELISTONLY to list the logical
file names. Server: Msg 3013, Level 16, State 1, Line 5 RESTORE DATABASE
is terminating abnormally
What could be the reason?
Is there a solution?
I am a Newbe on sqlserver
Vineadb.bak is created (15MB)
testdb.ldf and testdb.mdf are not created.What is the output of
RESTORE FILELISTONLY
FROM DISK = 'C:\Vineadb.bak'
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Henk Schreij" <henk@.schreijDOTnl> wrote in message
news:%23UeRnPh2DHA.2428@.tk2msftngp13.phx.gbl...
> I want to make a database copy on the same computer for testing purposes.
> In BOL I found in the Transact-SQL Reference:
> It may be necessary to create a copy of an existing database on the same
> computer for testing purposes. In this case, the database files for the
> original database already exist, so different file names need to be
> specified when the database copy is created during the restore operation.
> Make a copy of a database using BACKUP and RESTORE:
> This example uses both the BACKUP and RESTORE statements to make a copy of
> the Northwind database (I changed the destination path)
> BACKUP DATABASE Northwind
> TO DISK = 'c:\Northwind.bak'
> RESTORE FILELISTONLY FROM DISK = 'c:\Northwind.bak'
> RESTORE DATABASE TestDB
> FROM DISK = 'c:\Northwind.bak'
> WITH MOVE 'Northwind' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\testdb.mdf',
> MOVE 'Northwind_log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\testdb.ldf'
> GO
> This works fine, using SQLserver 2000 Developer with the Query analyzer.
> I deleted the testdb database (in the Enterprise manager, testdb.mdf and
> .ldf also disapeared)
> Then I tried the production database:
> BACKUP DATABASE Vineadb
> TO DISK = 'C:\Vineadb.bak'
> RESTORE FILELISTONLY
> FROM DISK = 'C:\Vineadb.bak'
> RESTORE DATABASE TestDB
> FROM DISK = 'C:\Vineadb.bak'
> WITH MOVE 'Vineadb' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\testdb.mdf',
> MOVE 'Vineadb_log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\testdb.ldf'
> GO
> This didn't work, giving the message:
> Processed 1848 pages for database 'Vineadb', file 'VineaDb_Data' on file
1.
> Processed 1 pages for database 'Vineadb', file 'VineaDb_Log' on file 1.
> BACKUP DATABASE successfully processed 1849 pages in 9.388 seconds (1.612
> MB/sec).
> (2 row(s) affected)
> Server: Msg 3234, Level 16, State 2, Line 5 Logical file 'Vineadb' is not
> part of database 'TestDB'. Use RESTORE FILELISTONLY to list the logical
> file names. Server: Msg 3013, Level 16, State 1, Line 5 RESTORE DATABASE
> is terminating abnormally
> What could be the reason?
> Is there a solution?
> I am a Newbe on sqlserver
> Vineadb.bak is created (15MB)
> testdb.ldf and testdb.mdf are not created.
>
>|||Jasper, using
RESTORE FILELISTONLY FROM DISK = 'C:\VineaDb.bak'
I get a Grid with:
. LogicalName
. Physical Name
. Type
. FileGroupName
. Size
1.
VineaDb_Data
C:\Program Files\Microsoft SQL Server\MSSQL\data\VineaDb_Data.MDF
D
PRIMARY
16449536
2.
VineaDb_Log
C:\Program Files\Microsoft SQL Server\MSSQL\data\VineaDb_Log.LDF
L
NULL
104595456
This is the path to the production database,
not to the just made C:\VineaDb.bak backup.
Could this be the reason?
Then I ask myself, why does it take these files, not the backup.
Remember, ... I am a newbie on SQLServer.
Henk.
-- Original Message --
From: "Jasper Smith" <jasper_smith9@.hotmail.com>
Newsgroups: microsoft.public.sqlserver.server
Sent: Wednesday, January 14, 2004 1:07 AM
Subject: Re: Problem copying a database for testing purposes
> What is the output of
> RESTORE FILELISTONLY
> FROM DISK = 'C:\Vineadb.bak'
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "Henk Schreij" <henk@.schreijDOTnl> wrote in message
> news:%23UeRnPh2DHA.2428@.tk2msftngp13.phx.gbl...
> > I want to make a database copy on the same computer for testing
purposes.
> >
> > In BOL I found in the Transact-SQL Reference:
> > ( . . . )
> > This example uses both the BACKUP and RESTORE statements to make a copy
of
> > the Northwind database (I changed the destination path)
> > ( . . . )
> > This works fine, using SQLserver 2000 Developer with the Query analyzer.
> > I deleted the testdb database (in the Enterprise manager, testdb.mdf and
> > .ldf also disapeared)
> >
> > Then I tried the production database:
> > BACKUP DATABASE Vineadb
> > TO DISK = 'C:\Vineadb.bak'
> > RESTORE FILELISTONLY
> > FROM DISK = 'C:\Vineadb.bak'
> > RESTORE DATABASE TestDB
> > FROM DISK = 'C:\Vineadb.bak'
> > WITH MOVE 'Vineadb' TO 'C:\Program Files\Microsoft SQL
> > Server\MSSQL\Data\testdb.mdf',
> > MOVE 'Vineadb_log' TO 'C:\Program Files\Microsoft SQL
> > Server\MSSQL\Data\testdb.ldf'
> > GO
> >
> > This didn't work, giving the message:
> >
> > Processed 1848 pages for database 'Vineadb', file 'VineaDb_Data' on file
> 1.
> > Processed 1 pages for database 'Vineadb', file 'VineaDb_Log' on file 1.
> > BACKUP DATABASE successfully processed 1849 pages in 9.388 seconds
(1.612
> > MB/sec).
> >
> > (2 row(s) affected)
> >
> > Server: Msg 3234, Level 16, State 2, Line 5 Logical file 'Vineadb' is
not
> > part of database 'TestDB'. Use RESTORE FILELISTONLY to list the
logical
> > file names. Server: Msg 3013, Level 16, State 1, Line 5 RESTORE
DATABASE
> > is terminating abnormally|||Its simply a case of not having the correct logical filename for the data
file
RESTORE DATABASE TestDB
FROM DISK = 'C:\Vineadb.bak'
WITH MOVE 'VineaDb_Data' TO 'C:\Program Files\Microsoft
SQLServer\MSSQL\Data\testdb.mdf',
MOVE 'VineaDb_Log' TO 'C:\Program Files\Microsoft
SQLServer\MSSQL\Data\testdb.ldf'
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Henk Schreij" <henk@.schreijDOTnl> wrote in message
news:%23JmoL7n2DHA.1924@.TK2MSFTNGP10.phx.gbl...
> Jasper, using
> RESTORE FILELISTONLY FROM DISK = 'C:\VineaDb.bak'
> I get a Grid with:
> . LogicalName
> . Physical Name
> . Type
> . FileGroupName
> . Size
> 1.
> VineaDb_Data
> C:\Program Files\Microsoft SQL Server\MSSQL\data\VineaDb_Data.MDF
> D
> PRIMARY
> 16449536
> 2.
> VineaDb_Log
> C:\Program Files\Microsoft SQL Server\MSSQL\data\VineaDb_Log.LDF
> L
> NULL
> 104595456
> This is the path to the production database,
> not to the just made C:\VineaDb.bak backup.
> Could this be the reason?
> Then I ask myself, why does it take these files, not the backup.
> Remember, ... I am a newbie on SQLServer.
> Henk.
> -- Original Message --
> From: "Jasper Smith" <jasper_smith9@.hotmail.com>
> Newsgroups: microsoft.public.sqlserver.server
> Sent: Wednesday, January 14, 2004 1:07 AM
> Subject: Re: Problem copying a database for testing purposes
>
> > What is the output of
> >
> > RESTORE FILELISTONLY
> > FROM DISK = 'C:\Vineadb.bak'
> >
> > --
> > HTH
> >
> > Jasper Smith (SQL Server MVP)
> >
> > I support PASS - the definitive, global
> > community for SQL Server professionals -
> > http://www.sqlpass.org
> >
> >
> > "Henk Schreij" <henk@.schreijDOTnl> wrote in message
> > news:%23UeRnPh2DHA.2428@.tk2msftngp13.phx.gbl...
> > > I want to make a database copy on the same computer for testing
> purposes.
> > >
> > > In BOL I found in the Transact-SQL Reference:
> > > ( . . . )
> > > This example uses both the BACKUP and RESTORE statements to make a
copy
> of
> > > the Northwind database (I changed the destination path)
> > > ( . . . )
> > > This works fine, using SQLserver 2000 Developer with the Query
analyzer.
> > > I deleted the testdb database (in the Enterprise manager, testdb.mdf
and
> > > .ldf also disapeared)
> > >
> > > Then I tried the production database:
> > > BACKUP DATABASE Vineadb
> > > TO DISK = 'C:\Vineadb.bak'
> > > RESTORE FILELISTONLY
> > > FROM DISK = 'C:\Vineadb.bak'
> > > RESTORE DATABASE TestDB
> > > FROM DISK = 'C:\Vineadb.bak'
> > > WITH MOVE 'Vineadb' TO 'C:\Program Files\Microsoft SQL
> > > Server\MSSQL\Data\testdb.mdf',
> > > MOVE 'Vineadb_log' TO 'C:\Program Files\Microsoft SQL
> > > Server\MSSQL\Data\testdb.ldf'
> > > GO
> > >
> > > This didn't work, giving the message:
> > >
> > > Processed 1848 pages for database 'Vineadb', file 'VineaDb_Data' on
file
> > 1.
> > > Processed 1 pages for database 'Vineadb', file 'VineaDb_Log' on file
1.
> > > BACKUP DATABASE successfully processed 1849 pages in 9.388 seconds
> (1.612
> > > MB/sec).
> > >
> > > (2 row(s) affected)
> > >
> > > Server: Msg 3234, Level 16, State 2, Line 5 Logical file 'Vineadb' is
> not
> > > part of database 'TestDB'. Use RESTORE FILELISTONLY to list the
> logical
> > > file names. Server: Msg 3013, Level 16, State 1, Line 5 RESTORE
> DATABASE
> > > is terminating abnormally
>
>|||Jasper,
Thank you,
This is what I needed.
Now it workes fine.
I'm so glad you told me that I was making a simple mistake.
I couldn't find out (being a newbie) what I was doing wrong.
Henk
"Jasper Smith" <jasper_smith9@.hotmail.com> schreef in bericht
news:e1EA65o2DHA.1744@.TK2MSFTNGP12.phx.gbl...
> Its simply a case of not having the correct logical filename for the data
> file
> RESTORE DATABASE TestDB
> FROM DISK = 'C:\Vineadb.bak'
> WITH MOVE 'VineaDb_Data' TO 'C:\Program Files\Microsoft
> SQLServer\MSSQL\Data\testdb.mdf',
> MOVE 'VineaDb_Log' TO 'C:\Program Files\Microsoft
> SQLServer\MSSQL\Data\testdb.ldf'
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "Henk Schreij" <henk@.schreijDOTnl> wrote in message
> news:%23JmoL7n2DHA.1924@.TK2MSFTNGP10.phx.gbl...
> >
> > Jasper, using
> > RESTORE FILELISTONLY FROM DISK = 'C:\VineaDb.bak'
> > I get a Grid with:
> > . LogicalName
> > . Physical Name
> > . Type
> > . FileGroupName
> > . Size
> > 1.
> > VineaDb_Data
> > C:\Program Files\Microsoft SQL Server\MSSQL\data\VineaDb_Data.MDF
> > D
> > PRIMARY
> > 16449536
> > 2.
> > VineaDb_Log
> > C:\Program Files\Microsoft SQL Server\MSSQL\data\VineaDb_Log.LDF
> > L
> > NULL
> > 104595456
> >
> > This is the path to the production database,
> > not to the just made C:\VineaDb.bak backup.
> > Could this be the reason?
> > Then I ask myself, why does it take these files, not the backup.
> > Remember, ... I am a newbie on SQLServer.
> >
> > Henk.
> >
> > -- Original Message --
> > From: "Jasper Smith" <jasper_smith9@.hotmail.com>
> > Newsgroups: microsoft.public.sqlserver.server
> > Sent: Wednesday, January 14, 2004 1:07 AM
> > Subject: Re: Problem copying a database for testing purposes
> >
> >
> > > What is the output of
> > >
> > > RESTORE FILELISTONLY
> > > FROM DISK = 'C:\Vineadb.bak'
> > >
> > > --
> > > HTH
> > >
> > > Jasper Smith (SQL Server MVP)
> > >
> > > I support PASS - the definitive, global
> > > community for SQL Server professionals -
> > > http://www.sqlpass.org
> > >
> > >
> > > "Henk Schreij" <henk@.schreijDOTnl> wrote in message
> > > news:%23UeRnPh2DHA.2428@.tk2msftngp13.phx.gbl...
> > > > I want to make a database copy on the same computer for testing
> > purposes.
> > > >
> > > > In BOL I found in the Transact-SQL Reference:
> > > > ( . . . )
> > > > This example uses both the BACKUP and RESTORE statements to make a
> copy
> > of
> > > > the Northwind database (I changed the destination path)
> > > > ( . . . )
> > > > This works fine, using SQLserver 2000 Developer with the Query
> analyzer.
> > > > I deleted the testdb database (in the Enterprise manager, testdb.mdf
> and
> > > > .ldf also disapeared)
> > > >
> > > > Then I tried the production database:
> > > > BACKUP DATABASE Vineadb
> > > > TO DISK = 'C:\Vineadb.bak'
> > > > RESTORE FILELISTONLY
> > > > FROM DISK = 'C:\Vineadb.bak'
> > > > RESTORE DATABASE TestDB
> > > > FROM DISK = 'C:\Vineadb.bak'
> > > > WITH MOVE 'Vineadb' TO 'C:\Program Files\Microsoft SQL
> > > > Server\MSSQL\Data\testdb.mdf',
> > > > MOVE 'Vineadb_log' TO 'C:\Program Files\Microsoft SQL
> > > > Server\MSSQL\Data\testdb.ldf'
> > > > GO
> > > >
> > > > This didn't work, giving the message:
> > > >
> > > > Processed 1848 pages for database 'Vineadb', file 'VineaDb_Data' on
> file
> > > 1.
> > > > Processed 1 pages for database 'Vineadb', file 'VineaDb_Log' on file
> 1.
> > > > BACKUP DATABASE successfully processed 1849 pages in 9.388 seconds
> > (1.612
> > > > MB/sec).
> > > >
> > > > (2 row(s) affected)
> > > >
> > > > Server: Msg 3234, Level 16, State 2, Line 5 Logical file 'Vineadb'
is
> > not
> > > > part of database 'TestDB'. Use RESTORE FILELISTONLY to list the
> > logical
> > > > file names. Server: Msg 3013, Level 16, State 1, Line 5 RESTORE
> > DATABASE
> > > > is terminating abnormally
> >
> >
> >
> >
>|||Jasper,
Thank you,
This is what I needed.
Now it workes fine.
I'm so glad you told me that I was making a simple mistake.
I couldn't find out (being a newbie) what I was doing wrong.
Henk
"Jasper Smith" <jasper_smith9@.hotmail.com> schreef in bericht
news:e1EA65o2DHA.1744@.TK2MSFTNGP12.phx.gbl...
> Its simply a case of not having the correct logical filename for the data
> file
> RESTORE DATABASE TestDB
> FROM DISK = 'C:\Vineadb.bak'
> WITH MOVE 'VineaDb_Data' TO 'C:\Program Files\Microsoft
> SQLServer\MSSQL\Data\testdb.mdf',
> MOVE 'VineaDb_Log' TO 'C:\Program Files\Microsoft
> SQLServer\MSSQL\Data\testdb.ldf'
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "Henk Schreij" <henk@.schreijDOTnl> wrote in message
> news:%23JmoL7n2DHA.1924@.TK2MSFTNGP10.phx.gbl...
> >
> > Jasper, using
> > RESTORE FILELISTONLY FROM DISK = 'C:\VineaDb.bak'
> > I get a Grid with:
> > . LogicalName
> > . Physical Name
> > . Type
> > . FileGroupName
> > . Size
> > 1.
> > VineaDb_Data
> > C:\Program Files\Microsoft SQL Server\MSSQL\data\VineaDb_Data.MDF
> > D
> > PRIMARY
> > 16449536
> > 2.
> > VineaDb_Log
> > C:\Program Files\Microsoft SQL Server\MSSQL\data\VineaDb_Log.LDF
> > L
> > NULL
> > 104595456
> >
> > This is the path to the production database,
> > not to the just made C:\VineaDb.bak backup.
> > Could this be the reason?
> > Then I ask myself, why does it take these files, not the backup.
> > Remember, ... I am a newbie on SQLServer.
> >
> > Henk.
> >
> > -- Original Message --
> > From: "Jasper Smith" <jasper_smith9@.hotmail.com>
> > Newsgroups: microsoft.public.sqlserver.server
> > Sent: Wednesday, January 14, 2004 1:07 AM
> > Subject: Re: Problem copying a database for testing purposes
> >
> >
> > > What is the output of
> > >
> > > RESTORE FILELISTONLY
> > > FROM DISK = 'C:\Vineadb.bak'
> > >
> > > --
> > > HTH
> > >
> > > Jasper Smith (SQL Server MVP)
> > >
> > > I support PASS - the definitive, global
> > > community for SQL Server professionals -
> > > http://www.sqlpass.org
> > >
> > >
> > > "Henk Schreij" <henk@.schreijDOTnl> wrote in message
> > > news:%23UeRnPh2DHA.2428@.tk2msftngp13.phx.gbl...
> > > > I want to make a database copy on the same computer for testing
> > purposes.
> > > >
> > > > In BOL I found in the Transact-SQL Reference:
> > > > ( . . . )
> > > > This example uses both the BACKUP and RESTORE statements to make
> > > > copy of the Northwind database (I changed the destination path)
> > > > ( . . . )
> > > > This works fine, using SQLserver 2000 Developer with Query analyzer.
> > > > I deleted the testdb database (in the Enterprise manager)
> > > >
> > > > Then I tried the production database:
> > > > BACKUP DATABASE Vineadb
> > > > TO DISK = 'C:\Vineadb.bak'
> > > > RESTORE FILELISTONLY
> > > > FROM DISK = 'C:\Vineadb.bak'
> > > > RESTORE DATABASE TestDB
> > > > FROM DISK = 'C:\Vineadb.bak'
> > > > WITH MOVE 'Vineadb' TO 'C:\Program Files\Microsoft SQL
> > > > Server\MSSQL\Data\testdb.mdf',
> > > > MOVE 'Vineadb_log' TO 'C:\Program Files\Microsoft SQL
> > > > Server\MSSQL\Data\testdb.ldf'
> > > > GO
> > > >
> > > > This didn't work, giving the message:
> > > >
> > > > Processed 1848 pages for db 'Vineadb', file 'VineaDb_Data' on file
1.
> > > > Processed 1 pages for db 'Vineadb', file 'VineaDb_Log' on file 1.
> > > > BACKUP DATABASE successfully processed 1849 pages in 9.388 sec
> > > > (2 row(s) affected)
> > > >
> > > > Server: Msg 3234, Level 16, State 2, Line 5 Logical file 'Vineadb'
> > > > is not part of database 'TestDB'.
> > > > Use RESTORE FILELISTONLY to list the logical
> > > > file names. Server: Msg 3013, Level 16, State 1, Line 5
> > > > RESTORE DATABASE is terminating abnormally