filter data from multiple tables crashes app!

i didnt think my sql qeury was that complicated that it would crash my web
app. all im trying to do is filter data between two tables. heres my query

<cfquery name="GetResults" datasource="#datasource#">
SELECT *
FROM Content, Content_Sites
WHERE Content.ContentID <> Content_Sites.ContentID
ORDER BY Content.ContentID DESC
</cfquery
equals works, but when i try not equals, it all goes haywire. any ideas?

TIAjonezy (jonezy@.donotmailmejonezy.com) writes:
> i didnt think my sql qeury was that complicated that it would crash my
> web app. all im trying to do is filter data between two tables. heres
> my query
><cfquery name="GetResults" datasource="#datasource#">
> SELECT *
> FROM Content, Content_Sites
> WHERE Content.ContentID <> Content_Sites.ContentID
> ORDER BY Content.ContentID DESC
></cfquery>
> equals works, but when i try not equals, it all goes haywire. any ideas?

Yes and no. Since I don't know your tables, and neither know what you
are trying to achieve, how could I really have any ideas?

But, OK, having seen people using <> in the wrong place before, I can
make a guess. Say that both table have a thousand rows. You are now
asking for all million combinations of these two thousand rows - save
those that have the same ID.

I guess what you are looking for is really something like:

SELECT *
FROM Content c ,
WHERE NOT EXISTS (SELECT *
FROM Content_Sites cs
WHERE c.ContentID = cs.ContentID)
ORDER BY c.ContentID DESC

That is, list all Content that does not have any content site.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||In article <vl786sjaijmr91@.corp.supernews.com>,
jonezy@.donotmailmejonezy.com says...
> i didnt think my sql qeury was that complicated that it would crash my web
> app. all im trying to do is filter data between two tables. heres my query
> <cfquery name="GetResults" datasource="#datasource#">
> SELECT *
> FROM Content, Content_Sites
> WHERE Content.ContentID <> Content_Sites.ContentID
> ORDER BY Content.ContentID DESC
> </cfquery>
> equals works, but when i try not equals, it all goes haywire. any ideas?

How big are the tables? You DO realize you've asked for a
cross product? That means the database is returning the
ENTIRE contents (minus one row) of the Content_Sites table
for each ROW of the Content table.

Assuming 500 rows in Content_Sites and 1000 rows in
Content, you are getting back 500,000 rows in your query.

--
Cam|||thanks.., dunno how i overlookd NOT EXISTS.

i also realized i forgot to include the table connection between content and
content_sites. guess i was in a hurry.

thanks again!

"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns93E9E8B075BFAYazorman@.127.0.0.1...
> jonezy (jonezy@.donotmailmejonezy.com) writes:
> > i didnt think my sql qeury was that complicated that it would crash my
> > web app. all im trying to do is filter data between two tables. heres
> > my query
> ><cfquery name="GetResults" datasource="#datasource#">
> > SELECT *
> > FROM Content, Content_Sites
> > WHERE Content.ContentID <> Content_Sites.ContentID
> > ORDER BY Content.ContentID DESC
> ></cfquery>
> > equals works, but when i try not equals, it all goes haywire. any
ideas?
> Yes and no. Since I don't know your tables, and neither know what you
> are trying to achieve, how could I really have any ideas?
> But, OK, having seen people using <> in the wrong place before, I can
> make a guess. Say that both table have a thousand rows. You are now
> asking for all million combinations of these two thousand rows - save
> those that have the same ID.
> I guess what you are looking for is really something like:
> SELECT *
> FROM Content c ,
> WHERE NOT EXISTS (SELECT *
> FROM Content_Sites cs
> WHERE c.ContentID = cs.ContentID)
> ORDER BY c.ContentID DESC
> That is, list all Content that does not have any content site.
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.aspsql

Filter a Report's Table using multiple Like conditions using Or

I would like to filter a report using multiple Like conditions

or - Change a filter in Reporting Services to OR rather than AND..

Example: (SameFieldName Like *1100) or (SameFieldName Like *1200).

When I try doing this on the Report's table properties - Filters tab - the And/Or automatically changes to "And" with using the "Like" Operator.

The only time "Or" appears is when I use the " = " Operator.

Or can someone show me how to use an expression to filter on multiple Like conditions.

Thanks!

Try the following filter:

Filter expression:
=(Fields!FName.Value like "*1100" OR Fields!FName.Value like "*1200")

Filter operator:
=

Filter value:
=True

-- Robert

|||You got it! It works!

Thank you

BBK

Filename Variable Trouble

