Restrict The Number Of Records From Query

Hi,
ive a table of over 90,000 records . is ther any method for restricting the number of records returned by a SELECT query
i want to implement a query system which will give me the first 50 records, next 50 ,... and so on
can ne body help ?Originally posted by baburajv
Hi,

ive a table of over 90,000 records . is ther any method for restricting the number of records returned by a SELECT query

i want to implement a query system which will give me the first 50 records, next 50 ,... and so on

can ne body help ?

select top 50 from table_name|||SELECT TOP 50 FROM TABLE_NAME

WILL RETURN THE TOP 50 ROWS FROM THE TABLE

HOW CAN I RETRIEVE THE NEXT 50 ROWS|||Originally posted by baburajv
SELECT TOP 50 FROM TABLE_NAME

WILL RETURN THE TOP 50 ROWS FROM THE TABLE

HOW CAN I RETRIEVE THE NEXT 50 ROWS

I think then u need to use temperory tables for it.|||select top NUMBER_OF_ROWS *
from table
where column NOT IN
(select top BEGIN_AT column
from table)

Restrict result rows with a database setting

I am using SQL Server 2000 SP4. Is it possible to limit the number of
returned rows in a query via some sort of database setting? We have a
customer table with over 100,000 records and are not able to modify the
application that queries this table.
If our users do not put in selection criteria, they get all 100,000 records
and subsequently crash the machine they are running from. I was wondering if
I could limit the maximum number of rows to 10,000 via a database setting? I
know I could do that via the SQL that is called, but I co not have control
over that.
Thanks for any suggestions,
Butch Smith
Hi
I guess you could try setting the configuration value of "query governor
cost limit" to a given value, but then it could have adverse effects else
where. You should look at getting the application changed to limit the time a
query takes and also making sure that an excessive number of records are not
returned.
John
"Butch" wrote:

> I am using SQL Server 2000 SP4. Is it possible to limit the number of
> returned rows in a query via some sort of database setting? We have a
> customer table with over 100,000 records and are not able to modify the
> application that queries this table.
> If our users do not put in selection criteria, they get all 100,000 records
> and subsequently crash the machine they are running from. I was wondering if
> I could limit the maximum number of rows to 10,000 via a database setting? I
> know I could do that via the SQL that is called, but I co not have control
> over that.
>
> Thanks for any suggestions,
> Butch Smith
>
>
|||You could try query govenor to limit the time a query can run for.
sp_configure 'query governor cost limit', '300'
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Butch" <smithb@.R3M0V3_CAPStntfireworks.com> wrote in message
news:%238kiT4r8GHA.2120@.TK2MSFTNGP03.phx.gbl...
>I am using SQL Server 2000 SP4. Is it possible to limit the number of
>returned rows in a query via some sort of database setting? We have a
>customer table with over 100,000 records and are not able to modify the
>application that queries this table.
> If our users do not put in selection criteria, they get all 100,000
> records and subsequently crash the machine they are running from. I was
> wondering if I could limit the maximum number of rows to 10,000 via a
> database setting? I know I could do that via the SQL that is called, but I
> co not have control over that.
>
> Thanks for any suggestions,
> Butch Smith
>
|||Maybe you can try to create a view with a select top of the table and rename
the table of 100000 records. Then create instead of triggers to make the view
updatable. But the application could crash with other things after that...
"Hilary Cotter" wrote:

> You could try query govenor to limit the time a query can run for.
> sp_configure 'query governor cost limit', '300'
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Butch" <smithb@.R3M0V3_CAPStntfireworks.com> wrote in message
> news:%238kiT4r8GHA.2120@.TK2MSFTNGP03.phx.gbl...
>
>
sql

Restrict result rows with a database setting

I am using SQL Server 2000 SP4. Is it possible to limit the number of
returned rows in a query via some sort of database setting? We have a
customer table with over 100,000 records and are not able to modify the
application that queries this table.
If our users do not put in selection criteria, they get all 100,000 records
and subsequently crash the machine they are running from. I was wondering if
I could limit the maximum number of rows to 10,000 via a database setting? I
know I could do that via the SQL that is called, but I co not have control
over that.
Thanks for any suggestions,
Butch SmithHi
I guess you could try setting the configuration value of "query governor
cost limit" to a given value, but then it could have adverse effects else
where. You should look at getting the application changed to limit the time
a
query takes and also making sure that an excessive number of records are not
returned.
John
"Butch" wrote:

