Parameterized Expression: March 2012

Friday, March 30, 2012

particulat table in Filegroup

quote:

> Dear Friends
> I have a small database of 1 gb i have created some
> Secondary data files for same. in the same database some
> tables are extensively used for the Modification hence i
> want to separte them form other table datafiles can i do
> the same.
> please suggest.
> Best regards
> NiitMalad

|||> If you have an existing table and you want to place it on another
filegroup

quote:

> you can only create a new table with the ON filegroup keywords

Or you could create a clustered index using the ON filegroup clause, and it
will move the data to the filegroup. Of course, if there's already a
clustered index, you'll have to drop it first. Which complicates things if
it's a referenced primary key.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

particulat table in Filegroup

Dear Friends
I have a small database of 1 gb i have created some
Secondary data files for same. in the same database some
tables are extensively used for the Modification hence i
want to separte them form other table datafiles can i do
the same.
please suggest.
Best regards
NiitMaladWhat you can do is create multiple filegroups and assign a datafile to a
filegroup.
You can specify the create table statement with the filegroup it must be
created on
CREATE Table tbl_demo (COL1 INT)
on FILEGROUP
where filegroup is the logical name of the filegroup.
If you have an existing table and you want to place it on another filegroup
you can only create a new table with the ON filegroup keywords, insert from
the first table, delete the first table and rename the new table, what means
: there is no alter table statement to move a table to another filegroup.
You first need to create the filegroups before you create the datafiles and
assign them onto a filegroup
--
Regards,
Dandy Weyn
MCSE, MCSA, MCDBA, MCT
www.dandyman.net
"niitmalad" <niitmalad@.yahoo.co.in> wrote in message
news:006601c3dd18$6132f930$a101280a@.phx.gbl...
> Dear Friends
> I have a small database of 1 gb i have created some
> Secondary data files for same. in the same database some
> tables are extensively used for the Modification hence i
> want to separte them form other table datafiles can i do
> the same.
> please suggest.
> Best regards
> NiitMalad|||> If you have an existing table and you want to place it on another
filegroup
> you can only create a new table with the ON filegroup keywords
Or you could create a clustered index using the ON filegroup clause, and it
will move the data to the filegroup. Of course, if there's already a
clustered index, you'll have to drop it first. Which complicates things if
it's a referenced primary key.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

partially update webpage

How to dynamically and partially Update the webpage? The content of
the part of the page is from the user's requerement and get data from
the server side.
The purpose is to speed up the page update. We have a page that most
of content are static, only small part is changed per user's select
and the replying data need to get from server side database. Thanks
for help. GeneKeep your content in a database. Then have the Web site query the database
to render the content.

Or just use a CMS such as SiteSimplify.

"GeneSZ" <jingshengzhou@.hotmail.com> wrote in message
news:60a82f9c.0308291149.3e754970@.posting.google.c om...
> How to dynamically and partially Update the webpage? The content of
> the part of the page is from the user's requerement and get data from
> the server side.
> The purpose is to speed up the page update. We have a page that most
> of content are static, only small part is changed per user's select
> and the replying data need to get from server side database. Thanks
> for help. Gene

Partially Bold text and Justify?

I have put following expression in a textbox.
="My name is " + First(Fields!Brok_Name.Value, "myDataset")
I just want to Bold the Field value and not the word "My name is ".
I also want to justify the text Alignment, how can I ?
I am using Sql Server Reporting Service 2005.
Any idea please ?
Best Regards,
LuqmanLuqman,
I don't think multiple formatting within a single text box is supported. You
might be able to embed an html character in the string like this ...>="My
name is " + First(Fields!Brok_Name.Value, "myDataset") + "" ...never
tried it but maybe it will work?
I have always used 2 text boxes. One for the label "My name is " and another
for the field value. Then I right align the label and left align the field
value. As far as justifying the complete string "My name is " + Field ...not
sure if it is possible with different bold values?
luqman wrote:
>I have put following expression in a textbox.
>="My name is " + First(Fields!Brok_Name.Value, "myDataset")
>I just want to Bold the Field value and not the word "My name is ".
>I also want to justify the text Alignment, how can I ?
>I am using Sql Server Reporting Service 2005.
>Any idea please ?
>Best Regards,
>Luqman
--
Message posted via http://www.sqlmonster.com|||Hi,
The Html character within the text is showing as text.
My expression is :
="My name is " + First(Fields!Brok_Name.Value, "myDataset") + ""
Output is :
My name is luqman 
Am I missing something ?
Best Regards,
Luqman
"wnichols via SQLMonster.com" <u3357@.uwe> wrote in message
news:7829016629ac6@.uwe...
> Luqman,
> I don't think multiple formatting within a single text box is supported.
You
> might be able to embed an html character in the string like this ...>="My
> name is " + First(Fields!Brok_Name.Value, "myDataset") + ""
...never
> tried it but maybe it will work?
> I have always used 2 text boxes. One for the label "My name is " and
another
> for the field value. Then I right align the label and left align the
field
> value. As far as justifying the complete string "My name is " + Field
...not
> sure if it is possible with different bold values?
>
> luqman wrote:
> >I have put following expression in a textbox.
> >
> >="My name is " + First(Fields!Brok_Name.Value, "myDataset")
> >
> >I just want to Bold the Field value and not the word "My name is ".
> >
> >I also want to justify the text Alignment, how can I ?
> >
> >I am using Sql Server Reporting Service 2005.
> >
> >Any idea please ?
> >
> >Best Regards,
> >
> >Luqman
> --
> Message posted via http://www.sqlmonster.com
>|||Luqman,
It was just a shot in the dark...guess it did not work. I don't think it is
possible to have multiple formats within the same textbox.
luqman wrote:
>Hi,
>The Html character within the text is showing as text.
>My expression is :
>="My name is " + First(Fields!Brok_Name.Value, "myDataset") + ""
>Output is :
>My name is luqman 
>Am I missing something ?
>Best Regards,
>Luqman
>> Luqman,
>[quoted text clipped - 26 lines]
>> --
>> Message posted via http://www.sqlmonster.com
--
Message posted via http://www.sqlmonster.com|||Hi,
In case, the textbox does not support multiple formats, can we display RTF
Text inside a textbox, saved in a database.
Best Regards,
Luqman
"wnichols via SQLMonster.com" <u3357@.uwe> wrote in message
news:7834e5fdfd74f@.uwe...
> Luqman,
> It was just a shot in the dark...guess it did not work. I don't think it
is
> possible to have multiple formats within the same textbox.
>
> luqman wrote:
> >Hi,
> >
> >The Html character within the text is showing as text.
> >
> >My expression is :
> >="My name is " + First(Fields!Brok_Name.Value, "myDataset") + ""
> >
> >Output is :
> >My name is luqman 
> >
> >Am I missing something ?
> >
> >Best Regards,
> >
> >Luqman
> >
> >> Luqman,
> >>
> >[quoted text clipped - 26 lines]
> >> --
> >> Message posted via http://www.sqlmonster.com
> --
> Message posted via http://www.sqlmonster.com
>|||Luqman,
That's my extent of the knowledge on the subject...Sorry.
luqman wrote:
>Hi,
>In case, the textbox does not support multiple formats, can we display RTF
>Text inside a textbox, saved in a database.
>Best Regards,
>Luqman
>> Luqman,
>[quoted text clipped - 25 lines]
>> --
>> Message posted via http://www.sqlmonster.com
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200709/1|||On Sep 17, 10:55 am, "wnichols via SQLMonster.com" <u3357@.uwe> wrote:
> Luqman,
> That's my extent of the knowledge on the subject...Sorry.
> luqman wrote:
> >Hi,
> >In case, the textbox does not support multiple formats, can we display RTF
> >Text inside a textbox, saved in a database.
> >Best Regards,
> >Luqman
> >> Luqman,
> >[quoted text clipped - 25 lines]
> >> --
> >> Message posted viahttp://www.sqlmonster.com
> --
> Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200709/1
You can't do it in 2005. I saw a video on reporting services 2008,
and it's a new feature microsoft is adding. Below is some links on
it:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2122612&SiteID=1
http://sqljunkies.com/WebLog/sqlbi/archive/2007/06/07/35631.aspx

partial transaction is not working

use northwind
BEGIN TRANSACTION namechange
UPDATE dbo.Employees
SET Title = 'team leader' where FirstName = 'Bishoy'
UPDATE dbo.Employees
SET TitleOfCourtesy = 'Dr' where FirstName = 'Bishoy'
SAVE TRANSACTION namesaved
UPDATE dbo.Employees
SET BirthDate = '8/15/1978' WHERE FirstName = 'Bishoy'
SELECT *
FROM dbo.Employees WHERE FirstName = 'Bishoy'
ROLLBACK TRANSACTION namesaved
COMMIT TRANSACTION namechange
"Bishoy George" <bishoycom@.gmail.com> wrote in message
news:%23RntioSeFHA.3700@.TK2MSFTNGP10.phx.gbl...
> use northwind
> BEGIN TRANSACTION namechange
> UPDATE dbo.Employees
> SET Title = 'team leader' where FirstName = 'Bishoy'
> UPDATE dbo.Employees
> SET TitleOfCourtesy = 'Dr' where FirstName = 'Bishoy'
> SAVE TRANSACTION namesaved
> UPDATE dbo.Employees
> SET BirthDate = '8/15/1978' WHERE FirstName = 'Bishoy'
> SELECT *
> FROM dbo.Employees WHERE FirstName = 'Bishoy'
>
> ROLLBACK TRANSACTION namesaved
> COMMIT TRANSACTION namechange
>
Nope, works perfectly.
Add another
SELECT * FROM dbo.Employees WHERE FirstName = 'Bishoy'
At the end. The BirthDate change is rolled back, but the Title change is
commited.
David
|||Also make sure you don't have an implicit transaction that is nesting your
changes without a commit.
Andrew J. Kelly SQL MVP
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:eggqJCaeFHA.3848@.TK2MSFTNGP10.phx.gbl...
> "Bishoy George" <bishoycom@.gmail.com> wrote in message
> news:%23RntioSeFHA.3700@.TK2MSFTNGP10.phx.gbl...
> Nope, works perfectly.
> Add another
> SELECT * FROM dbo.Employees WHERE FirstName = 'Bishoy'
> At the end. The BirthDate change is rolled back, but the Title change is
> commited.
> David
>
|||Yes , you are right , thank you.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:eggqJCaeFHA.3848@.TK2MSFTNGP10.phx.gbl...
> "Bishoy George" <bishoycom@.gmail.com> wrote in message
> news:%23RntioSeFHA.3700@.TK2MSFTNGP10.phx.gbl...
> Nope, works perfectly.
> Add another
> SELECT * FROM dbo.Employees WHERE FirstName = 'Bishoy'
> At the end. The BirthDate change is rolled back, but the Title change is
> commited.
> David
>
|||You could try save the files you want to attach on a safe place, create a database with same file
structure as you want to have for the one you want to attach, stop SQL server, copy the files you
want to attach over file files for the db you just created and start SQL server. Now, the db is
corrupt to some extent, the question is whether the database will be available with problems in it
or suspect when you start SQL server. MS Support is probably the best route in any case...
Some thoughts:
http://www.karaszi.com/SQLServer/inf...suspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Bishoy George" <bishoycom@.gmail.com> wrote in message
news:%23RntioSeFHA.3700@.TK2MSFTNGP10.phx.gbl...
> use northwind
> BEGIN TRANSACTION namechange
> UPDATE dbo.Employees
> SET Title = 'team leader' where FirstName = 'Bishoy'
> UPDATE dbo.Employees
> SET TitleOfCourtesy = 'Dr' where FirstName = 'Bishoy'
> SAVE TRANSACTION namesaved
> UPDATE dbo.Employees
> SET BirthDate = '8/15/1978' WHERE FirstName = 'Bishoy'
> SELECT *
> FROM dbo.Employees WHERE FirstName = 'Bishoy'
>
> ROLLBACK TRANSACTION namesaved
> COMMIT TRANSACTION namechange
>
>