I want have multiple records from 1-4500 that I must insert a picture into the database for. I am trying to set up a variable to do this if someone can show me my error I would apprieciate it. This table has 4 columns (id# Float is a FK) (ImageName nvarchar (10)) (ImageFile Varbinary(Max)) (rec# int PK Increment Seed).

declare @.jpg as int

Set @.jpg = 0

While @.jpg <4500

begin

set @.jpg = (@.jpg + 1)

use consumer

insert photo (id#, ImageName, ImageFile)

Select @.jpg, '@.jpg.jpg',

bulkcolumn from Openrowset (Bulk 'D:\Data\Pics\@.jpg.jpg', Single_Blob) as 'ImageFile'

My image name is the ID# in a jpg file format. So I want to insert for

example:

ID# ImageName ImageFile Rec#

1 1.jpg <binary value> 1

2 2.jpg <binary value> 2

3 3.jpg <binary value> 3

4 4.jpg <binary value> 4

there are also id#'s without pictures should this just skip them?

|||

You cannot use a variable like this:

'D:\Data\Pics\@.jpg.jpg' or this: '@.jpg.jpg',

You will need to do something like:

use consumer
go

declare @.jpg as int, @.query varchar(1000)
Set @.jpg = 0
While @.jpg < 4500
begin
set @.jpg = (@.jpg + 1)

set @.query = '
insert photo (id# ,ImageName ,ImageFile)
Select ' + cast(@.jpg as varchar(10)) + '
, ''' + cast(@.jpg as varchar(10)) + '.jpg' + '''
,bulkcolumn
from Openrowset (Bulk ''' + 'D:\Data\Pics\' + cast(@.jpg as varchar(10)) + '.jpg'', Single_Blob) as ImageFile'

select @.query
exec (@.query)
end

I don't have any experience with he bulk stuff, but the code runs, and appears to try to do the openrowset because it claims that the file doesn't exist. I got some of the idea from: http://community.sgdotnet.org/blogs/chuawenching/archive/2006/04/03/25601.aspx

|||

Thank you very much it does work I just have to build in if the file doesn't exist move on the the next record. This is great work Thank you again.

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

Msg 4860, Level 16, State 1, Line 2

Cannot bulk load. The file "D:\Data\Pics\33.jpg" does not exist.

|||

I too am having difficulty how to construct a variable for a file name.

I am trying to emulate the variable re: your blob...

-- I want to use some kind of variable, like this to use in the file:

DECLARE @.FIL VARCHAR(65)

SET @.FIL = 'C:\company folders\Documentation\INVENTORY.xls;'

--

SELECT FROM OPENROWSET('MSDASQL', 'Driver=Microsoft Excel Driver (*.xls);DBQ=C:\company folders\Documentation\INVENTORY.xls;', 'SELECT * FROM [Inventory$]')

AS DT

Anyone game in trying their hand?

I have also read the How to Pass a variable to a linked query, but have not gotten the quotations down right (','' )

Kind Regards,

Claudia.

|||

Take a look at your other post for a solution:

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1587125&SiteID=17

Chris

Filename Variable Trouble

I want have multiple records from 1-4500 that I must insert a picture into the database for. I am trying to set up a variable to do this if someone can show me my error I would apprieciate it. This table has 4 columns (id# Float is a FK) (ImageName nvarchar (10)) (ImageFile Varbinary(Max)) (rec# int PK Increment Seed).

declare @.jpg asint

Set @.jpg = 0

While @.jpg <4500

begin

set @.jpg =(@.jpg + 1)

use consumer

insert photo (id#, ImageName, ImageFile)

Select @.jpg,'@.jpg.jpg',

bulkcolumn fromOpenrowset(Bulk'D:\Data\Pics\@.jpg.jpg', Single_Blob)as'ImageFile'

My image name is the ID# in a jpg file format. So I want to insert for

example:

ID# ImageName ImageFile Rec#

1 1.jpg <binary value> 1

2 2.jpg <binary value> 2

3 3.jpg <binary value> 3

4 4.jpg <binary value> 4

there are also id#'s without pictures should this just skip them?

|||

You cannot use a variable like this:

'D:\Data\Pics\@.jpg.jpg' or this: '@.jpg.jpg',

You will need to do something like:

use consumer
go

declare @.jpg as int, @.query varchar(1000)
Set @.jpg = 0
While @.jpg < 4500
begin
set @.jpg = (@.jpg + 1)

set @.query = '
insert photo (id# ,ImageName ,ImageFile)
Select ' + cast(@.jpg as varchar(10)) + '
, ''' + cast(@.jpg as varchar(10)) + '.jpg' + '''
,bulkcolumn
from Openrowset (Bulk ''' + 'D:\Data\Pics\' + cast(@.jpg as varchar(10)) + '.jpg'', Single_Blob) as ImageFile'

select @.query
exec (@.query)
end

I don't have any experience with he bulk stuff, but the code runs, and appears to try to do the openrowset because it claims that the file doesn't exist. I got some of the idea from: http://community.sgdotnet.org/blogs/chuawenching/archive/2006/04/03/25601.aspx

|||

Thank you very much it does work I just have to build in if the file doesn't exist move on the the next record. This is great work Thank you again.

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

Msg 4860, Level 16, State 1, Line 2

Cannot bulk load. The file "D:\Data\Pics\33.jpg" does not exist.

|||

I too am having difficulty how to construct a variable for a file name.

I am trying to emulate the variable re: your blob...

-- I want to use some kind of variable, like this to use in the file:

DECLARE @.FIL VARCHAR(65)

SET @.FIL = 'C:\company folders\Documentation\INVENTORY.xls;'

--

SELECT FROMOPENROWSET('MSDASQL','Driver=Microsoft Excel Driver (*.xls);DBQ=C:\company folders\Documentation\INVENTORY.xls;','SELECT * FROM [Inventory$]')

AS DT

Anyone game in trying their hand?

I have also read the How to Pass a variable to a linked query, but have not gotten the quotations down right (','' )

Kind Regards,

Claudia.

|||

Take a look at your other post for a solution:

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1587125&SiteID=17

Chris

filegroups

Hi,
Due to the size of our tables we're considering the use of filegroups that
span multiple disk-arrays.
What's the best aproach:
- create a filegroup with files on multiple disks an let SQL Server figure
out/spread the table data across the disks automaticly.
or
- Analyse and assign the tables to groups/files/disks ourselves
tanx,
Derk JanDerk,
Create your user-defined filegroup(s). Change the default filegroup status
from PRIMARY to one of your user-defined filegroups (ALTER DATABASE). Use
the ON FILEGROUP clause of the CREATE INDEX and CREATE TABLE statements to
control the placement of your tables and indexes.
HTH
Jerry
"Derk Jan" <DerkJan@.discussions.microsoft.com> wrote in message
news:9A2C5762-98F9-470C-95CE-50F1DCDA05DC@.microsoft.com...
> Hi,
> Due to the size of our tables we're considering the use of filegroups that
> span multiple disk-arrays.
> What's the best aproach:
> - create a filegroup with files on multiple disks an let SQL Server figure
> out/spread the table data across the disks automaticly.
> or
> - Analyse and assign the tables to groups/files/disks ourselves
> tanx,
> Derk Jan
>|||Why does the size of the table warrant spanning multiple arrays? Are you
scanning the entire table each time you access it? How large are you
talking about? The size or number of rows in a table or database means much
less than how you use and access the data. Before you go moving stuff
around you should determine where the bottleneck really is and why. That
will play a major role in deciding how to split up data if required.
Andrew J. Kelly SQL MVP
"Derk Jan" <DerkJan@.discussions.microsoft.com> wrote in message
news:9A2C5762-98F9-470C-95CE-50F1DCDA05DC@.microsoft.com...
> Hi,
> Due to the size of our tables we're considering the use of filegroups that
> span multiple disk-arrays.
> What's the best aproach:
> - create a filegroup with files on multiple disks an let SQL Server figure
> out/spread the table data across the disks automaticly.
> or
> - Analyse and assign the tables to groups/files/disks ourselves
> tanx,
> Derk Jan
>

filegroups

Hi,
Due to the size of our tables we're considering the use of filegroups that
span multiple disk-arrays.
What's the best aproach:
- create a filegroup with files on multiple disks an let SQL Server figure
out/spread the table data across the disks automaticly.
or
- Analyse and assign the tables to groups/files/disks ourselves
tanx,
Derk JanDerk,
Create your user-defined filegroup(s). Change the default filegroup status
from PRIMARY to one of your user-defined filegroups (ALTER DATABASE). Use
the ON FILEGROUP clause of the CREATE INDEX and CREATE TABLE statements to
control the placement of your tables and indexes.
HTH
Jerry
"Derk Jan" <DerkJan@.discussions.microsoft.com> wrote in message
news:9A2C5762-98F9-470C-95CE-50F1DCDA05DC@.microsoft.com...
> Hi,
> Due to the size of our tables we're considering the use of filegroups that
> span multiple disk-arrays.
> What's the best aproach:
> - create a filegroup with files on multiple disks an let SQL Server figure
> out/spread the table data across the disks automaticly.
> or
> - Analyse and assign the tables to groups/files/disks ourselves
> tanx,
> Derk Jan
>|||Why does the size of the table warrant spanning multiple arrays? Are you
scanning the entire table each time you access it? How large are you
talking about? The size or number of rows in a table or database means much
less than how you use and access the data. Before you go moving stuff
around you should determine where the bottleneck really is and why. That
will play a major role in deciding how to split up data if required.
--
Andrew J. Kelly SQL MVP
"Derk Jan" <DerkJan@.discussions.microsoft.com> wrote in message
news:9A2C5762-98F9-470C-95CE-50F1DCDA05DC@.microsoft.com...
> Hi,
> Due to the size of our tables we're considering the use of filegroups that
> span multiple disk-arrays.
> What's the best aproach:
> - create a filegroup with files on multiple disks an let SQL Server figure
> out/spread the table data across the disks automaticly.
> or
> - Analyse and assign the tables to groups/files/disks ourselves
> tanx,
> Derk Jan
>

filegroups

Hi,
Due to the size of our tables we're considering the use of filegroups that
span multiple disk-arrays.
What's the best aproach:
- create a filegroup with files on multiple disks an let SQL Server figure
out/spread the table data across the disks automaticly.
or
- Analyse and assign the tables to groups/files/disks ourselves
tanx,
Derk Jan
Derk,
Create your user-defined filegroup(s). Change the default filegroup status
from PRIMARY to one of your user-defined filegroups (ALTER DATABASE). Use
the ON FILEGROUP clause of the CREATE INDEX and CREATE TABLE statements to
control the placement of your tables and indexes.
HTH
Jerry
"Derk Jan" <DerkJan@.discussions.microsoft.com> wrote in message
news:9A2C5762-98F9-470C-95CE-50F1DCDA05DC@.microsoft.com...
> Hi,
> Due to the size of our tables we're considering the use of filegroups that
> span multiple disk-arrays.
> What's the best aproach:
> - create a filegroup with files on multiple disks an let SQL Server figure
> out/spread the table data across the disks automaticly.
> or
> - Analyse and assign the tables to groups/files/disks ourselves
> tanx,
> Derk Jan
>
|||Why does the size of the table warrant spanning multiple arrays? Are you
scanning the entire table each time you access it? How large are you
talking about? The size or number of rows in a table or database means much
less than how you use and access the data. Before you go moving stuff
around you should determine where the bottleneck really is and why. That
will play a major role in deciding how to split up data if required.
Andrew J. Kelly SQL MVP
"Derk Jan" <DerkJan@.discussions.microsoft.com> wrote in message
news:9A2C5762-98F9-470C-95CE-50F1DCDA05DC@.microsoft.com...
> Hi,
> Due to the size of our tables we're considering the use of filegroups that
> span multiple disk-arrays.
> What's the best aproach:
> - create a filegroup with files on multiple disks an let SQL Server figure
> out/spread the table data across the disks automaticly.
> or
> - Analyse and assign the tables to groups/files/disks ourselves
> tanx,
> Derk Jan
>

Filegroup space diff on 2 servers

I have 2 servers and each have a database which has multiple filegroups
defined. One of this filegroup has 1 table on both server and data from one
is replicated to teh other which means practically same vol fo data on this
filegrroup. However what surprises me is that filegroup space on the 2nd
server is way above than on the first server from where the data is
replicated. Can anyone think fo any reason why there is so much fo
difference. Its abt 3 GB space difference that i notice. any help will be
greatly appreciated.
Message posted via http://www.sqlmonster.com
Probably due to fragmentation. Have you tried reindexing that table?
Andrew J. Kelly SQL MVP
"ishaan99 via SQLMonster.com" <forum@.nospam.SQLMonster.com> wrote in message
news:948ed8dbe7094b76b00e1b775c38a7b3@.SQLMonster.c om...
>I have 2 servers and each have a database which has multiple filegroups
> defined. One of this filegroup has 1 table on both server and data from
> one
> is replicated to teh other which means practically same vol fo data on
> this
> filegrroup. However what surprises me is that filegroup space on the 2nd
> server is way above than on the first server from where the data is
> replicated. Can anyone think fo any reason why there is so much fo
> difference. Its abt 3 GB space difference that i notice. any help will be
> greatly appreciated.
> --
> Message posted via http://www.sqlmonster.com

Filegroup space diff on 2 servers

I have 2 servers and each have a database which has multiple filegroups
defined. One of this filegroup has 1 table on both server and data from one
is replicated to teh other which means practically same vol fo data on this
filegrroup. However what surprises me is that filegroup space on the 2nd
server is way above than on the first server from where the data is
replicated. Can anyone think fo any reason why there is so much fo
difference. Its abt 3 GB space difference that i notice. any help will be
greatly appreciated.
--
Message posted via http://www.sqlmonster.comProbably due to fragmentation. Have you tried reindexing that table?
--
Andrew J. Kelly SQL MVP
"ishaan99 via SQLMonster.com" <forum@.nospam.SQLMonster.com> wrote in message
news:948ed8dbe7094b76b00e1b775c38a7b3@.SQLMonster.com...
>I have 2 servers and each have a database which has multiple filegroups
> defined. One of this filegroup has 1 table on both server and data from
> one
> is replicated to teh other which means practically same vol fo data on
> this
> filegrroup. However what surprises me is that filegroup space on the 2nd
> server is way above than on the first server from where the data is
> replicated. Can anyone think fo any reason why there is so much fo
> difference. Its abt 3 GB space difference that i notice. any help will be
> greatly appreciated.
> --
> Message posted via http://www.sqlmonster.com

Filegroup space diff on 2 servers

I have 2 servers and each have a database which has multiple filegroups
defined. One of this filegroup has 1 table on both server and data from one
is replicated to teh other which means practically same vol fo data on this
filegrroup. However what surprises me is that filegroup space on the 2nd
server is way above than on the first server from where the data is
replicated. Can anyone think fo any reason why there is so much fo
difference. Its abt 3 GB space difference that i notice. any help will be
greatly appreciated.
Message posted via http://www.droptable.comProbably due to fragmentation. Have you tried reindexing that table?
Andrew J. Kelly SQL MVP
"ishaan99 via droptable.com" <forum@.nospam.droptable.com> wrote in message
news:948ed8dbe7094b76b00e1b775c38a7b3@.SQ
droptable.com...
>I have 2 servers and each have a database which has multiple filegroups
> defined. One of this filegroup has 1 table on both server and data from
> one
> is replicated to teh other which means practically same vol fo data on
> this
> filegrroup. However what surprises me is that filegroup space on the 2nd
> server is way above than on the first server from where the data is
> replicated. Can anyone think fo any reason why there is so much fo
> difference. Its abt 3 GB space difference that i notice. any help will be
> greatly appreciated.
> --
> Message posted via http://www.droptable.com

Filegroup question

I was reading an article about the use of filegroups. I had alsways thought
that multiple files/ filegroups were pretty much pointless unless they were
on they're own raid array? However this article seems to disagree with that
theory and say that multiple files on the same disk array could improve
performance? Whats your take on this? And if its true, why not just split
your whole db into multiple files on the same disk array? Heres a snippet of
the article:
/*
If your database is very large and very busy, multiple files can be used to
increase performance. Here is one example of how you might use multiple
files. Let's say you have a single table with 10 million rows that is heavily
queried. If the table is in a single file, such as a single database file,
then SQL Server would only use one thread to perform a read of the rows in
the table. But if the table were divided into three physical files (all part
of the same filegroup), then SQL Server would use three threads (one per
physical file) to read the table, which potentially could be faster. In
addition, if each file were on its own separate physical disk or disk array,
the performance gain would even be greater.
*?
SQL will in different circusmstances be more aggresive in IO based on the
number of files. Is that good or bad if all the files are on the same array?
Well, like any performance question, the answer is it depends. <g>
You might get better performance this way if the 'single array' is a super
duper fast array on an EMC SAN. You might get worse performance if you're
on a low end arrary.
However, there are other cases where you might want multiiple files, on the
same array, irregardless of IO performance.
Take a look at this article...
http://www.windowsitpro.com/Article/...615/40615.html
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:B17EA0E2-638C-45CD-B160-C9E304549ADB@.microsoft.com...
> I was reading an article about the use of filegroups. I had alsways
thought
> that multiple files/ filegroups were pretty much pointless unless they
were
> on they're own raid array? However this article seems to disagree with
that
> theory and say that multiple files on the same disk array could improve
> performance? Whats your take on this? And if its true, why not just split
> your whole db into multiple files on the same disk array? Heres a snippet
of
> the article:
> /*
> If your database is very large and very busy, multiple files can be used
to
> increase performance. Here is one example of how you might use multiple
> files. Let's say you have a single table with 10 million rows that is
heavily
> queried. If the table is in a single file, such as a single database file,
> then SQL Server would only use one thread to perform a read of the rows in
> the table. But if the table were divided into three physical files (all
part
> of the same filegroup), then SQL Server would use three threads (one per
> physical file) to read the table, which potentially could be faster. In
> addition, if each file were on its own separate physical disk or disk
array,
> the performance gain would even be greater.
> *?

Filegroup question

I was reading an article about the use of filegroups. I had alsways thought
that multiple files/ filegroups were pretty much pointless unless they were
on they're own raid array? However this article seems to disagree with that
theory and say that multiple files on the same disk array could improve
performance? Whats your take on this? And if its true, why not just split
your whole db into multiple files on the same disk array? Heres a snippet of
the article:
/*
If your database is very large and very busy, multiple files can be used to
increase performance. Here is one example of how you might use multiple
files. Let's say you have a single table with 10 million rows that is heavil
y
queried. If the table is in a single file, such as a single database file,
then SQL Server would only use one thread to perform a read of the rows in
the table. But if the table were divided into three physical files (all part
of the same filegroup), then SQL Server would use three threads (one per
physical file) to read the table, which potentially could be faster. In
addition, if each file were on its own separate physical disk or disk array,
the performance gain would even be greater.
*?SQL will in different circusmstances be more aggresive in IO based on the
number of files. Is that good or bad if all the files are on the same array?
Well, like any performance question, the answer is it depends. <g>
You might get better performance this way if the 'single array' is a super
duper fast array on an EMC SAN. You might get worse performance if you're
on a low end arrary.
However, there are other cases where you might want multiiple files, on the
same array, irregardless of IO performance.
Take a look at this article...
http://www.windowsitpro.com/Article...0615/40615.html
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:B17EA0E2-638C-45CD-B160-C9E304549ADB@.microsoft.com...
> I was reading an article about the use of filegroups. I had alsways
thought
> that multiple files/ filegroups were pretty much pointless unless they
were
> on they're own raid array? However this article seems to disagree with
that
> theory and say that multiple files on the same disk array could improve
> performance? Whats your take on this? And if its true, why not just split
> your whole db into multiple files on the same disk array? Heres a snippet
of
> the article:
> /*
> If your database is very large and very busy, multiple files can be used
to
> increase performance. Here is one example of how you might use multiple
> files. Let's say you have a single table with 10 million rows that is
heavily
> queried. If the table is in a single file, such as a single database file,
> then SQL Server would only use one thread to perform a read of the rows in
> the table. But if the table were divided into three physical files (all
part
> of the same filegroup), then SQL Server would use three threads (one per
> physical file) to read the table, which potentially could be faster. In
> addition, if each file were on its own separate physical disk or disk
array,
> the performance gain would even be greater.
> *?

Filegroup question

I was reading an article about the use of filegroups. I had alsways thought
that multiple files/ filegroups were pretty much pointless unless they were
on they're own raid array? However this article seems to disagree with that
theory and say that multiple files on the same disk array could improve
performance? Whats your take on this? And if its true, why not just split
your whole db into multiple files on the same disk array? Heres a snippet of
the article:
/*
If your database is very large and very busy, multiple files can be used to
increase performance. Here is one example of how you might use multiple
files. Let's say you have a single table with 10 million rows that is heavily
queried. If the table is in a single file, such as a single database file,
then SQL Server would only use one thread to perform a read of the rows in
the table. But if the table were divided into three physical files (all part
of the same filegroup), then SQL Server would use three threads (one per
physical file) to read the table, which potentially could be faster. In
addition, if each file were on its own separate physical disk or disk array,
the performance gain would even be greater.
*?SQL will in different circusmstances be more aggresive in IO based on the
number of files. Is that good or bad if all the files are on the same array?
Well, like any performance question, the answer is it depends. <g>
You might get better performance this way if the 'single array' is a super
duper fast array on an EMC SAN. You might get worse performance if you're
on a low end arrary.
However, there are other cases where you might want multiiple files, on the
same array, irregardless of IO performance.
Take a look at this article...
http://www.windowsitpro.com/Article/ArticleID/40615/40615.html
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:B17EA0E2-638C-45CD-B160-C9E304549ADB@.microsoft.com...
> I was reading an article about the use of filegroups. I had alsways
thought
> that multiple files/ filegroups were pretty much pointless unless they
were
> on they're own raid array? However this article seems to disagree with
that
> theory and say that multiple files on the same disk array could improve
> performance? Whats your take on this? And if its true, why not just split
> your whole db into multiple files on the same disk array? Heres a snippet
of
> the article:
> /*
> If your database is very large and very busy, multiple files can be used
to
> increase performance. Here is one example of how you might use multiple
> files. Let's say you have a single table with 10 million rows that is
heavily
> queried. If the table is in a single file, such as a single database file,
> then SQL Server would only use one thread to perform a read of the rows in
> the table. But if the table were divided into three physical files (all
part
> of the same filegroup), then SQL Server would use three threads (one per
> physical file) to read the table, which potentially could be faster. In
> addition, if each file were on its own separate physical disk or disk
array,
> the performance gain would even be greater.
> *?

Filegroup Backups

I seem to be having an issue with file groups and differential backups. I
have a database with multiple filegoups. If I do a filegoup backups along
with a differential backup, I cannot restore the differential backup. I get
the following error:
Msg 4305, Level 16, State 1, Line 1
The log in this backup set begins at LSN 827000000462900035, which is too
recent to apply to the database. An earlier log backup that includes LSN
827000000460600001 can be restored.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
I have tried filegoup as well as database differential backups with the same
result. Restoring logs seems to work fine, but in my scenario, I don’t wan
t
to rely on 1 week or a month of transaction logs depending on how often I do
a full backup on each filegoup. First question, are differential backups
supported with filegroups? If so, is there a special way to do the
differential backup/restore?
BACKUP DATABASE [PartitionTest]
FILEGROUP = N'Primary'
TO DISK = N'c:\Primary.bak'
WITH NOFORMAT, INIT, NAME = N'PartitionTest-Full Filegroup Backup', SKIP,
NOREWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [PartitionTest]
FILEGROUP = N'Doc1'
TO DISK = N'c:\Doc1.bak'
WITH NOFORMAT, INIT, NAME = N'PartitionTest-Full Filegroup Backup', SKIP,
NOREWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [PartitionTest]
TO DISK = N'c:\diff.diff'
WITH NOFORMAT, DIFFERENTIAL
BACKUP DATABASE [PartitionTest] FILEGROUP = N'Primary'
TO DISK = N'c:\partition.diff'
WITH NOFORMAT, DIFFERENTIAL
RESTORE DATABASE [PartitionTest_Restore] filegroup = 'Primary'
FROM DISK = 'c:\Primary.bak'
with move 'PartitionTest' to 'c:\partitiontest2.mdf'
,move 'PartitionTest_log' to 'c:\partitiontest_log2.ldf'
,PARTIAL,norecovery,REPLACE
RESTORE DATABASE [PartitionTest_Restore] filegroup = 'Doc1'
FROM DISK = 'c:\doc1.bak'
with move 'PartitionTest_Doc1' to 'f:\data\PartitionTest_Doc1_1.ndf'
,standby = 'f:\standby.bak',REPLACE
-- this will error out
RESTORE log [PartitionTest_Restore]
FROM DISK = 'c:\diff.diff'
with norecovery
-- Same error.
RESTORE log [PartitionTest_Restore] FILEGROUP = N'Primary'
FROM DISK = 'c:\diff.diff'
with norecoveryDave B,
To restore a differential backup, you have to use "restore database"
statement instead "restore log". Also, when restoring multiple backups, you
need to use option "with norecovery" except in the final restore.
Check "restore database" in BOL for more info.
AMB
"Dave B" wrote:

> I seem to be having an issue with file groups and differential backups. I
> have a database with multiple filegoups. If I do a filegoup backups along
> with a differential backup, I cannot restore the differential backup. I ge
t
> the following error:
> Msg 4305, Level 16, State 1, Line 1
> The log in this backup set begins at LSN 827000000462900035, which is too
> recent to apply to the database. An earlier log backup that includes LSN
> 827000000460600001 can be restored.
> Msg 3013, Level 16, State 1, Line 1
> RESTORE LOG is terminating abnormally.
> I have tried filegoup as well as database differential backups with the sa
me
> result. Restoring logs seems to work fine, but in my scenario, I don’t w
ant
> to rely on 1 week or a month of transaction logs depending on how often I
do
> a full backup on each filegoup. First question, are differential backups
> supported with filegroups? If so, is there a special way to do the
> differential backup/restore?
>
> BACKUP DATABASE [PartitionTest]
> FILEGROUP = N'Primary'
> TO DISK = N'c:\Primary.bak'
> WITH NOFORMAT, INIT, NAME = N'PartitionTest-Full Filegroup Backup', SKIP
,
> NOREWIND, NOUNLOAD, STATS = 10
> GO
> BACKUP DATABASE [PartitionTest]
> FILEGROUP = N'Doc1'
> TO DISK = N'c:\Doc1.bak'
> WITH NOFORMAT, INIT, NAME = N'PartitionTest-Full Filegroup Backup', SKIP
,
> NOREWIND, NOUNLOAD, STATS = 10
> GO
> BACKUP DATABASE [PartitionTest]
> TO DISK = N'c:\diff.diff'
> WITH NOFORMAT, DIFFERENTIAL
> BACKUP DATABASE [PartitionTest] FILEGROUP = N'Primary'
> TO DISK = N'c:\partition.diff'
> WITH NOFORMAT, DIFFERENTIAL
>
> RESTORE DATABASE [PartitionTest_Restore] filegroup = 'Primary'
> FROM DISK = 'c:\Primary.bak'
> with move 'PartitionTest' to 'c:\partitiontest2.mdf'
> ,move 'PartitionTest_log' to 'c:\partitiontest_log2.ldf'
> ,PARTIAL,norecovery,REPLACE
> RESTORE DATABASE [PartitionTest_Restore] filegroup = 'Doc1'
> FROM DISK = 'c:\doc1.bak'
> with move 'PartitionTest_Doc1' to 'f:\data\PartitionTest_Doc1_1.ndf'
> ,standby = 'f:\standby.bak',REPLACE
> -- this will error out
> RESTORE log [PartitionTest_Restore]
> FROM DISK = 'c:\diff.diff'
> with norecovery
> -- Same error.
> RESTORE log [PartitionTest_Restore] FILEGROUP = N'Primary'
> FROM DISK = 'c:\diff.diff'
> with norecovery
>|||Thanks.
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Dave B,
> To restore a differential backup, you have to use "restore database"
> statement instead "restore log". Also, when restoring multiple backups, yo
u
> need to use option "with norecovery" except in the final restore.
> Check "restore database" in BOL for more info.
> AMB
> "Dave B" wrote:
>

Filegroup Backups

I seem to be having an issue with file groups and differential backups. I
have a database with multiple filegoups. If I do a filegoup backups along
with a differential backup, I cannot restore the differential backup. I get
the following error:
Msg 4305, Level 16, State 1, Line 1
The log in this backup set begins at LSN 827000000462900035, which is too
recent to apply to the database. An earlier log backup that includes LSN
827000000460600001 can be restored.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
I have tried filegoup as well as database differential backups with the same
result. Restoring logs seems to work fine, but in my scenario, I donâ't want
to rely on 1 week or a month of transaction logs depending on how often I do
a full backup on each filegoup. First question, are differential backups
supported with filegroups? If so, is there a special way to do the
differential backup/restore?
BACKUP DATABASE [PartitionTest]
FILEGROUP = N'Primary'
TO DISK = N'c:\Primary.bak'
WITH NOFORMAT, INIT, NAME = N'PartitionTest-Full Filegroup Backup', SKIP,
NOREWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [PartitionTest]
FILEGROUP = N'Doc1'
TO DISK = N'c:\Doc1.bak'
WITH NOFORMAT, INIT, NAME = N'PartitionTest-Full Filegroup Backup', SKIP,
NOREWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [PartitionTest]
TO DISK = N'c:\diff.diff'
WITH NOFORMAT, DIFFERENTIAL
BACKUP DATABASE [PartitionTest] FILEGROUP = N'Primary'
TO DISK = N'c:\partition.diff'
WITH NOFORMAT, DIFFERENTIAL
RESTORE DATABASE [PartitionTest_Restore] filegroup = 'Primary'
FROM DISK = 'c:\Primary.bak'
with move 'PartitionTest' to 'c:\partitiontest2.mdf'
,move 'PartitionTest_log' to 'c:\partitiontest_log2.ldf'
,PARTIAL,norecovery,REPLACE
RESTORE DATABASE [PartitionTest_Restore] filegroup = 'Doc1'
FROM DISK = 'c:\doc1.bak'
with move 'PartitionTest_Doc1' to 'f:\data\PartitionTest_Doc1_1.ndf'
,standby = 'f:\standby.bak',REPLACE
-- this will error out
RESTORE log [PartitionTest_Restore]
FROM DISK = 'c:\diff.diff'
with norecovery
-- Same error.
RESTORE log [PartitionTest_Restore] FILEGROUP = N'Primary'
FROM DISK = 'c:\diff.diff'
with norecoveryDave B,
To restore a differential backup, you have to use "restore database"
statement instead "restore log". Also, when restoring multiple backups, you
need to use option "with norecovery" except in the final restore.
Check "restore database" in BOL for more info.
AMB
"Dave B" wrote:
> I seem to be having an issue with file groups and differential backups. I
> have a database with multiple filegoups. If I do a filegoup backups along
> with a differential backup, I cannot restore the differential backup. I get
> the following error:
> Msg 4305, Level 16, State 1, Line 1
> The log in this backup set begins at LSN 827000000462900035, which is too
> recent to apply to the database. An earlier log backup that includes LSN
> 827000000460600001 can be restored.
> Msg 3013, Level 16, State 1, Line 1
> RESTORE LOG is terminating abnormally.
> I have tried filegoup as well as database differential backups with the same
> result. Restoring logs seems to work fine, but in my scenario, I donâ't want
> to rely on 1 week or a month of transaction logs depending on how often I do
> a full backup on each filegoup. First question, are differential backups
> supported with filegroups? If so, is there a special way to do the
> differential backup/restore?
>
> BACKUP DATABASE [PartitionTest]
> FILEGROUP = N'Primary'
> TO DISK = N'c:\Primary.bak'
> WITH NOFORMAT, INIT, NAME = N'PartitionTest-Full Filegroup Backup', SKIP,
> NOREWIND, NOUNLOAD, STATS = 10
> GO
> BACKUP DATABASE [PartitionTest]
> FILEGROUP = N'Doc1'
> TO DISK = N'c:\Doc1.bak'
> WITH NOFORMAT, INIT, NAME = N'PartitionTest-Full Filegroup Backup', SKIP,
> NOREWIND, NOUNLOAD, STATS = 10
> GO
> BACKUP DATABASE [PartitionTest]
> TO DISK = N'c:\diff.diff'
> WITH NOFORMAT, DIFFERENTIAL
> BACKUP DATABASE [PartitionTest] FILEGROUP = N'Primary'
> TO DISK = N'c:\partition.diff'
> WITH NOFORMAT, DIFFERENTIAL
>
> RESTORE DATABASE [PartitionTest_Restore] filegroup = 'Primary'
> FROM DISK = 'c:\Primary.bak'
> with move 'PartitionTest' to 'c:\partitiontest2.mdf'
> ,move 'PartitionTest_log' to 'c:\partitiontest_log2.ldf'
> ,PARTIAL,norecovery,REPLACE
> RESTORE DATABASE [PartitionTest_Restore] filegroup = 'Doc1'
> FROM DISK = 'c:\doc1.bak'
> with move 'PartitionTest_Doc1' to 'f:\data\PartitionTest_Doc1_1.ndf'
> ,standby = 'f:\standby.bak',REPLACE
> -- this will error out
> RESTORE log [PartitionTest_Restore]
> FROM DISK = 'c:\diff.diff'
> with norecovery
> -- Same error.
> RESTORE log [PartitionTest_Restore] FILEGROUP = N'Primary'
> FROM DISK = 'c:\diff.diff'
> with norecovery
>|||Thanks.
"Alejandro Mesa" wrote:
> Dave B,
> To restore a differential backup, you have to use "restore database"
> statement instead "restore log". Also, when restoring multiple backups, you
> need to use option "with norecovery" except in the final restore.
> Check "restore database" in BOL for more info.
> AMB
> "Dave B" wrote:
> > I seem to be having an issue with file groups and differential backups. I
> > have a database with multiple filegoups. If I do a filegoup backups along
> > with a differential backup, I cannot restore the differential backup. I get
> > the following error:
> >
> > Msg 4305, Level 16, State 1, Line 1
> > The log in this backup set begins at LSN 827000000462900035, which is too
> > recent to apply to the database. An earlier log backup that includes LSN
> > 827000000460600001 can be restored.
> > Msg 3013, Level 16, State 1, Line 1
> > RESTORE LOG is terminating abnormally.
> >
> > I have tried filegoup as well as database differential backups with the same
> > result. Restoring logs seems to work fine, but in my scenario, I donâ't want
> > to rely on 1 week or a month of transaction logs depending on how often I do
> > a full backup on each filegoup. First question, are differential backups
> > supported with filegroups? If so, is there a special way to do the
> > differential backup/restore?
> >
> >
> > BACKUP DATABASE [PartitionTest]
> > FILEGROUP = N'Primary'
> > TO DISK = N'c:\Primary.bak'
> > WITH NOFORMAT, INIT, NAME = N'PartitionTest-Full Filegroup Backup', SKIP,
> > NOREWIND, NOUNLOAD, STATS = 10
> > GO
> > BACKUP DATABASE [PartitionTest]
> > FILEGROUP = N'Doc1'
> > TO DISK = N'c:\Doc1.bak'
> > WITH NOFORMAT, INIT, NAME = N'PartitionTest-Full Filegroup Backup', SKIP,
> > NOREWIND, NOUNLOAD, STATS = 10
> > GO
> >
> > BACKUP DATABASE [PartitionTest]
> > TO DISK = N'c:\diff.diff'
> > WITH NOFORMAT, DIFFERENTIAL
> >
> > BACKUP DATABASE [PartitionTest] FILEGROUP = N'Primary'
> > TO DISK = N'c:\partition.diff'
> > WITH NOFORMAT, DIFFERENTIAL
> >
> >
> > RESTORE DATABASE [PartitionTest_Restore] filegroup = 'Primary'
> > FROM DISK = 'c:\Primary.bak'
> > with move 'PartitionTest' to 'c:\partitiontest2.mdf'
> > ,move 'PartitionTest_log' to 'c:\partitiontest_log2.ldf'
> > ,PARTIAL,norecovery,REPLACE
> >
> > RESTORE DATABASE [PartitionTest_Restore] filegroup = 'Doc1'
> > FROM DISK = 'c:\doc1.bak'
> > with move 'PartitionTest_Doc1' to 'f:\data\PartitionTest_Doc1_1.ndf'
> > ,standby = 'f:\standby.bak',REPLACE
> >
> > -- this will error out
> > RESTORE log [PartitionTest_Restore]
> > FROM DISK = 'c:\diff.diff'
> > with norecovery
> >
> > -- Same error.
> > RESTORE log [PartitionTest_Restore] FILEGROUP = N'Primary'
> > FROM DISK = 'c:\diff.diff'
> > with norecovery
> >
> >

Filegroup Backups

I seem to be having an issue with file groups and differential backups. I
have a database with multiple filegoups. If I do a filegoup backups along
with a differential backup, I cannot restore the differential backup. I get
the following error:
Msg 4305, Level 16, State 1, Line 1
The log in this backup set begins at LSN 827000000462900035, which is too
recent to apply to the database. An earlier log backup that includes LSN
827000000460600001 can be restored.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
I have tried filegoup as well as database differential backups with the same
result. Restoring logs seems to work fine, but in my scenario, I don’t want
to rely on 1 week or a month of transaction logs depending on how often I do
a full backup on each filegoup. First question, are differential backups
supported with filegroups? If so, is there a special way to do the
differential backup/restore?
BACKUP DATABASE [PartitionTest]
FILEGROUP = N'Primary'
TO DISK = N'c:\Primary.bak'
WITH NOFORMAT, INIT, NAME = N'PartitionTest-Full Filegroup Backup', SKIP,
NOREWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [PartitionTest]
FILEGROUP = N'Doc1'
TO DISK = N'c:\Doc1.bak'
WITH NOFORMAT, INIT, NAME = N'PartitionTest-Full Filegroup Backup', SKIP,
NOREWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [PartitionTest]
TO DISK = N'c:\diff.diff'
WITH NOFORMAT, DIFFERENTIAL
BACKUP DATABASE [PartitionTest]FILEGROUP = N'Primary'
TO DISK = N'c:\partition.diff'
WITH NOFORMAT, DIFFERENTIAL
RESTORE DATABASE [PartitionTest_Restore] filegroup = 'Primary'
FROM DISK = 'c:\Primary.bak'
with move 'PartitionTest' to 'c:\partitiontest2.mdf'
,move 'PartitionTest_log' to 'c:\partitiontest_log2.ldf'
,PARTIAL,norecovery,REPLACE
RESTORE DATABASE [PartitionTest_Restore] filegroup = 'Doc1'
FROM DISK = 'c:\doc1.bak'
with move 'PartitionTest_Doc1' to 'f:\data\PartitionTest_Doc1_1.ndf'
,standby = 'f:\standby.bak',REPLACE
-- this will error out
RESTORE log [PartitionTest_Restore]
FROM DISK = 'c:\diff.diff'
with norecovery
-- Same error.
RESTORE log [PartitionTest_Restore] FILEGROUP = N'Primary'
FROM DISK = 'c:\diff.diff'
with norecovery
Dave B,
To restore a differential backup, you have to use "restore database"
statement instead "restore log". Also, when restoring multiple backups, you
need to use option "with norecovery" except in the final restore.
Check "restore database" in BOL for more info.
AMB
"Dave B" wrote:

> I seem to be having an issue with file groups and differential backups. I
> have a database with multiple filegoups. If I do a filegoup backups along
> with a differential backup, I cannot restore the differential backup. I get
> the following error:
> Msg 4305, Level 16, State 1, Line 1
> The log in this backup set begins at LSN 827000000462900035, which is too
> recent to apply to the database. An earlier log backup that includes LSN
> 827000000460600001 can be restored.
> Msg 3013, Level 16, State 1, Line 1
> RESTORE LOG is terminating abnormally.
> I have tried filegoup as well as database differential backups with the same
> result. Restoring logs seems to work fine, but in my scenario, I don’t want
> to rely on 1 week or a month of transaction logs depending on how often I do
> a full backup on each filegoup. First question, are differential backups
> supported with filegroups? If so, is there a special way to do the
> differential backup/restore?
>
> BACKUP DATABASE [PartitionTest]
> FILEGROUP = N'Primary'
> TO DISK = N'c:\Primary.bak'
> WITH NOFORMAT, INIT, NAME = N'PartitionTest-Full Filegroup Backup', SKIP,
> NOREWIND, NOUNLOAD, STATS = 10
> GO
> BACKUP DATABASE [PartitionTest]
> FILEGROUP = N'Doc1'
> TO DISK = N'c:\Doc1.bak'
> WITH NOFORMAT, INIT, NAME = N'PartitionTest-Full Filegroup Backup', SKIP,
> NOREWIND, NOUNLOAD, STATS = 10
> GO
> BACKUP DATABASE [PartitionTest]
> TO DISK = N'c:\diff.diff'
> WITH NOFORMAT, DIFFERENTIAL
> BACKUP DATABASE [PartitionTest]FILEGROUP = N'Primary'
> TO DISK = N'c:\partition.diff'
> WITH NOFORMAT, DIFFERENTIAL
>
> RESTORE DATABASE [PartitionTest_Restore] filegroup = 'Primary'
> FROM DISK = 'c:\Primary.bak'
> with move 'PartitionTest' to 'c:\partitiontest2.mdf'
> ,move 'PartitionTest_log' to 'c:\partitiontest_log2.ldf'
> ,PARTIAL,norecovery,REPLACE
> RESTORE DATABASE [PartitionTest_Restore] filegroup = 'Doc1'
> FROM DISK = 'c:\doc1.bak'
> with move 'PartitionTest_Doc1' to 'f:\data\PartitionTest_Doc1_1.ndf'
> ,standby = 'f:\standby.bak',REPLACE
> -- this will error out
> RESTORE log [PartitionTest_Restore]
> FROM DISK = 'c:\diff.diff'
> with norecovery
> -- Same error.
> RESTORE log [PartitionTest_Restore] FILEGROUP = N'Primary'
> FROM DISK = 'c:\diff.diff'
> with norecovery
>
|||Thanks.
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Dave B,
> To restore a differential backup, you have to use "restore database"
> statement instead "restore log". Also, when restoring multiple backups, you
> need to use option "with norecovery" except in the final restore.
> Check "restore database" in BOL for more info.
> AMB
> "Dave B" wrote:

File Group backup and restores

I have a database that has multiple data files (.mdf, .ndf), the complete
backup of this database has now exceeded the size of the physical drive that
I used to store the backup file.
We have tried taking FileGroup Backups, placing files of the backup on
separate drives. The Backup is sucessful.
I am having a problem restoring the backup files. It seems that I have to
overwrite the original database file. How can I restore this FileGroup
backup to the same server but to a different database? Is this possible?
Thanks,
Sal
Read about the PARTIAL options of the RESTORE command. Also see below section of Books Online
"Partial Database Restore Operations".
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ssummo" <ssummo@.discussions.microsoft.com> wrote in message
news:D00E627D-5B14-43DD-9150-180E92339756@.microsoft.com...
>I have a database that has multiple data files (.mdf, .ndf), the complete
> backup of this database has now exceeded the size of the physical drive that
> I used to store the backup file.
> We have tried taking FileGroup Backups, placing files of the backup on
> separate drives. The Backup is sucessful.
> I am having a problem restoring the backup files. It seems that I have to
> overwrite the original database file. How can I restore this FileGroup
> backup to the same server but to a different database? Is this possible?
> Thanks,
> Sal
|||Let me clarify my question. Can a database be restored as a different
database name on the same server where the original backup was taken if using
a FileGroup Backup?
"Tibor Karaszi" wrote:

> Read about the PARTIAL options of the RESTORE command. Also see below section of Books Online
> "Partial Database Restore Operations".
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "ssummo" <ssummo@.discussions.microsoft.com> wrote in message
> news:D00E627D-5B14-43DD-9150-180E92339756@.microsoft.com...
>
|||If you follow the rules for a PARTIAL restore (as documented in Books Online), then the answer is
yes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ssummo" <ssummo@.discussions.microsoft.com> wrote in message
news:7489799D-888C-4141-96C4-BC0EC2FCC9BD@.microsoft.com...[vbcol=seagreen]
> Let me clarify my question. Can a database be restored as a different
> database name on the same server where the original backup was taken if using
> a FileGroup Backup?
>
> "Tibor Karaszi" wrote:

File Group backup and restores

I have a database that has multiple data files (.mdf, .ndf), the complete
backup of this database has now exceeded the size of the physical drive that
I used to store the backup file.
We have tried taking FileGroup Backups, placing files of the backup on
separate drives. The Backup is sucessful.
I am having a problem restoring the backup files. It seems that I have to
overwrite the original database file. How can I restore this FileGroup
backup to the same server but to a different database? Is this possible?
Thanks,
SalRead about the PARTIAL options of the RESTORE command. Also see below sectio
n of Books Online
"Partial Database Restore Operations".
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ssummo" <ssummo@.discussions.microsoft.com> wrote in message
news:D00E627D-5B14-43DD-9150-180E92339756@.microsoft.com...
>I have a database that has multiple data files (.mdf, .ndf), the complete
> backup of this database has now exceeded the size of the physical drive th
at
> I used to store the backup file.
> We have tried taking FileGroup Backups, placing files of the backup on
> separate drives. The Backup is sucessful.
> I am having a problem restoring the backup files. It seems that I have to
> overwrite the original database file. How can I restore this FileGroup
> backup to the same server but to a different database? Is this possible?
> Thanks,
> Sal|||Let me clarify my question. Can a database be restored as a different
database name on the same server where the original backup was taken if usin
g
a FileGroup Backup?
"Tibor Karaszi" wrote:

> Read about the PARTIAL options of the RESTORE command. Also see below sect
ion of Books Online
> "Partial Database Restore Operations".
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "ssummo" <ssummo@.discussions.microsoft.com> wrote in message
> news:D00E627D-5B14-43DD-9150-180E92339756@.microsoft.com...
>|||If you follow the rules for a PARTIAL restore (as documented in Books Online
), then the answer is
yes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ssummo" <ssummo@.discussions.microsoft.com> wrote in message
news:7489799D-888C-4141-96C4-BC0EC2FCC9BD@.microsoft.com...[vbcol=seagreen]
> Let me clarify my question. Can a database be restored as a different
> database name on the same server where the original backup was taken if us
ing
> a FileGroup Backup?
>
> "Tibor Karaszi" wrote:
>

File Group backup and restores

I have a database that has multiple data files (.mdf, .ndf), the complete
backup of this database has now exceeded the size of the physical drive that
I used to store the backup file.
We have tried taking FileGroup Backups, placing files of the backup on
separate drives. The Backup is sucessful.
I am having a problem restoring the backup files. It seems that I have to
overwrite the original database file. How can I restore this FileGroup
backup to the same server but to a different database? Is this possible?
Thanks,
SalRead about the PARTIAL options of the RESTORE command. Also see below section of Books Online
"Partial Database Restore Operations".
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ssummo" <ssummo@.discussions.microsoft.com> wrote in message
news:D00E627D-5B14-43DD-9150-180E92339756@.microsoft.com...
>I have a database that has multiple data files (.mdf, .ndf), the complete
> backup of this database has now exceeded the size of the physical drive that
> I used to store the backup file.
> We have tried taking FileGroup Backups, placing files of the backup on
> separate drives. The Backup is sucessful.
> I am having a problem restoring the backup files. It seems that I have to
> overwrite the original database file. How can I restore this FileGroup
> backup to the same server but to a different database? Is this possible?
> Thanks,
> Sal|||Let me clarify my question. Can a database be restored as a different
database name on the same server where the original backup was taken if using
a FileGroup Backup?
"Tibor Karaszi" wrote:
> Read about the PARTIAL options of the RESTORE command. Also see below section of Books Online
> "Partial Database Restore Operations".
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "ssummo" <ssummo@.discussions.microsoft.com> wrote in message
> news:D00E627D-5B14-43DD-9150-180E92339756@.microsoft.com...
> >I have a database that has multiple data files (.mdf, .ndf), the complete
> > backup of this database has now exceeded the size of the physical drive that
> > I used to store the backup file.
> > We have tried taking FileGroup Backups, placing files of the backup on
> > separate drives. The Backup is sucessful.
> > I am having a problem restoring the backup files. It seems that I have to
> > overwrite the original database file. How can I restore this FileGroup
> > backup to the same server but to a different database? Is this possible?
> > Thanks,
> > Sal
>|||If you follow the rules for a PARTIAL restore (as documented in Books Online), then the answer is
yes.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ssummo" <ssummo@.discussions.microsoft.com> wrote in message
news:7489799D-888C-4141-96C4-BC0EC2FCC9BD@.microsoft.com...
> Let me clarify my question. Can a database be restored as a different
> database name on the same server where the original backup was taken if using
> a FileGroup Backup?
>
> "Tibor Karaszi" wrote:
>> Read about the PARTIAL options of the RESTORE command. Also see below section of Books Online
>> "Partial Database Restore Operations".
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "ssummo" <ssummo@.discussions.microsoft.com> wrote in message
>> news:D00E627D-5B14-43DD-9150-180E92339756@.microsoft.com...
>> >I have a database that has multiple data files (.mdf, .ndf), the complete
>> > backup of this database has now exceeded the size of the physical drive that
>> > I used to store the backup file.
>> > We have tried taking FileGroup Backups, placing files of the backup on
>> > separate drives. The Backup is sucessful.
>> > I am having a problem restoring the backup files. It seems that I have to
>> > overwrite the original database file. How can I restore this FileGroup
>> > backup to the same server but to a different database? Is this possible?
>> > Thanks,
>> > Sal
>>