> I am using SQL Server 2000 SP4. Is it possible to limit the number of
> returned rows in a query via some sort of database setting? We have a
> customer table with over 100,000 records and are not able to modify the
> application that queries this table.
> If our users do not put in selection criteria, they get all 100,000 record
s
> and subsequently crash the machine they are running from. I was wondering
if
> I could limit the maximum number of rows to 10,000 via a database setting?
I
> know I could do that via the SQL that is called, but I co not have control
> over that.
>
> Thanks for any suggestions,
> Butch Smith
>
>|||You could try query govenor to limit the time a query can run for.
sp_configure 'query governor cost limit', '300'
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Butch" <smithb@.R3M0V3_CAPStntfireworks.com> wrote in message
news:%238kiT4r8GHA.2120@.TK2MSFTNGP03.phx.gbl...
>I am using SQL Server 2000 SP4. Is it possible to limit the number of
>returned rows in a query via some sort of database setting? We have a
>customer table with over 100,000 records and are not able to modify the
>application that queries this table.
> If our users do not put in selection criteria, they get all 100,000
> records and subsequently crash the machine they are running from. I was
> wondering if I could limit the maximum number of rows to 10,000 via a
> database setting? I know I could do that via the SQL that is called, but I
> co not have control over that.
>
> Thanks for any suggestions,
> Butch Smith
>|||Maybe you can try to create a view with a select top of the table and rename
the table of 100000 records. Then create instead of triggers to make the vie
w
updatable. But the application could crash with other things after that...
"Hilary Cotter" wrote:

> You could try query govenor to limit the time a query can run for.
> sp_configure 'query governor cost limit', '300'
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Butch" <smithb@.R3M0V3_CAPStntfireworks.com> wrote in message
> news:%238kiT4r8GHA.2120@.TK2MSFTNGP03.phx.gbl...
>
>

Restrict result rows with a database setting

I am using SQL Server 2000 SP4. Is it possible to limit the number of
returned rows in a query via some sort of database setting? We have a
customer table with over 100,000 records and are not able to modify the
application that queries this table.
If our users do not put in selection criteria, they get all 100,000 records
and subsequently crash the machine they are running from. I was wondering if
I could limit the maximum number of rows to 10,000 via a database setting? I
know I could do that via the SQL that is called, but I co not have control
over that.
Thanks for any suggestions,
Butch SmithHi
I guess you could try setting the configuration value of "query governor
cost limit" to a given value, but then it could have adverse effects else
where. You should look at getting the application changed to limit the time a
query takes and also making sure that an excessive number of records are not
returned.
John
"Butch" wrote:
> I am using SQL Server 2000 SP4. Is it possible to limit the number of
> returned rows in a query via some sort of database setting? We have a
> customer table with over 100,000 records and are not able to modify the
> application that queries this table.
> If our users do not put in selection criteria, they get all 100,000 records
> and subsequently crash the machine they are running from. I was wondering if
> I could limit the maximum number of rows to 10,000 via a database setting? I
> know I could do that via the SQL that is called, but I co not have control
> over that.
>
> Thanks for any suggestions,
> Butch Smith
>
>|||You could try query govenor to limit the time a query can run for.
sp_configure 'query governor cost limit', '300'
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Butch" <smithb@.R3M0V3_CAPStntfireworks.com> wrote in message
news:%238kiT4r8GHA.2120@.TK2MSFTNGP03.phx.gbl...
>I am using SQL Server 2000 SP4. Is it possible to limit the number of
>returned rows in a query via some sort of database setting? We have a
>customer table with over 100,000 records and are not able to modify the
>application that queries this table.
> If our users do not put in selection criteria, they get all 100,000
> records and subsequently crash the machine they are running from. I was
> wondering if I could limit the maximum number of rows to 10,000 via a
> database setting? I know I could do that via the SQL that is called, but I
> co not have control over that.
>
> Thanks for any suggestions,
> Butch Smith
>|||Maybe you can try to create a view with a select top of the table and rename
the table of 100000 records. Then create instead of triggers to make the view
updatable. But the application could crash with other things after that...
"Hilary Cotter" wrote:
> You could try query govenor to limit the time a query can run for.
> sp_configure 'query governor cost limit', '300'
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Butch" <smithb@.R3M0V3_CAPStntfireworks.com> wrote in message
> news:%238kiT4r8GHA.2120@.TK2MSFTNGP03.phx.gbl...
> >I am using SQL Server 2000 SP4. Is it possible to limit the number of
> >returned rows in a query via some sort of database setting? We have a
> >customer table with over 100,000 records and are not able to modify the
> >application that queries this table.
> >
> > If our users do not put in selection criteria, they get all 100,000
> > records and subsequently crash the machine they are running from. I was
> > wondering if I could limit the maximum number of rows to 10,000 via a
> > database setting? I know I could do that via the SQL that is called, but I
> > co not have control over that.
> >
> >
> > Thanks for any suggestions,
> >
> > Butch Smith
> >
>
>