partial transaction is not working

use northwind
BEGIN TRANSACTION namechange
UPDATE dbo.Employees
SET Title = 'team leader' where FirstName = 'Bishoy'
UPDATE dbo.Employees
SET TitleOfCourtesy = 'Dr' where FirstName = 'Bishoy'
SAVE TRANSACTION namesaved
UPDATE dbo.Employees
SET BirthDate = '8/15/1978' WHERE FirstName = 'Bishoy'
SELECT *
FROM dbo.Employees WHERE FirstName = 'Bishoy'
ROLLBACK TRANSACTION namesaved
COMMIT TRANSACTION namechange"Bishoy George" <bishoycom@.gmail.com> wrote in message
news:%23RntioSeFHA.3700@.TK2MSFTNGP10.phx.gbl...
> use northwind
> BEGIN TRANSACTION namechange
> UPDATE dbo.Employees
> SET Title = 'team leader' where FirstName = 'Bishoy'
> UPDATE dbo.Employees
> SET TitleOfCourtesy = 'Dr' where FirstName = 'Bishoy'
> SAVE TRANSACTION namesaved
> UPDATE dbo.Employees
> SET BirthDate = '8/15/1978' WHERE FirstName = 'Bishoy'
> SELECT *
> FROM dbo.Employees WHERE FirstName = 'Bishoy'
>
> ROLLBACK TRANSACTION namesaved
> COMMIT TRANSACTION namechange
>
Nope, works perfectly.
Add another
SELECT * FROM dbo.Employees WHERE FirstName = 'Bishoy'
At the end. The BirthDate change is rolled back, but the Title change is
commited.
David|||Also make sure you don't have an implicit transaction that is nesting your
changes without a commit.
Andrew J. Kelly SQL MVP
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:eggqJCaeFHA.3848@.TK2MSFTNGP10.phx.gbl...
> "Bishoy George" <bishoycom@.gmail.com> wrote in message
> news:%23RntioSeFHA.3700@.TK2MSFTNGP10.phx.gbl...
>> use northwind
>> BEGIN TRANSACTION namechange
>> UPDATE dbo.Employees
>> SET Title = 'team leader' where FirstName = 'Bishoy'
>> UPDATE dbo.Employees
>> SET TitleOfCourtesy = 'Dr' where FirstName = 'Bishoy'
>> SAVE TRANSACTION namesaved
>> UPDATE dbo.Employees
>> SET BirthDate = '8/15/1978' WHERE FirstName = 'Bishoy'
>> SELECT *
>> FROM dbo.Employees WHERE FirstName = 'Bishoy'
>>
>> ROLLBACK TRANSACTION namesaved
>> COMMIT TRANSACTION namechange
> Nope, works perfectly.
> Add another
> SELECT * FROM dbo.Employees WHERE FirstName = 'Bishoy'
> At the end. The BirthDate change is rolled back, but the Title change is
> commited.
> David
>|||Yes , you are right , thank you.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:eggqJCaeFHA.3848@.TK2MSFTNGP10.phx.gbl...
> "Bishoy George" <bishoycom@.gmail.com> wrote in message
> news:%23RntioSeFHA.3700@.TK2MSFTNGP10.phx.gbl...
>> use northwind
>> BEGIN TRANSACTION namechange
>> UPDATE dbo.Employees
>> SET Title = 'team leader' where FirstName = 'Bishoy'
>> UPDATE dbo.Employees
>> SET TitleOfCourtesy = 'Dr' where FirstName = 'Bishoy'
>> SAVE TRANSACTION namesaved
>> UPDATE dbo.Employees
>> SET BirthDate = '8/15/1978' WHERE FirstName = 'Bishoy'
>> SELECT *
>> FROM dbo.Employees WHERE FirstName = 'Bishoy'
>>
>> ROLLBACK TRANSACTION namesaved
>> COMMIT TRANSACTION namechange
> Nope, works perfectly.
> Add another
> SELECT * FROM dbo.Employees WHERE FirstName = 'Bishoy'
> At the end. The BirthDate change is rolled back, but the Title change is
> commited.
> David
>|||You could try save the files you want to attach on a safe place, create a database with same file
structure as you want to have for the one you want to attach, stop SQL server, copy the files you
want to attach over file files for the db you just created and start SQL server. Now, the db is
corrupt to some extent, the question is whether the database will be available with problems in it
or suspect when you start SQL server. MS Support is probably the best route in any case...
Some thoughts:
http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Bishoy George" <bishoycom@.gmail.com> wrote in message
news:%23RntioSeFHA.3700@.TK2MSFTNGP10.phx.gbl...
> use northwind
> BEGIN TRANSACTION namechange
> UPDATE dbo.Employees
> SET Title = 'team leader' where FirstName = 'Bishoy'
> UPDATE dbo.Employees
> SET TitleOfCourtesy = 'Dr' where FirstName = 'Bishoy'
> SAVE TRANSACTION namesaved
> UPDATE dbo.Employees
> SET BirthDate = '8/15/1978' WHERE FirstName = 'Bishoy'
> SELECT *
> FROM dbo.Employees WHERE FirstName = 'Bishoy'
>
> ROLLBACK TRANSACTION namesaved
> COMMIT TRANSACTION namechange
>
>

partial transaction is not working

use northwind
BEGIN TRANSACTION namechange
UPDATE dbo.Employees
SET Title = 'team leader' where FirstName = 'Bishoy'
UPDATE dbo.Employees
SET TitleOfCourtesy = 'Dr' where FirstName = 'Bishoy'
SAVE TRANSACTION namesaved
UPDATE dbo.Employees
SET BirthDate = '8/15/1978' WHERE FirstName = 'Bishoy'
SELECT *
FROM dbo.Employees WHERE FirstName = 'Bishoy'
ROLLBACK TRANSACTION namesaved
COMMIT TRANSACTION namechange"Bishoy George" <bishoycom@.gmail.com> wrote in message
news:%23RntioSeFHA.3700@.TK2MSFTNGP10.phx.gbl...
> use northwind
> BEGIN TRANSACTION namechange
> UPDATE dbo.Employees
> SET Title = 'team leader' where FirstName = 'Bishoy'
> UPDATE dbo.Employees
> SET TitleOfCourtesy = 'Dr' where FirstName = 'Bishoy'
> SAVE TRANSACTION namesaved
> UPDATE dbo.Employees
> SET BirthDate = '8/15/1978' WHERE FirstName = 'Bishoy'
> SELECT *
> FROM dbo.Employees WHERE FirstName = 'Bishoy'
>
> ROLLBACK TRANSACTION namesaved
> COMMIT TRANSACTION namechange
>
Nope, works perfectly.
Add another
SELECT * FROM dbo.Employees WHERE FirstName = 'Bishoy'
At the end. The BirthDate change is rolled back, but the Title change is
commited.
David|||Also make sure you don't have an implicit transaction that is nesting your
changes without a commit.
Andrew J. Kelly SQL MVP
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:eggqJCaeFHA.3848@.TK2MSFTNGP10.phx.gbl...
> "Bishoy George" <bishoycom@.gmail.com> wrote in message
> news:%23RntioSeFHA.3700@.TK2MSFTNGP10.phx.gbl...
> Nope, works perfectly.
> Add another
> SELECT * FROM dbo.Employees WHERE FirstName = 'Bishoy'
> At the end. The BirthDate change is rolled back, but the Title change is
> commited.
> David
>|||Yes , you are right , thank you.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:eggqJCaeFHA.3848@.TK2MSFTNGP10.phx.gbl...
> "Bishoy George" <bishoycom@.gmail.com> wrote in message
> news:%23RntioSeFHA.3700@.TK2MSFTNGP10.phx.gbl...
> Nope, works perfectly.
> Add another
> SELECT * FROM dbo.Employees WHERE FirstName = 'Bishoy'
> At the end. The BirthDate change is rolled back, but the Title change is
> commited.
> David
>|||You could try save the files you want to attach on a safe place, create a da
tabase with same file
structure as you want to have for the one you want to attach, stop SQL serve
r, copy the files you
want to attach over file files for the db you just created and start SQL ser
ver. Now, the db is
corrupt to some extent, the question is whether the database will be availab
le with problems in it
or suspect when you start SQL server. MS Support is probably the best route
in any case...
Some thoughts:
http://www.karaszi.com/SQLServer/in..._suspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Bishoy George" <bishoycom@.gmail.com> wrote in message
news:%23RntioSeFHA.3700@.TK2MSFTNGP10.phx.gbl...
> use northwind
> BEGIN TRANSACTION namechange
> UPDATE dbo.Employees
> SET Title = 'team leader' where FirstName = 'Bishoy'
> UPDATE dbo.Employees
> SET TitleOfCourtesy = 'Dr' where FirstName = 'Bishoy'
> SAVE TRANSACTION namesaved
> UPDATE dbo.Employees
> SET BirthDate = '8/15/1978' WHERE FirstName = 'Bishoy'
> SELECT *
> FROM dbo.Employees WHERE FirstName = 'Bishoy'
>
> ROLLBACK TRANSACTION namesaved
> COMMIT TRANSACTION namechange
>
>

