Dropping a set of tables

Hi
I am using SQL server 7 database and ASP as front end. I run an application where a text file is loaded into database. After this is done procedures are run to create a set of tables that have snapshots of the data in the text file.. each time i load a new text file i want to create the snapshots.. i hve written a stored procedure to create tables and insert values into the tables.. however how do i delete the tables i created the previous time.. the number of snapshots and their names will depend on the size of the text file.. how do i refer to all the snapshots created and drop them all before creating new ones?
plese guide
regdsIn your stored procedure, you should drop the temporary tables you created when you do not need them. If I'm not wrong, SQL server 2000 will drop the temporary tebles for you. But I suggest that you should drop them in the code if you do not need them.

Dropping a file in a filegroup that does not exist.

Hi,
I moved a database from an older sql box to one of our new servers. One of
the files in the prmiary file group was not moved and the server was wiped...
the filename is sysft_ix_STS_neo_1414639615. Everytime I try to do a backup
of that database I get a "file or firegroup is not online..." error message
and SQL won't let me drop it because it does not exist. Can anyone help me
out?Hi Henry
If there was data in this filegroup then you would have to resort to your
last backup.
John
"Henry" wrote:
> Hi,
> I moved a database from an older sql box to one of our new servers. One of
> the files in the prmiary file group was not moved and the server was wiped...
> the filename is sysft_ix_STS_neo_1414639615. Everytime I try to do a backup
> of that database I get a "file or firegroup is not online..." error message
> and SQL won't let me drop it because it does not exist. Can anyone help me
> out?|||I'm pretty certain this is the full text index and that rebuilding or removing full-text indexing
would solve this. This is what I recall from earlier post with the same problem.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:7E297789-FEFB-41D9-BF17-90BF96129198@.microsoft.com...
> Hi Henry
> If there was data in this filegroup then you would have to resort to your
> last backup.
> John
> "Henry" wrote:
>> Hi,
>> I moved a database from an older sql box to one of our new servers. One of
>> the files in the prmiary file group was not moved and the server was wiped...
>> the filename is sysft_ix_STS_neo_1414639615. Everytime I try to do a backup
>> of that database I get a "file or firegroup is not online..." error message
>> and SQL won't let me drop it because it does not exist. Can anyone help me
>> out?|||Hi Tibor
You are probably right! sp_help_fulltext_catalogs might verify this!
John
"Tibor Karaszi" wrote:
> I'm pretty certain this is the full text index and that rebuilding or removing full-text indexing
> would solve this. This is what I recall from earlier post with the same problem.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:7E297789-FEFB-41D9-BF17-90BF96129198@.microsoft.com...
> > Hi Henry
> >
> > If there was data in this filegroup then you would have to resort to your
> > last backup.
> >
> > John
> >
> > "Henry" wrote:
> >
> >> Hi,
> >>
> >> I moved a database from an older sql box to one of our new servers. One of
> >> the files in the prmiary file group was not moved and the server was wiped...
> >> the filename is sysft_ix_STS_neo_1414639615. Everytime I try to do a backup
> >> of that database I get a "file or firegroup is not online..." error message
> >> and SQL won't let me drop it because it does not exist. Can anyone help me
> >> out?
>

Dropping a file in a filegroup that does not exist.

Hi,
I moved a database from an older sql box to one of our new servers. One of
the files in the prmiary file group was not moved and the server was wiped..
.
the filename is sysft_ix_STS_neo_1414639615. Everytime I try to do a backup
of that database I get a "file or firegroup is not online..." error message
and SQL won't let me drop it because it does not exist. Can anyone help me
out?Hi Henry
If there was data in this filegroup then you would have to resort to your
last backup.
John
"Henry" wrote:

> Hi,
> I moved a database from an older sql box to one of our new servers. One o
f
> the files in the prmiary file group was not moved and the server was wiped
..
> the filename is sysft_ix_STS_neo_1414639615. Everytime I try to do a back
up
> of that database I get a "file or firegroup is not online..." error messag
e
> and SQL won't let me drop it because it does not exist. Can anyone help m
e
> out?|||Hi Henry
If there was data in this filegroup then you would have to resort to your
last backup.
John
"Henry" wrote:

> Hi,
> I moved a database from an older sql box to one of our new servers. One o
f
> the files in the prmiary file group was not moved and the server was wiped
..
> the filename is sysft_ix_STS_neo_1414639615. Everytime I try to do a back
up
> of that database I get a "file or firegroup is not online..." error messag
e
> and SQL won't let me drop it because it does not exist. Can anyone help m
e
> out?|||I'm pretty certain this is the full text index and that rebuilding or removi
ng full-text indexing
would solve this. This is what I recall from earlier post with the same prob
lem.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:7E297789-FEFB-41D9-BF17-90BF96129198@.microsoft.com...[vbcol=seagreen]
> Hi Henry
> If there was data in this filegroup then you would have to resort to your
> last backup.
> John
> "Henry" wrote:
>|||Hi Tibor
You are probably right! sp_help_fulltext_catalogs might verify this!
John
"Tibor Karaszi" wrote:

> I'm pretty certain this is the full text index and that rebuilding or remo
ving full-text indexing
> would solve this. This is what I recall from earlier post with the same pr
oblem.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:7E297789-FEFB-41D9-BF17-90BF96129198@.microsoft.com...
>|||I'm pretty certain this is the full text index and that rebuilding or removi
ng full-text indexing
would solve this. This is what I recall from earlier post with the same prob
lem.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:7E297789-FEFB-41D9-BF17-90BF96129198@.microsoft.com...[vbcol=seagreen]
> Hi Henry
> If there was data in this filegroup then you would have to resort to your
> last backup.
> John
> "Henry" wrote:
>|||Hi Tibor
You are probably right! sp_help_fulltext_catalogs might verify this!
John
"Tibor Karaszi" wrote:

> I'm pretty certain this is the full text index and that rebuilding or remo
ving full-text indexing
> would solve this. This is what I recall from earlier post with the same pr
oblem.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:7E297789-FEFB-41D9-BF17-90BF96129198@.microsoft.com...
>

Droping Log File

I have two transaction log files, one on C drive and other on D drive. I want to drop the file from that database that is on C drive. Can anyone help me out on this with complete syntax.

I have taken a down time of one and half hour to accomplish this task

Here is what think

Take a full database backup
Take transaction log backup
Shrinkfile using DBCC with truncateonly option
emptyfile using dbcc or drop file using alter database remove

thanks for your input guys

RomeYou can do it all on EM. But the most important thing is to do a checkpoint to make sure everything is on disk, and then do a log backup. The log file should be empty, and you should be able to delete the log file from EM.

drop the time

I have a field in my table that is set as DATETIME
The data that gets imported to it is a text file and has teh data with teh
time.
for example, 1/1/2006 4:57:12 PM
I want to get rid of teh time part
How can I make it so the data that I already have in the table will drop teh
time?
Hi
In a SQL Server datetime field you will always have a time part even if you
don't specify the time it will default to midnight (00:00.000).
You don't say how the data is imported and if that is the only way the
column gets populated?
If you use DTS to load the data, you could use an activeX transform to
truncate the datetime field before it is inserted. If you use BCP/BULKINSERT
you can specify a format file that splits off the time and ignores it.
Another method is to load the data into a staging table and manipulate it
from there whilst it is being inserted into the final destination. You could
also use an instead of trigger (but you would need to make sure that it fires
for your bulk insert), or possibly have a computed column that truncates the
datetime.
John
"Johnfli" wrote:

> I have a field in my table that is set as DATETIME
> The data that gets imported to it is a text file and has teh data with teh
> time.
> for example, 1/1/2006 4:57:12 PM
> I want to get rid of teh time part
> How can I make it so the data that I already have in the table will drop teh
> time?
>
>
|||ummm, hmmmm, ok, so how do I do any of teh items you mentioned?
I have DTS setup as teh text file is ftp'd to us. Teh time is included in
the text file and teh people sending it to us are not interested in dropping
off teh time portion.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:CCF11FA2-4E88-4642-8242-AEA7213D590A@.microsoft.com...[vbcol=seagreen]
> Hi
> In a SQL Server datetime field you will always have a time part even if
> you
> don't specify the time it will default to midnight (00:00.000).
> You don't say how the data is imported and if that is the only way the
> column gets populated?
> If you use DTS to load the data, you could use an activeX transform to
> truncate the datetime field before it is inserted. If you use
> BCP/BULKINSERT
> you can specify a format file that splits off the time and ignores it.
> Another method is to load the data into a staging table and manipulate it
> from there whilst it is being inserted into the final destination. You
> could
> also use an instead of trigger (but you would need to make sure that it
> fires
> for your bulk insert), or possibly have a computed column that truncates
> the
> datetime.
> John
> "Johnfli" wrote:
|||Hi
I assume it is the same DTS package that will load the file?
Check out http://www.sqldts.com/default.aspx?279,4 on how to use the
ActiveX transform. You will need to use the left function on the source
column to just insert the date part.
John
Johnfli wrote:[vbcol=seagreen]
> ummm, hmmmm, ok, so how do I do any of teh items you mentioned?
> I have DTS setup as teh text file is ftp'd to us. Teh time is included in
> the text file and teh people sending it to us are not interested in dropping
> off teh time portion.
>
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:CCF11FA2-4E88-4642-8242-AEA7213D590A@.microsoft.com...
|||cool, I will give it a shot.
Thank you
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:1143826579.603455.255570@.g10g2000cwb.googlegr oups.com...
> Hi
> I assume it is the same DTS package that will load the file?
> Check out http://www.sqldts.com/default.aspx?279,4 on how to use the
> ActiveX transform. You will need to use the left function on the source
> column to just insert the date part.
> John
> Johnfli wrote:
>

drop the time