Restrict Port Security

I'm going to be installing a sql server behind a firewall. I'd appreciate
any best practice advice on changing the default port number and also wanted
to know if there was a way to secure a sql server by granting access only to
specific IP addresses?
Hi Scott,
Really best practice is changing the default port number of the SQL Server.
But granting access to specific IP addresses only machine 1 to machine 2 or
ip address.
Hugs,
Rodrigo Fernandes
MCSE/MCDBA
"scott" wrote:

> I'm going to be installing a sql server behind a firewall. I'd appreciate
> any best practice advice on changing the default port number and also wanted
> to know if there was a way to secure a sql server by granting access only to
> specific IP addresses?
>
>
|||What do you mean by saying 'but granting access ...'?
"Rodrigo Fernandes" <RodrigoFernandes@.discussions.microsoft.com> wrote in
message news:94A130F7-B240-44DF-9054-9E2BC08D9DE3@.microsoft.com...[vbcol=seagreen]
> Hi Scott,
> Really best practice is changing the default port number of the SQL
> Server.
> But granting access to specific IP addresses only machine 1 to machine 2
> or
> ip address.
> Hugs,
> Rodrigo Fernandes
> MCSE/MCDBA
> "scott" wrote:

Restrict Port Security

I'm going to be installing a sql server behind a firewall. I'd appreciate
any best practice advice on changing the default port number and also wanted
to know if there was a way to secure a sql server by granting access only to
specific IP addresses?Hi Scott,
Really best practice is changing the default port number of the SQL Server.
But granting access to specific IP addresses only machine 1 to machine 2 or
ip address.
Hugs,
Rodrigo Fernandes
MCSE/MCDBA
"scott" wrote:

> I'm going to be installing a sql server behind a firewall. I'd appreciate
> any best practice advice on changing the default port number and also want
ed
> to know if there was a way to secure a sql server by granting access only
to
> specific IP addresses?
>
>|||What do you mean by saying 'but granting access ...'?
"Rodrigo Fernandes" <RodrigoFernandes@.discussions.microsoft.com> wrote in
message news:94A130F7-B240-44DF-9054-9E2BC08D9DE3@.microsoft.com...[vbcol=seagreen]
> Hi Scott,
> Really best practice is changing the default port number of the SQL
> Server.
> But granting access to specific IP addresses only machine 1 to machine 2
> or
> ip address.
> Hugs,
> Rodrigo Fernandes
> MCSE/MCDBA
> "scott" wrote:
>

Restrict Port Security

I'm going to be installing a sql server behind a firewall. I'd appreciate
any best practice advice on changing the default port number and also wanted
to know if there was a way to secure a sql server by granting access only to
specific IP addresses?Hi Scott,
Really best practice is changing the default port number of the SQL Server.
But granting access to specific IP addresses only machine 1 to machine 2 or
ip address.
Hugs,
Rodrigo Fernandes
MCSE/MCDBA
"scott" wrote:
> I'm going to be installing a sql server behind a firewall. I'd appreciate
> any best practice advice on changing the default port number and also wanted
> to know if there was a way to secure a sql server by granting access only to
> specific IP addresses?
>
>|||What do you mean by saying 'but granting access ...'?
"Rodrigo Fernandes" <RodrigoFernandes@.discussions.microsoft.com> wrote in
message news:94A130F7-B240-44DF-9054-9E2BC08D9DE3@.microsoft.com...
> Hi Scott,
> Really best practice is changing the default port number of the SQL
> Server.
> But granting access to specific IP addresses only machine 1 to machine 2
> or
> ip address.
> Hugs,
> Rodrigo Fernandes
> MCSE/MCDBA
> "scott" wrote:
>> I'm going to be installing a sql server behind a firewall. I'd appreciate
>> any best practice advice on changing the default port number and also
>> wanted
>> to know if there was a way to secure a sql server by granting access only
>> to
>> specific IP addresses?
>>sql