Partial Success

Log Explorer from Lumigent absolutely rocks. I was able to view the three
major delete transactions, generate sql scripts for them, and run them on
the db. I've now got files back in sharepoint.
The sharepoint strucure does not look like the users are saying it should,
so I'm either executing the scripts in the wrong order or they're not
shooting me straight. But I can deal with that. ;-)
Thanks a million for all of your help.
"Jack" <anonymous@.microsoft.com> wrote in message
news:%23N8QDhV7GHA.4604@.TK2MSFTNGP03.phx.gbl...
>I goofed and did not have a maintenance plan running. Someone deleted a
>folder in Sharepoint with thousands of documents.
> Since no backup has been run, the transaction log has not been truncated.
> Is there any way to roll back all transactions since Monday, or am I
> hosed?
>
I'm happy for you, Jack. :-)
I'm also a bit surprised that the transaction still existed in the log. Either you were *very* lucky
that the log records are still around. Or, a db backup was actually taken of the database at some
point in time. In any case, I have a feeling that you will look over your backup strategy now... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jack" <anonymous@.microsoft.com> wrote in message news:OKGZU8Y7GHA.3836@.TK2MSFTNGP02.phx.gbl...
> Log Explorer from Lumigent absolutely rocks. I was able to view the three major delete
> transactions, generate sql scripts for them, and run them on the db. I've now got files back in
> sharepoint.
> The sharepoint strucure does not look like the users are saying it should, so I'm either executing
> the scripts in the wrong order or they're not shooting me straight. But I can deal with that.
> ;-)
> Thanks a million for all of your help.
>
> "Jack" <anonymous@.microsoft.com> wrote in message news:%23N8QDhV7GHA.4604@.TK2MSFTNGP03.phx.gbl...
>

Partial Success

Log Explorer from Lumigent absolutely rocks. I was able to view the three
major delete transactions, generate sql scripts for them, and run them on
the db. I've now got files back in sharepoint.
The sharepoint strucure does not look like the users are saying it should,
so I'm either executing the scripts in the wrong order or they're not
shooting me straight. But I can deal with that. ;-)
Thanks a million for all of your help.
"Jack" <anonymous@.microsoft.com> wrote in message
news:%23N8QDhV7GHA.4604@.TK2MSFTNGP03.phx.gbl...
>I goofed and did not have a maintenance plan running. Someone deleted a
>folder in Sharepoint with thousands of documents.
> Since no backup has been run, the transaction log has not been truncated.
> Is there any way to roll back all transactions since Monday, or am I
> hosed?
>I'm happy for you, Jack. :-)
I'm also a bit surprised that the transaction still existed in the log. Eith
er you were *very* lucky
that the log records are still around. Or, a db backup was actually taken of
the database at some
point in time. In any case, I have a feeling that you will look over your ba
ckup strategy now... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jack" <anonymous@.microsoft.com> wrote in message news:OKGZU8Y7GHA.3836@.TK2MSFTNGP02.phx.gbl
..
> Log Explorer from Lumigent absolutely rocks. I was able to view the three
major delete
> transactions, generate sql scripts for them, and run them on the db. I've
now got files back in
> sharepoint.
> The sharepoint strucure does not look like the users are saying it should,
so I'm either executing
> the scripts in the wrong order or they're not shooting me straight. But I
can deal with that.
> ;-)
> Thanks a million for all of your help.
>
> "Jack" <anonymous@.microsoft.com> wrote in message news:%23N8QDhV7GHA.4604@.
TK2MSFTNGP03.phx.gbl...
>

PARTIAL SQL Server RESTORE's

Please..
Can someone confirm that with a PARTIAL FILEGROUP SQL Server RESTORE you
have to have a Full Database Backup (containing all the Filegroups including
the PRIMARY) before you can do a PARTIAL filegroup RESTORE.
Even if you have a performed a filegroup Backup which contains the PRIMARY
filegroup and all the other filegroups then it will not work and you get the
error message:
Server: Msg 3135, Level 16, State 2, Line 1
"was created by BACKUP DATABASE...FILE=<name> and cannot be used for this
restore operation."
All the examples I have seen on the net use a FULL Database backup as a
starting point and I have seen the following posting which kind of confirms
my suspisions from someone with a microsoft.com email address
http://groups.google.com/groups?q=pa...TNGP12&rnum=25
Furtheremore, all the documentation in BOL is very very murky on this
particular subject area:
Partial Database Restore Operations
"Partial restore operations work with database filegroups. The primary
filegroup is always restored, along with the files that you specify and
their corresponding filegroups. The result is a subset of the database.
Filegroups that are not restored are marked as offline and are not
accessible.
Partial restore operations are accomplished with the PARTIAL clause of the
RESTORE statement. You can also use the PARTIAL option when restoring a full
database backup. Partial database restore of file backups is not supported."
What it says is "Partial database restore of file backups is not supported"
I think it should ALSO say "Partial database restore of filegroup and file
backups is not supported"
Many Thanks
Paul McMillan
Paul,
I agree with your points, and this is how I understand that things work as well. Also, I just ran a test,
which also confirms it (see script at below, adapted from example in BOL). Did you perform the Books Online
feedback on this (the envelope top left in the right pane)?
USE master
GO
DROP DATABASE mywind_part
GO
DROP DATABASE mywind
GO
CREATE DATABASE mywind
GO
ALTER DATABASE mywind ADD FILEGROUP new_customers
ALTER DATABASE mywind ADD FILEGROUP sales
GO
ALTER DATABASE mywind ADD FILE
(NAME='mywind_data_1',
FILENAME='c:\mw.dat1')
TO FILEGROUP new_customers
ALTER DATABASE mywind
ADD FILE
(NAME='mywind_data_2',
FILENAME='c:\mw.dat2')
TO FILEGROUP sales
GO
CREATE TABLE mywind..t2 (id int) ON sales
-- A full database backup is performed.
-- Then the t1 table is created on new_customers.
-- The transaction log is backed up:
BACKUP DATABASE mywind
filegroup = 'sales', filegroup='primary', filegroup = 'new_customers'
TO DISK ='c:\mywind.dmp'
WITH INIT
GO
USE mywind
GO
CREATE TABLE t1 (id int) ON new_customers
GO
BACKUP LOG mywind TO DISK='c:\mywind.dmp'
WITH NOINIT
GO
-- At some point, it becomes necessary to restore the t2 table
-- on the sales filegroup. RESTORE FILELISTONLY lists the database
-- files and the filegroups in which they reside.
-- RESTORE HEADERONLY lists the contents of the backup medium:
-- RESTORE FILELISTONLY FROM DISK='c:\mywind.dmp'
-- GO
-- RESTORE HEADERONLY FROM DISK='c:\mywind.dmp'
-- GO
-- The RESTORE DATABASE statement restores the database under a different name
-- and the sales filegroup using the WITH PARTIAL and NORECOVERY options.
-- In addition, the primary file and filegroup (mywind), the log (mywind_log),
-- and all files in the restored filegroup (in this example, mywind_data_2 is the
-- only file in sales) are moved to a new location. The log is then recovered:
RESTORE DATABASE mywind_part
FILEGROUP = 'sales'
FROM DISK='c:\mywind.dmp'
WITH FILE=1,RECOVERY,PARTIAL,
-- WITH FILE=1,NORECOVERY,PARTIAL,
MOVE 'mywind' TO 'c:\mw2.pri',
MOVE 'mywind_log' TO 'c:\mw2.log',
MOVE 'mywind_data_2' TO 'c:\mw2.dat2'
GO
-- RESTORE LOG mywind_part
-- FROM DISK = 'c:\mywind.dmp'
-- WITH FILE = 2,RECOVERY
-- GO
--Notice that t2 is accessible after the partial restore operation.
SELECT COUNT(*) FROM mywind_part..t2
--Notice that t1 is not accessible after the partial log restore operation.
SELECT COUNT(*) FROM mywind_part..t1
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul McMillan" <paul.mcmillan@.email4u.com> wrote in message news:%23xZPXcXTEHA.2716@.tk2msftngp13.phx.gbl...
> Please..
> Can someone confirm that with a PARTIAL FILEGROUP SQL Server RESTORE you
> have to have a Full Database Backup (containing all the Filegroups including
> the PRIMARY) before you can do a PARTIAL filegroup RESTORE.
> Even if you have a performed a filegroup Backup which contains the PRIMARY
> filegroup and all the other filegroups then it will not work and you get the
> error message:
> Server: Msg 3135, Level 16, State 2, Line 1
> "was created by BACKUP DATABASE...FILE=<name> and cannot be used for this
> restore operation."
>
> All the examples I have seen on the net use a FULL Database backup as a
> starting point and I have seen the following posting which kind of confirms
> my suspisions from someone with a microsoft.com email address
>
http://groups.google.com/groups?q=pa...TNGP12&rnum=25
> Furtheremore, all the documentation in BOL is very very murky on this
> particular subject area:
> Partial Database Restore Operations
> "Partial restore operations work with database filegroups. The primary
> filegroup is always restored, along with the files that you specify and
> their corresponding filegroups. The result is a subset of the database.
> Filegroups that are not restored are marked as offline and are not
> accessible.
> Partial restore operations are accomplished with the PARTIAL clause of the
> RESTORE statement. You can also use the PARTIAL option when restoring a full
> database backup. Partial database restore of file backups is not supported."
> What it says is "Partial database restore of file backups is not supported"
> I think it should ALSO say "Partial database restore of filegroup and file
> backups is not supported"
> Many Thanks
> Paul McMillan
>
|||Tibor
Thanks - As you get the same, it proves I am not going mad and I tried your
code with the same results. So I believe the documentation is a bit
misleading in this area...
Re: Did you perform the Books Online feedback on this (the envelope top left
in the right pane)?
No - But I have now! - outlining what I believe is unclear documentation.
Thanks again
Paul

PARTIAL SQL Server RESTORE's