I have a field in my table that is set as DATETIME
The data that gets imported to it is a text file and has teh data with teh
time.
for example, 1/1/2006 4:57:12 PM
I want to get rid of teh time part
How can I make it so the data that I already have in the table will drop teh
time'Hi
In a SQL Server datetime field you will always have a time part even if you
don't specify the time it will default to midnight (00:00.000).
You don't say how the data is imported and if that is the only way the
column gets populated?
If you use DTS to load the data, you could use an activeX transform to
truncate the datetime field before it is inserted. If you use BCP/BULKINSERT
you can specify a format file that splits off the time and ignores it.
Another method is to load the data into a staging table and manipulate it
from there whilst it is being inserted into the final destination. You could
also use an instead of trigger (but you would need to make sure that it fires
for your bulk insert), or possibly have a computed column that truncates the
datetime.
John
"Johnfli" wrote:
> I have a field in my table that is set as DATETIME
> The data that gets imported to it is a text file and has teh data with teh
> time.
> for example, 1/1/2006 4:57:12 PM
> I want to get rid of teh time part
> How can I make it so the data that I already have in the table will drop teh
> time'
>
>|||ummm, hmmmm, ok, so how do I do any of teh items you mentioned? :)
I have DTS setup as teh text file is ftp'd to us. Teh time is included in
the text file and teh people sending it to us are not interested in dropping
off teh time portion.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:CCF11FA2-4E88-4642-8242-AEA7213D590A@.microsoft.com...
> Hi
> In a SQL Server datetime field you will always have a time part even if
> you
> don't specify the time it will default to midnight (00:00.000).
> You don't say how the data is imported and if that is the only way the
> column gets populated?
> If you use DTS to load the data, you could use an activeX transform to
> truncate the datetime field before it is inserted. If you use
> BCP/BULKINSERT
> you can specify a format file that splits off the time and ignores it.
> Another method is to load the data into a staging table and manipulate it
> from there whilst it is being inserted into the final destination. You
> could
> also use an instead of trigger (but you would need to make sure that it
> fires
> for your bulk insert), or possibly have a computed column that truncates
> the
> datetime.
> John
> "Johnfli" wrote:
>> I have a field in my table that is set as DATETIME
>> The data that gets imported to it is a text file and has teh data with
>> teh
>> time.
>> for example, 1/1/2006 4:57:12 PM
>> I want to get rid of teh time part
>> How can I make it so the data that I already have in the table will drop
>> teh
>> time'
>>|||Hi
I assume it is the same DTS package that will load the file?
Check out http://www.sqldts.com/default.aspx?279,4 on how to use the
ActiveX transform. You will need to use the left function on the source
column to just insert the date part.
John
Johnfli wrote:
> ummm, hmmmm, ok, so how do I do any of teh items you mentioned? :)
> I have DTS setup as teh text file is ftp'd to us. Teh time is included in
> the text file and teh people sending it to us are not interested in dropping
> off teh time portion.
>
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:CCF11FA2-4E88-4642-8242-AEA7213D590A@.microsoft.com...
> > Hi
> >
> > In a SQL Server datetime field you will always have a time part even if
> > you
> > don't specify the time it will default to midnight (00:00.000).
> >
> > You don't say how the data is imported and if that is the only way the
> > column gets populated?
> >
> > If you use DTS to load the data, you could use an activeX transform to
> > truncate the datetime field before it is inserted. If you use
> > BCP/BULKINSERT
> > you can specify a format file that splits off the time and ignores it.
> > Another method is to load the data into a staging table and manipulate it
> > from there whilst it is being inserted into the final destination. You
> > could
> > also use an instead of trigger (but you would need to make sure that it
> > fires
> > for your bulk insert), or possibly have a computed column that truncates
> > the
> > datetime.
> >
> > John
> >
> > "Johnfli" wrote:
> >
> >> I have a field in my table that is set as DATETIME
> >>
> >> The data that gets imported to it is a text file and has teh data with
> >> teh
> >> time.
> >> for example, 1/1/2006 4:57:12 PM
> >>
> >> I want to get rid of teh time part
> >>
> >> How can I make it so the data that I already have in the table will drop
> >> teh
> >> time'
> >>
> >>
> >>|||cool, I will give it a shot.
Thank you
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:1143826579.603455.255570@.g10g2000cwb.googlegroups.com...
> Hi
> I assume it is the same DTS package that will load the file?
> Check out http://www.sqldts.com/default.aspx?279,4 on how to use the
> ActiveX transform. You will need to use the left function on the source
> column to just insert the date part.
> John
> Johnfli wrote:
>> ummm, hmmmm, ok, so how do I do any of teh items you mentioned? :)
>> I have DTS setup as teh text file is ftp'd to us. Teh time is included
>> in
>> the text file and teh people sending it to us are not interested in
>> dropping
>> off teh time portion.
>>
>>
>> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> news:CCF11FA2-4E88-4642-8242-AEA7213D590A@.microsoft.com...
>> > Hi
>> >
>> > In a SQL Server datetime field you will always have a time part even if
>> > you
>> > don't specify the time it will default to midnight (00:00.000).
>> >
>> > You don't say how the data is imported and if that is the only way the
>> > column gets populated?
>> >
>> > If you use DTS to load the data, you could use an activeX transform to
>> > truncate the datetime field before it is inserted. If you use
>> > BCP/BULKINSERT
>> > you can specify a format file that splits off the time and ignores it.
>> > Another method is to load the data into a staging table and manipulate
>> > it
>> > from there whilst it is being inserted into the final destination. You
>> > could
>> > also use an instead of trigger (but you would need to make sure that it
>> > fires
>> > for your bulk insert), or possibly have a computed column that
>> > truncates
>> > the
>> > datetime.
>> >
>> > John
>> >
>> > "Johnfli" wrote:
>> >
>> >> I have a field in my table that is set as DATETIME
>> >>
>> >> The data that gets imported to it is a text file and has teh data with
>> >> teh
>> >> time.
>> >> for example, 1/1/2006 4:57:12 PM
>> >>
>> >> I want to get rid of teh time part
>> >>
>> >> How can I make it so the data that I already have in the table will
>> >> drop
>> >> teh
>> >> time'
>> >>
>> >>
>> >>
>

drop the time

I have a field in my table that is set as DATETIME
The data that gets imported to it is a text file and has teh data with teh
time.
for example, 1/1/2006 4:57:12 PM
I want to get rid of teh time part
How can I make it so the data that I already have in the table will drop teh
time'Hi
In a SQL Server datetime field you will always have a time part even if you
don't specify the time it will default to midnight (00:00.000).
You don't say how the data is imported and if that is the only way the
column gets populated?
If you use DTS to load the data, you could use an activeX transform to
truncate the datetime field before it is inserted. If you use BCP/BULKINSERT
you can specify a format file that splits off the time and ignores it.
Another method is to load the data into a staging table and manipulate it
from there whilst it is being inserted into the final destination. You could
also use an instead of trigger (but you would need to make sure that it fire
s
for your bulk insert), or possibly have a computed column that truncates the
datetime.
John
"Johnfli" wrote:

> I have a field in my table that is set as DATETIME
> The data that gets imported to it is a text file and has teh data with teh
> time.
> for example, 1/1/2006 4:57:12 PM
> I want to get rid of teh time part
> How can I make it so the data that I already have in the table will drop t
eh
> time'
>
>|||ummm, hmmmm, ok, so how do I do any of teh items you mentioned?
I have DTS setup as teh text file is ftp'd to us. Teh time is included in
the text file and teh people sending it to us are not interested in dropping
off teh time portion.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:CCF11FA2-4E88-4642-8242-AEA7213D590A@.microsoft.com...[vbcol=seagreen]
> Hi
> In a SQL Server datetime field you will always have a time part even if
> you
> don't specify the time it will default to midnight (00:00.000).
> You don't say how the data is imported and if that is the only way the
> column gets populated?
> If you use DTS to load the data, you could use an activeX transform to
> truncate the datetime field before it is inserted. If you use
> BCP/BULKINSERT
> you can specify a format file that splits off the time and ignores it.
> Another method is to load the data into a staging table and manipulate it
> from there whilst it is being inserted into the final destination. You
> could
> also use an instead of trigger (but you would need to make sure that it
> fires
> for your bulk insert), or possibly have a computed column that truncates
> the
> datetime.
> John
> "Johnfli" wrote:
>|||Hi
I assume it is the same DTS package that will load the file?
Check out http://www.sqldts.com/default.aspx?279,4 on how to use the
ActiveX transform. You will need to use the left function on the source
column to just insert the date part.
John
Johnfli wrote:[vbcol=seagreen]
> ummm, hmmmm, ok, so how do I do any of teh items you mentioned?
> I have DTS setup as teh text file is ftp'd to us. Teh time is included
in
> the text file and teh people sending it to us are not interested in droppi
ng
> off teh time portion.
>
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:CCF11FA2-4E88-4642-8242-AEA7213D590A@.microsoft.com...|||cool, I will give it a shot.
Thank you
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:1143826579.603455.255570@.g10g2000cwb.googlegroups.com...
> Hi
> I assume it is the same DTS package that will load the file?
> Check out http://www.sqldts.com/default.aspx?279,4 on how to use the
> ActiveX transform. You will need to use the left function on the source
> column to just insert the date part.
> John
> Johnfli wrote:
>

Drop Primary Transaction Log File

Hi All,
I attemping to move a transaction log file online to another location. I performed the following steps;
- Created a second(log_name2) log file.
- ran DBCC Shrinkfile(log_name1,emptyfile)
- alter database mydb
remove file log_name1
* However, I receive the following error 5020 "The primary data or log file cannot be removed from a database".
Is it possible to remove the original log file?
Mike,
You might have to take the database offline by doing:
1.sp_detach_db
2.Then attach the database back using sp_attach_db but this time mentioning
the new path for ldf file.Refer BooksOnLine for syntax of the two commands.
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"MikeDBA" <anonymous@.discussions.microsoft.com> wrote in message
news:94423849-18A7-4E5F-9296-ED3EBDD3CCD3@.microsoft.com...
> Hi All,
> I attemping to move a transaction log file online to another location. I
performed the following steps;
> - Created a second(log_name2) log file.
> - ran DBCC Shrinkfile(log_name1,emptyfile)
> - alter database mydb
> remove file log_name1
> * However, I receive the following error 5020 "The primary data or log
file cannot be removed from a database".
> Is it possible to remove the original log file?
|||Hi,
In your case , I feel that the only solution is detach and attach the
databases.
Steps:
1. detach the database using sp_detach_db
2. copy the LDF to new location
3. Attach it back using sp_attach_db
Have a look into the below link:
http://msdn.microsoft.com/library/de...us/createdb/cm
_8_des_03_9dbn.asp
Thanks
Hari
MCDBA
"MikeDBA" <anonymous@.discussions.microsoft.com> wrote in message
news:94423849-18A7-4E5F-9296-ED3EBDD3CCD3@.microsoft.com...
> Hi All,
> I attemping to move a transaction log file online to another location. I
performed the following steps;
> - Created a second(log_name2) log file.
> - ran DBCC Shrinkfile(log_name1,emptyfile)
> - alter database mydb
> remove file log_name1
> * However, I receive the following error 5020 "The primary data or log
file cannot be removed from a database".
> Is it possible to remove the original log file?

Drop Primary Transaction Log File

Hi All,
I attemping to move a transaction log file online to another location. I per
formed the following steps;
- Created a second(log_name2) log file.
- ran DBCC Shrinkfile(log_name1,emptyfile)
- alter database mydb
remove file log_name1
* However, I receive the following error 5020 "The primary data or log file
cannot be removed from a database".
Is it possible to remove the original log file?Mike,
You might have to take the database offline by doing:
1.sp_detach_db
2.Then attach the database back using sp_attach_db but this time mentioning
the new path for ldf file.Refer BooksOnLine for syntax of the two commands.
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"MikeDBA" <anonymous@.discussions.microsoft.com> wrote in message
news:94423849-18A7-4E5F-9296-ED3EBDD3CCD3@.microsoft.com...
> Hi All,
> I attemping to move a transaction log file online to another location. I
performed the following steps;
> - Created a second(log_name2) log file.
> - ran DBCC Shrinkfile(log_name1,emptyfile)
> - alter database mydb
> remove file log_name1
> * However, I receive the following error 5020 "The primary data or log
file cannot be removed from a database".
> Is it possible to remove the original log file?|||Hi,
In your case , I feel that the only solution is detach and attach the
databases.
Steps:
1. detach the database using sp_detach_db
2. copy the LDF to new location
3. Attach it back using sp_attach_db
Have a look into the below link:
http://msdn.microsoft.com/library/d...-us/createdb/cm
_8_des_03_9dbn.asp
Thanks
Hari
MCDBA
"MikeDBA" <anonymous@.discussions.microsoft.com> wrote in message
news:94423849-18A7-4E5F-9296-ED3EBDD3CCD3@.microsoft.com...
> Hi All,
> I attemping to move a transaction log file online to another location. I
performed the following steps;
> - Created a second(log_name2) log file.
> - ran DBCC Shrinkfile(log_name1,emptyfile)
> - alter database mydb
> remove file log_name1
> * However, I receive the following error 5020 "The primary data or log
file cannot be removed from a database".
> Is it possible to remove the original log file?