Restrict number of connections to a databse.

Hi
Is there anyway I can restrict the number of connections to a SQL Server
database ?
How can we do that.?
Also what is the best way to find out number of current connections to the
database using a query ?
Thanksselect @.@.maxconnections
can be set using sp_configure
sp_who and sp_who2 show you the number of connections
--
Dandy Weyn, Belgium
MCSE, MCSA, MCDBA, MCT
http://www.dandyman.net
Check my SQL Server resource pages (currently under construction)
http://www.dandyman.net/sql
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:ODXIsYx#DHA.3500@.tk2msftngp13.phx.gbl...
> Hi
> Is there anyway I can restrict the number of connections to a SQL Server
> database ?
> How can we do that.?
> Also what is the best way to find out number of current connections to the
> database using a query ?
> Thanks
>|||Hi,
Is there anyway I can restrict the number of connections to a SQL Server
database ?
You can restrict the connections only SQL Server wide not database level.
How can we do that.?
SP_configure 'user connections',numberofconnections
(By default it is 0 and allow maximum)
Also what is the best way to find out number of current connections to the
database using a query ?
select substring(db_name(dbid),1,30) as DB_name ,count(*) as Connection
from sysprocesses
group by substring(db_name(dbid),1,30)
Thanks
Hari
MCDBA
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:ODXIsYx#DHA.3500@.tk2msftngp13.phx.gbl...
> Hi
> Is there anyway I can restrict the number of connections to a SQL Server
> database ?
> How can we do that.?
> Also what is the best way to find out number of current connections to the
> database using a query ?
> Thanks
>|||bear in mind that the connections is only configurable at a server level and
not at a database level, thus sp_configure 'show advanced'
,1 must be set inorder to be able to view and set this option.
also to finding the current active connections can be done by using sp_who2
active
this shows you the active users only.
Olu Adedeji
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:ODXIsYx#DHA.3500@.tk2msftngp13.phx.gbl...
> Hi
> Is there anyway I can restrict the number of connections to a SQL Server
> database ?
> How can we do that.?
> Also what is the best way to find out number of current connections to the
> database using a query ?
> Thanks
>

Restrict number of connections to a databse.

Hi
Is there anyway I can restrict the number of connections to a SQL Server
database ?
How can we do that.?
Also what is the best way to find out number of current connections to the
database using a query ?
Thanksselect @.@.maxconnections
can be set using sp_configure
sp_who and sp_who2 show you the number of connections
--
Dandy Weyn, Belgium
MCSE, MCSA, MCDBA, MCT
http://www.dandyman.net
Check my SQL Server resource pages (currently under construction)
http://www.dandyman.net/sql
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:ODXIsYx#DHA.3500@.tk2msftngp13.phx.gbl...
> Hi
> Is there anyway I can restrict the number of connections to a SQL Server
> database ?
> How can we do that.?
> Also what is the best way to find out number of current connections to the
> database using a query ?
> Thanks
>|||Hi,
Is there anyway I can restrict the number of connections to a SQL Server
database ?
You can restrict the connections only SQL Server wide not database level.
How can we do that.?
SP_configure 'user connections',numberofconnections
(By default it is 0 and allow maximum)
Also what is the best way to find out number of current connections to the
database using a query ?
select substring(db_name(dbid),1,30) as DB_name ,count(*) as Connection
from sysprocesses
group by substring(db_name(dbid),1,30)
Thanks
Hari
MCDBA
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:ODXIsYx#DHA.3500@.tk2msftngp13.phx.gbl...
> Hi
> Is there anyway I can restrict the number of connections to a SQL Server
> database ?
> How can we do that.?
> Also what is the best way to find out number of current connections to the
> database using a query ?
> Thanks
>|||bear in mind that the connections is only configurable at a server level and
not at a database level, thus sp_configure 'show advanced'
,1 must be set inorder to be able to view and set this option.
also to finding the current active connections can be done by using sp_who2
active
this shows you the active users only.
--
Olu Adedeji
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:ODXIsYx#DHA.3500@.tk2msftngp13.phx.gbl...
> Hi
> Is there anyway I can restrict the number of connections to a SQL Server
> database ?
> How can we do that.?
> Also what is the best way to find out number of current connections to the
> database using a query ?
> Thanks
>