Please..
Can someone confirm that with a PARTIAL FILEGROUP SQL Server RESTORE you
have to have a Full Database Backup (containing all the Filegroups including
the PRIMARY) before you can do a PARTIAL filegroup RESTORE.
Even if you have a performed a filegroup Backup which contains the PRIMARY
filegroup and all the other filegroups then it will not work and you get the
error message:
Server: Msg 3135, Level 16, State 2, Line 1
"was created by BACKUP DATABASE...FILE=<name> and cannot be used for this
restore operation."
All the examples I have seen on the net use a FULL Database backup as a
starting point and I have seen the following posting which kind of confirms
my suspisions from someone with a microsoft.com email address
http://groups.google.com/groups?q=partial+sql+server+restore+full+backup&start=20&hl=en&lr=&ie=UTF-8&selm=eJxAxlwzCHA.1576%40TK2MSFTNGP12&rnum=25
Furtheremore, all the documentation in BOL is very very murky on this
particular subject area:
Partial Database Restore Operations
"Partial restore operations work with database filegroups. The primary
filegroup is always restored, along with the files that you specify and
their corresponding filegroups. The result is a subset of the database.
Filegroups that are not restored are marked as offline and are not
accessible.
Partial restore operations are accomplished with the PARTIAL clause of the
RESTORE statement. You can also use the PARTIAL option when restoring a full
database backup. Partial database restore of file backups is not supported."
What it says is "Partial database restore of file backups is not supported"
I think it should ALSO say "Partial database restore of filegroup and file
backups is not supported"
Many Thanks
Paul McMillanPaul,
I agree with your points, and this is how I understand that things work as well. Also, I just ran a test,
which also confirms it (see script at below, adapted from example in BOL). Did you perform the Books Online
feedback on this (the envelope top left in the right pane)?
USE master
GO
DROP DATABASE mywind_part
GO
DROP DATABASE mywind
GO
CREATE DATABASE mywind
GO
ALTER DATABASE mywind ADD FILEGROUP new_customers
ALTER DATABASE mywind ADD FILEGROUP sales
GO
ALTER DATABASE mywind ADD FILE
(NAME='mywind_data_1',
FILENAME='c:\mw.dat1')
TO FILEGROUP new_customers
ALTER DATABASE mywind
ADD FILE
(NAME='mywind_data_2',
FILENAME='c:\mw.dat2')
TO FILEGROUP sales
GO
CREATE TABLE mywind..t2 (id int) ON sales
-- A full database backup is performed.
-- Then the t1 table is created on new_customers.
-- The transaction log is backed up:
BACKUP DATABASE mywind
filegroup = 'sales', filegroup='primary', filegroup = 'new_customers'
TO DISK ='c:\mywind.dmp'
WITH INIT
GO
USE mywind
GO
CREATE TABLE t1 (id int) ON new_customers
GO
BACKUP LOG mywind TO DISK='c:\mywind.dmp'
WITH NOINIT
GO
-- At some point, it becomes necessary to restore the t2 table
-- on the sales filegroup. RESTORE FILELISTONLY lists the database
-- files and the filegroups in which they reside.
-- RESTORE HEADERONLY lists the contents of the backup medium:
-- RESTORE FILELISTONLY FROM DISK='c:\mywind.dmp'
-- GO
-- RESTORE HEADERONLY FROM DISK='c:\mywind.dmp'
-- GO
-- The RESTORE DATABASE statement restores the database under a different name
-- and the sales filegroup using the WITH PARTIAL and NORECOVERY options.
-- In addition, the primary file and filegroup (mywind), the log (mywind_log),
-- and all files in the restored filegroup (in this example, mywind_data_2 is the
-- only file in sales) are moved to a new location. The log is then recovered:
RESTORE DATABASE mywind_part
FILEGROUP = 'sales'
FROM DISK='c:\mywind.dmp'
WITH FILE=1,RECOVERY,PARTIAL,
-- WITH FILE=1,NORECOVERY,PARTIAL,
MOVE 'mywind' TO 'c:\mw2.pri',
MOVE 'mywind_log' TO 'c:\mw2.log',
MOVE 'mywind_data_2' TO 'c:\mw2.dat2'
GO
-- RESTORE LOG mywind_part
-- FROM DISK = 'c:\mywind.dmp'
-- WITH FILE = 2,RECOVERY
-- GO
--Notice that t2 is accessible after the partial restore operation.
SELECT COUNT(*) FROM mywind_part..t2
--Notice that t1 is not accessible after the partial log restore operation.
SELECT COUNT(*) FROM mywind_part..t1
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul McMillan" <paul.mcmillan@.email4u.com> wrote in message news:%23xZPXcXTEHA.2716@.tk2msftngp13.phx.gbl...
> Please..
> Can someone confirm that with a PARTIAL FILEGROUP SQL Server RESTORE you
> have to have a Full Database Backup (containing all the Filegroups including
> the PRIMARY) before you can do a PARTIAL filegroup RESTORE.
> Even if you have a performed a filegroup Backup which contains the PRIMARY
> filegroup and all the other filegroups then it will not work and you get the
> error message:
> Server: Msg 3135, Level 16, State 2, Line 1
> "was created by BACKUP DATABASE...FILE=<name> and cannot be used for this
> restore operation."
>
> All the examples I have seen on the net use a FULL Database backup as a
> starting point and I have seen the following posting which kind of confirms
> my suspisions from someone with a microsoft.com email address
>
http://groups.google.com/groups?q=partial+sql+server+restore+full+backup&start=20&hl=en&lr=&ie=UTF-8&selm=eJxAxlwzCHA.1576%40TK2MSFTNGP12&rnum=25
> Furtheremore, all the documentation in BOL is very very murky on this
> particular subject area:
> Partial Database Restore Operations
> "Partial restore operations work with database filegroups. The primary
> filegroup is always restored, along with the files that you specify and
> their corresponding filegroups. The result is a subset of the database.
> Filegroups that are not restored are marked as offline and are not
> accessible.
> Partial restore operations are accomplished with the PARTIAL clause of the
> RESTORE statement. You can also use the PARTIAL option when restoring a full
> database backup. Partial database restore of file backups is not supported."
> What it says is "Partial database restore of file backups is not supported"
> I think it should ALSO say "Partial database restore of filegroup and file
> backups is not supported"
> Many Thanks
> Paul McMillan
>|||Tibor
Thanks - As you get the same, it proves I am not going mad and I tried your
code with the same results. So I believe the documentation is a bit
misleading in this area...
Re: Did you perform the Books Online feedback on this (the envelope top left
in the right pane)?
No - But I have now! - outlining what I believe is unclear documentation.
Thanks again
Paul

PARTIAL SQL Server RESTORE's

Please..
Can someone confirm that with a PARTIAL FILEGROUP SQL Server RESTORE you
have to have a Full Database Backup (containing all the Filegroups including
the PRIMARY) before you can do a PARTIAL filegroup RESTORE.
Even if you have a performed a filegroup Backup which contains the PRIMARY
filegroup and all the other filegroups then it will not work and you get the
error message:
Server: Msg 3135, Level 16, State 2, Line 1
"was created by BACKUP DATABASE...FILE=<name> and cannot be used for this
restore operation."
All the examples I have seen on the net use a FULL Database backup as a
starting point and I have seen the following posting which kind of confirms
my suspisions from someone with a microsoft.com email address
&start=20&
hl=en&lr=&ie=UTF-8&selm=eJxAxlwzCHA.1576%40TK2MSFTNGP12&rnum=25" target="_blank">http://groups.google.com/groups? q=...FTNGP12&rnum=25
Furtheremore, all the documentation in BOL is very very murky on this
particular subject area:
Partial Database Restore Operations
"Partial restore operations work with database filegroups. The primary
filegroup is always restored, along with the files that you specify and
their corresponding filegroups. The result is a subset of the database.
Filegroups that are not restored are marked as offline and are not
accessible.
Partial restore operations are accomplished with the PARTIAL clause of the
RESTORE statement. You can also use the PARTIAL option when restoring a full
database backup. Partial database restore of file backups is not supported."
What it says is "Partial database restore of file backups is not supported"
I think it should ALSO say "Partial database restore of filegroup and file
backups is not supported"
Many Thanks
Paul McMillanPaul,
I agree with your points, and this is how I understand that things work as w
ell. Also, I just ran a test,
which also confirms it (see script at below, adapted from example in BOL). D
id you perform the Books Online
feedback on this (the envelope top left in the right pane)?
USE master
GO
DROP DATABASE mywind_part
GO
DROP DATABASE mywind
GO
CREATE DATABASE mywind
GO
ALTER DATABASE mywind ADD FILEGROUP new_customers
ALTER DATABASE mywind ADD FILEGROUP sales
GO
ALTER DATABASE mywind ADD FILE
(NAME='mywind_data_1',
FILENAME='c:\mw.dat1')
TO FILEGROUP new_customers
ALTER DATABASE mywind
ADD FILE
(NAME='mywind_data_2',
FILENAME='c:\mw.dat2')
TO FILEGROUP sales
GO
CREATE TABLE mywind..t2 (id int) ON sales
-- A full database backup is performed.
-- Then the t1 table is created on new_customers.
-- The transaction log is backed up:
BACKUP DATABASE mywind
filegroup = 'sales', filegroup='primary', filegroup = 'new_customers'
TO DISK ='c:\mywind.dmp'
WITH INIT
GO
USE mywind
GO
CREATE TABLE t1 (id int) ON new_customers
GO
BACKUP LOG mywind TO DISK='c:\mywind.dmp'
WITH NOINIT
GO
-- At some point, it becomes necessary to restore the t2 table
-- on the sales filegroup. RESTORE FILELISTONLY lists the database
-- files and the filegroups in which they reside.
-- RESTORE HEADERONLY lists the contents of the backup medium:
-- RESTORE FILELISTONLY FROM DISK='c:\mywind.dmp'
-- GO
-- RESTORE HEADERONLY FROM DISK='c:\mywind.dmp'
-- GO
-- The RESTORE DATABASE statement restores the database under a different na
me
-- and the sales filegroup using the WITH PARTIAL and NORECOVERY options.
-- In addition, the primary file and filegroup (mywind), the log (mywind_log
),
-- and all files in the restored filegroup (in this example, mywind_data_2 i
s the
-- only file in sales) are moved to a new location. The log is then recover
ed:
RESTORE DATABASE mywind_part
FILEGROUP = 'sales'
FROM DISK='c:\mywind.dmp'
WITH FILE=1,RECOVERY,PARTIAL,
-- WITH FILE=1,NORECOVERY,PARTIAL,
MOVE 'mywind' TO 'c:\mw2.pri',
MOVE 'mywind_log' TO 'c:\mw2.log',
MOVE 'mywind_data_2' TO 'c:\mw2.dat2'
GO
-- RESTORE LOG mywind_part
-- FROM DISK = 'c:\mywind.dmp'
-- WITH FILE = 2,RECOVERY
-- GO
--Notice that t2 is accessible after the partial restore operation.
SELECT COUNT(*) FROM mywind_part..t2
--Notice that t1 is not accessible after the partial log restore operation.
SELECT COUNT(*) FROM mywind_part..t1
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul McMillan" <paul.mcmillan@.email4u.com> wrote in message news:%23xZPXcXTEHA.2716@.tk2msft
ngp13.phx.gbl...
> Please..
> Can someone confirm that with a PARTIAL FILEGROUP SQL Server RESTORE you
> have to have a Full Database Backup (containing all the Filegroups includi
ng
> the PRIMARY) before you can do a PARTIAL filegroup RESTORE.
> Even if you have a performed a filegroup Backup which contains the PRIMARY
> filegroup and all the other filegroups then it will not work and you get t
he
> error message:
> Server: Msg 3135, Level 16, State 2, Line 1
> "was created by BACKUP DATABASE...FILE=<name> and cannot be used for this
> restore operation."
>
> All the examples I have seen on the net use a FULL Database backup as a
> starting point and I have seen the following posting which kind of confirm
s
> my suspisions from someone with a microsoft.com email address
>
&start=20&hl=en&lr=&ie=UTF
-8&selm=eJxAxlwzCHA.1576%40TK2MSFTNGP12&rnum=25" target="_blank">http://groups.google.com/groups? q=...FTNGP12&rnum=25
> Furtheremore, all the documentation in BOL is very very murky on this
> particular subject area:
> Partial Database Restore Operations
> "Partial restore operations work with database filegroups. The primary
> filegroup is always restored, along with the files that you specify and
> their corresponding filegroups. The result is a subset of the database.
> Filegroups that are not restored are marked as offline and are not
> accessible.
> Partial restore operations are accomplished with the PARTIAL clause of the
> RESTORE statement. You can also use the PARTIAL option when restoring a fu
ll
> database backup. Partial database restore of file backups is not supported
."
> What it says is "Partial database restore of file backups is not supported
"
> I think it should ALSO say "Partial database restore of filegroup and file
> backups is not supported"
> Many Thanks
> Paul McMillan
>|||Tibor
Thanks - As you get the same, it proves I am not going mad and I tried your
code with the same results. So I believe the documentation is a bit
misleading in this area...
Re: Did you perform the Books Online feedback on this (the envelope top left
in the right pane)?
No - But I have now! - outlining what I believe is unclear documentation.
Thanks again
Paul