drop or delete view

hello,

im creating a sp that creates a view from a query then bcp to csv file.

my problem is that when i start the sp, it complains the sp already exists....yes it does, however, i don't if im going about this the wrong way...but i tried the following to no avail

DROP IF EXISTS v_participantTrades

now, does that syntax not work for views?

IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'v_participantTrades'))

DROP VIEW v_participantTrades

|||

aah that explains the whole, create view needs to be the first statement!

thank you

|||

exuse my incompetence however,

Invalid Object name 'sys.views'.

blaaa was my ineptitude - sysobjects for my version

cheers

Drop large transaction log file and create new

Team,
Could you sned me some idea, how can I remove or replace transaction log file on MS SQL 2000 server? Data size is about 2GB but trx log is more than 35GB. This database include only static data...there are no transactions. I have about 5 million records in one table and there are 13 tables with 40 000-50 000 records.

It's very urgent because we need to clean up space on NT server tonight.

Thanks,
AttilaOriginally posted by horvata
Team,
Could you sned me some idea, how can I remove or replace transaction log file on MS SQL 2000 server? Data size is about 2GB but trx log is more than 35GB. This database include only static data...there are no transactions. I have about 5 million records in one table and there are 13 tables with 40 000-50 000 records.

It's very urgent because we need to clean up space on NT server tonight.

Thanks,
Attila

See: http://dbforums.com/t546372.html|||Thanks a lot for your help.|||Originally posted by DBA
See: http://dbforums.com/t546372.html

Whou much time you spend to make you database full backup ??
Wich type of backup do you do ?

If you log is no longer used (because you data is static) you can use the command bellow

sp_detach_db <dbname>

GO

CREATE DATABASE <dbname>
ON PRIMARY (FILENAME = '<path>.dbname.extension')
FOR ATTACH
GO

Ps: Make 2 full backups of you database before do this. On filename choose the path of you datafile <only>, forget you logfile.

Jorge|||Just for your information...
I created a new database and I exported old objects into new db. After that I dropped old database and I created new database with the original name and then I exported back db objects. Now I have a DB Maitenence Plan which is working fine and there are no issue with log file size.

Thanks,
Attila|||Originally posted by horvata
Just for your information...
I created a new database and I exported old objects into new db. After that I dropped old database and I created new database with the original name and then I exported back db objects. Now I have a DB Maitenence Plan which is working fine and there are no issue with log file size.

Thanks,
Attila

Hi Attila, I discover another way to do this.

Ps:Allways execute a full backup before.

First execute
EXEC sp_detach_db 'database_name', 'true'

Rename your physical log file on Operation system
After this execute the following command.

EXEC sp_attach_single_file_db @.dbname = 'database_name',
@.physname = 'path\database_name.extension'

This command works. I haver already done this.

Jorge Demattos
Bank of America.

drop an extended proc that doesn't exist

the latest hotfix for sql2000 gets an error when it tries
to replace an extended proc named xp_mergexpusage in the
replsys.sql file. Here is a small test case to show the
issue. Any ideas on how to resolve this?
Script:--
select * from sysobjects
where name = 'xp_mergexpusage'
go
execute dbo.sp_dropextendedproc 'xp_mergexpusage'
go
execute sp_addextendedproc 'xp_mergexpusage', 'xprepl.dll'
go
result:---
(0 row(s) affected)
Server: Msg 3701, Level 11, State 5, Procedure
sp_dropextendedproc, Line 18
Cannot drop the procedure 'xp_mergexpusage', because it
does not exist in the system catalog.
Server: Msg 2714, Level 16, State 7, Procedure
sp_addextendedproc, Line 26
There is already an object named 'xp_mergexpusage' in the
database.If you are in this state all sp_addextendedproc calls should fail.
Try running this:
select max(id) from master.dbo.sysobjects
dbcc traceon(3604)
dbcc dbinfo('master')
dbcc traceoff(3604)
What is the value returned by the first query and the value of dbi_nextid =returned by the dbcc dbinfo ?
If they are indentical, try doing:
create table master.dbo.t1(c1 int)
drop table master.dbo.t1
And run the query again, see if the values are no longer identical.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2003 All rights reserved.
"Gerald Bowers" <gerald.bowers@.wachovia.com> wrote in message
news:018201c356cb$5a3294c0$a501280a@.phx.gbl...
> the latest hotfix for sql2000 gets an error when it tries
> to replace an extended proc named xp_mergexpusage in the
> replsys.sql file. Here is a small test case to show the
> issue. Any ideas on how to resolve this?
> Script:--
> select * from sysobjects
> where name = 'xp_mergexpusage'
> go
> execute dbo.sp_dropextendedproc 'xp_mergexpusage'
> go
> execute sp_addextendedproc 'xp_mergexpusage', 'xprepl.dll'
> go
> result:---
> (0 row(s) affected)
> Server: Msg 3701, Level 11, State 5, Procedure
> sp_dropextendedproc, Line 18
> Cannot drop the procedure 'xp_mergexpusage', because it
> does not exist in the system catalog.
> Server: Msg 2714, Level 16, State 7, Procedure
> sp_addextendedproc, Line 26
> There is already an object named 'xp_mergexpusage' in the
> database.
>|||You are correct, upon working with it further, later sp_addextendedproc calls did in fact fail.
You solution did rectify the situation. Do you know what series of events gets it into that state?
Thanks,
Gerald
>--Original Message--
>If you are in this state all sp_addextendedproc calls should fail.
>Try running this:
>select max(id) from master.dbo.sysobjects
>dbcc traceon(3604)
>dbcc dbinfo('master')
>dbcc traceoff(3604)
>What is the value returned by the first query and the value of dbi_nextid =3D
>returned by the dbcc dbinfo ?
>If they are indentical, try doing:
>create table master.dbo.t1(c1 int)
>drop table master.dbo.t1
>And run the query again, see if the values are no longer identical.
>GertD@.SQLDev.Net
>Please reply only to the newsgroups.
>This posting is provided "AS IS" with no warranties, and confers no rights.
>You assume all risk for your use.
>Copyright =A9 SQLDev.Net 1991-2003 All rights reserved.
>"Gerald Bowers" <gerald.bowers@.wachovia.com> wrote in message
>news:018201c356cb$5a3294c0$a501280a@.phx.gbl...
>> the latest hotfix for sql2000 gets an error when it tries
>> to replace an extended proc named xp_mergexpusage in the
>> replsys.sql file. Here is a small test case to show the
>> issue. Any ideas on how to resolve this?
>> Script:--
>> select * from sysobjects
>> where name =3D 'xp_mergexpusage'
>> go
>> execute dbo.sp_dropextendedproc 'xp_mergexpusage'
>> go
>> execute sp_addextendedproc 'xp_mergexpusage', 'xprepl.dll'
>> go
>> result:---
>> (0 row(s) affected)
>> Server: Msg 3701, Level 11, State 5, Procedure
>> sp_dropextendedproc, Line 18
>> Cannot drop the procedure 'xp_mergexpusage', because it
>> does not exist in the system catalog.
>> Server: Msg 2714, Level 16, State 7, Procedure
>> sp_addextendedproc, Line 26
>> There is already an object named 'xp_mergexpusage' in the
>> database.
>
>.
>

Drop all the connections to the DataBase

Hi ,
I am using the following code in a batch file to take the back up of a database. but some times it fails with the message
"Cannot access the database becuase it is being used by another process." Is there any way i can force all the connections
to the database to be dropped using code. Any help will be greatly appreciated.
isql -b -S localhost -E -U xyzuser -P xyz -Q "sp_detach_db 'JMS', 'true'"
copy C:\JJMSdb\JMS_Data.MDF C:\JJMSdb\JMS_Data_2004-03-29_14-54-5933.MDF
copy C:\JJMSdb\JMS_Log.LDF C:\JJMSdb\JMS_Log_2004-03-29_14-54-5933.LDF
isql -b -S localhost -E -U xyzuser -P xyzpwd -Q "sp_attach_db @.dbname = 'JMS', @.filename1 = 'C:\JJMSdb\JMS_Data.MDF', @.filename2 = 'C:\JJMSdb\JMS_Log.LDF'"
Thanks,
Ram
Ram
This procedure written by Narayana Vyas Kondreddi
CREATE PROC sp_dboption2
(
@.dbname sysname = NULL, --Database name
@.optname varchar(35) = NULL, --Option name
@.optvalue varchar(5) = NULL, --Option value, either 'true' or 'false'
@.wait int = NULL --Seconds to wait, before killing the existing
connections
)
AS
BEGIN
/************************************************** *************************
********************************
Copyright 2001 Narayana Vyas Kondreddi. All rights reserved.
Purpose: The system stored procedure sp_dboption fails to set databases in
'read only'/'single user'/'offline'
modes if the database is in use. This procedure works as a wrapper around
sp_dboption and overcomes that
limitation by killing all the active connections. You can configure it to
kill the connections immediately,
or after waiting for a specified interval. This procedure simulates the
new ALTER TABLE syntax of SQL Server
2000 (the ROLLBACK IMMEDIATE and ROLLBACK AFTER options along with
OFFLINE, ONLINE, READ_ONLY, READ_WRITE,
SINGLE_USER, RESTRICTED_USER, MULTI_USER).
Written by: Narayana Vyas Kondreddi
http://vyaskn.tripod.com
Tested on: SQL Server 7.0, Service Pack 3
Date created: October-29-2001 1:30 AM Indian Standard Time
Date modified: October-29-2001 1:30 AM Indian Standard Time
Email: vyaskn@.hotmail.com
Usage: Just run this complete script in the master database to create this
stored procedure. As far as syntax is
concerned, this procedure works very similar to the system stored
procedure sp_dboption. It has an additional
parameter @.wait, which can be used, to wait for a specified number of
seconds, before killing the connections.
The settable database option names need to be specified in full. For
example, the option name 'single' is
considered invalid and 'single user' is considered valid.
To bring pubs database into single user mode:
EXEC sp_dboption2 'pubs', 'single user', 'true'
To bring pubs database into single user mode. Wait for 30 seconds, for
current connections to leave and
start killing the connections after 30 seconds:
EXEC sp_dboption2 'pubs', 'single user', 'true', 30
To bring pubs database into read/write mode:
EXEC sp_dboption2 'pubs', 'read only', 'false'
To bring pubs database into read/write mode. Wait for 30 seconds, for
current connections to leave and
start killing the connections after 30 seconds:
EXEC sp_dboption2 'pubs', 'read only', 'false', 30
************************************************** **************************
*******************************/
DECLARE @.dbid int, @.spid int, @.execstr varchar(15), @.waittime varchar(15),
@.final_chk int
--Only the following options require that, no other connections should
access the database
IF (LOWER(@.optname) IN ('offline', 'read only', 'single user')) AND
(LOWER(@.optvalue) IN('true', 'false'))
BEGIN
--Determining whether to wait, before killing the existing connections
IF @.wait > 0
BEGIN
SET @.waittime = (SELECT CONVERT(varchar, DATEADD(s, @.wait, GETDATE()),
14))
WAITFOR TIME @.waittime --Wait the specified number of seconds
END
SET @.dbid = DB_ID(@.dbname) --Getting the database_id for the specified
database
--Get the lowest spid
TryAgain:
SET @.spid = (SELECT MIN(spid) FROM master..sysprocesses WHERE dbid =
@.dbid)
WHILE @.spid IS NOT NULL
BEGIN
IF @.spid <> @.@.SPID --To avoid the KILL attempt on own connection
BEGIN
SET @.execstr = 'KILL ' + LTRIM(STR(@.spid))
EXEC(@.execstr) --Killing the connection
END
--Get the spid higher than the last spid
SET @.spid = (SELECT MIN(spid) FROM master..sysprocesses WHERE dbid =
@.dbid AND spid > @.spid)
END
END
SET @.final_chk = (SELECT COUNT(spid) FROM master..sysprocesses WHERE dbid =
@.dbid)
IF (@.final_chk = 0) OR (@.final_chk = 1 AND DB_NAME() = @.dbname)
BEGIN
EXEC sp_dboption @.dbname, @.optname, @.optvalue --Calling sp_dboption to
complete the job
END
ELSE
BEGIN
GOTO TryAgain --New connections popped up, or killed connections aren't
cleaned up yet, so try killing them again
END
END
"Ram" <anonymous@.discussions.microsoft.com> wrote in message
news:F0E88B1A-A105-4E24-9BEE-CEB5FC97F532@.microsoft.com...
> Hi ,
> I am using the following code in a batch file to take the back up of a
database. but some times it fails with the message
> "Cannot access the database becuase it is being used by another process."
Is there any way i can force all the connections
> to the database to be dropped using code. Any help will be greatly
appreciated.
> isql -b -S localhost -E -U xyzuser -P xyz -Q "sp_detach_db 'JMS', 'true'"
> copy C:\JJMSdb\JMS_Data.MDF C:\JJMSdb\JMS_Data_2004-03-29_14-54-5933.MDF
> copy C:\JJMSdb\JMS_Log.LDF C:\JJMSdb\JMS_Log_2004-03-29_14-54-5933.LDF
> isql -b -S localhost -E -U xyzuser -P xyzpwd -Q "sp_attach_db @.dbname =
'JMS', @.filename1 = 'C:\JJMSdb\JMS_Data.MDF', @.filename2 =
'C:\JJMSdb\JMS_Log.LDF'"
> Thanks,
> Ram
>
>
|||Why would you KILL connections instead of using the "ALTER=20
DATABASE dbname set SINGLE_USER with rollback immediate"=20
syntax that you mention? It is much more clean than=20
issuing a bunch of KILL commands. What if you KILLed a=20
process that was going to take a long time to die or just=20
hung? You could potentially corrupt your database using=20
KILL commands. KILL should be used very carefully. =20
Van