Restoring deleted records with out of row blobs?

I have a database that had a number of rows in multiple tables deleted -
fortunately there was very little activity after these deletions (mostly more
deletions a few days later).
I have the log files, but the original inserts just have the pointer to the
blob. I tried generating undo scripts with apexsql Log but it doesn't even
reference the blob column when the script generates (restoring from a
detached mdf does give the pointer, but that pointer doesn't go anywhere and
a select on it is blank/null).
How is one supposed to go about restoring records / rolling back a
transaction log for deleting a row with an out-of-row blob? Am I totally
screwed even though nothing else happened in the database aside from the
deletes before the backup was taken?
"Andrew Meinert" <AndrewMeinert@.discussions.microsoft.com> wrote in message
news:5CE119C0-80AE-4A16-A452-799A41971BC5@.microsoft.com...
>I have a database that had a number of rows in multiple tables deleted -
> fortunately there was very little activity after these deletions (mostly
> more
> deletions a few days later).
> I have the log files, but the original inserts just have the pointer to
> the
> blob. I tried generating undo scripts with apexsql Log but it doesn't
> even
> reference the blob column when the script generates (restoring from a
> detached mdf does give the pointer, but that pointer doesn't go anywhere
> and
> a select on it is blank/null).
> How is one supposed to go about restoring records / rolling back a
> transaction log for deleting a row with an out-of-row blob? Am I totally
> screwed even though nothing else happened in the database aside from the
> deletes before the backup was taken?
Use the last full backup and the subsequent log backups to do a
point-in-time restore to a new database. Then do INSERTS from the restored
database to your real database.
David
|||"David Browne" wrote:

> "Andrew Meinert" <AndrewMeinert@.discussions.microsoft.com> wrote in message
> news:5CE119C0-80AE-4A16-A452-799A41971BC5@.microsoft.com...
>
> Use the last full backup and the subsequent log backups to do a
> point-in-time restore to a new database. Then do INSERTS from the restored
> database to your real database.
> David
>
Unfortunately the database was created between full backups and the only
remaining full backup is after the fact. There's no way to roll back the
transactions from the ldf?
How are blobs handled in the transaction log? Is it just a limitation of
the log viewer that I'm using that I don't see the the anything about the
content of the blobs aside from the pointer in row?
|||"David Browne" wrote:

> "Andrew Meinert" <AndrewMeinert@.discussions.microsoft.com> wrote in message
> news:5CE119C0-80AE-4A16-A452-799A41971BC5@.microsoft.com...
>
> Use the last full backup and the subsequent log backups to do a
> point-in-time restore to a new database. Then do INSERTS from the restored
> database to your real database.
> David
>
So far it looks like
http://www.red-gate.com/products/SQL_Log_Rescue/index.htm will help me. My
frustrations seem to stem from a limitation with the other software I had
tried. I can see the correct data in the demo of log rescue... here's to
hoping.

Restoring deleted records with out of row blobs?