Partial searches over a lot of fields

Hi All,
I have the following scenario. I have a table called Invoice. This
has around 30 columns of which i have to do a retrieval based on
filter conditions on 10 columns. These filters need to be partial
searches i.e. for e.g the Customer name could be 'Arun', 'Parthiv',
'Aaron', now i should be able to search the customer as 'ar' and it
should return 'Arun' and 'Parthiv'. My concern is there are 10 columns
on which this like '%x%' search has to be done and there will
practically be hudreds of thousands of rows. can anybody suggest me to
improve the performance of such a query. Currently what i am thinkin
of is
select Id, Memo, .. FROM Invoice where CustomerName like '%' + @.Name +
'%' and etc.
P.S. am using ASP.Net as the front end.ArunPrakash (arunprakashb@.yahoo.com) writes:
> I have the following scenario. I have a table called Invoice. This
> has around 30 columns of which i have to do a retrieval based on
> filter conditions on 10 columns. These filters need to be partial
> searches i.e. for e.g the Customer name could be 'Arun', 'Parthiv',
> 'Aaron', now i should be able to search the customer as 'ar' and it
> should return 'Arun' and 'Parthiv'. My concern is there are 10 columns
> on which this like '%x%' search has to be done and there will
> practically be hudreds of thousands of rows. can anybody suggest me to
> improve the performance of such a query. Currently what i am thinkin
> of is
> select Id, Memo, .. FROM Invoice where CustomerName like '%' + @.Name +
> '%' and etc.

You can use SELECT TOP or SET ROWCOUNT to restrict the number of rows
returned. A good value is probably 2000. If you get 2000 rows, you tell
the user to refine his conditions.

Also keep in mind, that there could be a great difference in performance
when searching for names that start with 'Ar', or have 'ar' anywhere in
the name. If the search column is indexed, that index can be used
for the case "starts with", but not "contains".

There are also a couple of considerations of how to compose the query
to make the search effective. You may be interested in the article
http://www.sommarskog.se/dyn-search.html on my web site.

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

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

partial search performance

To search for partial string we are using WHERE column_name LIKE '%str%'.
But its taking long time to return the results. Is there any way to improve
the performance.
What is the datatype of the column you are searching against. An index would
help majorly but it has to been an allowable type.
/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
"Ramu" <Ramu@.discussions.microsoft.com> wrote in message
news:2C4BB57B-48AA-41A1-9594-48DCA08AB50A@.microsoft.com...
> To search for partial string we are using WHERE column_name LIKE
> '%str%'.
> But its taking long time to return the results. Is there any way to
> improve
> the performance.
|||datatype of the column is varchar(max). This column contains the description
of the product. Users want to search for a string rather than a word. If
search by a word is the requirement, I would have gone with FTE.
"Warren Brunk" wrote:

> What is the datatype of the column you are searching against. An index would
> help majorly but it has to been an allowable type.
> --
> /*
> Warren Brunk - MCITP,MCTS,MCDBA
> www.techintsolutions.com
> */
> "Ramu" <Ramu@.discussions.microsoft.com> wrote in message
> news:2C4BB57B-48AA-41A1-9594-48DCA08AB50A@.microsoft.com...
>
>
|||using a wildcard at the start of a LIKE expression does not use an INDEX SEEK
but rather an INDEX SCAN so the query will be slow even if you created
indexes on this column
http://myitforum.com/cs2/blogs/jnelson/archive/2007/11/16/108354.aspx
Misbah Arefin
"Ramu" wrote:
[vbcol=seagreen]
> datatype of the column is varchar(max). This column contains the description
> of the product. Users want to search for a string rather than a word. If
> search by a word is the requirement, I would have gone with FTE.
>
> "Warren Brunk" wrote:

Partial row at end of file - best way to handle?

Hi,

I have a file where there is a partial row at the end. It doesn't cause an error, but I get a "partial row" warning during execution.

What do most people do with these partial rows? Do they just ignore them as long as they don't cause errors? Or is it better to handle the partial row with a conditional split, for example?

Just wondering what other people's thoughts on this are. I tend to be of the "get rid of it" camp, but maybe that's overkill? Just looking for opinions, best practices.

Thanks

Hi Sadie,

I'm not sure I know what you mean. Can you paste the last few lines of the file up here? Also, tell us how you have the connection manager configured (i.e. delimited, fixed-length etc...)

-Jamie

|||

Data:

col1, col2, col3, col4, etc

col1 --> partial row

This gives a warning, but doesn't cause an error.

Comma delimited conn mgr.

|||Is it always the last row, or can it be in the middle?|||

It's only the last row.