>--Original Message--
>Ram
>This procedure written by Narayana Vyas Kondreddi
>CREATE PROC sp_dboption2
>(
> @.dbname sysname =3D NULL, --Database name
> @.optname varchar(35) =3D NULL, --Option name
> @.optvalue varchar(5) =3D NULL, --Option value,=20
either 'true' or 'false'
> @.wait int =3D NULL --Seconds to wait, before killing=20
the existing
>connections
>)
>AS
>BEGIN
>/************************************************** *******
******************
>********************************
> Copyright =A9 2001 Narayana Vyas Kondreddi. All rights=20
reserved.
>Purpose: The system stored procedure sp_dboption fails to=20
set databases in
>'read only'/'single user'/'offline'
> modes if the database is in use. This procedure works=20
as a wrapper around
>sp_dboption and overcomes that
> limitation by killing all the active connections. You=20
can configure it to
>kill the connections immediately,
> or after waiting for a specified interval. This=20
procedure simulates the
>new ALTER TABLE syntax of SQL Server
> 2000 (the ROLLBACK IMMEDIATE and ROLLBACK AFTER options=20
along with
>OFFLINE, ONLINE, READ_ONLY, READ_WRITE,
> SINGLE_USER, RESTRICTED_USER, MULTI_USER).
>Written by: Narayana Vyas Kondreddi
> http://vyaskn.tripod.com
>Tested on: SQL Server 7.0, Service Pack 3
>Date created: October-29-2001 1:30 AM Indian Standard Time
>Date modified: October-29-2001 1:30 AM Indian Standard=20
Time
>Email: vyaskn@.hotmail.com
>Usage: Just run this complete script in the master=20
database to create this
>stored procedure. As far as syntax is
> concerned, this procedure works very similar to the=20
system stored
>procedure sp_dboption. It has an additional
> parameter @.wait, which can be used, to wait for a=20
specified number of
>seconds, before killing the connections.
> The settable database option names need to be specified=20
in full. For
>example, the option name 'single' is
> considered invalid and 'single user' is considered=20
valid.
> To bring pubs database into single user mode:
> EXEC sp_dboption2 'pubs', 'single user', 'true'
> To bring pubs database into single user mode. Wait for=20
30 seconds, for
>current connections to leave and
> start killing the connections after 30 seconds:
> EXEC sp_dboption2 'pubs', 'single user', 'true', 30
> To bring pubs database into read/write mode:
> EXEC sp_dboption2 'pubs', 'read only', 'false'
> To bring pubs database into read/write mode. Wait for=20
30 seconds, for
>current connections to leave and
> start killing the connections after 30 seconds:
> EXEC sp_dboption2 'pubs', 'read only', 'false', 30
>
>************************************************* *********
******************
>*******************************/
> DECLARE @.dbid int, @.spid int, @.execstr varchar(15),=20
@.waittime varchar(15),
>@.final_chk int
> --Only the following options require that, no other=20
connections should
>access the database
> IF (LOWER(@.optname) IN ('offline', 'read only', 'single=20
user')) AND
>(LOWER(@.optvalue) IN('true', 'false'))
> BEGIN
> --Determining whether to wait, before killing the=20
existing connections
> IF @.wait > 0
> BEGIN
> SET @.waittime =3D (SELECT CONVERT(varchar, DATEADD(s,=20
@.wait, GETDATE()),
>14))
> WAITFOR TIME @.waittime --Wait the specified number of=20
seconds
> END
> SET @.dbid =3D DB_ID(@.dbname) --Getting the database_id=20
for the specified
>database
> --Get the lowest spid
> TryAgain:
> SET @.spid =3D (SELECT MIN(spid) FROM=20
master..sysprocesses WHERE dbid =3D
>@.dbid)
> WHILE @.spid IS NOT NULL
> BEGIN
> IF @.spid <> @.@.SPID --To avoid the KILL attempt on own=20
connection
> BEGIN
> SET @.execstr =3D 'KILL ' + LTRIM(STR(@.spid))
> EXEC(@.execstr) --Killing the connection
> END
> --Get the spid higher than the last spid
> SET @.spid =3D (SELECT MIN(spid) FROM=20
master..sysprocesses WHERE dbid =3D
>@.dbid AND spid > @.spid)
> END
> END
> SET @.final_chk =3D (SELECT COUNT(spid) FROM=20
master..sysprocesses WHERE dbid =3D
>@.dbid)
> IF (@.final_chk =3D 0) OR (@.final_chk =3D 1 AND DB_NAME() =3D=20
@.dbname)
> BEGIN
> EXEC sp_dboption @.dbname, @.optname, @.optvalue --Calling=20
sp_dboption to
>complete the job
> END
> ELSE
> BEGIN
> GOTO TryAgain --New connections popped up, or killed=20
connections aren't
>cleaned up yet, so try killing them again
> END
>END
>
>
>"Ram" <anonymous@.discussions.microsoft.com> wrote in=20
message
>news:F0E88B1A-A105-4E24-9BEE-CEB5FC97F532@.microsoft.com...
the back up of a
>database. but some times it fails with the message
another process."
>Is there any way i can force all the connections
will be greatly
>appreciated.
Q "sp_detach_db 'JMS', 'true'"
29_14-54-5933.MDF
29_14-54-5933.LDF
Q "sp_attach_db @.dbname =3D
>'JMS', @.filename1 =3D 'C:\JJMSdb\JMS_Data.MDF', @.filename2 =3D
>'C:\JJMSdb\JMS_Log.LDF'"
>
>.
>
|||Van
SQL Server 7.0
If you have an active users in your database you will not be able use 'SET
SINGLE USER' mode
Server: Msg 15089, Level 11, State 1, Procedure sp_dboption, Line 400
Cannot change the 'single user' option of a database while another user is
in the database.
"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:15e7101c416a4$9ad58a70$a501280a@.phx.gbl...
Why would you KILL connections instead of using the "ALTER
DATABASE dbname set SINGLE_USER with rollback immediate"
syntax that you mention? It is much more clean than
issuing a bunch of KILL commands. What if you KILLed a
process that was going to take a long time to die or just
hung? You could potentially corrupt your database using
KILL commands. KILL should be used very carefully.
Van

>--Original Message--
>Ram
>This procedure written by Narayana Vyas Kondreddi
>CREATE PROC sp_dboption2
>(
> @.dbname sysname = NULL, --Database name
> @.optname varchar(35) = NULL, --Option name
> @.optvalue varchar(5) = NULL, --Option value,
either 'true' or 'false'
> @.wait int = NULL --Seconds to wait, before killing
the existing
>connections
>)
>AS
>BEGIN
>/************************************************** *******
******************
>********************************
> Copyright 2001 Narayana Vyas Kondreddi. All rights
reserved.
>Purpose: The system stored procedure sp_dboption fails to
set databases in
>'read only'/'single user'/'offline'
> modes if the database is in use. This procedure works
as a wrapper around
>sp_dboption and overcomes that
> limitation by killing all the active connections. You
can configure it to
>kill the connections immediately,
> or after waiting for a specified interval. This
procedure simulates the
>new ALTER TABLE syntax of SQL Server
> 2000 (the ROLLBACK IMMEDIATE and ROLLBACK AFTER options
along with
>OFFLINE, ONLINE, READ_ONLY, READ_WRITE,
> SINGLE_USER, RESTRICTED_USER, MULTI_USER).
>Written by: Narayana Vyas Kondreddi
> http://vyaskn.tripod.com
>Tested on: SQL Server 7.0, Service Pack 3
>Date created: October-29-2001 1:30 AM Indian Standard Time
>Date modified: October-29-2001 1:30 AM Indian Standard
Time
>Email: vyaskn@.hotmail.com
>Usage: Just run this complete script in the master
database to create this
>stored procedure. As far as syntax is
> concerned, this procedure works very similar to the
system stored
>procedure sp_dboption. It has an additional
> parameter @.wait, which can be used, to wait for a
specified number of
>seconds, before killing the connections.
> The settable database option names need to be specified
in full. For
>example, the option name 'single' is
> considered invalid and 'single user' is considered
valid.
> To bring pubs database into single user mode:
> EXEC sp_dboption2 'pubs', 'single user', 'true'
> To bring pubs database into single user mode. Wait for
30 seconds, for
>current connections to leave and
> start killing the connections after 30 seconds:
> EXEC sp_dboption2 'pubs', 'single user', 'true', 30
> To bring pubs database into read/write mode:
> EXEC sp_dboption2 'pubs', 'read only', 'false'
> To bring pubs database into read/write mode. Wait for
30 seconds, for
>current connections to leave and
> start killing the connections after 30 seconds:
> EXEC sp_dboption2 'pubs', 'read only', 'false', 30
>
>************************************************* *********
******************
>*******************************/
> DECLARE @.dbid int, @.spid int, @.execstr varchar(15),
@.waittime varchar(15),
>@.final_chk int
> --Only the following options require that, no other
connections should
>access the database
> IF (LOWER(@.optname) IN ('offline', 'read only', 'single
user')) AND
>(LOWER(@.optvalue) IN('true', 'false'))
> BEGIN
> --Determining whether to wait, before killing the
existing connections
> IF @.wait > 0
> BEGIN
> SET @.waittime = (SELECT CONVERT(varchar, DATEADD(s,
@.wait, GETDATE()),
>14))
> WAITFOR TIME @.waittime --Wait the specified number of
seconds
> END
> SET @.dbid = DB_ID(@.dbname) --Getting the database_id
for the specified
>database
> --Get the lowest spid
> TryAgain:
> SET @.spid = (SELECT MIN(spid) FROM
master..sysprocesses WHERE dbid =
>@.dbid)
> WHILE @.spid IS NOT NULL
> BEGIN
> IF @.spid <> @.@.SPID --To avoid the KILL attempt on own
connection
> BEGIN
> SET @.execstr = 'KILL ' + LTRIM(STR(@.spid))
> EXEC(@.execstr) --Killing the connection
> END
> --Get the spid higher than the last spid
> SET @.spid = (SELECT MIN(spid) FROM
master..sysprocesses WHERE dbid =
>@.dbid AND spid > @.spid)
> END
> END
> SET @.final_chk = (SELECT COUNT(spid) FROM
master..sysprocesses WHERE dbid =
>@.dbid)
> IF (@.final_chk = 0) OR (@.final_chk = 1 AND DB_NAME() =
@.dbname)
> BEGIN
> EXEC sp_dboption @.dbname, @.optname, @.optvalue --Calling
sp_dboption to
>complete the job
> END
> ELSE
> BEGIN
> GOTO TryAgain --New connections popped up, or killed
connections aren't
>cleaned up yet, so try killing them again
> END
>END
>
>
>"Ram" <anonymous@.discussions.microsoft.com> wrote in
message
>news:F0E88B1A-A105-4E24-9BEE-CEB5FC97F532@.microsoft.com...
the back up of a
>database. but some times it fails with the message
another process."
>Is there any way i can force all the connections
will be greatly
>appreciated.
Q "sp_detach_db 'JMS', 'true'"
29_14-54-5933.MDF
29_14-54-5933.LDF
Q "sp_attach_db @.dbname =
>'JMS', @.filename1 = 'C:\JJMSdb\JMS_Data.MDF', @.filename2 =
>'C:\JJMSdb\JMS_Log.LDF'"
>
>.
>
|||True, SQL 7.0 doesn't support the "with rollback=20
immediate" syntax (or at least I don't think it does). =20
The person who posted this question didn't state what=20
version of SQL they are using. But even if it is version=20
7.0 and my suggestion that uses the "with rollback=20
immediate" doesn't work, it's still not a good idea to=20
have an SP with KILL commands that runs as a scheduled=20
process. KILL commands should be monitored and used=20
carefully to ensure data and database corruption do not=20
occur. It would be better to stop SQL server, start it=20
back up and then put it into single user mode for the=20
detach. This could be done with NET STOP and NET START=20
from the batch file.
A better solution to the whole thing may be to just use=20
the BACKUP DATABASE syntax and not worry about putting it=20
in single user mode or detaching.

>--Original Message--
>Van
>SQL Server 7.0
>If you have an active users in your database you will not=20
be able use 'SET
>SINGLE USER' mode
>Server: Msg 15089, Level 11, State 1, Procedure=20
sp_dboption, Line 400
>Cannot change the 'single user' option of a database=20
while another user is
>in the database.
>
>"Van Jones" <anonymous@.discussions.microsoft.com> wrote=20
in message
>news:15e7101c416a4$9ad58a70$a501280a@.phx.gbl...
>Why would you KILL connections instead of using the "ALTER
>DATABASE dbname set SINGLE_USER with rollback immediate"
>syntax that you mention? It is much more clean than
>issuing a bunch of KILL commands. What if you KILLed a
>process that was going to take a long time to die or just
>hung? You could potentially corrupt your database using
>KILL commands. KILL should be used very carefully.
>Van
>
>either 'true' or 'false'
>the existing
*
>******************
>reserved.
>set databases in
>as a wrapper around
>can configure it to
>procedure simulates the
>along with
Time
>Time
>database to create this
>system stored
>specified number of
>in full. For
>valid.
>30 seconds, for
>30 seconds, for
*
>******************
>@.waittime varchar(15),
>connections should
>user')) AND
>existing connections
>@.wait, GETDATE()),
>seconds
>for the specified
>master..sysprocesses WHERE dbid =3D
>connection
>master..sysprocesses WHERE dbid =3D
>master..sysprocesses WHERE dbid =3D
>@.dbname)
>sp_dboption to
>connections aren't
>message
CEB5FC97F532@.microsoft.com...
>the back up of a
>another process."
>will be greatly
>Q "sp_detach_db 'JMS', 'true'"
>29_14-54-5933.MDF
>29_14-54-5933.LDF
>Q "sp_attach_db @.dbname =3D
=3D
>
>.
>

Drop all the connections to the DataBase

Hi ,
I am using the following code in a batch file to take the back up of a datab
ase. but some times it fails with the message
"Cannot access the database becuase it is being used by another process." Is
there any way i can force all the connections
to the database to be dropped using code. Any help will be greatly apprecia
ted.
isql -b -S localhost -E -U xyzuser -P xyz -Q "sp_detach_db 'JMS', 'true'"
copy C:\JJMSdb\JMS_Data.MDF C:\JJMSdb\JMS_Data_2004-03-29_14-54-5933.MDF
copy C:\JJMSdb\JMS_Log.LDF C:\JJMSdb\JMS_Log_2004-03-29_14-54-5933.LDF
isql -b -S localhost -E -U xyzuser -P xyzpwd -Q "sp_attach_db @.dbname = 'JMS
', @.filename1 = 'C:\JJMSdb\JMS_Data.MDF', @.filename2 = 'C:\JJMSdb\JMS_Log.LD
F'"
Thanks,
RamRam
This procedure written by Narayana Vyas Kondreddi
CREATE PROC sp_dboption2
(
@.dbname sysname = NULL, --Database name
@.optname varchar(35) = NULL, --Option name
@.optvalue varchar(5) = NULL, --Option value, either 'true' or 'false'
@.wait int = NULL --Seconds to wait, before killing the existing
connections
)
AS
BEGIN
/ ****************************************
***********************************
********************************
Copyright 2001 Narayana Vyas Kondreddi. All rights reserved.
Purpose: The system stored procedure sp_dboption fails to set databases in
'read only'/'single user'/'offline'
modes if the database is in use. This procedure works as a wrapper around
sp_dboption and overcomes that
limitation by killing all the active connections. You can configure it to
kill the connections immediately,
or after waiting for a specified interval. This procedure simulates the
new ALTER TABLE syntax of SQL Server
2000 (the ROLLBACK IMMEDIATE and ROLLBACK AFTER options along with
OFFLINE, ONLINE, READ_ONLY, READ_WRITE,
SINGLE_USER, RESTRICTED_USER, MULTI_USER).
Written by: Narayana Vyas Kondreddi
http://vyaskn.tripod.com
Tested on: SQL Server 7.0, Service Pack 3
Date created: October-29-2001 1:30 AM Indian Standard Time
Date modified: October-29-2001 1:30 AM Indian Standard Time
Email: vyaskn@.hotmail.com
Usage: Just run this complete script in the master database to create this
stored procedure. As far as syntax is
concerned, this procedure works very similar to the system stored
procedure sp_dboption. It has an additional
parameter @.wait, which can be used, to wait for a specified number of
seconds, before killing the connections.
The settable database option names need to be specified in full. For
example, the option name 'single' is
considered invalid and 'single user' is considered valid.
To bring pubs database into single user mode:
EXEC sp_dboption2 'pubs', 'single user', 'true'
To bring pubs database into single user mode. Wait for 30 seconds, for
current connections to leave and
start killing the connections after 30 seconds:
EXEC sp_dboption2 'pubs', 'single user', 'true', 30
To bring pubs database into read/write mode:
EXEC sp_dboption2 'pubs', 'read only', 'false'
To bring pubs database into read/write mode. Wait for 30 seconds, for
current connections to leave and
start killing the connections after 30 seconds:
EXEC sp_dboption2 'pubs', 'read only', 'false', 30
****************************************
************************************
*******************************/
DECLARE @.dbid int, @.spid int, @.execstr varchar(15), @.waittime varchar(15),
@.final_chk int
--Only the following options require that, no other connections should
access the database
IF (LOWER(@.optname) IN ('offline', 'read only', 'single user')) AND
(LOWER(@.optvalue) IN('true', 'false'))
BEGIN
--Determining whether to wait, before killing the existing connections
IF @.wait > 0
BEGIN
SET @.waittime = (SELECT CONVERT(varchar, DATEADD(s, @.wait, GETDATE()),
14))
WAITFOR TIME @.waittime --Wait the specified number of seconds
END
SET @.dbid = DB_ID(@.dbname) --Getting the database_id for the specified
database
--Get the lowest spid
TryAgain:
SET @.spid = (SELECT MIN(spid) FROM master..sysprocesses WHERE dbid =
@.dbid)
WHILE @.spid IS NOT NULL
BEGIN
IF @.spid <> @.@.SPID --To avoid the KILL attempt on own connection
BEGIN
SET @.execstr = 'KILL ' + LTRIM(STR(@.spid))
EXEC(@.execstr) --Killing the connection
END
--Get the spid higher than the last spid
SET @.spid = (SELECT MIN(spid) FROM master..sysprocesses WHERE dbid =
@.dbid AND spid > @.spid)
END
END
SET @.final_chk = (SELECT COUNT(spid) FROM master..sysprocesses WHERE dbid =
@.dbid)
IF (@.final_chk = 0) OR (@.final_chk = 1 AND DB_NAME() = @.dbname)
BEGIN
EXEC sp_dboption @.dbname, @.optname, @.optvalue --Calling sp_dboption to
complete the job
END
ELSE
BEGIN
GOTO TryAgain --New connections popped up, or killed connections aren't
cleaned up yet, so try killing them again
END
END
"Ram" <anonymous@.discussions.microsoft.com> wrote in message
news:F0E88B1A-A105-4E24-9BEE-CEB5FC97F532@.microsoft.com...
> Hi ,
> I am using the following code in a batch file to take the back up of a
database. but some times it fails with the message
> "Cannot access the database becuase it is being used by another process."
Is there any way i can force all the connections
> to the database to be dropped using code. Any help will be greatly
appreciated.
> isql -b -S localhost -E -U xyzuser -P xyz -Q "sp_detach_db 'JMS', 'true'"
> copy C:\JJMSdb\JMS_Data.MDF C:\JJMSdb\JMS_Data_2004-03-29_14-54-5933.MDF
> copy C:\JJMSdb\JMS_Log.LDF C:\JJMSdb\JMS_Log_2004-03-29_14-54-5933.LDF
> isql -b -S localhost -E -U xyzuser -P xyzpwd -Q "sp_attach_db @.dbname =
'JMS', @.filename1 = 'C:\JJMSdb\JMS_Data.MDF', @.filename2 =
'C:\JJMSdb\JMS_Log.LDF'"
> Thanks,
> Ram
>
>|||Why would you KILL connections instead of using the "ALTER=20
DATABASE dbname set SINGLE_USER with rollback immediate"=20
syntax that you mention? It is much more clean than=20
issuing a bunch of KILL commands. What if you KILLed a=20
process that was going to take a long time to die or just=20
hung? You could potentially corrupt your database using=20
KILL commands. KILL should be used very carefully. =20
Van

>--Original Message--
>Ram
>This procedure written by Narayana Vyas Kondreddi
>CREATE PROC sp_dboption2
>(
> @.dbname sysname =3D NULL, --Database name
> @.optname varchar(35) =3D NULL, --Option name
> @.optvalue varchar(5) =3D NULL, --Option value,=20
either 'true' or 'false'
> @.wait int =3D NULL --Seconds to wait, before killing=20
the existing
>connections
> )
>AS
>BEGIN
>/ ****************************************
*****************
******************
>********************************
> Copyright =A9 2001 Narayana Vyas Kondreddi. All rights=20
reserved.
>Purpose: The system stored procedure sp_dboption fails to=20
set databases in
>'read only'/'single user'/'offline'
> modes if the database is in use. This procedure works=20
as a wrapper around
>sp_dboption and overcomes that
> limitation by killing all the active connections. You=20
can configure it to
>kill the connections immediately,
> or after waiting for a specified interval. This=20
procedure simulates the
>new ALTER TABLE syntax of SQL Server
> 2000 (the ROLLBACK IMMEDIATE and ROLLBACK AFTER options=20
along with
>OFFLINE, ONLINE, READ_ONLY, READ_WRITE,
> SINGLE_USER, RESTRICTED_USER, MULTI_USER).
>Written by: Narayana Vyas Kondreddi
> http://vyaskn.tripod.com
>Tested on: SQL Server 7.0, Service Pack 3
>Date created: October-29-2001 1:30 AM Indian Standard Time
>Date modified: October-29-2001 1:30 AM Indian Standard=20
Time
>Email: vyaskn@.hotmail.com
>Usage: Just run this complete script in the master=20
database to create this
>stored procedure. As far as syntax is
> concerned, this procedure works very similar to the=20
system stored
>procedure sp_dboption. It has an additional
> parameter @.wait, which can be used, to wait for a=20
specified number of
>seconds, before killing the connections.
> The settable database option names need to be specified=20
in full. For
>example, the option name 'single' is
> considered invalid and 'single user' is considered=20
valid.
> To bring pubs database into single user mode:
> EXEC sp_dboption2 'pubs', 'single user', 'true'
> To bring pubs database into single user mode. Wait for=20
30 seconds, for
>current connections to leave and
> start killing the connections after 30 seconds:
> EXEC sp_dboption2 'pubs', 'single user', 'true', 30
> To bring pubs database into read/write mode:
> EXEC sp_dboption2 'pubs', 'read only', 'false'
> To bring pubs database into read/write mode. Wait for=20
30 seconds, for
>current connections to leave and
> start killing the connections after 30 seconds:
> EXEC sp_dboption2 'pubs', 'read only', 'false', 30
>
> ****************************************
******************
******************
>*******************************/
> DECLARE @.dbid int, @.spid int, @.execstr varchar(15),=20
@.waittime varchar(15),
>@.final_chk int
> --Only the following options require that, no other=20
connections should
>access the database
> IF (LOWER(@.optname) IN ('offline', 'read only', 'single=20
user')) AND
>(LOWER(@.optvalue) IN('true', 'false'))
> BEGIN
> --Determining whether to wait, before killing the=20
existing connections
> IF @.wait > 0
> BEGIN
> SET @.waittime =3D (SELECT CONVERT(varchar, DATEADD(s,=20
@.wait, GETDATE()),
>14))
> WAITFOR TIME @.waittime --Wait the specified number of=20
seconds
> END
> SET @.dbid =3D DB_ID(@.dbname) --Getting the database_id=20
for the specified
>database
> --Get the lowest spid
> TryAgain:
> SET @.spid =3D (SELECT MIN(spid) FROM=20
master..sysprocesses WHERE dbid =3D
>@.dbid)
> WHILE @.spid IS NOT NULL
> BEGIN
> IF @.spid <> @.@.SPID --To avoid the KILL attempt on own=20
connection
> BEGIN
> SET @.execstr =3D 'KILL ' + LTRIM(STR(@.spid))
> EXEC(@.execstr) --Killing the connection
> END
> --Get the spid higher than the last spid
> SET @.spid =3D (SELECT MIN(spid) FROM=20
master..sysprocesses WHERE dbid =3D
>@.dbid AND spid > @.spid)
> END
> END
> SET @.final_chk =3D (SELECT COUNT(spid) FROM=20
master..sysprocesses WHERE dbid =3D
>@.dbid)
> IF (@.final_chk =3D 0) OR (@.final_chk =3D 1 AND DB_NAME() =3D=20
@.dbname)
> BEGIN
> EXEC sp_dboption @.dbname, @.optname, @.optvalue --Calling=20
sp_dboption to
>complete the job
> END
> ELSE
> BEGIN
> GOTO TryAgain --New connections popped up, or killed=20
connections aren't
>cleaned up yet, so try killing them again
> END
>END
>
>
>"Ram" <anonymous@.discussions.microsoft.com> wrote in=20
message
>news:F0E88B1A-A105-4E24-9BEE-CEB5FC97F532@.microsoft.com...
the back up of a
>database. but some times it fails with the message
another process."
>Is there any way i can force all the connections
will be greatly
>appreciated.
Q "sp_detach_db 'JMS', 'true'"
29_14-54-5933.MDF
29_14-54-5933.LDF
Q "sp_attach_db @.dbname =3D
>'JMS', @.filename1 =3D 'C:\JJMSdb\JMS_Data.MDF', @.filename2 =3D
>'C:\JJMSdb\JMS_Log.LDF'"
>
>.
>|||Van
SQL Server 7.0
If you have an active users in your database you will not be able use 'SET
SINGLE USER' mode
Server: Msg 15089, Level 11, State 1, Procedure sp_dboption, Line 400
Cannot change the 'single user' option of a database while another user is
in the database.
"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:15e7101c416a4$9ad58a70$a501280a@.phx
.gbl...
Why would you KILL connections instead of using the "ALTER
DATABASE dbname set SINGLE_USER with rollback immediate"
syntax that you mention? It is much more clean than
issuing a bunch of KILL commands. What if you KILLed a
process that was going to take a long time to die or just
hung? You could potentially corrupt your database using
KILL commands. KILL should be used very carefully.
Van

>--Original Message--
>Ram
>This procedure written by Narayana Vyas Kondreddi
>CREATE PROC sp_dboption2
>(
> @.dbname sysname = NULL, --Database name
> @.optname varchar(35) = NULL, --Option name
> @.optvalue varchar(5) = NULL, --Option value,
either 'true' or 'false'
> @.wait int = NULL --Seconds to wait, before killing
the existing
>connections
> )
>AS
>BEGIN
>/ ****************************************
*****************
******************
>********************************
> Copyright 2001 Narayana Vyas Kondreddi. All rights
reserved.
>Purpose: The system stored procedure sp_dboption fails to
set databases in
>'read only'/'single user'/'offline'
> modes if the database is in use. This procedure works
as a wrapper around
>sp_dboption and overcomes that
> limitation by killing all the active connections. You
can configure it to
>kill the connections immediately,
> or after waiting for a specified interval. This
procedure simulates the
>new ALTER TABLE syntax of SQL Server
> 2000 (the ROLLBACK IMMEDIATE and ROLLBACK AFTER options
along with
>OFFLINE, ONLINE, READ_ONLY, READ_WRITE,
> SINGLE_USER, RESTRICTED_USER, MULTI_USER).
>Written by: Narayana Vyas Kondreddi
> http://vyaskn.tripod.com
>Tested on: SQL Server 7.0, Service Pack 3
>Date created: October-29-2001 1:30 AM Indian Standard Time
>Date modified: October-29-2001 1:30 AM Indian Standard
Time
>Email: vyaskn@.hotmail.com
>Usage: Just run this complete script in the master
database to create this
>stored procedure. As far as syntax is
> concerned, this procedure works very similar to the
system stored
>procedure sp_dboption. It has an additional
> parameter @.wait, which can be used, to wait for a
specified number of
>seconds, before killing the connections.
> The settable database option names need to be specified
in full. For
>example, the option name 'single' is
> considered invalid and 'single user' is considered
valid.
> To bring pubs database into single user mode:
> EXEC sp_dboption2 'pubs', 'single user', 'true'
> To bring pubs database into single user mode. Wait for
30 seconds, for
>current connections to leave and
> start killing the connections after 30 seconds:
> EXEC sp_dboption2 'pubs', 'single user', 'true', 30
> To bring pubs database into read/write mode:
> EXEC sp_dboption2 'pubs', 'read only', 'false'
> To bring pubs database into read/write mode. Wait for
30 seconds, for
>current connections to leave and
> start killing the connections after 30 seconds:
> EXEC sp_dboption2 'pubs', 'read only', 'false', 30
>
> ****************************************
******************
******************
>*******************************/
> DECLARE @.dbid int, @.spid int, @.execstr varchar(15),
@.waittime varchar(15),
>@.final_chk int
> --Only the following options require that, no other
connections should
>access the database
> IF (LOWER(@.optname) IN ('offline', 'read only', 'single
user')) AND
>(LOWER(@.optvalue) IN('true', 'false'))
> BEGIN
> --Determining whether to wait, before killing the
existing connections
> IF @.wait > 0
> BEGIN
> SET @.waittime = (SELECT CONVERT(varchar, DATEADD(s,
@.wait, GETDATE()),
>14))
> WAITFOR TIME @.waittime --Wait the specified number of
seconds
> END
> SET @.dbid = DB_ID(@.dbname) --Getting the database_id
for the specified
>database
> --Get the lowest spid
> TryAgain:
> SET @.spid = (SELECT MIN(spid) FROM
master..sysprocesses WHERE dbid =
>@.dbid)
> WHILE @.spid IS NOT NULL
> BEGIN
> IF @.spid <> @.@.SPID --To avoid the KILL attempt on own
connection
> BEGIN
> SET @.execstr = 'KILL ' + LTRIM(STR(@.spid))
> EXEC(@.execstr) --Killing the connection
> END
> --Get the spid higher than the last spid
> SET @.spid = (SELECT MIN(spid) FROM
master..sysprocesses WHERE dbid =
>@.dbid AND spid > @.spid)
> END
> END
> SET @.final_chk = (SELECT COUNT(spid) FROM
master..sysprocesses WHERE dbid =
>@.dbid)
> IF (@.final_chk = 0) OR (@.final_chk = 1 AND DB_NAME() =
@.dbname)
> BEGIN
> EXEC sp_dboption @.dbname, @.optname, @.optvalue --Calling
sp_dboption to
>complete the job
> END
> ELSE
> BEGIN
> GOTO TryAgain --New connections popped up, or killed
connections aren't
>cleaned up yet, so try killing them again
> END
>END
>
>
>"Ram" <anonymous@.discussions.microsoft.com> wrote in
message
>news:F0E88B1A-A105-4E24-9BEE-CEB5FC97F532@.microsoft.com...
the back up of a
>database. but some times it fails with the message
another process."
>Is there any way i can force all the connections
will be greatly
>appreciated.
Q "sp_detach_db 'JMS', 'true'"
29_14-54-5933.MDF
29_14-54-5933.LDF
Q "sp_attach_db @.dbname =
>'JMS', @.filename1 = 'C:\JJMSdb\JMS_Data.MDF', @.filename2 =
>'C:\JJMSdb\JMS_Log.LDF'"
>
>.
>|||True, SQL 7.0 doesn't support the "with rollback=20
immediate" syntax (or at least I don't think it does). =20
The person who posted this question didn't state what=20
version of SQL they are using. But even if it is version=20
7.0 and my suggestion that uses the "with rollback=20
immediate" doesn't work, it's still not a good idea to=20
have an SP with KILL commands that runs as a scheduled=20
process. KILL commands should be monitored and used=20
carefully to ensure data and database corruption do not=20
occur. It would be better to stop SQL server, start it=20
back up and then put it into single user mode for the=20
detach. This could be done with NET STOP and NET START=20
from the batch file.
A better solution to the whole thing may be to just use=20
the BACKUP DATABASE syntax and not worry about putting it=20
in single user mode or detaching.

>--Original Message--
>Van
>SQL Server 7.0
>If you have an active users in your database you will not=20
be able use 'SET
>SINGLE USER' mode
>Server: Msg 15089, Level 11, State 1, Procedure=20
sp_dboption, Line 400
>Cannot change the 'single user' option of a database=20
while another user is
>in the database.
>
>"Van Jones" <anonymous@.discussions.microsoft.com> wrote=20
in message
> news:15e7101c416a4$9ad58a70$a501280a@.phx
.gbl...
>Why would you KILL connections instead of using the "ALTER
>DATABASE dbname set SINGLE_USER with rollback immediate"
>syntax that you mention? It is much more clean than
>issuing a bunch of KILL commands. What if you KILLed a
>process that was going to take a long time to die or just
>hung? You could potentially corrupt your database using
>KILL commands. KILL should be used very carefully.
>Van
>
>either 'true' or 'false'
>the existing
*
>******************
>reserved.
>set databases in
>as a wrapper around
>can configure it to
>procedure simulates the
>along with
Time
>Time
>database to create this
>system stored
>specified number of
>in full. For
>valid.
>30 seconds, for
>30 seconds, for
*
>******************
>@.waittime varchar(15),
>connections should
>user')) AND
>existing connections
>@.wait, GETDATE()),
>seconds
>for the specified
>master..sysprocesses WHERE dbid =3D
>connection
>master..sysprocesses WHERE dbid =3D
>master..sysprocesses WHERE dbid =3D
>@.dbname)
>sp_dboption to
>connections aren't
>message
CEB5FC97F532@.microsoft.com...
>the back up of a
>another process."
>will be greatly
>Q "sp_detach_db 'JMS', 'true'"
>29_14-54-5933.MDF
>29_14-54-5933.LDF
>Q "sp_attach_db @.dbname =3D
=3D
>
>.
>

Drivers of Sqlserver 200

hi

I want to know file's list of SQLSERVER2000 Driver

I write a program , it connect to sqlserver2000 in Eternet and

client side of software install in every computer but i have to install sqlserver2000 in every client pc

how can i connect to SQLServer2000 Server (in a network) with out installing Sqlserver2000 ?

Tanks

Hi,

you just have to install the MDAC Components to access SQL Server.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Depending on your exact needs in addition to MDAC you may also want to try SQL Server Native Client, JDBC, or ADO.NET.

Check out http://msdn.microsoft.com/data for more info.

drive is corupted

Hi,
the drive with the data file is corupted, however the
drive where the transaction log is located is fine.
What is the best aproach for recovering the db ?
Can I restore the db from the last full db backup and
then backup the curent log and apply to it ?
The only thing that has happened beetween the full db
backup and crash is one transaction log backup.
Any view is apreciated!Mirna,
> What is the best aproach for recovering the db ?
Are you currently dong log backups? What is the recovery model for the database. If it is simple,
then you can only recovery up to the latest database backup. If it is full and you also do log
backup, do a log backup now using the NO_TRUNCATE parameter and then restore the latest database
backup and all subsequent log backups (including this last one).
> Can I restore the db from the last full db backup and
> then backup the curent log and apply to it ?
No, as soon as you restored you lost the stuff in the log file. Always start with performing a log
backup as I explained above.
> The only thing that has happened beetween the full db
> backup and crash is one transaction log backup.
Then it seems that you are in good shape. Do this last log backup and then the restore stuff.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mirna" <anonymous@.discussions.microsoft.com> wrote in message
news:09c601c3b8d3$d5a16400$a501280a@.phx.gbl...
> Hi,
> the drive with the data file is corupted, however the
> drive where the transaction log is located is fine.
> What is the best aproach for recovering the db ?
> Can I restore the db from the last full db backup and
> then backup the curent log and apply to it ?
> The only thing that has happened beetween the full db
> backup and crash is one transaction log backup.
> Any view is apreciated!|||Thanks Tibor for a quick answer!
The recovery model for the database is full.
mdf file is not accesible as well as the last transaction
log backup( they reside on the same drive and they are not
backed up to a tape before the failure ).
I can not perform the transaction log backup since the
problem is actually OS not the hardware.It looks like that
I will have to go to a last full db backup unless I can
somehow apply the transaction log (ldf file).
In other words I have full db backup of Mydb.BAK and ldf
file of the same db. Since the transaction log backup
was performed once I am wondering if that made the log(ldf)
invalid. I am backing the log as a part of the db
maintenance plan.
Thanks in advance!
>--Original Message--
>Mirna,
>> What is the best aproach for recovering the db ?
>Are you currently dong log backups? What is the recovery
model for the database. If it is simple,
>then you can only recovery up to the latest database
backup. If it is full and you also do log
>backup, do a log backup now using the NO_TRUNCATE
parameter and then restore the latest database
>backup and all subsequent log backups (including this
last one).
>
>> Can I restore the db from the last full db backup and
>> then backup the curent log and apply to it ?
>No, as soon as you restored you lost the stuff in the log
file. Always start with performing a log
>backup as I explained above.
>
>> The only thing that has happened beetween the full db
>> backup and crash is one transaction log backup.
>Then it seems that you are in good shape. Do this last
log backup and then the restore stuff.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Mirna" <anonymous@.discussions.microsoft.com> wrote in
message
>news:09c601c3b8d3$d5a16400$a501280a@.phx.gbl...
>> Hi,
>> the drive with the data file is corupted, however the
>> drive where the transaction log is located is fine.
>> What is the best aproach for recovering the db ?
>> Can I restore the db from the last full db backup and
>> then backup the curent log and apply to it ?
>> The only thing that has happened beetween the full db
>> backup and crash is one transaction log backup.
>> Any view is apreciated!
>
>.
>|||> The recovery model for the database is full.
> mdf file is not accesible as well as the last transaction
> log backup
Ouch. Then you would not be able to use a log backup even if you managed to do one at this point in
time. This is because the log is emptied each time you do a log backup, so now you are in a
situation where you did a log backup and that is lost. You have a "hole" in your sequence of log
records.
If it weren't for that prior log backup is lost, you could actually product this last one log backup
event though the OS install is damaged (or if SQL Server install is damaged). You would "fake"
another database on another SQL Server, slide in that log file (ldf) into that install and then do
the log backup using NO_TRUNCATE on that installation. There's a KB article on that subject. This is
only FYI as this unfortunately won't help you because of that missing prior log backup.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mirna" <anonymous@.discussions.microsoft.com> wrote in message
news:0ad701c3b8df$3be49600$a401280a@.phx.gbl...
> Thanks Tibor for a quick answer!
> The recovery model for the database is full.
> mdf file is not accesible as well as the last transaction
> log backup( they reside on the same drive and they are not
> backed up to a tape before the failure ).
> I can not perform the transaction log backup since the
> problem is actually OS not the hardware.It looks like that
> I will have to go to a last full db backup unless I can
> somehow apply the transaction log (ldf file).
> In other words I have full db backup of Mydb.BAK and ldf
> file of the same db. Since the transaction log backup
> was performed once I am wondering if that made the log(ldf)
> invalid. I am backing the log as a part of the db
> maintenance plan.
> Thanks in advance!
>
> >--Original Message--
> >Mirna,
> >
> >> What is the best aproach for recovering the db ?
> >
> >Are you currently dong log backups? What is the recovery
> model for the database. If it is simple,
> >then you can only recovery up to the latest database
> backup. If it is full and you also do log
> >backup, do a log backup now using the NO_TRUNCATE
> parameter and then restore the latest database
> >backup and all subsequent log backups (including this
> last one).
> >
> >
> >> Can I restore the db from the last full db backup and
> >> then backup the curent log and apply to it ?
> >
> >No, as soon as you restored you lost the stuff in the log
> file. Always start with performing a log
> >backup as I explained above.
> >
> >
> >> The only thing that has happened beetween the full db
> >> backup and crash is one transaction log backup.
> >
> >Then it seems that you are in good shape. Do this last
> log backup and then the restore stuff.
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at: http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> >"Mirna" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:09c601c3b8d3$d5a16400$a501280a@.phx.gbl...
> >> Hi,
> >>
> >> the drive with the data file is corupted, however the
> >> drive where the transaction log is located is fine.
> >>
> >> What is the best aproach for recovering the db ?
> >> Can I restore the db from the last full db backup and
> >> then backup the curent log and apply to it ?
> >> The only thing that has happened beetween the full db
> >> backup and crash is one transaction log backup.
> >>
> >> Any view is apreciated!
> >
> >
> >.
> >|||Thanks Tibor,
I understand what you are saying.. too bad that we will
have to go to a last full db backup. Thank You again!
>--Original Message--
>> The recovery model for the database is full.
>> mdf file is not accesible as well as the last
transaction
>> log backup
>Ouch. Then you would not be able to use a log backup even
if you managed to do one at this point in
>time. This is because the log is emptied each time you do
a log backup, so now you are in a
>situation where you did a log backup and that is lost.
You have a "hole" in your sequence of log
>records.
>If it weren't for that prior log backup is lost, you
could actually product this last one log backup
>event though the OS install is damaged (or if SQL Server
install is damaged). You would "fake"
>another database on another SQL Server, slide in that log
file (ldf) into that install and then do
>the log backup using NO_TRUNCATE on that installation.
There's a KB article on that subject. This is
>only FYI as this unfortunately won't help you because of
that missing prior log backup.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Mirna" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0ad701c3b8df$3be49600$a401280a@.phx.gbl...
>> Thanks Tibor for a quick answer!
>> The recovery model for the database is full.
>> mdf file is not accesible as well as the last
transaction
>> log backup( they reside on the same drive and they are
not
>> backed up to a tape before the failure ).
>> I can not perform the transaction log backup since the
>> problem is actually OS not the hardware.It looks like
that
>> I will have to go to a last full db backup unless I can
>> somehow apply the transaction log (ldf file).
>> In other words I have full db backup of Mydb.BAK and ldf
>> file of the same db. Since the transaction log backup
>> was performed once I am wondering if that made the log
(ldf)
>> invalid. I am backing the log as a part of the db
>> maintenance plan.
>> Thanks in advance!
>>
>> >--Original Message--
>> >Mirna,
>> >
>> >> What is the best aproach for recovering the db ?
>> >
>> >Are you currently dong log backups? What is the
recovery
>> model for the database. If it is simple,
>> >then you can only recovery up to the latest database
>> backup. If it is full and you also do log
>> >backup, do a log backup now using the NO_TRUNCATE
>> parameter and then restore the latest database
>> >backup and all subsequent log backups (including this
>> last one).
>> >
>> >
>> >> Can I restore the db from the last full db backup
and
>> >> then backup the curent log and apply to it ?
>> >
>> >No, as soon as you restored you lost the stuff in the
log
>> file. Always start with performing a log
>> >backup as I explained above.
>> >
>> >
>> >> The only thing that has happened beetween the full db
>> >> backup and crash is one transaction log backup.
>> >
>> >Then it seems that you are in good shape. Do this last
>> log backup and then the restore stuff.
>> >--
>> >Tibor Karaszi, SQL Server MVP
>> >Archive at: http://groups.google.com/groups?
>> oi=djq&as_ugroup=microsoft.public.sqlserver
>> >
>> >
>> >"Mirna" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:09c601c3b8d3$d5a16400$a501280a@.phx.gbl...
>> >> Hi,
>> >>
>> >> the drive with the data file is corupted, however the
>> >> drive where the transaction log is located is fine.
>> >>
>> >> What is the best aproach for recovering the db ?
>> >> Can I restore the db from the last full db backup
and
>> >> then backup the curent log and apply to it ?
>> >> The only thing that has happened beetween the full db
>> >> backup and crash is one transaction log backup.
>> >>
>> >> Any view is apreciated!
>> >
>> >
>> >.
>> >
>
>.
>|||Is there a way that ldf file can be used to recover the
database if the transaction log backup and .mdf file is
lost ? Does Microsoft has any utility that can recover
what has been flushed to mdf file during the transaction
log backup ?
Any view is apreciated
>--Original Message--
>Thanks Tibor,
>I understand what you are saying.. too bad that we will
>have to go to a last full db backup. Thank You again!
>>--Original Message--
>> The recovery model for the database is full.
>> mdf file is not accesible as well as the last
>transaction
>> log backup
>>Ouch. Then you would not be able to use a log backup
even
>if you managed to do one at this point in
>>time. This is because the log is emptied each time you
do
>a log backup, so now you are in a
>>situation where you did a log backup and that is lost.
>You have a "hole" in your sequence of log
>>records.
>>If it weren't for that prior log backup is lost, you
>could actually product this last one log backup
>>event though the OS install is damaged (or if SQL Server
>install is damaged). You would "fake"
>>another database on another SQL Server, slide in that
log
>file (ldf) into that install and then do
>>the log backup using NO_TRUNCATE on that installation.
>There's a KB article on that subject. This is
>>only FYI as this unfortunately won't help you because of
>that missing prior log backup.
>>--
>>Tibor Karaszi, SQL Server MVP
>>Archive at: http://groups.google.com/groups?
>oi=djq&as_ugroup=microsoft.public.sqlserver
>>
>>"Mirna" <anonymous@.discussions.microsoft.com> wrote in
>message
>>news:0ad701c3b8df$3be49600$a401280a@.phx.gbl...
>> Thanks Tibor for a quick answer!
>> The recovery model for the database is full.
>> mdf file is not accesible as well as the last
>transaction
>> log backup( they reside on the same drive and they are
>not
>> backed up to a tape before the failure ).
>> I can not perform the transaction log backup since the
>> problem is actually OS not the hardware.It looks like
>that
>> I will have to go to a last full db backup unless I can
>> somehow apply the transaction log (ldf file).
>> In other words I have full db backup of Mydb.BAK and
ldf
>> file of the same db. Since the transaction log backup
>> was performed once I am wondering if that made the log
>(ldf)
>> invalid. I am backing the log as a part of the db
>> maintenance plan.
>> Thanks in advance!
>>
>> >--Original Message--
>> >Mirna,
>> >
>> >> What is the best aproach for recovering the db ?
>> >
>> >Are you currently dong log backups? What is the
>recovery
>> model for the database. If it is simple,
>> >then you can only recovery up to the latest database
>> backup. If it is full and you also do log
>> >backup, do a log backup now using the NO_TRUNCATE
>> parameter and then restore the latest database
>> >backup and all subsequent log backups (including this
>> last one).
>> >
>> >
>> >> Can I restore the db from the last full db backup
>and
>> >> then backup the curent log and apply to it ?
>> >
>> >No, as soon as you restored you lost the stuff in the
>log
>> file. Always start with performing a log
>> >backup as I explained above.
>> >
>> >
>> >> The only thing that has happened beetween the full
db
>> >> backup and crash is one transaction log backup.
>> >
>> >Then it seems that you are in good shape. Do this last
>> log backup and then the restore stuff.
>> >--
>> >Tibor Karaszi, SQL Server MVP
>> >Archive at: http://groups.google.com/groups?
>> oi=djq&as_ugroup=microsoft.public.sqlserver
>> >
>> >
>> >"Mirna" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:09c601c3b8d3$d5a16400$a501280a@.phx.gbl...
>> >> Hi,
>> >>
>> >> the drive with the data file is corupted, however
the
>> >> drive where the transaction log is located is fine.
>> >>
>> >> What is the best aproach for recovering the db ?
>> >> Can I restore the db from the last full db backup
>and
>> >> then backup the curent log and apply to it ?
>> >> The only thing that has happened beetween the full
db
>> >> backup and crash is one transaction log backup.
>> >>
>> >> Any view is apreciated!
>> >
>> >
>> >.
>> >
>>
>>.
>.
>|||> Is there a way that ldf file can be used to recover the
> database if the transaction log backup and .mdf file is
> lost ?
No. As I described, you have a missing log backup.
> Does Microsoft has any utility that can recover
> what has been flushed to mdf file during the transaction
> log backup ?
This is not how things work ("flushed to mdf file"). Read about transaction log handling and backup
and restore in Books Online.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mirna" <anonymous@.discussions.microsoft.com> wrote in message
news:b43501c3b8f1$7cf00640$a601280a@.phx.gbl...
> Is there a way that ldf file can be used to recover the
> database if the transaction log backup and .mdf file is
> lost ? Does Microsoft has any utility that can recover
> what has been flushed to mdf file during the transaction
> log backup ?
> Any view is apreciated
> >--Original Message--
> >Thanks Tibor,
> >
> >I understand what you are saying.. too bad that we will
> >have to go to a last full db backup. Thank You again!
> >
> >>--Original Message--
> >> The recovery model for the database is full.
> >> mdf file is not accesible as well as the last
> >transaction
> >> log backup
> >>
> >>Ouch. Then you would not be able to use a log backup
> even
> >if you managed to do one at this point in
> >>time. This is because the log is emptied each time you
> do
> >a log backup, so now you are in a
> >>situation where you did a log backup and that is lost.
> >You have a "hole" in your sequence of log
> >>records.
> >>
> >>If it weren't for that prior log backup is lost, you
> >could actually product this last one log backup
> >>event though the OS install is damaged (or if SQL Server
> >install is damaged). You would "fake"
> >>another database on another SQL Server, slide in that
> log
> >file (ldf) into that install and then do
> >>the log backup using NO_TRUNCATE on that installation.
> >There's a KB article on that subject. This is
> >>only FYI as this unfortunately won't help you because of
> >that missing prior log backup.
> >>
> >>--
> >>Tibor Karaszi, SQL Server MVP
> >>Archive at: http://groups.google.com/groups?
> >oi=djq&as_ugroup=microsoft.public.sqlserver
> >>
> >>
> >>"Mirna" <anonymous@.discussions.microsoft.com> wrote in
> >message
> >>news:0ad701c3b8df$3be49600$a401280a@.phx.gbl...
> >> Thanks Tibor for a quick answer!
> >>
> >> The recovery model for the database is full.
> >> mdf file is not accesible as well as the last
> >transaction
> >> log backup( they reside on the same drive and they are
> >not
> >> backed up to a tape before the failure ).
> >> I can not perform the transaction log backup since the
> >> problem is actually OS not the hardware.It looks like
> >that
> >> I will have to go to a last full db backup unless I can
> >> somehow apply the transaction log (ldf file).
> >> In other words I have full db backup of Mydb.BAK and
> ldf
> >> file of the same db. Since the transaction log backup
> >> was performed once I am wondering if that made the log
> >(ldf)
> >> invalid. I am backing the log as a part of the db
> >> maintenance plan.
> >>
> >> Thanks in advance!
> >>
> >>
> >> >--Original Message--
> >> >Mirna,
> >> >
> >> >> What is the best aproach for recovering the db ?
> >> >
> >> >Are you currently dong log backups? What is the
> >recovery
> >> model for the database. If it is simple,
> >> >then you can only recovery up to the latest database
> >> backup. If it is full and you also do log
> >> >backup, do a log backup now using the NO_TRUNCATE
> >> parameter and then restore the latest database
> >> >backup and all subsequent log backups (including this
> >> last one).
> >> >
> >> >
> >> >> Can I restore the db from the last full db backup
> >and
> >> >> then backup the curent log and apply to it ?
> >> >
> >> >No, as soon as you restored you lost the stuff in the
> >log
> >> file. Always start with performing a log
> >> >backup as I explained above.
> >> >
> >> >
> >> >> The only thing that has happened beetween the full
> db
> >> >> backup and crash is one transaction log backup.
> >> >
> >> >Then it seems that you are in good shape. Do this last
> >> log backup and then the restore stuff.
> >> >--
> >> >Tibor Karaszi, SQL Server MVP
> >> >Archive at: http://groups.google.com/groups?
> >> oi=djq&as_ugroup=microsoft.public.sqlserver
> >> >
> >> >
> >> >"Mirna" <anonymous@.discussions.microsoft.com> wrote in
> >> message
> >> >news:09c601c3b8d3$d5a16400$a501280a@.phx.gbl...
> >> >> Hi,
> >> >>
> >> >> the drive with the data file is corupted, however
> the
> >> >> drive where the transaction log is located is fine.
> >> >>
> >> >> What is the best aproach for recovering the db ?
> >> >> Can I restore the db from the last full db backup
> >and
> >> >> then backup the curent log and apply to it ?
> >> >> The only thing that has happened beetween the full
> db
> >> >> backup and crash is one transaction log backup.
> >> >>
> >> >> Any view is apreciated!
> >> >
> >> >
> >> >.
> >> >
> >>
> >>
> >>.
> >>
> >.
> >