I have a database that had a number of rows in multiple tables deleted -
fortunately there was very little activity after these deletions (mostly more
deletions a few days later).
I have the log files, but the original inserts just have the pointer to the
blob. I tried generating undo scripts with apexsql Log but it doesn't even
reference the blob column when the script generates (restoring from a
detached mdf does give the pointer, but that pointer doesn't go anywhere and
a select on it is blank/null).
How is one supposed to go about restoring records / rolling back a
transaction log for deleting a row with an out-of-row blob? Am I totally
screwed even though nothing else happened in the database aside from the
deletes before the backup was taken?"Andrew Meinert" <AndrewMeinert@.discussions.microsoft.com> wrote in message
news:5CE119C0-80AE-4A16-A452-799A41971BC5@.microsoft.com...
>I have a database that had a number of rows in multiple tables deleted -
> fortunately there was very little activity after these deletions (mostly
> more
> deletions a few days later).
> I have the log files, but the original inserts just have the pointer to
> the
> blob. I tried generating undo scripts with apexsql Log but it doesn't
> even
> reference the blob column when the script generates (restoring from a
> detached mdf does give the pointer, but that pointer doesn't go anywhere
> and
> a select on it is blank/null).
> How is one supposed to go about restoring records / rolling back a
> transaction log for deleting a row with an out-of-row blob? Am I totally
> screwed even though nothing else happened in the database aside from the
> deletes before the backup was taken?
Use the last full backup and the subsequent log backups to do a
point-in-time restore to a new database. Then do INSERTS from the restored
database to your real database.
David|||"David Browne" wrote:
> "Andrew Meinert" <AndrewMeinert@.discussions.microsoft.com> wrote in message
> news:5CE119C0-80AE-4A16-A452-799A41971BC5@.microsoft.com...
> >I have a database that had a number of rows in multiple tables deleted -
> > fortunately there was very little activity after these deletions (mostly
> > more
> > deletions a few days later).
> >
> > I have the log files, but the original inserts just have the pointer to
> > the
> > blob. I tried generating undo scripts with apexsql Log but it doesn't
> > even
> > reference the blob column when the script generates (restoring from a
> > detached mdf does give the pointer, but that pointer doesn't go anywhere
> > and
> > a select on it is blank/null).
> >
> > How is one supposed to go about restoring records / rolling back a
> > transaction log for deleting a row with an out-of-row blob? Am I totally
> > screwed even though nothing else happened in the database aside from the
> > deletes before the backup was taken?
>
> Use the last full backup and the subsequent log backups to do a
> point-in-time restore to a new database. Then do INSERTS from the restored
> database to your real database.
> David
>
So far it looks like
http://www.red-gate.com/products/SQL_Log_Rescue/index.htm will help me. My
frustrations seem to stem from a limitation with the other software I had
tried. I can see the correct data in the demo of log rescue... here's to
hoping.

Restoring deleted records with out of row blobs?

I have a database that had a number of rows in multiple tables deleted -
fortunately there was very little activity after these deletions (mostly mor
e
deletions a few days later).
I have the log files, but the original inserts just have the pointer to the
blob. I tried generating undo scripts with apexsql Log but it doesn't even
reference the blob column when the script generates (restoring from a
detached mdf does give the pointer, but that pointer doesn't go anywhere and
a select on it is blank/null).
How is one supposed to go about restoring records / rolling back a
transaction log for deleting a row with an out-of-row blob? Am I totally
screwed even though nothing else happened in the database aside from the
deletes before the backup was taken?"Andrew Meinert" <AndrewMeinert@.discussions.microsoft.com> wrote in message
news:5CE119C0-80AE-4A16-A452-799A41971BC5@.microsoft.com...
>I have a database that had a number of rows in multiple tables deleted -
> fortunately there was very little activity after these deletions (mostly
> more
> deletions a few days later).
> I have the log files, but the original inserts just have the pointer to
> the
> blob. I tried generating undo scripts with apexsql Log but it doesn't
> even
> reference the blob column when the script generates (restoring from a
> detached mdf does give the pointer, but that pointer doesn't go anywhere
> and
> a select on it is blank/null).
> How is one supposed to go about restoring records / rolling back a
> transaction log for deleting a row with an out-of-row blob? Am I totally
> screwed even though nothing else happened in the database aside from the
> deletes before the backup was taken?
Use the last full backup and the subsequent log backups to do a
point-in-time restore to a new database. Then do INSERTS from the restored
database to your real database.
David|||"David Browne" wrote:

> "Andrew Meinert" <AndrewMeinert@.discussions.microsoft.com> wrote in messag
e
> news:5CE119C0-80AE-4A16-A452-799A41971BC5@.microsoft.com...
>
> Use the last full backup and the subsequent log backups to do a
> point-in-time restore to a new database. Then do INSERTS from the restore
d
> database to your real database.
> David
>
Unfortunately the database was created between full backups and the only
remaining full backup is after the fact. There's no way to roll back the
transactions from the ldf?
How are blobs handled in the transaction log? Is it just a limitation of
the log viewer that I'm using that I don't see the the anything about the
content of the blobs aside from the pointer in row?|||"David Browne" wrote:

> "Andrew Meinert" <AndrewMeinert@.discussions.microsoft.com> wrote in messag
e
> news:5CE119C0-80AE-4A16-A452-799A41971BC5@.microsoft.com...
>
> Use the last full backup and the subsequent log backups to do a
> point-in-time restore to a new database. Then do INSERTS from the restore
d
> database to your real database.
> David
>
So far it looks like
http://www.red-gate.com/products/SQ...escue/index.htm will help me. My
frustrations seem to stem from a limitation with the other software I had
tried. I can see the correct data in the demo of log rescue... here's to
hoping.