If you have partial rows in the middle of the file, this causes an error (unless you're ignoring errors). But SSIS ignores the partial row at the very end without any special error handling.

|||

sadie519590 wrote:

It's only the last row.

If you have partial rows in the middle of the file, this causes an error (unless you're ignoring errors). But SSIS ignores the partial row at the very end without any special error handling.

Then you can leave it. No harm, no foul. Why is the last row incomplete though? Can it be fixed at the source?|||

Can't be fixed at the source, these are automatically generated extracts that get automatically ftp'd and loaded.

Which goes back to my original question:

What is the best practice for handling these partial rows at the end of file?

If they're ignored, SSIS gives a warning, but no errors. So the package still runs.

But I can also add a conditional split to remove it, which is extra work, but ensures all junk rows are removed.

I was just wondering what other people do with these partial end of file rows?

|||Is the row a footer or something? What makes it "partial"?

I don't accept "bad" files for input into SSIS. Unless the mandate is given from above, I consider files like this to be bad and those who build it need to fix it.

With that said, it's up to you. I don't think there is a best practice. Use whatever you're comfortable with. If you log warnings, and get tired of sifting through them, then perhaps you want to graciously handle the bad rows.|||Yes, these are footers, not bad rows, per se.|||Not sure there is a best practice on this, as Phil said. If it is not causing a problem, I'd leave it.

Partial Restore from Log

We have a database for our Beta site whose backups were not working successfully. A developer inadvertently dropped and recreated a
table with important data in it today, and so we are stuck without a backup.
We would like to restore the database to the state it was in this morning, and hoped to be able to do it from the transaction log.
The problem is, I need to apply the log only up until the point that the DROP TABLE was issued as I don't want to lose the data
again.
All the documentation seems to indicate that what we want to do is possible, but I can't find how to do specifically what we want to
do. Can someone point me in the right direction?
Look at the "STOPAT" parameter of the "RESTORE LOG" command in Books Online.
The STOPAT parameter allows you to identify the point in time where you
would like to restore your database to. The time you would want to use is
the point just before your table got deleted.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Erik Moore" <erikmoore@.austin.rr.nospam.com> wrote in message
news:O6LTBdRPEHA.2132@.TK2MSFTNGP11.phx.gbl...
> We have a database for our Beta site whose backups were not working
successfully. A developer inadvertently dropped and recreated a
> table with important data in it today, and so we are stuck without a
backup.
> We would like to restore the database to the state it was in this morning,
and hoped to be able to do it from the transaction log.
> The problem is, I need to apply the log only up until the point that the
DROP TABLE was issued as I don't want to lose the data
> again.
> All the documentation seems to indicate that what we want to do is
possible, but I can't find how to do specifically what we want to
> do. Can someone point me in the right direction?
>
>
|||> Look at the "STOPAT" parameter of the "RESTORE LOG" command in Books Online.
Thanks- I was just trying to get STOPAT to work, but I seem to be missing a basic concept or two about the RESTORE LOG command.
I created a copy of my real database by scripting out all the tables and running the script. I figured I would restore the copy of
the database so I could leave all the other data in the production database in tact, and after the restore, I could just copy over
the data from the lost table. I tried:
RESTORE LOG CP2
FROM DISK = 'E:\DATA\MAC\MSSQL\LOG\CPO_LOG.LDF'
WITH NORECOVERY , STOPAT = '2004-02-12 8:30:00'
Where CP2 is the fresh, empty copy of the database, and CPO_LOG is the log from the real database. (I have taken CPO offline
temporarily). But when I run the code above, I get the following message:
"Server: Msg 4306, Level 16, State 1, Line 1
The preceding restore operation did not specify WITH NORECOVERY or WITH STANDBY. Restart the restore sequence, specifying WITH
NORECOVERY or WITH STANDBY for all but the final step.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally."
All the examples I can find seem to assume that I have a database backup that I am restoring from first, and _then_ I will apply the
log.
|||The basic concept of getting this to work, is making sure you have a
complete sequence of backups. Meaning you need at a mimimum a full database
backup and a transaction log backup where the table was deleted (provided no
other backups occured between the full and the transaction log backup) If
there were a number of transaction logs since the last full backup you need
all these backups. Also if you performed differential backups then based on
which files you are using for the restore you might need one of those. Do
you have all these backups, a complete sequence of backups from the full to
that last transaction log backup? If you don't have a complete set of
backups you will not be able to perform what I suggested.
Also the transaction log backup is not the actual transaction log (file
ending in .LDF), but separate file that is created when you issue a BACKUP
LOG command. Based on your restore command looks like you are trying to use
the actual transcation log for your "FROM DISK=" option.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Erik Moore" <erikmoore@.austin.rr.nospam.com> wrote in message
news:%23MqGiBSPEHA.308@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
Online.
> Thanks- I was just trying to get STOPAT to work, but I seem to be missing
a basic concept or two about the RESTORE LOG command.
> I created a copy of my real database by scripting out all the tables and
running the script. I figured I would restore the copy of
> the database so I could leave all the other data in the production
database in tact, and after the restore, I could just copy over
> the data from the lost table. I tried:
> RESTORE LOG CP2
> FROM DISK = 'E:\DATA\MAC\MSSQL\LOG\CPO_LOG.LDF'
> WITH NORECOVERY , STOPAT = '2004-02-12 8:30:00'
> Where CP2 is the fresh, empty copy of the database, and CPO_LOG is the log
from the real database. (I have taken CPO offline
> temporarily). But when I run the code above, I get the following message:
> "Server: Msg 4306, Level 16, State 1, Line 1
> The preceding restore operation did not specify WITH NORECOVERY or WITH
STANDBY. Restart the restore sequence, specifying WITH
> NORECOVERY or WITH STANDBY for all but the final step.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE LOG is terminating abnormally."
> All the examples I can find seem to assume that I have a database backup
that I am restoring from first, and _then_ I will apply the
> log.
>
>
|||> The basic concept of getting this to work, is making sure you have a
> complete sequence of backups.
Well the problem is, there are _no_ backups of the database or of the log. But as I understand it, since the log was not backup up
at all, it should contain all the transactions since the birth of the database. (The log file is some 5GB in size).
I was hoping to accomplish this by starting with an empty database (the way it was when the log was started), and just applying the
complete log.
I'm getting a sinking feeling that this may not be possible?
|||If you don't have a full backup of you database then you are correct and you
can't restore from the transaction log. One glimmer of hope might be to try
and use a third party tools to get information out of the transaction log.
I've never used these. There is a tool called "Log Explorer" from Lumigent
which might help you out.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Erik Moore" <erikmoore@.austin.rr.nospam.com> wrote in message
news:OK668PSPEHA.1512@.TK2MSFTNGP10.phx.gbl...
> Well the problem is, there are _no_ backups of the database or of the log.
But as I understand it, since the log was not backup up
> at all, it should contain all the transactions since the birth of the
database. (The log file is some 5GB in size).
> I was hoping to accomplish this by starting with an empty database (the
way it was when the log was started), and just applying the
> complete log.
> I'm getting a sinking feeling that this may not be possible?
>
>
>
|||Thanks, I really appreciate your help. We're looking at alternative means of restoring the data from a sencondary source.
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message news:uSmtaXSPEHA.2636@.TK2MSFTNGP10.phx.gbl...
> If you don't have a full backup of you database then you are correct and you
> can't restore from the transaction log. One glimmer of hope might be to try
> and use a third party tools to get information out of the transaction log.
> I've never used these. There is a tool called "Log Explorer" from Lumigent
> which might help you out.
> --
> ----
> ----
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Erik Moore" <erikmoore@.austin.rr.nospam.com> wrote in message
> news:OK668PSPEHA.1512@.TK2MSFTNGP10.phx.gbl...
> But as I understand it, since the log was not backup up
> database. (The log file is some 5GB in size).
> way it was when the log was started), and just applying the
>
|||<<Well the problem is, there are _no_ backups of the database or of the log. But as I understand it, since the
log was not backup up at all, it should contain all the transactions since the birth of the database.>>
Above is a common misconception. Until you do your first database backup, the database is in "auto log
truncate mode" which means that SQL Server will truncate the log when 70% full or at checkpoint. So, you
cannot be certain to find your log records even when using some log reader tool.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Erik Moore" <erikmoore@.austin.rr.nospam.com> wrote in message news:OK668PSPEHA.1512@.TK2MSFTNGP10.phx.gbl...
> Well the problem is, there are _no_ backups of the database or of the log. But as I understand it, since the
log was not backup up
> at all, it should contain all the transactions since the birth of the database. (The log file is some 5GB in
size).
> I was hoping to accomplish this by starting with an empty database (the way it was when the log was
started), and just applying the
> complete log.
> I'm getting a sinking feeling that this may not be possible?
>
>
>

Partial Restore from Log

We have a database for our Beta site whose backups were not working successf
ully. A developer inadvertently dropped and recreated a
table with important data in it today, and so we are stuck without a backup.
We would like to restore the database to the state it was in this morning, a
nd hoped to be able to do it from the transaction log.
The problem is, I need to apply the log only up until the point that the DRO
P TABLE was issued as I don't want to lose the data
again.
All the documentation seems to indicate that what we want to do is possible,
but I can't find how to do specifically what we want to
do. Can someone point me in the right direction?Look at the "STOPAT" parameter of the "RESTORE LOG" command in Books Online.
The STOPAT parameter allows you to identify the point in time where you
would like to restore your database to. The time you would want to use is
the point just before your table got deleted.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Erik Moore" <erikmoore@.austin.rr.nospam.com> wrote in message
news:O6LTBdRPEHA.2132@.TK2MSFTNGP11.phx.gbl...
> We have a database for our Beta site whose backups were not working
successfully. A developer inadvertently dropped and recreated a
> table with important data in it today, and so we are stuck without a
backup.
> We would like to restore the database to the state it was in this morning,
and hoped to be able to do it from the transaction log.
> The problem is, I need to apply the log only up until the point that the
DROP TABLE was issued as I don't want to lose the data
> again.
> All the documentation seems to indicate that what we want to do is
possible, but I can't find how to do specifically what we want to
> do. Can someone point me in the right direction?
>
>|||> Look at the "STOPAT" parameter of the "RESTORE LOG" command in Books Onlin
e.
Thanks- I was just trying to get STOPAT to work, but I seem to be missing a
basic concept or two about the RESTORE LOG command.
I created a copy of my real database by scripting out all the tables and run
ning the script. I figured I would restore the copy of
the database so I could leave all the other data in the production database
in tact, and after the restore, I could just copy over
the data from the lost table. I tried:
RESTORE LOG CP2
FROM DISK = 'E:\DATA\MAC\MSSQL\LOG\CPO_LOG.LDF'
WITH NORECOVERY , STOPAT = '2004-02-12 8:30:00'
Where CP2 is the fresh, empty copy of the database, and CPO_LOG is the log f
rom the real database. (I have taken CPO offline
temporarily). But when I run the code above, I get the following message:
"Server: Msg 4306, Level 16, State 1, Line 1
The preceding restore operation did not specify WITH NORECOVERY or WITH STAN
DBY. Restart the restore sequence, specifying WITH
NORECOVERY or WITH STANDBY for all but the final step.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally."
All the examples I can find seem to assume that I have a database backup tha
t I am restoring from first, and _then_ I will apply the
log.|||The basic concept of getting this to work, is making sure you have a
complete sequence of backups. Meaning you need at a mimimum a full database
backup and a transaction log backup where the table was deleted (provided no
other backups occured between the full and the transaction log backup) If
there were a number of transaction logs since the last full backup you need
all these backups. Also if you performed differential backups then based on
which files you are using for the restore you might need one of those. Do
you have all these backups, a complete sequence of backups from the full to
that last transaction log backup? If you don't have a complete set of
backups you will not be able to perform what I suggested.
Also the transaction log backup is not the actual transaction log (file
ending in .LDF), but separate file that is created when you issue a BACKUP
LOG command. Based on your restore command looks like you are trying to use
the actual transcation log for your "FROM DISK=" option.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Erik Moore" <erikmoore@.austin.rr.nospam.com> wrote in message
news:%23MqGiBSPEHA.308@.TK2MSFTNGP11.phx.gbl...
Online.[vbcol=seagreen]
> Thanks- I was just trying to get STOPAT to work, but I seem to be missing
a basic concept or two about the RESTORE LOG command.
> I created a copy of my real database by scripting out all the tables and
running the script. I figured I would restore the copy of
> the database so I could leave all the other data in the production
database in tact, and after the restore, I could just copy over
> the data from the lost table. I tried:
> RESTORE LOG CP2
> FROM DISK = 'E:\DATA\MAC\MSSQL\LOG\CPO_LOG.LDF'
> WITH NORECOVERY , STOPAT = '2004-02-12 8:30:00'
> Where CP2 is the fresh, empty copy of the database, and CPO_LOG is the log
from the real database. (I have taken CPO offline
> temporarily). But when I run the code above, I get the following message:
> "Server: Msg 4306, Level 16, State 1, Line 1
> The preceding restore operation did not specify WITH NORECOVERY or WITH
STANDBY. Restart the restore sequence, specifying WITH
> NORECOVERY or WITH STANDBY for all but the final step.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE LOG is terminating abnormally."
> All the examples I can find seem to assume that I have a database backup
that I am restoring from first, and _then_ I will apply the
> log.
>
>|||> The basic concept of getting this to work, is making sure you have a
> complete sequence of backups.
Well the problem is, there are _no_ backups of the database or of the log. B
ut as I understand it, since the log was not backup up
at all, it should contain all the transactions since the birth of the databa
se. (The log file is some 5GB in size).
I was hoping to accomplish this by starting with an empty database (the way
it was when the log was started), and just applying the
complete log.
I'm getting a sinking feeling that this may not be possible?|||If you don't have a full backup of you database then you are correct and you
can't restore from the transaction log. One glimmer of hope might be to try
and use a third party tools to get information out of the transaction log.
I've never used these. There is a tool called "Log Explorer" from Lumigent
which might help you out.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Erik Moore" <erikmoore@.austin.rr.nospam.com> wrote in message
news:OK668PSPEHA.1512@.TK2MSFTNGP10.phx.gbl...
> Well the problem is, there are _no_ backups of the database or of the log.
But as I understand it, since the log was not backup up
> at all, it should contain all the transactions since the birth of the
database. (The log file is some 5GB in size).
> I was hoping to accomplish this by starting with an empty database (the
way it was when the log was started), and just applying the
> complete log.
> I'm getting a sinking feeling that this may not be possible?
>
>
>|||Thanks, I really appreciate your help. We're looking at alternative means of
restoring the data from a sencondary source.
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message news:uSmtaXSPEHA.2636@.TK2MSFT
NGP10.phx.gbl...
> If you don't have a full backup of you database then you are correct and y
ou
> can't restore from the transaction log. One glimmer of hope might be to t
ry
> and use a third party tools to get information out of the transaction log.
> I've never used these. There is a tool called "Log Explorer" from Lumigen
t
> which might help you out.
> --
> ----
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Erik Moore" <erikmoore@.austin.rr.nospam.com> wrote in message
> news:OK668PSPEHA.1512@.TK2MSFTNGP10.phx.gbl...
> But as I understand it, since the log was not backup up
> database. (The log file is some 5GB in size).
> way it was when the log was started), and just applying the
>|||<<Well the problem is, there are _no_ backups of the database or of the log.
But as I understand it, since the
log was not backup up at all, it should contain all the transactions since t
he birth of the database.>>
Above is a common misconception. Until you do your first database backup, th
e database is in "auto log
truncate mode" which means that SQL Server will truncate the log when 70% fu
ll or at checkpoint. So, you
cannot be certain to find your log records even when using some log reader t
ool.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Erik Moore" <erikmoore@.austin.rr.nospam.com> wrote in message news:OK668PSPEHA.1512@.TK2MSFT
NGP10.phx.gbl...
> Well the problem is, there are _no_ backups of the database or of the log. But as
I understand it, since the
log was not backup up
> at all, it should contain all the transactions since the birth of the database. (T
he log file is some 5GB in
size).
> I was hoping to accomplish this by starting with an empty database (the way it was
when the log was
started), and just applying the
> complete log.
> I'm getting a sinking feeling that this may not be possible?
>
>
>

Partial Restore from Log

We have a database for our Beta site whose backups were not working successfully. A developer inadvertently dropped and recreated a
table with important data in it today, and so we are stuck without a backup.
We would like to restore the database to the state it was in this morning, and hoped to be able to do it from the transaction log.
The problem is, I need to apply the log only up until the point that the DROP TABLE was issued as I don't want to lose the data
again.
All the documentation seems to indicate that what we want to do is possible, but I can't find how to do specifically what we want to
do. Can someone point me in the right direction?Look at the "STOPAT" parameter of the "RESTORE LOG" command in Books Online.
The STOPAT parameter allows you to identify the point in time where you
would like to restore your database to. The time you would want to use is
the point just before your table got deleted.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Erik Moore" <erikmoore@.austin.rr.nospam.com> wrote in message
news:O6LTBdRPEHA.2132@.TK2MSFTNGP11.phx.gbl...
> We have a database for our Beta site whose backups were not working
successfully. A developer inadvertently dropped and recreated a
> table with important data in it today, and so we are stuck without a
backup.
> We would like to restore the database to the state it was in this morning,
and hoped to be able to do it from the transaction log.
> The problem is, I need to apply the log only up until the point that the
DROP TABLE was issued as I don't want to lose the data
> again.
> All the documentation seems to indicate that what we want to do is
possible, but I can't find how to do specifically what we want to
> do. Can someone point me in the right direction?
>
>|||> Look at the "STOPAT" parameter of the "RESTORE LOG" command in Books Online.
Thanks- I was just trying to get STOPAT to work, but I seem to be missing a basic concept or two about the RESTORE LOG command.
I created a copy of my real database by scripting out all the tables and running the script. I figured I would restore the copy of
the database so I could leave all the other data in the production database in tact, and after the restore, I could just copy over
the data from the lost table. I tried:
RESTORE LOG CP2
FROM DISK = 'E:\DATA\MAC\MSSQL\LOG\CPO_LOG.LDF'
WITH NORECOVERY , STOPAT = '2004-02-12 8:30:00'
Where CP2 is the fresh, empty copy of the database, and CPO_LOG is the log from the real database. (I have taken CPO offline
temporarily). But when I run the code above, I get the following message:
"Server: Msg 4306, Level 16, State 1, Line 1
The preceding restore operation did not specify WITH NORECOVERY or WITH STANDBY. Restart the restore sequence, specifying WITH
NORECOVERY or WITH STANDBY for all but the final step.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally."
All the examples I can find seem to assume that I have a database backup that I am restoring from first, and _then_ I will apply the
log.|||The basic concept of getting this to work, is making sure you have a
complete sequence of backups. Meaning you need at a mimimum a full database
backup and a transaction log backup where the table was deleted (provided no
other backups occured between the full and the transaction log backup) If
there were a number of transaction logs since the last full backup you need
all these backups. Also if you performed differential backups then based on
which files you are using for the restore you might need one of those. Do
you have all these backups, a complete sequence of backups from the full to
that last transaction log backup? If you don't have a complete set of
backups you will not be able to perform what I suggested.
Also the transaction log backup is not the actual transaction log (file
ending in .LDF), but separate file that is created when you issue a BACKUP
LOG command. Based on your restore command looks like you are trying to use
the actual transcation log for your "FROM DISK=" option.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Erik Moore" <erikmoore@.austin.rr.nospam.com> wrote in message
news:%23MqGiBSPEHA.308@.TK2MSFTNGP11.phx.gbl...
> > Look at the "STOPAT" parameter of the "RESTORE LOG" command in Books
Online.
> Thanks- I was just trying to get STOPAT to work, but I seem to be missing
a basic concept or two about the RESTORE LOG command.
> I created a copy of my real database by scripting out all the tables and
running the script. I figured I would restore the copy of
> the database so I could leave all the other data in the production
database in tact, and after the restore, I could just copy over
> the data from the lost table. I tried:
> RESTORE LOG CP2
> FROM DISK = 'E:\DATA\MAC\MSSQL\LOG\CPO_LOG.LDF'
> WITH NORECOVERY , STOPAT = '2004-02-12 8:30:00'
> Where CP2 is the fresh, empty copy of the database, and CPO_LOG is the log
from the real database. (I have taken CPO offline
> temporarily). But when I run the code above, I get the following message:
> "Server: Msg 4306, Level 16, State 1, Line 1
> The preceding restore operation did not specify WITH NORECOVERY or WITH
STANDBY. Restart the restore sequence, specifying WITH
> NORECOVERY or WITH STANDBY for all but the final step.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE LOG is terminating abnormally."
> All the examples I can find seem to assume that I have a database backup
that I am restoring from first, and _then_ I will apply the
> log.
>
>|||> The basic concept of getting this to work, is making sure you have a
> complete sequence of backups.
Well the problem is, there are _no_ backups of the database or of the log. But as I understand it, since the log was not backup up
at all, it should contain all the transactions since the birth of the database. (The log file is some 5GB in size).
I was hoping to accomplish this by starting with an empty database (the way it was when the log was started), and just applying the
complete log.
I'm getting a sinking feeling that this may not be possible?|||If you don't have a full backup of you database then you are correct and you
can't restore from the transaction log. One glimmer of hope might be to try
and use a third party tools to get information out of the transaction log.
I've never used these. There is a tool called "Log Explorer" from Lumigent
which might help you out.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Erik Moore" <erikmoore@.austin.rr.nospam.com> wrote in message
news:OK668PSPEHA.1512@.TK2MSFTNGP10.phx.gbl...
> > The basic concept of getting this to work, is making sure you have a
> > complete sequence of backups.
> Well the problem is, there are _no_ backups of the database or of the log.
But as I understand it, since the log was not backup up
> at all, it should contain all the transactions since the birth of the
database. (The log file is some 5GB in size).
> I was hoping to accomplish this by starting with an empty database (the
way it was when the log was started), and just applying the
> complete log.
> I'm getting a sinking feeling that this may not be possible?
>
>
>|||Thanks, I really appreciate your help. We're looking at alternative means of restoring the data from a sencondary source.
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message news:uSmtaXSPEHA.2636@.TK2MSFTNGP10.phx.gbl...
> If you don't have a full backup of you database then you are correct and you
> can't restore from the transaction log. One glimmer of hope might be to try
> and use a third party tools to get information out of the transaction log.
> I've never used these. There is a tool called "Log Explorer" from Lumigent
> which might help you out.
> --
> ----
> ----
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Erik Moore" <erikmoore@.austin.rr.nospam.com> wrote in message
> news:OK668PSPEHA.1512@.TK2MSFTNGP10.phx.gbl...
> > > The basic concept of getting this to work, is making sure you have a
> > > complete sequence of backups.
> >
> > Well the problem is, there are _no_ backups of the database or of the log.
> But as I understand it, since the log was not backup up
> > at all, it should contain all the transactions since the birth of the
> database. (The log file is some 5GB in size).
> >
> > I was hoping to accomplish this by starting with an empty database (the
> way it was when the log was started), and just applying the
> > complete log.
> >
> > I'm getting a sinking feeling that this may not be possible?
> >
> >
> >
> >
> >
>|||<<Well the problem is, there are _no_ backups of the database or of the log. But as I understand it, since the
log was not backup up at all, it should contain all the transactions since the birth of the database.>>
Above is a common misconception. Until you do your first database backup, the database is in "auto log
truncate mode" which means that SQL Server will truncate the log when 70% full or at checkpoint. So, you
cannot be certain to find your log records even when using some log reader tool.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Erik Moore" <erikmoore@.austin.rr.nospam.com> wrote in message news:OK668PSPEHA.1512@.TK2MSFTNGP10.phx.gbl...
> > The basic concept of getting this to work, is making sure you have a
> > complete sequence of backups.
> Well the problem is, there are _no_ backups of the database or of the log. But as I understand it, since the
log was not backup up
> at all, it should contain all the transactions since the birth of the database. (The log file is some 5GB in
size).
> I was hoping to accomplish this by starting with an empty database (the way it was when the log was
started), and just applying the
> complete log.
> I'm getting a sinking feeling that this may not be possible?
>
>
>

Partial Restore (only of a table)

Do I need the bank in the air for 24:00, is there as I do a partial restore
of a backup, in other words, without stopping the bank in use to just
recuperate a table of your backup?Restore the database to somewhere else and DTS the table across.
Frank Dulk wrote:
> Do I need the bank in the air for 24:00, is there as I do a partial restore
> of a backup, in other words, without stopping the bank in use to just
> recuperate a table of your backup?
>
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

Partial Restoration

We have taken a full database dump in sql server 2000.
Now we want to restore only the data file without restoring the log file .
will it be possible to do this.
Rajeev,
When you restore the database backup (we don't use the term 'dump' anymore)
SQL Server will automatically create a log file - every database must have
one.
Hope this helps,
Ron
Ron Talmage
SQL Server MVP
"Rajeev R" <Rajeev R@.discussions.microsoft.com> wrote in message
news:8535A309-7F5D-4D25-95DA-806CB3DFE323@.microsoft.com...
> We have taken a full database dump in sql server 2000.
> Now we want to restore only the data file without restoring the log file .
> will it be possible to do this.

Partial Restoration

We have taken a full database dump in sql server 2000.
Now we want to restore only the data file without restoring the log file .
will it be possible to do this.Rajeev,
When you restore the database backup (we don't use the term 'dump' anymore)
SQL Server will automatically create a log file - every database must have
one.
Hope this helps,
Ron
--
Ron Talmage
SQL Server MVP
"Rajeev R" <Rajeev R@.discussions.microsoft.com> wrote in message
news:8535A309-7F5D-4D25-95DA-806CB3DFE323@.microsoft.com...
> We have taken a full database dump in sql server 2000.
> Now we want to restore only the data file without restoring the log file .
> will it be possible to do this.

Partial Restoration

We have taken a full database dump in sql server 2000.
Now we want to restore only the data file without restoring the log file .
will it be possible to do this.Rajeev,
When you restore the database backup (we don't use the term 'dump' anymore)
SQL Server will automatically create a log file - every database must have
one.
Hope this helps,
Ron
--
Ron Talmage
SQL Server MVP
"Rajeev R" <Rajeev R@.discussions.microsoft.com> wrote in message
news:8535A309-7F5D-4D25-95DA-806CB3DFE323@.microsoft.com...
> We have taken a full database dump in sql server 2000.
> Now we want to restore only the data file without restoring the log file .
> will it be possible to do this.

Partial Replication

Hi
Is it possible to replicate just some records in a table and can the filter
be implemented (I am trying to avoid a situation that employees will have
the full version database while they are away from the office [they only
need te records that are assigned to them])
Thank you,
Shmuel Shulman
SBS Technologies LTD
See "replication, filtering options" in BOL.
"S Shulman" <smshulman@.hotmail.com> wrote in message
news:uB8E%2378hFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Hi
> Is it possible to replicate just some records in a table and can the
> filter be implemented (I am trying to avoid a situation that employees
> will have the full version database while they are away from the office
> [they only need te records that are assigned to them])
> Thank you,
> Shmuel Shulman
> SBS Technologies LTD
>
|||You can filter replication horizontally or vertically using a filter
you create.
Example:
You have a table that has a column "User"
In the merge agent parameters use option -Hosname [Dave] ( or whatever
value works)
Then in the filter clause use:
User = hostname()
That particular subscriber will only get records where User = "Dave"
This is for merge and I'm not sure about the other types of
replication.
|||Thanks all for your help
Shmuel
<seanbell68@.gmail.com> wrote in message
news:1121305341.878967.300120@.o13g2000cwo.googlegr oups.com...
> You can filter replication horizontally or vertically using a filter
> you create.
> Example:
> You have a table that has a column "User"
> In the merge agent parameters use option -Hosname [Dave] ( or whatever
> value works)
> Then in the filter clause use:
> User = hostname()
> That particular subscriber will only get records where User = "Dave"
> This is for merge and I'm not sure about the other types of
> replication.
>

Partial Indexes

Anyone knows when Partial Indexes (indexes created on the first n characters
of a char or varchar field) will be available in SQL Server?
Does SQL Server 2005 have this feature?
TIA
Eduardo SicouretYou can simulate it by creating an index on a computed column:
ALTER TABLE YourTable
ADD First10Characters AS (LEFT(YourColumn, 10))
CREATE INDEX IX_First10Characters
ON YourTable (First10Characters)
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Eduardo Sicouret" <esicouret> wrote in message
news:OGROGUdfGHA.3652@.TK2MSFTNGP02.phx.gbl...
> Anyone knows when Partial Indexes (indexes created on the first n
> characters of a char or varchar field) will be available in SQL Server?
> Does SQL Server 2005 have this feature?
> TIA
> Eduardo Sicouret
>|||Thanks for answering.
I know that workaround is available, but my specific question is if "partial
indexes" are available in SQL Server 2005?
I don't have disk space to insert another column to my tables.
Regards,
Eduardo Sicouret
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> escribi en el
mensaje news:O2QRGcdfGHA.3488@.TK2MSFTNGP02.phx.gbl...
> You can simulate it by creating an index on a computed column:
> ALTER TABLE YourTable
> ADD First10Characters AS (LEFT(YourColumn, 10))
> CREATE INDEX IX_First10Characters
> ON YourTable (First10Characters)
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Eduardo Sicouret" <esicouret> wrote in message
> news:OGROGUdfGHA.3652@.TK2MSFTNGP02.phx.gbl...
>|||Hi Eduardo
No, there is no such thing as partial indexes.
However, the disk space requirement for the partial index would be the same
as the disk space required for the index on the computed column. So how do
you figure you have disk space for an index but don't have disk space for
the computed column?
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Eduardo Sicouret" <esicouret> wrote in message
news:urRqyfdfGHA.4464@.TK2MSFTNGP04.phx.gbl...
> Thanks for answering.
> I know that workaround is available, but my specific question is if
> "partial indexes" are available in SQL Server 2005?
> I don't have disk space to insert another column to my tables.
> Regards,
> Eduardo Sicouret
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> escribi en el
> mensaje news:O2QRGcdfGHA.3488@.TK2MSFTNGP02.phx.gbl...
>|||I will have the space of the computed column + the space of the index on the
computed column...
That will be 2 times the computed column...
Regards,
Eduardo Sicouret
"Kalen Delaney" <replies@.public_newsgroups.com> escribi en el mensaje
news:upIMcqdfGHA.4464@.TK2MSFTNGP04.phx.gbl...
> Hi Eduardo
> No, there is no such thing as partial indexes.
> However, the disk space requirement for the partial index would be the
> same as the disk space required for the index on the computed column. So
> how do you figure you have disk space for an index but don't have disk
> space for the computed column?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Eduardo Sicouret" <esicouret> wrote in message
> news:urRqyfdfGHA.4464@.TK2MSFTNGP04.phx.gbl...
>|||Untrue. Computed columns are not persisted, unless you use SQL Server
2005's "persisted computed column" feature, explicitly (by adding the
keyword PERSISTED after the column definition).
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Eduardo Sicouret" <esicouret> wrote in message
news:umHRwFefGHA.4828@.TK2MSFTNGP05.phx.gbl...
>I will have the space of the computed column + the space of the index on
>the computed column...
> That will be 2 times the computed column...
> Regards,
> Eduardo Sicouret
> "Kalen Delaney" <replies@.public_newsgroups.com> escribi en el mensaje
> news:upIMcqdfGHA.4464@.TK2MSFTNGP04.phx.gbl...
>|||The computed column takes no space until you create the index on it.
How big is the partial column you think you need to index and how are
computing the size of the index?
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Eduardo Sicouret" <esicouret> wrote in message
news:umHRwFefGHA.4828@.TK2MSFTNGP05.phx.gbl...
>I will have the space of the computed column + the space of the index on
>the computed column...
> That will be 2 times the computed column...
> Regards,
> Eduardo Sicouret
> "Kalen Delaney" <replies@.public_newsgroups.com> escribi en el mensaje
> news:upIMcqdfGHA.4464@.TK2MSFTNGP04.phx.gbl...
>|||didn't know that computed columns takes no space...
I will test this workaround to see if it works. The problem is that my
tables are 2 million records big so increasing a column has a considerably
big impact.
Regards,
Eduardo Sicouret
"Kalen Delaney" <replies@.public_newsgroups.com> escribi en el mensaje
news:u7juBLefGHA.3456@.TK2MSFTNGP05.phx.gbl...
> The computed column takes no space until you create the index on it.
> How big is the partial column you think you need to index and how are
> computing the size of the index?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Eduardo Sicouret" <esicouret> wrote in message
> news:umHRwFefGHA.4828@.TK2MSFTNGP05.phx.gbl...
>|||2 million is not all that may rows at all. I occasionally create tables with
2 million rows just for testing purposes.
How big is the partial column you want to index? How much free space do you
have?
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Eduardo Sicouret" <esicouret> wrote in message
news:uQy0PlefGHA.4276@.TK2MSFTNGP03.phx.gbl...
> didn't know that computed columns takes no space...
> I will test this workaround to see if it works. The problem is that my
> tables are 2 million records big so increasing a column has a considerably
> big impact.
> Regards,
> Eduardo Sicouret
> "Kalen Delaney" <replies@.public_newsgroups.com> escribi en el mensaje
> news:u7juBLefGHA.3456@.TK2MSFTNGP05.phx.gbl...
>|||Unfortunately I have only one 16GB disk and 700MB free after truncating and
shrinking the log. And this disk is also for the O.S.
The main table is accesed several times a second, so increasing a column is
a difficult task. table is 1.7GB big.
To reduce the indexes size I thought of a solution like partial indexing.
regards...
Eduardo Sicouret
"Kalen Delaney" <replies@.public_newsgroups.com> escribi en el mensaje
news:u0cPCrefGHA.2188@.TK2MSFTNGP04.phx.gbl...
>2 million is not all that may rows at all. I occasionally create tables
>with 2 million rows just for testing purposes.
> How big is the partial column you want to index? How much free space do
> you have?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Eduardo Sicouret" <esicouret> wrote in message
> news:uQy0PlefGHA.4276@.TK2MSFTNGP03.phx.gbl...
>
>

Friday, March 30, 2012

particulat table in Filegroup

particulat table in Filegroup

partially update webpage

Partially Bold text and Justify?

partial transaction is not working

partial transaction is not working

partial transaction is not working

Partial Success

Partial Success

PARTIAL SQL Server RESTORE's

PARTIAL SQL Server RESTORE's

PARTIAL SQL Server RESTORE's

Partial searches over a lot of fields

partial search performance

Partial row at end of file - best way to handle?

Partial Restore from Log

Partial Restore from Log

Partial Restore from Log

Partial Restore (only of a table)

Partial Restoration

Partial Restoration

Partial Restoration

Partial Replication

Partial Indexes

Parameterized Expression

Blog Archive

About Me