Hi, is there a software that can dump the SQL result into a file or to another database? I am using the MSSQL Server and I am accessing it remotely. Is there a program where I can issue an SQL statement and then I can dump the result into a file (in SQL inserts or just plain CSV).
Is there a free version?
Thanks!if you are using thr qery analyzer go to query-->results to file|||Hi, is there a software that can dump the SQL result into a file or to another database? I am using the MSSQL Server and I am accessing it remotely. Is there a program where I can issue an SQL statement and then I can dump the result into a file (in SQL inserts or just plain CSV).
Is there a free version?
Thanks!
You can try this -
From command prompt:
osql.exe -S YourServerName -U sa -P secretcode -Q "EXEC sp_who2" -o "E:\output.txt"
From T-SQL:
EXEC master..xp_cmdshell 'osql.exe -S YourServerName -U sa -P secretcode -Q "EXEC sp_who2" -o "E:\output.txt"'
Showing posts with label dump. Show all posts
Showing posts with label dump. Show all posts
Wednesday, March 21, 2012
Dumping SQL Server Variables
In Oracle and other databases there is a way to dump all the values that hav
e
been set using the SET command. For example, SET ROWCOUNT.
Is there a way to do this in SQL Server?
The reason I ask is because somewhere in a stream of some 1000 sql files
someone using some SET parameters that are scewing up some down stream files
.
In one case, setting SET ROWCOUNT 0 fixes the problem. We are doing a select
insert from one table into another and only 1995 rows are being inserted whe
n
we know there are 4979. Using SET ROWCOUNT 0 clears up the problem but we
want to find out where along the way things are getting screwed up.
Searching for SET ROWCOUNT has not yielded any result.
What I would like to do is dump all the SET variables to a file or screen or
someplace before the problem file runs.
As an FYI, the files are all being executed via SQL-DMO but I do not believe
there any internal SQL-DMO limitationsI don't think there's a way to get this value - it's a property of the
session that doesn't seem to be stored in any table. You can trace the
workload with SQL Profiler and look for SET ROWCOUNT.
Steve Kass
Drew University
enzo_maini@.dotnetfan.net wrote:
>In Oracle and other databases there is a way to dump all the values that ha
ve
>been set using the SET command. For example, SET ROWCOUNT.
>Is there a way to do this in SQL Server?
>The reason I ask is because somewhere in a stream of some 1000 sql files
>someone using some SET parameters that are scewing up some down stream file
s.
> In one case, setting SET ROWCOUNT 0 fixes the problem. We are doing a sele
ct
>insert from one table into another and only 1995 rows are being inserted wh
en
>we know there are 4979. Using SET ROWCOUNT 0 clears up the problem but we
>want to find out where along the way things are getting screwed up.
>Searching for SET ROWCOUNT has not yielded any result.
>What I would like to do is dump all the SET variables to a file or screen o
r
>someplace before the problem file runs.
>As an FYI, the files are all being executed via SQL-DMO but I do not believ
e
>there any internal SQL-DMO limitations
>|||Hi Enzo
DBCC USEROPTIONS will show you the settings (including the SET ROWCOUNT
value) for the current connection.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"enzo_maini@.dotnetfan.net"
<enzo_maini@.dotnetfan.net@.discussions.microsoft.com> wrote in message
news:074E8B77-B9A9-4959-A962-CD09660B7793@.microsoft.com...
> In Oracle and other databases there is a way to dump all the values that
> have
> been set using the SET command. For example, SET ROWCOUNT.
> Is there a way to do this in SQL Server?
> The reason I ask is because somewhere in a stream of some 1000 sql files
> someone using some SET parameters that are scewing up some down stream
> files.
> In one case, setting SET ROWCOUNT 0 fixes the problem. We are doing a
> select
> insert from one table into another and only 1995 rows are being inserted
> when
> we know there are 4979. Using SET ROWCOUNT 0 clears up the problem but we
> want to find out where along the way things are getting screwed up.
> Searching for SET ROWCOUNT has not yielded any result.
> What I would like to do is dump all the SET variables to a file or screen
> or
> someplace before the problem file runs.
> As an FYI, the files are all being executed via SQL-DMO but I do not
> believe
> there any internal SQL-DMO limitations|||Thanks for the correction, Kalen!
SK
Kalen Delaney wrote:
>Hi Enzo
>DBCC USEROPTIONS will show you the settings (including the SET ROWCOUNT
>value) for the current connection.
>
>sql
e
been set using the SET command. For example, SET ROWCOUNT.
Is there a way to do this in SQL Server?
The reason I ask is because somewhere in a stream of some 1000 sql files
someone using some SET parameters that are scewing up some down stream files
.
In one case, setting SET ROWCOUNT 0 fixes the problem. We are doing a select
insert from one table into another and only 1995 rows are being inserted whe
n
we know there are 4979. Using SET ROWCOUNT 0 clears up the problem but we
want to find out where along the way things are getting screwed up.
Searching for SET ROWCOUNT has not yielded any result.
What I would like to do is dump all the SET variables to a file or screen or
someplace before the problem file runs.
As an FYI, the files are all being executed via SQL-DMO but I do not believe
there any internal SQL-DMO limitationsI don't think there's a way to get this value - it's a property of the
session that doesn't seem to be stored in any table. You can trace the
workload with SQL Profiler and look for SET ROWCOUNT.
Steve Kass
Drew University
enzo_maini@.dotnetfan.net wrote:
>In Oracle and other databases there is a way to dump all the values that ha
ve
>been set using the SET command. For example, SET ROWCOUNT.
>Is there a way to do this in SQL Server?
>The reason I ask is because somewhere in a stream of some 1000 sql files
>someone using some SET parameters that are scewing up some down stream file
s.
> In one case, setting SET ROWCOUNT 0 fixes the problem. We are doing a sele
ct
>insert from one table into another and only 1995 rows are being inserted wh
en
>we know there are 4979. Using SET ROWCOUNT 0 clears up the problem but we
>want to find out where along the way things are getting screwed up.
>Searching for SET ROWCOUNT has not yielded any result.
>What I would like to do is dump all the SET variables to a file or screen o
r
>someplace before the problem file runs.
>As an FYI, the files are all being executed via SQL-DMO but I do not believ
e
>there any internal SQL-DMO limitations
>|||Hi Enzo
DBCC USEROPTIONS will show you the settings (including the SET ROWCOUNT
value) for the current connection.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"enzo_maini@.dotnetfan.net"
<enzo_maini@.dotnetfan.net@.discussions.microsoft.com> wrote in message
news:074E8B77-B9A9-4959-A962-CD09660B7793@.microsoft.com...
> In Oracle and other databases there is a way to dump all the values that
> have
> been set using the SET command. For example, SET ROWCOUNT.
> Is there a way to do this in SQL Server?
> The reason I ask is because somewhere in a stream of some 1000 sql files
> someone using some SET parameters that are scewing up some down stream
> files.
> In one case, setting SET ROWCOUNT 0 fixes the problem. We are doing a
> select
> insert from one table into another and only 1995 rows are being inserted
> when
> we know there are 4979. Using SET ROWCOUNT 0 clears up the problem but we
> want to find out where along the way things are getting screwed up.
> Searching for SET ROWCOUNT has not yielded any result.
> What I would like to do is dump all the SET variables to a file or screen
> or
> someplace before the problem file runs.
> As an FYI, the files are all being executed via SQL-DMO but I do not
> believe
> there any internal SQL-DMO limitations|||Thanks for the correction, Kalen!
SK
Kalen Delaney wrote:
>Hi Enzo
>DBCC USEROPTIONS will show you the settings (including the SET ROWCOUNT
>value) for the current connection.
>
>sql
Dumping SQL Server Variables
In Oracle and other databases there is a way to dump all the values that have
been set using the SET command. For example, SET ROWCOUNT.
Is there a way to do this in SQL Server?
The reason I ask is because somewhere in a stream of some 1000 sql files
someone using some SET parameters that are scewing up some down stream files.
In one case, setting SET ROWCOUNT 0 fixes the problem. We are doing a select
insert from one table into another and only 1995 rows are being inserted when
we know there are 4979. Using SET ROWCOUNT 0 clears up the problem but we
want to find out where along the way things are getting screwed up.
Searching for SET ROWCOUNT has not yielded any result.
What I would like to do is dump all the SET variables to a file or screen or
someplace before the problem file runs.
As an FYI, the files are all being executed via SQL-DMO but I do not believe
there any internal SQL-DMO limitations
I don't think there's a way to get this value - it's a property of the
session that doesn't seem to be stored in any table. You can trace the
workload with SQL Profiler and look for SET ROWCOUNT.
Steve Kass
Drew University
enzo_maini@.dotnetfan.net wrote:
>In Oracle and other databases there is a way to dump all the values that have
>been set using the SET command. For example, SET ROWCOUNT.
>Is there a way to do this in SQL Server?
>The reason I ask is because somewhere in a stream of some 1000 sql files
>someone using some SET parameters that are scewing up some down stream files.
> In one case, setting SET ROWCOUNT 0 fixes the problem. We are doing a select
>insert from one table into another and only 1995 rows are being inserted when
>we know there are 4979. Using SET ROWCOUNT 0 clears up the problem but we
>want to find out where along the way things are getting screwed up.
>Searching for SET ROWCOUNT has not yielded any result.
>What I would like to do is dump all the SET variables to a file or screen or
>someplace before the problem file runs.
>As an FYI, the files are all being executed via SQL-DMO but I do not believe
>there any internal SQL-DMO limitations
>
|||Hi Enzo
DBCC USEROPTIONS will show you the settings (including the SET ROWCOUNT
value) for the current connection.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"enzo_maini@.dotnetfan.net"
<enzo_maini@.dotnetfan.net@.discussions.microsoft.co m> wrote in message
news:074E8B77-B9A9-4959-A962-CD09660B7793@.microsoft.com...
> In Oracle and other databases there is a way to dump all the values that
> have
> been set using the SET command. For example, SET ROWCOUNT.
> Is there a way to do this in SQL Server?
> The reason I ask is because somewhere in a stream of some 1000 sql files
> someone using some SET parameters that are scewing up some down stream
> files.
> In one case, setting SET ROWCOUNT 0 fixes the problem. We are doing a
> select
> insert from one table into another and only 1995 rows are being inserted
> when
> we know there are 4979. Using SET ROWCOUNT 0 clears up the problem but we
> want to find out where along the way things are getting screwed up.
> Searching for SET ROWCOUNT has not yielded any result.
> What I would like to do is dump all the SET variables to a file or screen
> or
> someplace before the problem file runs.
> As an FYI, the files are all being executed via SQL-DMO but I do not
> believe
> there any internal SQL-DMO limitations
|||Thanks for the correction, Kalen!
SK
Kalen Delaney wrote:
>Hi Enzo
>DBCC USEROPTIONS will show you the settings (including the SET ROWCOUNT
>value) for the current connection.
>
>
been set using the SET command. For example, SET ROWCOUNT.
Is there a way to do this in SQL Server?
The reason I ask is because somewhere in a stream of some 1000 sql files
someone using some SET parameters that are scewing up some down stream files.
In one case, setting SET ROWCOUNT 0 fixes the problem. We are doing a select
insert from one table into another and only 1995 rows are being inserted when
we know there are 4979. Using SET ROWCOUNT 0 clears up the problem but we
want to find out where along the way things are getting screwed up.
Searching for SET ROWCOUNT has not yielded any result.
What I would like to do is dump all the SET variables to a file or screen or
someplace before the problem file runs.
As an FYI, the files are all being executed via SQL-DMO but I do not believe
there any internal SQL-DMO limitations
I don't think there's a way to get this value - it's a property of the
session that doesn't seem to be stored in any table. You can trace the
workload with SQL Profiler and look for SET ROWCOUNT.
Steve Kass
Drew University
enzo_maini@.dotnetfan.net wrote:
>In Oracle and other databases there is a way to dump all the values that have
>been set using the SET command. For example, SET ROWCOUNT.
>Is there a way to do this in SQL Server?
>The reason I ask is because somewhere in a stream of some 1000 sql files
>someone using some SET parameters that are scewing up some down stream files.
> In one case, setting SET ROWCOUNT 0 fixes the problem. We are doing a select
>insert from one table into another and only 1995 rows are being inserted when
>we know there are 4979. Using SET ROWCOUNT 0 clears up the problem but we
>want to find out where along the way things are getting screwed up.
>Searching for SET ROWCOUNT has not yielded any result.
>What I would like to do is dump all the SET variables to a file or screen or
>someplace before the problem file runs.
>As an FYI, the files are all being executed via SQL-DMO but I do not believe
>there any internal SQL-DMO limitations
>
|||Hi Enzo
DBCC USEROPTIONS will show you the settings (including the SET ROWCOUNT
value) for the current connection.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"enzo_maini@.dotnetfan.net"
<enzo_maini@.dotnetfan.net@.discussions.microsoft.co m> wrote in message
news:074E8B77-B9A9-4959-A962-CD09660B7793@.microsoft.com...
> In Oracle and other databases there is a way to dump all the values that
> have
> been set using the SET command. For example, SET ROWCOUNT.
> Is there a way to do this in SQL Server?
> The reason I ask is because somewhere in a stream of some 1000 sql files
> someone using some SET parameters that are scewing up some down stream
> files.
> In one case, setting SET ROWCOUNT 0 fixes the problem. We are doing a
> select
> insert from one table into another and only 1995 rows are being inserted
> when
> we know there are 4979. Using SET ROWCOUNT 0 clears up the problem but we
> want to find out where along the way things are getting screwed up.
> Searching for SET ROWCOUNT has not yielded any result.
> What I would like to do is dump all the SET variables to a file or screen
> or
> someplace before the problem file runs.
> As an FYI, the files are all being executed via SQL-DMO but I do not
> believe
> there any internal SQL-DMO limitations
|||Thanks for the correction, Kalen!
SK
Kalen Delaney wrote:
>Hi Enzo
>DBCC USEROPTIONS will show you the settings (including the SET ROWCOUNT
>value) for the current connection.
>
>
Dumping SQL Server Variables
In Oracle and other databases there is a way to dump all the values that have
been set using the SET command. For example, SET ROWCOUNT.
Is there a way to do this in SQL Server?
The reason I ask is because somewhere in a stream of some 1000 sql files
someone using some SET parameters that are scewing up some down stream files.
In one case, setting SET ROWCOUNT 0 fixes the problem. We are doing a select
insert from one table into another and only 1995 rows are being inserted when
we know there are 4979. Using SET ROWCOUNT 0 clears up the problem but we
want to find out where along the way things are getting screwed up.
Searching for SET ROWCOUNT has not yielded any result.
What I would like to do is dump all the SET variables to a file or screen or
someplace before the problem file runs.
As an FYI, the files are all being executed via SQL-DMO but I do not believe
there any internal SQL-DMO limitationsI don't think there's a way to get this value - it's a property of the
session that doesn't seem to be stored in any table. You can trace the
workload with SQL Profiler and look for SET ROWCOUNT.
Steve Kass
Drew University
enzo_maini@.dotnetfan.net wrote:
>In Oracle and other databases there is a way to dump all the values that have
>been set using the SET command. For example, SET ROWCOUNT.
>Is there a way to do this in SQL Server?
>The reason I ask is because somewhere in a stream of some 1000 sql files
>someone using some SET parameters that are scewing up some down stream files.
> In one case, setting SET ROWCOUNT 0 fixes the problem. We are doing a select
>insert from one table into another and only 1995 rows are being inserted when
>we know there are 4979. Using SET ROWCOUNT 0 clears up the problem but we
>want to find out where along the way things are getting screwed up.
>Searching for SET ROWCOUNT has not yielded any result.
>What I would like to do is dump all the SET variables to a file or screen or
>someplace before the problem file runs.
>As an FYI, the files are all being executed via SQL-DMO but I do not believe
>there any internal SQL-DMO limitations
>|||Hi Enzo
DBCC USEROPTIONS will show you the settings (including the SET ROWCOUNT
value) for the current connection.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"enzo_maini@.dotnetfan.net"
<enzo_maini@.dotnetfan.net@.discussions.microsoft.com> wrote in message
news:074E8B77-B9A9-4959-A962-CD09660B7793@.microsoft.com...
> In Oracle and other databases there is a way to dump all the values that
> have
> been set using the SET command. For example, SET ROWCOUNT.
> Is there a way to do this in SQL Server?
> The reason I ask is because somewhere in a stream of some 1000 sql files
> someone using some SET parameters that are scewing up some down stream
> files.
> In one case, setting SET ROWCOUNT 0 fixes the problem. We are doing a
> select
> insert from one table into another and only 1995 rows are being inserted
> when
> we know there are 4979. Using SET ROWCOUNT 0 clears up the problem but we
> want to find out where along the way things are getting screwed up.
> Searching for SET ROWCOUNT has not yielded any result.
> What I would like to do is dump all the SET variables to a file or screen
> or
> someplace before the problem file runs.
> As an FYI, the files are all being executed via SQL-DMO but I do not
> believe
> there any internal SQL-DMO limitations|||Thanks for the correction, Kalen!
SK
Kalen Delaney wrote:
>Hi Enzo
>DBCC USEROPTIONS will show you the settings (including the SET ROWCOUNT
>value) for the current connection.
>
>
been set using the SET command. For example, SET ROWCOUNT.
Is there a way to do this in SQL Server?
The reason I ask is because somewhere in a stream of some 1000 sql files
someone using some SET parameters that are scewing up some down stream files.
In one case, setting SET ROWCOUNT 0 fixes the problem. We are doing a select
insert from one table into another and only 1995 rows are being inserted when
we know there are 4979. Using SET ROWCOUNT 0 clears up the problem but we
want to find out where along the way things are getting screwed up.
Searching for SET ROWCOUNT has not yielded any result.
What I would like to do is dump all the SET variables to a file or screen or
someplace before the problem file runs.
As an FYI, the files are all being executed via SQL-DMO but I do not believe
there any internal SQL-DMO limitationsI don't think there's a way to get this value - it's a property of the
session that doesn't seem to be stored in any table. You can trace the
workload with SQL Profiler and look for SET ROWCOUNT.
Steve Kass
Drew University
enzo_maini@.dotnetfan.net wrote:
>In Oracle and other databases there is a way to dump all the values that have
>been set using the SET command. For example, SET ROWCOUNT.
>Is there a way to do this in SQL Server?
>The reason I ask is because somewhere in a stream of some 1000 sql files
>someone using some SET parameters that are scewing up some down stream files.
> In one case, setting SET ROWCOUNT 0 fixes the problem. We are doing a select
>insert from one table into another and only 1995 rows are being inserted when
>we know there are 4979. Using SET ROWCOUNT 0 clears up the problem but we
>want to find out where along the way things are getting screwed up.
>Searching for SET ROWCOUNT has not yielded any result.
>What I would like to do is dump all the SET variables to a file or screen or
>someplace before the problem file runs.
>As an FYI, the files are all being executed via SQL-DMO but I do not believe
>there any internal SQL-DMO limitations
>|||Hi Enzo
DBCC USEROPTIONS will show you the settings (including the SET ROWCOUNT
value) for the current connection.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"enzo_maini@.dotnetfan.net"
<enzo_maini@.dotnetfan.net@.discussions.microsoft.com> wrote in message
news:074E8B77-B9A9-4959-A962-CD09660B7793@.microsoft.com...
> In Oracle and other databases there is a way to dump all the values that
> have
> been set using the SET command. For example, SET ROWCOUNT.
> Is there a way to do this in SQL Server?
> The reason I ask is because somewhere in a stream of some 1000 sql files
> someone using some SET parameters that are scewing up some down stream
> files.
> In one case, setting SET ROWCOUNT 0 fixes the problem. We are doing a
> select
> insert from one table into another and only 1995 rows are being inserted
> when
> we know there are 4979. Using SET ROWCOUNT 0 clears up the problem but we
> want to find out where along the way things are getting screwed up.
> Searching for SET ROWCOUNT has not yielded any result.
> What I would like to do is dump all the SET variables to a file or screen
> or
> someplace before the problem file runs.
> As an FYI, the files are all being executed via SQL-DMO but I do not
> believe
> there any internal SQL-DMO limitations|||Thanks for the correction, Kalen!
SK
Kalen Delaney wrote:
>Hi Enzo
>DBCC USEROPTIONS will show you the settings (including the SET ROWCOUNT
>value) for the current connection.
>
>
Dumping sql server
Hi
Is there a way to dump all the sql server data into some sort of file that
can be imported into a local sql server later? I have used sql server dumper
but it is not very stable and crashes during sump.
Thanks
RegardsBACKUP DATABASE?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"John" <John@.nospam.infovis.co.uk> wrote in message news:eoAZqSDhHHA.4552@.TK2MSFTNGP04.phx.gbl...
> Hi
> Is there a way to dump all the sql server data into some sort of file that
> can be imported into a local sql server later? I have used sql server dumper
> but it is not very stable and crashes during sump.
> Thanks
> Regards
>|||Remote, does not work for some reason possibly insufficient rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eQl9syDhHHA.392@.TK2MSFTNGP06.phx.gbl...
> BACKUP DATABASE?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:eoAZqSDhHHA.4552@.TK2MSFTNGP04.phx.gbl...
>> Hi
>> Is there a way to dump all the sql server data into some sort of file
>> that can be imported into a local sql server later? I have used sql
>> server dumper but it is not very stable and crashes during sump.
>> Thanks
>> Regards|||Hi John
"John" wrote:
> Remote, does not work for some reason possibly insufficient rights.
>
If you want to post the command you are using it may help? Also the full
error message would be useful. If you want the backup to be placed on a
different server you can use a UNC path. Make sure that you also have
directory permissions as well as server/database permissions sysadmin,
db_owner or db_backupoperator. See BACKUP in Books Online for more.
Databases can also be detached and attached, it is usually best to attach
the database to an instance at the same service pack/hotfix number, but SQL
2000 databases can be attached to SQL 2005 instances, although you may have
to change incompatibilities if you want to use the latest compatibility mode.
If you are changing instance you will need to script logins and possibly
resolved orphaned users see
http://support.microsoft.com/default.aspx/kb/314546 for more.
John
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:eQl9syDhHHA.392@.TK2MSFTNGP06.phx.gbl...
> > BACKUP DATABASE?
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://sqlblog.com/blogs/tibor_karaszi
> >
> >
> > "John" <John@.nospam.infovis.co.uk> wrote in message
> > news:eoAZqSDhHHA.4552@.TK2MSFTNGP04.phx.gbl...
> >> Hi
> >>
> >> Is there a way to dump all the sql server data into some sort of file
> >> that can be imported into a local sql server later? I have used sql
> >> server dumper but it is not very stable and crashes during sump.
> >>
> >> Thanks
> >>
> >> Regards
>
>|||Can't you backup the database to a file local on the SQL Server machine and then FTP the file to
your machine?
Other methods include script out all the objects and data and then re-create the objects locally and
then import that data. I doubt you will find something as such as rock solid as backup. I've listed
some tools to generate scripts and some with data at
http://www.karaszi.com/SQLServer/info_generate_script.asp.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%234nMS%23DhHHA.4692@.TK2MSFTNGP04.phx.gbl...
> Remote, does not work for some reason possibly insufficient rights.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:eQl9syDhHHA.392@.TK2MSFTNGP06.phx.gbl...
>> BACKUP DATABASE?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "John" <John@.nospam.infovis.co.uk> wrote in message news:eoAZqSDhHHA.4552@.TK2MSFTNGP04.phx.gbl...
>> Hi
>> Is there a way to dump all the sql server data into some sort of file that can be imported into
>> a local sql server later? I have used sql server dumper but it is not very stable and crashes
>> during sump.
>> Thanks
>> Regards
>
Is there a way to dump all the sql server data into some sort of file that
can be imported into a local sql server later? I have used sql server dumper
but it is not very stable and crashes during sump.
Thanks
RegardsBACKUP DATABASE?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"John" <John@.nospam.infovis.co.uk> wrote in message news:eoAZqSDhHHA.4552@.TK2MSFTNGP04.phx.gbl...
> Hi
> Is there a way to dump all the sql server data into some sort of file that
> can be imported into a local sql server later? I have used sql server dumper
> but it is not very stable and crashes during sump.
> Thanks
> Regards
>|||Remote, does not work for some reason possibly insufficient rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eQl9syDhHHA.392@.TK2MSFTNGP06.phx.gbl...
> BACKUP DATABASE?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:eoAZqSDhHHA.4552@.TK2MSFTNGP04.phx.gbl...
>> Hi
>> Is there a way to dump all the sql server data into some sort of file
>> that can be imported into a local sql server later? I have used sql
>> server dumper but it is not very stable and crashes during sump.
>> Thanks
>> Regards|||Hi John
"John" wrote:
> Remote, does not work for some reason possibly insufficient rights.
>
If you want to post the command you are using it may help? Also the full
error message would be useful. If you want the backup to be placed on a
different server you can use a UNC path. Make sure that you also have
directory permissions as well as server/database permissions sysadmin,
db_owner or db_backupoperator. See BACKUP in Books Online for more.
Databases can also be detached and attached, it is usually best to attach
the database to an instance at the same service pack/hotfix number, but SQL
2000 databases can be attached to SQL 2005 instances, although you may have
to change incompatibilities if you want to use the latest compatibility mode.
If you are changing instance you will need to script logins and possibly
resolved orphaned users see
http://support.microsoft.com/default.aspx/kb/314546 for more.
John
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:eQl9syDhHHA.392@.TK2MSFTNGP06.phx.gbl...
> > BACKUP DATABASE?
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://sqlblog.com/blogs/tibor_karaszi
> >
> >
> > "John" <John@.nospam.infovis.co.uk> wrote in message
> > news:eoAZqSDhHHA.4552@.TK2MSFTNGP04.phx.gbl...
> >> Hi
> >>
> >> Is there a way to dump all the sql server data into some sort of file
> >> that can be imported into a local sql server later? I have used sql
> >> server dumper but it is not very stable and crashes during sump.
> >>
> >> Thanks
> >>
> >> Regards
>
>|||Can't you backup the database to a file local on the SQL Server machine and then FTP the file to
your machine?
Other methods include script out all the objects and data and then re-create the objects locally and
then import that data. I doubt you will find something as such as rock solid as backup. I've listed
some tools to generate scripts and some with data at
http://www.karaszi.com/SQLServer/info_generate_script.asp.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%234nMS%23DhHHA.4692@.TK2MSFTNGP04.phx.gbl...
> Remote, does not work for some reason possibly insufficient rights.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:eQl9syDhHHA.392@.TK2MSFTNGP06.phx.gbl...
>> BACKUP DATABASE?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "John" <John@.nospam.infovis.co.uk> wrote in message news:eoAZqSDhHHA.4552@.TK2MSFTNGP04.phx.gbl...
>> Hi
>> Is there a way to dump all the sql server data into some sort of file that can be imported into
>> a local sql server later? I have used sql server dumper but it is not very stable and crashes
>> during sump.
>> Thanks
>> Regards
>
Dumping sql server
Hi
Is there a way to dump all the sql server data into some sort of file that
can be imported into a local sql server later? I have used sql server dumper
but it is not very stable and crashes during sump.
Thanks
RegardsBACKUP DATABASE?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"John" <John@.nospam.infovis.co.uk> wrote in message news:eoAZqSDhHHA.4552@.TK2MSFTNGP04.phx.g
bl...
> Hi
> Is there a way to dump all the sql server data into some sort of file that
> can be imported into a local sql server later? I have used sql server dump
er
> but it is not very stable and crashes during sump.
> Thanks
> Regards
>|||Remote, does not work for some reason possibly insufficient rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eQl9syDhHHA.392@.TK2MSFTNGP06.phx.gbl...[vbcol=seagreen]
> BACKUP DATABASE?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:eoAZqSDhHHA.4552@.TK2MSFTNGP04.phx.gbl...|||Hi John
"John" wrote:
> Remote, does not work for some reason possibly insufficient rights.
>
If you want to post the command you are using it may help? Also the full
error message would be useful. If you want the backup to be placed on a
different server you can use a UNC path. Make sure that you also have
directory permissions as well as server/database permissions sysadmin,
db_owner or db_backupoperator. See BACKUP in Books Online for more.
Databases can also be detached and attached, it is usually best to attach
the database to an instance at the same service pack/hotfix number, but SQL
2000 databases can be attached to SQL 2005 instances, although you may have
to change incompatibilities if you want to use the latest compatibility mode
.
If you are changing instance you will need to script logins and possibly
resolved orphaned users see
http://support.microsoft.com/default.aspx/kb/314546 for more.
John
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:eQl9syDhHHA.392@.TK2MSFTNGP06.phx.gbl...
>
>|||Can't you backup the database to a file local on the SQL Server machine and
then FTP the file to
your machine?
Other methods include script out all the objects and data and then re-create
the objects locally and
then import that data. I doubt you will find something as such as rock solid
as backup. I've listed
some tools to generate scripts and some with data at
http://www.karaszi.com/SQLServer/in...ate_script.asp.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%234nMS%23DhHHA.4692@.TK2MSFTNGP04.phx.gbl...
> Remote, does not work for some reason possibly insufficient rights.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:eQl9syDhHHA.392@.TK2MSFTNGP06.phx.gbl...
>sql
Is there a way to dump all the sql server data into some sort of file that
can be imported into a local sql server later? I have used sql server dumper
but it is not very stable and crashes during sump.
Thanks
RegardsBACKUP DATABASE?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"John" <John@.nospam.infovis.co.uk> wrote in message news:eoAZqSDhHHA.4552@.TK2MSFTNGP04.phx.g
bl...
> Hi
> Is there a way to dump all the sql server data into some sort of file that
> can be imported into a local sql server later? I have used sql server dump
er
> but it is not very stable and crashes during sump.
> Thanks
> Regards
>|||Remote, does not work for some reason possibly insufficient rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eQl9syDhHHA.392@.TK2MSFTNGP06.phx.gbl...[vbcol=seagreen]
> BACKUP DATABASE?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:eoAZqSDhHHA.4552@.TK2MSFTNGP04.phx.gbl...|||Hi John
"John" wrote:
> Remote, does not work for some reason possibly insufficient rights.
>
If you want to post the command you are using it may help? Also the full
error message would be useful. If you want the backup to be placed on a
different server you can use a UNC path. Make sure that you also have
directory permissions as well as server/database permissions sysadmin,
db_owner or db_backupoperator. See BACKUP in Books Online for more.
Databases can also be detached and attached, it is usually best to attach
the database to an instance at the same service pack/hotfix number, but SQL
2000 databases can be attached to SQL 2005 instances, although you may have
to change incompatibilities if you want to use the latest compatibility mode
.
If you are changing instance you will need to script logins and possibly
resolved orphaned users see
http://support.microsoft.com/default.aspx/kb/314546 for more.
John
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:eQl9syDhHHA.392@.TK2MSFTNGP06.phx.gbl...
>
>|||Can't you backup the database to a file local on the SQL Server machine and
then FTP the file to
your machine?
Other methods include script out all the objects and data and then re-create
the objects locally and
then import that data. I doubt you will find something as such as rock solid
as backup. I've listed
some tools to generate scripts and some with data at
http://www.karaszi.com/SQLServer/in...ate_script.asp.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%234nMS%23DhHHA.4692@.TK2MSFTNGP04.phx.gbl...
> Remote, does not work for some reason possibly insufficient rights.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:eQl9syDhHHA.392@.TK2MSFTNGP06.phx.gbl...
>sql
Dumping SQL database
My site is hosted with 1and1. They tell me I need to dump my SQL database before uploading it to their site. I have never done this before. I have looked in the online help and have asked them for help, but nothing seems to work. I've tried queries like the following:
DUMP DATABASE dbname TO DISK = 'C:\folder\filename'
It tells me it doesn't recognize "dbname", which is the name of the MDF file (without the ".mdf" extension). So I'm not sure what I should be using for database name.
I'm using Visual Studio 2005 Professional. Can someone help me with dumping my database? Thanks!
you can either Detach a database in which case you should beable to copy the mdf to a new location then reattach.
or you can backup a database to a file and then you copy the .bak file to the new location and restore the database.
this link would indicate that 1and1 want you to backup your database http://msdn2.microsoft.com/en-us/library/ms187315.aspx
its pretty easy to backup a database through the SQL Server IDE... and this link gives a detailed script that will backup all your databases...
http://www.sqlmag.com/Articles/ArticleID/46560/46560.html?Ad=1
Dumping incremental changes to textfile
I was wondering if there is a way to schedule a tast that will dump a
fixed width text file of all the new entries in a table. So if I had
a table with like
fixed width text file of all the new entries in a table. So if I had
a table with like
username - varchar(20)
created - smalldatetime
I could get a weekly feed each week of all the new users in a text
file. I know I could write a script that would go through and do this
by looking at the time stamp, and the last time that the file
previously ran and get the new dates but I was hoping there was a
built way to do this. Or perhaps a more elegant solution.
Thanks,
Charlie"charliek" <charlie_knudsen@.hotmail.com> wrote in message
news:964kpvc52gfj8mp7vf05oktlquaukcfgdm@.4ax.com...
> I was wondering if there is a way to schedule a tast that will dump a
> fixed width text file of all the new entries in a table. So if I had
> a table with like
> username - varchar(20)
> created - smalldatetime
> I could get a weekly feed each week of all the new users in a text
> file. I know I could write a script that would go through and do this
> by looking at the time stamp, and the last time that the file
> previously ran and get the new dates but I was hoping there was a
> built way to do this. Or perhaps a more elegant solution.
> Thanks,
> Charlie
Using a script is fine, or you could look at BCP or DTS as well. There's no
automatic functionality for this, so you'll have to set something up
yourself.
Simon|||On Sat, 25 Oct 2003 14:38:12 +0200, "Simon Hayes" <sql@.hayes.ch>
wrote:
>"charliek" <charlie_knudsen@.hotmail.com> wrote in message
>news:964kpvc52gfj8mp7vf05oktlquaukcfgdm@.4ax.com...
>> I was wondering if there is a way to schedule a tast that will dump a
>> fixed width text file of all the new entries in a table. So if I had
>> a table with like
>>
>> username - varchar(20)
>> created - smalldatetime
>>
>> I could get a weekly feed each week of all the new users in a text
>> file. I know I could write a script that would go through and do this
>> by looking at the time stamp, and the last time that the file
>> previously ran and get the new dates but I was hoping there was a
>> built way to do this. Or perhaps a more elegant solution.
>>
>> Thanks,
>>
>> Charlie
>Using a script is fine, or you could look at BCP or DTS as well. There's no
>automatic functionality for this, so you'll have to set something up
>yourself.
>Simon
Thanks for the info. I thought that this may be the case, however I
wanted to make sure that I was not overlooking a easy or more
efficient solution.
Thanks,
Charliesql
dumping data from database
Hi:)
I need to dump data from database in to *.sql file. I try it using DUMP
DATABASE but it not work that I need it.
I need to dump data from database in to *.sql file. I try it using DUMP
DATABASE but it not work that I need it.
So, how can I dump data from database in to *.sql file and then run it
using: osql -E -i nameOfDumpedDatabase.sql
Thanks for help, greetings, gregory :)"rozrabiak" <brak@.maila.pl> wrote in message
news:c6r83b$5ra$1@.news.onet.pl...
> Hi:)
> I need to dump data from database in to *.sql file. I try it using DUMP
> DATABASE but it not work that I need it.
> So, how can I dump data from database in to *.sql file and then run it
> using: osql -E -i nameOfDumpedDatabase.sql
>
> Thanks for help, greetings, gregory :)
You can use bcp.exe or DTS to export data to a file and load it again, or if
you prefer INSERT statements then see here:
http://vyaskn.tripod.com/code.htm#inserts
If you want everything (objects and data), then BACKUP and RESTORE is often
the easiest way.
Simon|||rozrabiak (brak@.maila.pl) writes:
> I need to dump data from database in to *.sql file. I try it using DUMP
> DATABASE but it not work that I need it.
> So, how can I dump data from database in to *.sql file and then run it
> using: osql -E -i nameOfDumpedDatabase.sql
In Enterprise Manager, find the database, right-click, select All Tasks,
and in that submenu you find scripting options.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns94DAF1247AB58Yazorman@.127.0.0.1>...
> rozrabiak (brak@.maila.pl) writes:
> > I need to dump data from database in to *.sql file. I try it using DUMP
> > DATABASE but it not work that I need it.
> > So, how can I dump data from database in to *.sql file and then run it
> > using: osql -E -i nameOfDumpedDatabase.sql
> In Enterprise Manager, find the database, right-click, select All Tasks,
> and in that submenu you find scripting options.
There is a Java program called Super*SQL which will unload any tables
you have into an ASCII file that you can then run or load into a
different database. For more information visit www.sqlmagic.com.|||rozrabiak <brak@.maila.pl> wrote in message news:<c6r83b$5ra$1@.news.onet.pl>...
> Hi:)
> I need to dump data from database in to *.sql file. I try it using DUMP
> DATABASE but it not work that I need it.
Hi, you could also look at this article which describes a Java utility
to dump both data and schema information to an SQL file.
http://isocra.com/articles/article.php?id=java_db_dump
We wrote it because we needed to synchronize two remote SQLServer
databases as well as a MySQL development database.
In fact it's pretty easy to integrate it into a JSP to give the same
sort of dumping functionality as phpMyAdmin but for SQLServer.
dumping data from database
Hi:)
I need to dump data from database in to *.sql file. I try it using DUMP
DATABASE but it not work that I need it.
I need to dump data from database in to *.sql file. I try it using DUMP
DATABASE but it not work that I need it.
So, how can I dump data from database in to *.sql file and then run it
using: osql -E -i nameOfDumpedDatabase.sql
Thanks for help, greetings, gregory :)"rozrabiak" <brak@.maila.pl> wrote in message
news:c6r83b$5ra$1@.news.onet.pl...
> Hi:)
> I need to dump data from database in to *.sql file. I try it using DUMP
> DATABASE but it not work that I need it.
> So, how can I dump data from database in to *.sql file and then run it
> using: osql -E -i nameOfDumpedDatabase.sql
>
> Thanks for help, greetings, gregory :)
You can use bcp.exe or DTS to export data to a file and load it again, or if
you prefer INSERT statements then see here:
http://vyaskn.tripod.com/code.htm#inserts
If you want everything (objects and data), then BACKUP and RESTORE is often
the easiest way.
Simon|||rozrabiak (brak@.maila.pl) writes:
> I need to dump data from database in to *.sql file. I try it using DUMP
> DATABASE but it not work that I need it.
> So, how can I dump data from database in to *.sql file and then run it
> using: osql -E -i nameOfDumpedDatabase.sql
In Enterprise Manager, find the database, right-click, select All Tasks,
and in that submenu you find scripting options.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns94DAF1247AB58Yazorman@.127.0.0.1>...
> rozrabiak (brak@.maila.pl) writes:
> > I need to dump data from database in to *.sql file. I try it using DUMP
> > DATABASE but it not work that I need it.
> > So, how can I dump data from database in to *.sql file and then run it
> > using: osql -E -i nameOfDumpedDatabase.sql
> In Enterprise Manager, find the database, right-click, select All Tasks,
> and in that submenu you find scripting options.
There is a Java program called Super*SQL which will unload any tables
you have into an ASCII file that you can then run or load into a
different database. For more information visit www.sqlmagic.com.
Dumping all SQL queries.
Hi - is it possible to dump the query text of every query run
against a particular database, or all databases, in an SQL Server
2000 installation? We've an issue whereby a 3rd. party application
running queries over ODBC sometimes claims that gobbledegook is
returned, and it would be very useful to identify the query that's
being claimed is the problem. I suspect that it's an application
issue rather than a SQL Server one.
against a particular database, or all databases, in an SQL Server
2000 installation? We've an issue whereby a 3rd. party application
running queries over ODBC sometimes claims that gobbledegook is
returned, and it would be very useful to identify the query that's
being claimed is the problem. I suspect that it's an application
issue rather than a SQL Server one.
__________________________________________________ ___________
Are you Catholic ?
http://www.CatholicEmail.com
100s of FREE email addresses -->
http://www.UltimateEmail.com
Send an Online Greeting Card http://www.UltimateEcards.com"sqlserver yeahbaby" <Use-Author-Address-Header@.[127.1]> wrote in message
news:20031104161149.53F073965@.sitemail.everyone.ne t...
> Hi - is it possible to dump the query text of every query run
> against a particular database, or all databases, in an SQL Server
> 2000 installation? We've an issue whereby a 3rd. party application
> running queries over ODBC sometimes claims that gobbledegook is
> returned, and it would be very useful to identify the query that's
> being claimed is the problem. I suspect that it's an application
> issue rather than a SQL Server one.
Look at profiler.
> __________________________________________________ ___________
> Are you Catholic ?
> http://www.CatholicEmail.com
> 100s of FREE email addresses -->
> http://www.UltimateEmail.com
> Send an Online Greeting Card http://www.UltimateEcards.com
Dump Transaction With Truncate Only
I currently have maintenance plans defined to run backups for all DB's. For
one specific DB I have the reovery model set to simple. When the backup runs
for this DB I am receiving the following msg in the log: Backup Failed to
Complete the Command Dump Transaction xxxx With Truncate Only'. The backup
of the database completes successfully. There is not an error code
associated with the message. I have researched with little success. Any
thoughts as to what is occurring?
Thank you...paldba
Have you got it set to backup the transaction log as part of this
maintainence plan? As in simple mode the transaction log cannot be backed up.
"paldba" wrote:
> I currently have maintenance plans defined to run backups for all DB's. For
> one specific DB I have the reovery model set to simple. When the backup runs
> for this DB I am receiving the following msg in the log: Backup Failed to
> Complete the Command Dump Transaction xxxx With Truncate Only'. The backup
> of the database completes successfully. There is not an error code
> associated with the message. I have researched with little success. Any
> thoughts as to what is occurring?
> Thank you...paldba
|||No I do not.
"Russell" wrote:
[vbcol=seagreen]
> Have you got it set to backup the transaction log as part of this
> maintainence plan? As in simple mode the transaction log cannot be backed up.
> "paldba" wrote:
|||Seems you have a maint plan where you have included transaction log backups. and one of your
database is in simple recovery mode. You cannot perform log backup for a database in simple recovery
mode.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"paldba" <paldba@.discussions.microsoft.com> wrote in message
news:EF18B7A5-F0CA-4925-8306-D7317C474B58@.microsoft.com...
> I currently have maintenance plans defined to run backups for all DB's. For
> one specific DB I have the reovery model set to simple. When the backup runs
> for this DB I am receiving the following msg in the log: Backup Failed to
> Complete the Command Dump Transaction xxxx With Truncate Only'. The backup
> of the database completes successfully. There is not an error code
> associated with the message. I have researched with little success. Any
> thoughts as to what is occurring?
> Thank you...paldba
|||For a database set to simple recovery, you cannot backup the transaction
log - even to truncate the log (which is unnecessary). Attempting to do so
results in an error.
"paldba" <paldba@.discussions.microsoft.com> wrote in message
news:DAC6AE7F-260F-4364-8C05-5EF31B7A272F@.microsoft.com...[vbcol=seagreen]
> No I do not.
> "Russell" wrote:
backed up.[vbcol=seagreen]
DB's. For[vbcol=seagreen]
backup runs[vbcol=seagreen]
Failed to[vbcol=seagreen]
backup[vbcol=seagreen]
Any[vbcol=seagreen]
|||Thank you for your response. I have verifed several times that that
maintenance plans are not set up to perform transaction log backups. This
msg just started occurring in the logs after months of running error free.
No changes have been made to the DB or the maintenance plan.
"Tibor Karaszi" wrote:
> Seems you have a maint plan where you have included transaction log backups. and one of your
> database is in simple recovery mode. You cannot perform log backup for a database in simple recovery
> mode.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "paldba" <paldba@.discussions.microsoft.com> wrote in message
> news:EF18B7A5-F0CA-4925-8306-D7317C474B58@.microsoft.com...
>
>
|||Ahh, I missed the WITH TRUNCATE_ONLY part. What version of SQL server? Someone is executing this
command and you need to hunt that person down and ask why...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"paldba" <paldba@.discussions.microsoft.com> wrote in message
news:1AFC4C74-927F-40DB-AD0C-D002E76DF3C7@.microsoft.com...[vbcol=seagreen]
> Thank you for your response. I have verifed several times that that
> maintenance plans are not set up to perform transaction log backups. This
> msg just started occurring in the logs after months of running error free.
> No changes have been made to the DB or the maintenance plan.
> "Tibor Karaszi" wrote:
recovery[vbcol=seagreen]
|||We are running SQL Server 2000. The message that appears in the log is
occurs exactly when the maintenance plan is running. I reran the maintenance
plan a few moments ago and the same message was generated in the log.
"Tibor Karaszi" wrote:
> Ahh, I missed the WITH TRUNCATE_ONLY part. What version of SQL server? Someone is executing this
> command and you need to hunt that person down and ask why...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "paldba" <paldba@.discussions.microsoft.com> wrote in message
> news:1AFC4C74-927F-40DB-AD0C-D002E76DF3C7@.microsoft.com...
> recovery
>
>
|||Strange. I don't use maint plans myself. I can only assume that is either a bug in maint wiz, or
some really strange design decision.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"paldba" <paldba@.discussions.microsoft.com> wrote in message
news:87B7E14E-EB90-433B-8F26-FDED7E1FD1A4@.microsoft.com...[vbcol=seagreen]
> We are running SQL Server 2000. The message that appears in the log is
> occurs exactly when the maintenance plan is running. I reran the maintenance
> plan a few moments ago and the same message was generated in the log.
> "Tibor Karaszi" wrote:
|||Delete and recreate the plan?
Jeff
"paldba" <paldba@.discussions.microsoft.com> wrote in message
news:87B7E14E-EB90-433B-8F26-FDED7E1FD1A4@.microsoft.com...
> We are running SQL Server 2000. The message that appears in the log is
> occurs exactly when the maintenance plan is running. I reran the
maintenance[vbcol=seagreen]
> plan a few moments ago and the same message was generated in the log.
> "Tibor Karaszi" wrote:
Someone is executing this[vbcol=seagreen]
This[vbcol=seagreen]
free.[vbcol=seagreen]
backups. and one of your[vbcol=seagreen]
for a database in simple[vbcol=seagreen]
DB's. For[vbcol=seagreen]
backup runs[vbcol=seagreen]
Failed to[vbcol=seagreen]
The backup[vbcol=seagreen]
code[vbcol=seagreen]
success. Any[vbcol=seagreen]
one specific DB I have the reovery model set to simple. When the backup runs
for this DB I am receiving the following msg in the log: Backup Failed to
Complete the Command Dump Transaction xxxx With Truncate Only'. The backup
of the database completes successfully. There is not an error code
associated with the message. I have researched with little success. Any
thoughts as to what is occurring?
Thank you...paldba
Have you got it set to backup the transaction log as part of this
maintainence plan? As in simple mode the transaction log cannot be backed up.
"paldba" wrote:
> I currently have maintenance plans defined to run backups for all DB's. For
> one specific DB I have the reovery model set to simple. When the backup runs
> for this DB I am receiving the following msg in the log: Backup Failed to
> Complete the Command Dump Transaction xxxx With Truncate Only'. The backup
> of the database completes successfully. There is not an error code
> associated with the message. I have researched with little success. Any
> thoughts as to what is occurring?
> Thank you...paldba
|||No I do not.
"Russell" wrote:
[vbcol=seagreen]
> Have you got it set to backup the transaction log as part of this
> maintainence plan? As in simple mode the transaction log cannot be backed up.
> "paldba" wrote:
|||Seems you have a maint plan where you have included transaction log backups. and one of your
database is in simple recovery mode. You cannot perform log backup for a database in simple recovery
mode.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"paldba" <paldba@.discussions.microsoft.com> wrote in message
news:EF18B7A5-F0CA-4925-8306-D7317C474B58@.microsoft.com...
> I currently have maintenance plans defined to run backups for all DB's. For
> one specific DB I have the reovery model set to simple. When the backup runs
> for this DB I am receiving the following msg in the log: Backup Failed to
> Complete the Command Dump Transaction xxxx With Truncate Only'. The backup
> of the database completes successfully. There is not an error code
> associated with the message. I have researched with little success. Any
> thoughts as to what is occurring?
> Thank you...paldba
|||For a database set to simple recovery, you cannot backup the transaction
log - even to truncate the log (which is unnecessary). Attempting to do so
results in an error.
"paldba" <paldba@.discussions.microsoft.com> wrote in message
news:DAC6AE7F-260F-4364-8C05-5EF31B7A272F@.microsoft.com...[vbcol=seagreen]
> No I do not.
> "Russell" wrote:
backed up.[vbcol=seagreen]
DB's. For[vbcol=seagreen]
backup runs[vbcol=seagreen]
Failed to[vbcol=seagreen]
backup[vbcol=seagreen]
Any[vbcol=seagreen]
|||Thank you for your response. I have verifed several times that that
maintenance plans are not set up to perform transaction log backups. This
msg just started occurring in the logs after months of running error free.
No changes have been made to the DB or the maintenance plan.
"Tibor Karaszi" wrote:
> Seems you have a maint plan where you have included transaction log backups. and one of your
> database is in simple recovery mode. You cannot perform log backup for a database in simple recovery
> mode.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "paldba" <paldba@.discussions.microsoft.com> wrote in message
> news:EF18B7A5-F0CA-4925-8306-D7317C474B58@.microsoft.com...
>
>
|||Ahh, I missed the WITH TRUNCATE_ONLY part. What version of SQL server? Someone is executing this
command and you need to hunt that person down and ask why...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"paldba" <paldba@.discussions.microsoft.com> wrote in message
news:1AFC4C74-927F-40DB-AD0C-D002E76DF3C7@.microsoft.com...[vbcol=seagreen]
> Thank you for your response. I have verifed several times that that
> maintenance plans are not set up to perform transaction log backups. This
> msg just started occurring in the logs after months of running error free.
> No changes have been made to the DB or the maintenance plan.
> "Tibor Karaszi" wrote:
recovery[vbcol=seagreen]
|||We are running SQL Server 2000. The message that appears in the log is
occurs exactly when the maintenance plan is running. I reran the maintenance
plan a few moments ago and the same message was generated in the log.
"Tibor Karaszi" wrote:
> Ahh, I missed the WITH TRUNCATE_ONLY part. What version of SQL server? Someone is executing this
> command and you need to hunt that person down and ask why...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "paldba" <paldba@.discussions.microsoft.com> wrote in message
> news:1AFC4C74-927F-40DB-AD0C-D002E76DF3C7@.microsoft.com...
> recovery
>
>
|||Strange. I don't use maint plans myself. I can only assume that is either a bug in maint wiz, or
some really strange design decision.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"paldba" <paldba@.discussions.microsoft.com> wrote in message
news:87B7E14E-EB90-433B-8F26-FDED7E1FD1A4@.microsoft.com...[vbcol=seagreen]
> We are running SQL Server 2000. The message that appears in the log is
> occurs exactly when the maintenance plan is running. I reran the maintenance
> plan a few moments ago and the same message was generated in the log.
> "Tibor Karaszi" wrote:
|||Delete and recreate the plan?
Jeff
"paldba" <paldba@.discussions.microsoft.com> wrote in message
news:87B7E14E-EB90-433B-8F26-FDED7E1FD1A4@.microsoft.com...
> We are running SQL Server 2000. The message that appears in the log is
> occurs exactly when the maintenance plan is running. I reran the
maintenance[vbcol=seagreen]
> plan a few moments ago and the same message was generated in the log.
> "Tibor Karaszi" wrote:
Someone is executing this[vbcol=seagreen]
This[vbcol=seagreen]
free.[vbcol=seagreen]
backups. and one of your[vbcol=seagreen]
for a database in simple[vbcol=seagreen]
DB's. For[vbcol=seagreen]
backup runs[vbcol=seagreen]
Failed to[vbcol=seagreen]
The backup[vbcol=seagreen]
code[vbcol=seagreen]
success. Any[vbcol=seagreen]
Dump Transaction With Truncate Only
I currently have maintenance plans defined to run backups for all DB's. For
one specific DB I have the reovery model set to simple. When the backup runs
for this DB I am receiving the following msg in the log: Backup Failed to
Complete the Command Dump Transaction xxxx With Truncate Only'. The backup
of the database completes successfully. There is not an error code
associated with the message. I have researched with little success. Any
thoughts as to what is occurring?
Thank you...paldbaHave you got it set to backup the transaction log as part of this
maintainence plan? As in simple mode the transaction log cannot be backed up.
"paldba" wrote:
> I currently have maintenance plans defined to run backups for all DB's. For
> one specific DB I have the reovery model set to simple. When the backup runs
> for this DB I am receiving the following msg in the log: Backup Failed to
> Complete the Command Dump Transaction xxxx With Truncate Only'. The backup
> of the database completes successfully. There is not an error code
> associated with the message. I have researched with little success. Any
> thoughts as to what is occurring?
> Thank you...paldba|||No I do not.
"Russell" wrote:
> Have you got it set to backup the transaction log as part of this
> maintainence plan? As in simple mode the transaction log cannot be backed up.
> "paldba" wrote:
> > I currently have maintenance plans defined to run backups for all DB's. For
> > one specific DB I have the reovery model set to simple. When the backup runs
> > for this DB I am receiving the following msg in the log: Backup Failed to
> > Complete the Command Dump Transaction xxxx With Truncate Only'. The backup
> > of the database completes successfully. There is not an error code
> > associated with the message. I have researched with little success. Any
> > thoughts as to what is occurring?
> >
> > Thank you...paldba|||Seems you have a maint plan where you have included transaction log backups. and one of your
database is in simple recovery mode. You cannot perform log backup for a database in simple recovery
mode.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"paldba" <paldba@.discussions.microsoft.com> wrote in message
news:EF18B7A5-F0CA-4925-8306-D7317C474B58@.microsoft.com...
> I currently have maintenance plans defined to run backups for all DB's. For
> one specific DB I have the reovery model set to simple. When the backup runs
> for this DB I am receiving the following msg in the log: Backup Failed to
> Complete the Command Dump Transaction xxxx With Truncate Only'. The backup
> of the database completes successfully. There is not an error code
> associated with the message. I have researched with little success. Any
> thoughts as to what is occurring?
> Thank you...paldba|||For a database set to simple recovery, you cannot backup the transaction
log - even to truncate the log (which is unnecessary). Attempting to do so
results in an error.
"paldba" <paldba@.discussions.microsoft.com> wrote in message
news:DAC6AE7F-260F-4364-8C05-5EF31B7A272F@.microsoft.com...
> No I do not.
> "Russell" wrote:
> > Have you got it set to backup the transaction log as part of this
> > maintainence plan? As in simple mode the transaction log cannot be
backed up.
> >
> > "paldba" wrote:
> >
> > > I currently have maintenance plans defined to run backups for all
DB's. For
> > > one specific DB I have the reovery model set to simple. When the
backup runs
> > > for this DB I am receiving the following msg in the log: Backup
Failed to
> > > Complete the Command Dump Transaction xxxx With Truncate Only'. The
backup
> > > of the database completes successfully. There is not an error code
> > > associated with the message. I have researched with little success.
Any
> > > thoughts as to what is occurring?
> > >
> > > Thank you...paldba|||Thank you for your response. I have verifed several times that that
maintenance plans are not set up to perform transaction log backups. This
msg just started occurring in the logs after months of running error free.
No changes have been made to the DB or the maintenance plan.
"Tibor Karaszi" wrote:
> Seems you have a maint plan where you have included transaction log backups. and one of your
> database is in simple recovery mode. You cannot perform log backup for a database in simple recovery
> mode.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "paldba" <paldba@.discussions.microsoft.com> wrote in message
> news:EF18B7A5-F0CA-4925-8306-D7317C474B58@.microsoft.com...
> > I currently have maintenance plans defined to run backups for all DB's. For
> > one specific DB I have the reovery model set to simple. When the backup runs
> > for this DB I am receiving the following msg in the log: Backup Failed to
> > Complete the Command Dump Transaction xxxx With Truncate Only'. The backup
> > of the database completes successfully. There is not an error code
> > associated with the message. I have researched with little success. Any
> > thoughts as to what is occurring?
> >
> > Thank you...paldba
>
>|||Ahh, I missed the WITH TRUNCATE_ONLY part. What version of SQL server? Someone is executing this
command and you need to hunt that person down and ask why...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"paldba" <paldba@.discussions.microsoft.com> wrote in message
news:1AFC4C74-927F-40DB-AD0C-D002E76DF3C7@.microsoft.com...
> Thank you for your response. I have verifed several times that that
> maintenance plans are not set up to perform transaction log backups. This
> msg just started occurring in the logs after months of running error free.
> No changes have been made to the DB or the maintenance plan.
> "Tibor Karaszi" wrote:
> > Seems you have a maint plan where you have included transaction log backups. and one of your
> > database is in simple recovery mode. You cannot perform log backup for a database in simple
recovery
> > mode.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "paldba" <paldba@.discussions.microsoft.com> wrote in message
> > news:EF18B7A5-F0CA-4925-8306-D7317C474B58@.microsoft.com...
> > > I currently have maintenance plans defined to run backups for all DB's. For
> > > one specific DB I have the reovery model set to simple. When the backup runs
> > > for this DB I am receiving the following msg in the log: Backup Failed to
> > > Complete the Command Dump Transaction xxxx With Truncate Only'. The backup
> > > of the database completes successfully. There is not an error code
> > > associated with the message. I have researched with little success. Any
> > > thoughts as to what is occurring?
> > >
> > > Thank you...paldba
> >
> >
> >|||We are running SQL Server 2000. The message that appears in the log is
occurs exactly when the maintenance plan is running. I reran the maintenance
plan a few moments ago and the same message was generated in the log.
"Tibor Karaszi" wrote:
> Ahh, I missed the WITH TRUNCATE_ONLY part. What version of SQL server? Someone is executing this
> command and you need to hunt that person down and ask why...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "paldba" <paldba@.discussions.microsoft.com> wrote in message
> news:1AFC4C74-927F-40DB-AD0C-D002E76DF3C7@.microsoft.com...
> > Thank you for your response. I have verifed several times that that
> > maintenance plans are not set up to perform transaction log backups. This
> > msg just started occurring in the logs after months of running error free.
> > No changes have been made to the DB or the maintenance plan.
> >
> > "Tibor Karaszi" wrote:
> >
> > > Seems you have a maint plan where you have included transaction log backups. and one of your
> > > database is in simple recovery mode. You cannot perform log backup for a database in simple
> recovery
> > > mode.
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > >
> > >
> > > "paldba" <paldba@.discussions.microsoft.com> wrote in message
> > > news:EF18B7A5-F0CA-4925-8306-D7317C474B58@.microsoft.com...
> > > > I currently have maintenance plans defined to run backups for all DB's. For
> > > > one specific DB I have the reovery model set to simple. When the backup runs
> > > > for this DB I am receiving the following msg in the log: Backup Failed to
> > > > Complete the Command Dump Transaction xxxx With Truncate Only'. The backup
> > > > of the database completes successfully. There is not an error code
> > > > associated with the message. I have researched with little success. Any
> > > > thoughts as to what is occurring?
> > > >
> > > > Thank you...paldba
> > >
> > >
> > >
>
>|||Strange. I don't use maint plans myself. I can only assume that is either a bug in maint wiz, or
some really strange design decision.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"paldba" <paldba@.discussions.microsoft.com> wrote in message
news:87B7E14E-EB90-433B-8F26-FDED7E1FD1A4@.microsoft.com...
> We are running SQL Server 2000. The message that appears in the log is
> occurs exactly when the maintenance plan is running. I reran the maintenance
> plan a few moments ago and the same message was generated in the log.
> "Tibor Karaszi" wrote:
>> Ahh, I missed the WITH TRUNCATE_ONLY part. What version of SQL server? Someone is executing this
>> command and you need to hunt that person down and ask why...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "paldba" <paldba@.discussions.microsoft.com> wrote in message
>> news:1AFC4C74-927F-40DB-AD0C-D002E76DF3C7@.microsoft.com...
>> > Thank you for your response. I have verifed several times that that
>> > maintenance plans are not set up to perform transaction log backups. This
>> > msg just started occurring in the logs after months of running error free.
>> > No changes have been made to the DB or the maintenance plan.
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> > > Seems you have a maint plan where you have included transaction log backups. and one of your
>> > > database is in simple recovery mode. You cannot perform log backup for a database in simple
>> recovery
>> > > mode.
>> > >
>> > > --
>> > > Tibor Karaszi, SQL Server MVP
>> > > http://www.karaszi.com/sqlserver/default.asp
>> > > http://www.solidqualitylearning.com/
>> > >
>> > >
>> > > "paldba" <paldba@.discussions.microsoft.com> wrote in message
>> > > news:EF18B7A5-F0CA-4925-8306-D7317C474B58@.microsoft.com...
>> > > > I currently have maintenance plans defined to run backups for all DB's. For
>> > > > one specific DB I have the reovery model set to simple. When the backup runs
>> > > > for this DB I am receiving the following msg in the log: Backup Failed to
>> > > > Complete the Command Dump Transaction xxxx With Truncate Only'. The backup
>> > > > of the database completes successfully. There is not an error code
>> > > > associated with the message. I have researched with little success. Any
>> > > > thoughts as to what is occurring?
>> > > >
>> > > > Thank you...paldba
>> > >
>> > >
>> > >
>>|||Delete and recreate the plan?
Jeff
"paldba" <paldba@.discussions.microsoft.com> wrote in message
news:87B7E14E-EB90-433B-8F26-FDED7E1FD1A4@.microsoft.com...
> We are running SQL Server 2000. The message that appears in the log is
> occurs exactly when the maintenance plan is running. I reran the
maintenance
> plan a few moments ago and the same message was generated in the log.
> "Tibor Karaszi" wrote:
> > Ahh, I missed the WITH TRUNCATE_ONLY part. What version of SQL server?
Someone is executing this
> > command and you need to hunt that person down and ask why...
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "paldba" <paldba@.discussions.microsoft.com> wrote in message
> > news:1AFC4C74-927F-40DB-AD0C-D002E76DF3C7@.microsoft.com...
> > > Thank you for your response. I have verifed several times that that
> > > maintenance plans are not set up to perform transaction log backups.
This
> > > msg just started occurring in the logs after months of running error
free.
> > > No changes have been made to the DB or the maintenance plan.
> > >
> > > "Tibor Karaszi" wrote:
> > >
> > > > Seems you have a maint plan where you have included transaction log
backups. and one of your
> > > > database is in simple recovery mode. You cannot perform log backup
for a database in simple
> > recovery
> > > > mode.
> > > >
> > > > --
> > > > Tibor Karaszi, SQL Server MVP
> > > > http://www.karaszi.com/sqlserver/default.asp
> > > > http://www.solidqualitylearning.com/
> > > >
> > > >
> > > > "paldba" <paldba@.discussions.microsoft.com> wrote in message
> > > > news:EF18B7A5-F0CA-4925-8306-D7317C474B58@.microsoft.com...
> > > > > I currently have maintenance plans defined to run backups for all
DB's. For
> > > > > one specific DB I have the reovery model set to simple. When the
backup runs
> > > > > for this DB I am receiving the following msg in the log: Backup
Failed to
> > > > > Complete the Command Dump Transaction xxxx With Truncate Only'.
The backup
> > > > > of the database completes successfully. There is not an error
code
> > > > > associated with the message. I have researched with little
success. Any
> > > > > thoughts as to what is occurring?
> > > > >
> > > > > Thank you...paldba
> > > >
> > > >
> > > >
> >
> >
> >|||Resolved. I deleted and redefined the plan exactly as it was and the msg's
have mysteriously disappeared. Thanks for the suggestion. Since there
weren't any changes to the maintenance plan, I didn't think to delete it.
Thanks much!
"Jeff Dillon" wrote:
> Delete and recreate the plan?
> Jeff
> "paldba" <paldba@.discussions.microsoft.com> wrote in message
> news:87B7E14E-EB90-433B-8F26-FDED7E1FD1A4@.microsoft.com...
> > We are running SQL Server 2000. The message that appears in the log is
> > occurs exactly when the maintenance plan is running. I reran the
> maintenance
> > plan a few moments ago and the same message was generated in the log.
> >
> > "Tibor Karaszi" wrote:
> >
> > > Ahh, I missed the WITH TRUNCATE_ONLY part. What version of SQL server?
> Someone is executing this
> > > command and you need to hunt that person down and ask why...
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > >
> > >
> > > "paldba" <paldba@.discussions.microsoft.com> wrote in message
> > > news:1AFC4C74-927F-40DB-AD0C-D002E76DF3C7@.microsoft.com...
> > > > Thank you for your response. I have verifed several times that that
> > > > maintenance plans are not set up to perform transaction log backups.
> This
> > > > msg just started occurring in the logs after months of running error
> free.
> > > > No changes have been made to the DB or the maintenance plan.
> > > >
> > > > "Tibor Karaszi" wrote:
> > > >
> > > > > Seems you have a maint plan where you have included transaction log
> backups. and one of your
> > > > > database is in simple recovery mode. You cannot perform log backup
> for a database in simple
> > > recovery
> > > > > mode.
> > > > >
> > > > > --
> > > > > Tibor Karaszi, SQL Server MVP
> > > > > http://www.karaszi.com/sqlserver/default.asp
> > > > > http://www.solidqualitylearning.com/
> > > > >
> > > > >
> > > > > "paldba" <paldba@.discussions.microsoft.com> wrote in message
> > > > > news:EF18B7A5-F0CA-4925-8306-D7317C474B58@.microsoft.com...
> > > > > > I currently have maintenance plans defined to run backups for all
> DB's. For
> > > > > > one specific DB I have the reovery model set to simple. When the
> backup runs
> > > > > > for this DB I am receiving the following msg in the log: Backup
> Failed to
> > > > > > Complete the Command Dump Transaction xxxx With Truncate Only'.
> The backup
> > > > > > of the database completes successfully. There is not an error
> code
> > > > > > associated with the message. I have researched with little
> success. Any
> > > > > > thoughts as to what is occurring?
> > > > > >
> > > > > > Thank you...paldba
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
one specific DB I have the reovery model set to simple. When the backup runs
for this DB I am receiving the following msg in the log: Backup Failed to
Complete the Command Dump Transaction xxxx With Truncate Only'. The backup
of the database completes successfully. There is not an error code
associated with the message. I have researched with little success. Any
thoughts as to what is occurring?
Thank you...paldbaHave you got it set to backup the transaction log as part of this
maintainence plan? As in simple mode the transaction log cannot be backed up.
"paldba" wrote:
> I currently have maintenance plans defined to run backups for all DB's. For
> one specific DB I have the reovery model set to simple. When the backup runs
> for this DB I am receiving the following msg in the log: Backup Failed to
> Complete the Command Dump Transaction xxxx With Truncate Only'. The backup
> of the database completes successfully. There is not an error code
> associated with the message. I have researched with little success. Any
> thoughts as to what is occurring?
> Thank you...paldba|||No I do not.
"Russell" wrote:
> Have you got it set to backup the transaction log as part of this
> maintainence plan? As in simple mode the transaction log cannot be backed up.
> "paldba" wrote:
> > I currently have maintenance plans defined to run backups for all DB's. For
> > one specific DB I have the reovery model set to simple. When the backup runs
> > for this DB I am receiving the following msg in the log: Backup Failed to
> > Complete the Command Dump Transaction xxxx With Truncate Only'. The backup
> > of the database completes successfully. There is not an error code
> > associated with the message. I have researched with little success. Any
> > thoughts as to what is occurring?
> >
> > Thank you...paldba|||Seems you have a maint plan where you have included transaction log backups. and one of your
database is in simple recovery mode. You cannot perform log backup for a database in simple recovery
mode.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"paldba" <paldba@.discussions.microsoft.com> wrote in message
news:EF18B7A5-F0CA-4925-8306-D7317C474B58@.microsoft.com...
> I currently have maintenance plans defined to run backups for all DB's. For
> one specific DB I have the reovery model set to simple. When the backup runs
> for this DB I am receiving the following msg in the log: Backup Failed to
> Complete the Command Dump Transaction xxxx With Truncate Only'. The backup
> of the database completes successfully. There is not an error code
> associated with the message. I have researched with little success. Any
> thoughts as to what is occurring?
> Thank you...paldba|||For a database set to simple recovery, you cannot backup the transaction
log - even to truncate the log (which is unnecessary). Attempting to do so
results in an error.
"paldba" <paldba@.discussions.microsoft.com> wrote in message
news:DAC6AE7F-260F-4364-8C05-5EF31B7A272F@.microsoft.com...
> No I do not.
> "Russell" wrote:
> > Have you got it set to backup the transaction log as part of this
> > maintainence plan? As in simple mode the transaction log cannot be
backed up.
> >
> > "paldba" wrote:
> >
> > > I currently have maintenance plans defined to run backups for all
DB's. For
> > > one specific DB I have the reovery model set to simple. When the
backup runs
> > > for this DB I am receiving the following msg in the log: Backup
Failed to
> > > Complete the Command Dump Transaction xxxx With Truncate Only'. The
backup
> > > of the database completes successfully. There is not an error code
> > > associated with the message. I have researched with little success.
Any
> > > thoughts as to what is occurring?
> > >
> > > Thank you...paldba|||Thank you for your response. I have verifed several times that that
maintenance plans are not set up to perform transaction log backups. This
msg just started occurring in the logs after months of running error free.
No changes have been made to the DB or the maintenance plan.
"Tibor Karaszi" wrote:
> Seems you have a maint plan where you have included transaction log backups. and one of your
> database is in simple recovery mode. You cannot perform log backup for a database in simple recovery
> mode.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "paldba" <paldba@.discussions.microsoft.com> wrote in message
> news:EF18B7A5-F0CA-4925-8306-D7317C474B58@.microsoft.com...
> > I currently have maintenance plans defined to run backups for all DB's. For
> > one specific DB I have the reovery model set to simple. When the backup runs
> > for this DB I am receiving the following msg in the log: Backup Failed to
> > Complete the Command Dump Transaction xxxx With Truncate Only'. The backup
> > of the database completes successfully. There is not an error code
> > associated with the message. I have researched with little success. Any
> > thoughts as to what is occurring?
> >
> > Thank you...paldba
>
>|||Ahh, I missed the WITH TRUNCATE_ONLY part. What version of SQL server? Someone is executing this
command and you need to hunt that person down and ask why...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"paldba" <paldba@.discussions.microsoft.com> wrote in message
news:1AFC4C74-927F-40DB-AD0C-D002E76DF3C7@.microsoft.com...
> Thank you for your response. I have verifed several times that that
> maintenance plans are not set up to perform transaction log backups. This
> msg just started occurring in the logs after months of running error free.
> No changes have been made to the DB or the maintenance plan.
> "Tibor Karaszi" wrote:
> > Seems you have a maint plan where you have included transaction log backups. and one of your
> > database is in simple recovery mode. You cannot perform log backup for a database in simple
recovery
> > mode.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "paldba" <paldba@.discussions.microsoft.com> wrote in message
> > news:EF18B7A5-F0CA-4925-8306-D7317C474B58@.microsoft.com...
> > > I currently have maintenance plans defined to run backups for all DB's. For
> > > one specific DB I have the reovery model set to simple. When the backup runs
> > > for this DB I am receiving the following msg in the log: Backup Failed to
> > > Complete the Command Dump Transaction xxxx With Truncate Only'. The backup
> > > of the database completes successfully. There is not an error code
> > > associated with the message. I have researched with little success. Any
> > > thoughts as to what is occurring?
> > >
> > > Thank you...paldba
> >
> >
> >|||We are running SQL Server 2000. The message that appears in the log is
occurs exactly when the maintenance plan is running. I reran the maintenance
plan a few moments ago and the same message was generated in the log.
"Tibor Karaszi" wrote:
> Ahh, I missed the WITH TRUNCATE_ONLY part. What version of SQL server? Someone is executing this
> command and you need to hunt that person down and ask why...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "paldba" <paldba@.discussions.microsoft.com> wrote in message
> news:1AFC4C74-927F-40DB-AD0C-D002E76DF3C7@.microsoft.com...
> > Thank you for your response. I have verifed several times that that
> > maintenance plans are not set up to perform transaction log backups. This
> > msg just started occurring in the logs after months of running error free.
> > No changes have been made to the DB or the maintenance plan.
> >
> > "Tibor Karaszi" wrote:
> >
> > > Seems you have a maint plan where you have included transaction log backups. and one of your
> > > database is in simple recovery mode. You cannot perform log backup for a database in simple
> recovery
> > > mode.
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > >
> > >
> > > "paldba" <paldba@.discussions.microsoft.com> wrote in message
> > > news:EF18B7A5-F0CA-4925-8306-D7317C474B58@.microsoft.com...
> > > > I currently have maintenance plans defined to run backups for all DB's. For
> > > > one specific DB I have the reovery model set to simple. When the backup runs
> > > > for this DB I am receiving the following msg in the log: Backup Failed to
> > > > Complete the Command Dump Transaction xxxx With Truncate Only'. The backup
> > > > of the database completes successfully. There is not an error code
> > > > associated with the message. I have researched with little success. Any
> > > > thoughts as to what is occurring?
> > > >
> > > > Thank you...paldba
> > >
> > >
> > >
>
>|||Strange. I don't use maint plans myself. I can only assume that is either a bug in maint wiz, or
some really strange design decision.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"paldba" <paldba@.discussions.microsoft.com> wrote in message
news:87B7E14E-EB90-433B-8F26-FDED7E1FD1A4@.microsoft.com...
> We are running SQL Server 2000. The message that appears in the log is
> occurs exactly when the maintenance plan is running. I reran the maintenance
> plan a few moments ago and the same message was generated in the log.
> "Tibor Karaszi" wrote:
>> Ahh, I missed the WITH TRUNCATE_ONLY part. What version of SQL server? Someone is executing this
>> command and you need to hunt that person down and ask why...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "paldba" <paldba@.discussions.microsoft.com> wrote in message
>> news:1AFC4C74-927F-40DB-AD0C-D002E76DF3C7@.microsoft.com...
>> > Thank you for your response. I have verifed several times that that
>> > maintenance plans are not set up to perform transaction log backups. This
>> > msg just started occurring in the logs after months of running error free.
>> > No changes have been made to the DB or the maintenance plan.
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> > > Seems you have a maint plan where you have included transaction log backups. and one of your
>> > > database is in simple recovery mode. You cannot perform log backup for a database in simple
>> recovery
>> > > mode.
>> > >
>> > > --
>> > > Tibor Karaszi, SQL Server MVP
>> > > http://www.karaszi.com/sqlserver/default.asp
>> > > http://www.solidqualitylearning.com/
>> > >
>> > >
>> > > "paldba" <paldba@.discussions.microsoft.com> wrote in message
>> > > news:EF18B7A5-F0CA-4925-8306-D7317C474B58@.microsoft.com...
>> > > > I currently have maintenance plans defined to run backups for all DB's. For
>> > > > one specific DB I have the reovery model set to simple. When the backup runs
>> > > > for this DB I am receiving the following msg in the log: Backup Failed to
>> > > > Complete the Command Dump Transaction xxxx With Truncate Only'. The backup
>> > > > of the database completes successfully. There is not an error code
>> > > > associated with the message. I have researched with little success. Any
>> > > > thoughts as to what is occurring?
>> > > >
>> > > > Thank you...paldba
>> > >
>> > >
>> > >
>>|||Delete and recreate the plan?
Jeff
"paldba" <paldba@.discussions.microsoft.com> wrote in message
news:87B7E14E-EB90-433B-8F26-FDED7E1FD1A4@.microsoft.com...
> We are running SQL Server 2000. The message that appears in the log is
> occurs exactly when the maintenance plan is running. I reran the
maintenance
> plan a few moments ago and the same message was generated in the log.
> "Tibor Karaszi" wrote:
> > Ahh, I missed the WITH TRUNCATE_ONLY part. What version of SQL server?
Someone is executing this
> > command and you need to hunt that person down and ask why...
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "paldba" <paldba@.discussions.microsoft.com> wrote in message
> > news:1AFC4C74-927F-40DB-AD0C-D002E76DF3C7@.microsoft.com...
> > > Thank you for your response. I have verifed several times that that
> > > maintenance plans are not set up to perform transaction log backups.
This
> > > msg just started occurring in the logs after months of running error
free.
> > > No changes have been made to the DB or the maintenance plan.
> > >
> > > "Tibor Karaszi" wrote:
> > >
> > > > Seems you have a maint plan where you have included transaction log
backups. and one of your
> > > > database is in simple recovery mode. You cannot perform log backup
for a database in simple
> > recovery
> > > > mode.
> > > >
> > > > --
> > > > Tibor Karaszi, SQL Server MVP
> > > > http://www.karaszi.com/sqlserver/default.asp
> > > > http://www.solidqualitylearning.com/
> > > >
> > > >
> > > > "paldba" <paldba@.discussions.microsoft.com> wrote in message
> > > > news:EF18B7A5-F0CA-4925-8306-D7317C474B58@.microsoft.com...
> > > > > I currently have maintenance plans defined to run backups for all
DB's. For
> > > > > one specific DB I have the reovery model set to simple. When the
backup runs
> > > > > for this DB I am receiving the following msg in the log: Backup
Failed to
> > > > > Complete the Command Dump Transaction xxxx With Truncate Only'.
The backup
> > > > > of the database completes successfully. There is not an error
code
> > > > > associated with the message. I have researched with little
success. Any
> > > > > thoughts as to what is occurring?
> > > > >
> > > > > Thank you...paldba
> > > >
> > > >
> > > >
> >
> >
> >|||Resolved. I deleted and redefined the plan exactly as it was and the msg's
have mysteriously disappeared. Thanks for the suggestion. Since there
weren't any changes to the maintenance plan, I didn't think to delete it.
Thanks much!
"Jeff Dillon" wrote:
> Delete and recreate the plan?
> Jeff
> "paldba" <paldba@.discussions.microsoft.com> wrote in message
> news:87B7E14E-EB90-433B-8F26-FDED7E1FD1A4@.microsoft.com...
> > We are running SQL Server 2000. The message that appears in the log is
> > occurs exactly when the maintenance plan is running. I reran the
> maintenance
> > plan a few moments ago and the same message was generated in the log.
> >
> > "Tibor Karaszi" wrote:
> >
> > > Ahh, I missed the WITH TRUNCATE_ONLY part. What version of SQL server?
> Someone is executing this
> > > command and you need to hunt that person down and ask why...
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > >
> > >
> > > "paldba" <paldba@.discussions.microsoft.com> wrote in message
> > > news:1AFC4C74-927F-40DB-AD0C-D002E76DF3C7@.microsoft.com...
> > > > Thank you for your response. I have verifed several times that that
> > > > maintenance plans are not set up to perform transaction log backups.
> This
> > > > msg just started occurring in the logs after months of running error
> free.
> > > > No changes have been made to the DB or the maintenance plan.
> > > >
> > > > "Tibor Karaszi" wrote:
> > > >
> > > > > Seems you have a maint plan where you have included transaction log
> backups. and one of your
> > > > > database is in simple recovery mode. You cannot perform log backup
> for a database in simple
> > > recovery
> > > > > mode.
> > > > >
> > > > > --
> > > > > Tibor Karaszi, SQL Server MVP
> > > > > http://www.karaszi.com/sqlserver/default.asp
> > > > > http://www.solidqualitylearning.com/
> > > > >
> > > > >
> > > > > "paldba" <paldba@.discussions.microsoft.com> wrote in message
> > > > > news:EF18B7A5-F0CA-4925-8306-D7317C474B58@.microsoft.com...
> > > > > > I currently have maintenance plans defined to run backups for all
> DB's. For
> > > > > > one specific DB I have the reovery model set to simple. When the
> backup runs
> > > > > > for this DB I am receiving the following msg in the log: Backup
> Failed to
> > > > > > Complete the Command Dump Transaction xxxx With Truncate Only'.
> The backup
> > > > > > of the database completes successfully. There is not an error
> code
> > > > > > associated with the message. I have researched with little
> success. Any
> > > > > > thoughts as to what is occurring?
> > > > > >
> > > > > > Thank you...paldba
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
Dump Transaction Log
Hello,
I'm getting the following error on 1 of our DB's;
The log file for database 'database' is full. Back up the transaction
log for the database to free up some log space.
How can I dump the transaction log?
Thanks,
C
BACKUP LOG { database_name | @.database_name_var }
{
[ WITH
{ NO_LOG | TRUNCATE_ONLY } ]
}
example:
BACKUP LOG Northwind WITH TRUNCATE_ONLY
if you dont need your transaction logs backed up regularly (not worried
about data loss, etc) then I recommend placing the database in SIMPLE
Recovery Mode.
Greg Jackson
PDX, Oregon
"Craig Alexander" <craig@.itas.net> wrote in message
news:2582929c.0407121143.16fa26b9@.posting.google.c om...
> Hello,
> I'm getting the following error on 1 of our DB's;
> The log file for database 'database' is full. Back up the transaction
> log for the database to free up some log space.
>
> How can I dump the transaction log?
> Thanks,
> C
|||backup log <databasename> to disk = 'filename'
Also check you recovery mode
select databasepropertyex('databasename','Recovery')
If you don't need point in time recovery, set the database to simple
recovery mode
alter database <databasename> set recovery simple
and just use full backups. If you require more upto date backups set up a
regular job to backup your transaction log (the easisest way is to use a
maintenance plan). You can also set the log file to autogrow to prevent this
issue assuming you have done one of the steps above. You should set your log
to a reasonable size so its not constally growing. Have a look at
INF: Shrinking the Transaction Log in
SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default...;en-us;Q272318
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/default...b;EN-US;256650
INF: Causes of SQL Transaction Log Filling Up
http://support.microsoft.com/default...b;EN-US;110139
INFO: Reasons Why SQL Transaction Log Is Not Being Truncated
http://support.microsoft.com/default...kb;EN-US;62866
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Craig Alexander" <craig@.itas.net> wrote in message
news:2582929c.0407121143.16fa26b9@.posting.google.c om...
> Hello,
> I'm getting the following error on 1 of our DB's;
> The log file for database 'database' is full. Back up the transaction
> log for the database to free up some log space.
>
> How can I dump the transaction log?
> Thanks,
> C
I'm getting the following error on 1 of our DB's;
The log file for database 'database' is full. Back up the transaction
log for the database to free up some log space.
How can I dump the transaction log?
Thanks,
C
BACKUP LOG { database_name | @.database_name_var }
{
[ WITH
{ NO_LOG | TRUNCATE_ONLY } ]
}
example:
BACKUP LOG Northwind WITH TRUNCATE_ONLY
if you dont need your transaction logs backed up regularly (not worried
about data loss, etc) then I recommend placing the database in SIMPLE
Recovery Mode.
Greg Jackson
PDX, Oregon
"Craig Alexander" <craig@.itas.net> wrote in message
news:2582929c.0407121143.16fa26b9@.posting.google.c om...
> Hello,
> I'm getting the following error on 1 of our DB's;
> The log file for database 'database' is full. Back up the transaction
> log for the database to free up some log space.
>
> How can I dump the transaction log?
> Thanks,
> C
|||backup log <databasename> to disk = 'filename'
Also check you recovery mode
select databasepropertyex('databasename','Recovery')
If you don't need point in time recovery, set the database to simple
recovery mode
alter database <databasename> set recovery simple
and just use full backups. If you require more upto date backups set up a
regular job to backup your transaction log (the easisest way is to use a
maintenance plan). You can also set the log file to autogrow to prevent this
issue assuming you have done one of the steps above. You should set your log
to a reasonable size so its not constally growing. Have a look at
INF: Shrinking the Transaction Log in
SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default...;en-us;Q272318
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/default...b;EN-US;256650
INF: Causes of SQL Transaction Log Filling Up
http://support.microsoft.com/default...b;EN-US;110139
INFO: Reasons Why SQL Transaction Log Is Not Being Truncated
http://support.microsoft.com/default...kb;EN-US;62866
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Craig Alexander" <craig@.itas.net> wrote in message
news:2582929c.0407121143.16fa26b9@.posting.google.c om...
> Hello,
> I'm getting the following error on 1 of our DB's;
> The log file for database 'database' is full. Back up the transaction
> log for the database to free up some log space.
>
> How can I dump the transaction log?
> Thanks,
> C
Dump Transaction Log
Hello,
I'm getting the following error on 1 of our DB's;
The log file for database 'database' is full. Back up the transaction
log for the database to free up some log space.
How can I dump the transaction log?
Thanks,
CBACKUP LOG { database_name | @.database_name_var }
{
[ WITH
{ NO_LOG | TRUNCATE_ONLY } ]
}
example:
BACKUP LOG Northwind WITH TRUNCATE_ONLY
if you dont need your transaction logs backed up regularly (not worried
about data loss, etc) then I recommend placing the database in SIMPLE
Recovery Mode.
Greg Jackson
PDX, Oregon
"Craig Alexander" <craig@.itas.net> wrote in message
news:2582929c.0407121143.16fa26b9@.posting.google.com...
> Hello,
> I'm getting the following error on 1 of our DB's;
> The log file for database 'database' is full. Back up the transaction
> log for the database to free up some log space.
>
> How can I dump the transaction log?
> Thanks,
> C|||backup log <databasename> to disk = 'filename'
Also check you recovery mode
select databasepropertyex('databasename','Recovery')
If you don't need point in time recovery, set the database to simple
recovery mode
alter database <databasename> set recovery simple
and just use full backups. If you require more upto date backups set up a
regular job to backup your transaction log (the easisest way is to use a
maintenance plan). You can also set the log file to autogrow to prevent this
issue assuming you have done one of the steps above. You should set your log
to a reasonable size so its not constally growing. Have a look at
INF: Shrinking the Transaction Log in
SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q272318
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/default.aspx?scid=kb;EN-US;256650
INF: Causes of SQL Transaction Log Filling Up
http://support.microsoft.com/default.aspx?scid=kb;EN-US;110139
INFO: Reasons Why SQL Transaction Log Is Not Being Truncated
http://support.microsoft.com/default.aspx?scid=kb;EN-US;62866
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Craig Alexander" <craig@.itas.net> wrote in message
news:2582929c.0407121143.16fa26b9@.posting.google.com...
> Hello,
> I'm getting the following error on 1 of our DB's;
> The log file for database 'database' is full. Back up the transaction
> log for the database to free up some log space.
>
> How can I dump the transaction log?
> Thanks,
> Csql
I'm getting the following error on 1 of our DB's;
The log file for database 'database' is full. Back up the transaction
log for the database to free up some log space.
How can I dump the transaction log?
Thanks,
CBACKUP LOG { database_name | @.database_name_var }
{
[ WITH
{ NO_LOG | TRUNCATE_ONLY } ]
}
example:
BACKUP LOG Northwind WITH TRUNCATE_ONLY
if you dont need your transaction logs backed up regularly (not worried
about data loss, etc) then I recommend placing the database in SIMPLE
Recovery Mode.
Greg Jackson
PDX, Oregon
"Craig Alexander" <craig@.itas.net> wrote in message
news:2582929c.0407121143.16fa26b9@.posting.google.com...
> Hello,
> I'm getting the following error on 1 of our DB's;
> The log file for database 'database' is full. Back up the transaction
> log for the database to free up some log space.
>
> How can I dump the transaction log?
> Thanks,
> C|||backup log <databasename> to disk = 'filename'
Also check you recovery mode
select databasepropertyex('databasename','Recovery')
If you don't need point in time recovery, set the database to simple
recovery mode
alter database <databasename> set recovery simple
and just use full backups. If you require more upto date backups set up a
regular job to backup your transaction log (the easisest way is to use a
maintenance plan). You can also set the log file to autogrow to prevent this
issue assuming you have done one of the steps above. You should set your log
to a reasonable size so its not constally growing. Have a look at
INF: Shrinking the Transaction Log in
SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q272318
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/default.aspx?scid=kb;EN-US;256650
INF: Causes of SQL Transaction Log Filling Up
http://support.microsoft.com/default.aspx?scid=kb;EN-US;110139
INFO: Reasons Why SQL Transaction Log Is Not Being Truncated
http://support.microsoft.com/default.aspx?scid=kb;EN-US;62866
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Craig Alexander" <craig@.itas.net> wrote in message
news:2582929c.0407121143.16fa26b9@.posting.google.com...
> Hello,
> I'm getting the following error on 1 of our DB's;
> The log file for database 'database' is full. Back up the transaction
> log for the database to free up some log space.
>
> How can I dump the transaction log?
> Thanks,
> Csql
Dump Transaction Log
Hello,
I'm getting the following error on 1 of our DB's;
The log file for database 'database' is full. Back up the transaction
log for the database to free up some log space.
How can I dump the transaction log?
Thanks,
CBACKUP LOG { database_name | @.database_name_var }
{
[ WITH
{ NO_LOG | TRUNCATE_ONLY } ]
}
example:
BACKUP LOG Northwind WITH TRUNCATE_ONLY
if you dont need your transaction logs backed up regularly (not worried
about data loss, etc) then I recommend placing the database in SIMPLE
Recovery Mode.
Greg Jackson
PDX, Oregon
"Craig Alexander" <craig@.itas.net> wrote in message
news:2582929c.0407121143.16fa26b9@.posting.google.com...
> Hello,
> I'm getting the following error on 1 of our DB's;
> The log file for database 'database' is full. Back up the transaction
> log for the database to free up some log space.
>
> How can I dump the transaction log?
> Thanks,
> C|||backup log <databasename> to disk = 'filename'
Also check you recovery mode
select databasepropertyex('databasename','Recov
ery')
If you don't need point in time recovery, set the database to simple
recovery mode
alter database <databasename> set recovery simple
and just use full backups. If you require more upto date backups set up a
regular job to backup your transaction log (the easisest way is to use a
maintenance plan). You can also set the log file to autogrow to prevent this
issue assuming you have done one of the steps above. You should set your log
to a reasonable size so its not constally growing. Have a look at
INF: Shrinking the Transaction Log in
SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/defaul...b;en-us;Q272318
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/defaul...kb;EN-US;256650
INF: Causes of SQL Transaction Log Filling Up
http://support.microsoft.com/defaul...kb;EN-US;110139
INFO: Reasons Why SQL Transaction Log Is Not Being Truncated
http://support.microsoft.com/defaul...=kb;EN-US;62866
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Craig Alexander" <craig@.itas.net> wrote in message
news:2582929c.0407121143.16fa26b9@.posting.google.com...
> Hello,
> I'm getting the following error on 1 of our DB's;
> The log file for database 'database' is full. Back up the transaction
> log for the database to free up some log space.
>
> How can I dump the transaction log?
> Thanks,
> C
I'm getting the following error on 1 of our DB's;
The log file for database 'database' is full. Back up the transaction
log for the database to free up some log space.
How can I dump the transaction log?
Thanks,
CBACKUP LOG { database_name | @.database_name_var }
{
[ WITH
{ NO_LOG | TRUNCATE_ONLY } ]
}
example:
BACKUP LOG Northwind WITH TRUNCATE_ONLY
if you dont need your transaction logs backed up regularly (not worried
about data loss, etc) then I recommend placing the database in SIMPLE
Recovery Mode.
Greg Jackson
PDX, Oregon
"Craig Alexander" <craig@.itas.net> wrote in message
news:2582929c.0407121143.16fa26b9@.posting.google.com...
> Hello,
> I'm getting the following error on 1 of our DB's;
> The log file for database 'database' is full. Back up the transaction
> log for the database to free up some log space.
>
> How can I dump the transaction log?
> Thanks,
> C|||backup log <databasename> to disk = 'filename'
Also check you recovery mode
select databasepropertyex('databasename','Recov
ery')
If you don't need point in time recovery, set the database to simple
recovery mode
alter database <databasename> set recovery simple
and just use full backups. If you require more upto date backups set up a
regular job to backup your transaction log (the easisest way is to use a
maintenance plan). You can also set the log file to autogrow to prevent this
issue assuming you have done one of the steps above. You should set your log
to a reasonable size so its not constally growing. Have a look at
INF: Shrinking the Transaction Log in
SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/defaul...b;en-us;Q272318
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/defaul...kb;EN-US;256650
INF: Causes of SQL Transaction Log Filling Up
http://support.microsoft.com/defaul...kb;EN-US;110139
INFO: Reasons Why SQL Transaction Log Is Not Being Truncated
http://support.microsoft.com/defaul...=kb;EN-US;62866
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Craig Alexander" <craig@.itas.net> wrote in message
news:2582929c.0407121143.16fa26b9@.posting.google.com...
> Hello,
> I'm getting the following error on 1 of our DB's;
> The log file for database 'database' is full. Back up the transaction
> log for the database to free up some log space.
>
> How can I dump the transaction log?
> Thanks,
> C
DUMP TRANSACTION database WITH NO_LOG
Hello there,
I am trying to learn about MS-SQL Server on my own and am faced with a
problem that requires action quickly, even though I would prefer to take my
time and dig out the answer from all the documentation available.
On the web site that I manage, some log files grow to be as large or larger
than the MDF files themselves, and I am concernced with preserving hard disk
space for obvious reasons. I asked the web hosting company's tech support
department for a way to reduce the size of these log files, and they pointed
me to this suggestion:
DUMP TRANSACTION database WITH NO_LOG
While I have used this command to reduce the log file size successfully, I
am concerned that I am actually truncating data that has not been committed
to the database yet. Am I worrying needlessly? Or am I actually losing dat
a
when I run this command?
I have observed that when I run this query from the SQL Query Analyzer that
the log file does not instantly get smaller in size. Rather it gets
substantially smaller a few hours later. The MDF file is about 5.3 GB and
the LDF file is about 9 GB.
Thank you for whatever help you can offer.
Sincerely,
Mr. MixterMick,
Don't worry. This command will truncate committed transactions from the
transaction log. If you don't have much control over your database in terms
of backups, etc, consider changing the recovery model to simple, this means
that committed transactions are removed from the transaction log, which will
stop it growing.
Once you have changed the recovery model and truncated the log, you will
need to shrink the log file using DBCC SHRINKFILE. Look that up in BOL.
Sounds like your hosting company admins need to go on a SQL Server course.
Regards,
Mark.
"Mick Keily" wrote:
> Hello there,
> I am trying to learn about MS-SQL Server on my own and am faced with a
> problem that requires action quickly, even though I would prefer to take m
y
> time and dig out the answer from all the documentation available.
> On the web site that I manage, some log files grow to be as large or large
r
> than the MDF files themselves, and I am concernced with preserving hard di
sk
> space for obvious reasons. I asked the web hosting company's tech support
> department for a way to reduce the size of these log files, and they point
ed
> me to this suggestion:
> DUMP TRANSACTION database WITH NO_LOG
> While I have used this command to reduce the log file size successfully, I
> am concerned that I am actually truncating data that has not been committe
d
> to the database yet. Am I worrying needlessly? Or am I actually losing d
ata
> when I run this command?
> I have observed that when I run this query from the SQL Query Analyzer tha
t
> the log file does not instantly get smaller in size. Rather it gets
> substantially smaller a few hours later. The MDF file is about 5.3 GB and
> the LDF file is about 9 GB.
> Thank you for whatever help you can offer.
> Sincerely,
> Mr. Mixter
I am trying to learn about MS-SQL Server on my own and am faced with a
problem that requires action quickly, even though I would prefer to take my
time and dig out the answer from all the documentation available.
On the web site that I manage, some log files grow to be as large or larger
than the MDF files themselves, and I am concernced with preserving hard disk
space for obvious reasons. I asked the web hosting company's tech support
department for a way to reduce the size of these log files, and they pointed
me to this suggestion:
DUMP TRANSACTION database WITH NO_LOG
While I have used this command to reduce the log file size successfully, I
am concerned that I am actually truncating data that has not been committed
to the database yet. Am I worrying needlessly? Or am I actually losing dat
a
when I run this command?
I have observed that when I run this query from the SQL Query Analyzer that
the log file does not instantly get smaller in size. Rather it gets
substantially smaller a few hours later. The MDF file is about 5.3 GB and
the LDF file is about 9 GB.
Thank you for whatever help you can offer.
Sincerely,
Mr. MixterMick,
Don't worry. This command will truncate committed transactions from the
transaction log. If you don't have much control over your database in terms
of backups, etc, consider changing the recovery model to simple, this means
that committed transactions are removed from the transaction log, which will
stop it growing.
Once you have changed the recovery model and truncated the log, you will
need to shrink the log file using DBCC SHRINKFILE. Look that up in BOL.
Sounds like your hosting company admins need to go on a SQL Server course.
Regards,
Mark.
"Mick Keily" wrote:
> Hello there,
> I am trying to learn about MS-SQL Server on my own and am faced with a
> problem that requires action quickly, even though I would prefer to take m
y
> time and dig out the answer from all the documentation available.
> On the web site that I manage, some log files grow to be as large or large
r
> than the MDF files themselves, and I am concernced with preserving hard di
sk
> space for obvious reasons. I asked the web hosting company's tech support
> department for a way to reduce the size of these log files, and they point
ed
> me to this suggestion:
> DUMP TRANSACTION database WITH NO_LOG
> While I have used this command to reduce the log file size successfully, I
> am concerned that I am actually truncating data that has not been committe
d
> to the database yet. Am I worrying needlessly? Or am I actually losing d
ata
> when I run this command?
> I have observed that when I run this query from the SQL Query Analyzer tha
t
> the log file does not instantly get smaller in size. Rather it gets
> substantially smaller a few hours later. The MDF file is about 5.3 GB and
> the LDF file is about 9 GB.
> Thank you for whatever help you can offer.
> Sincerely,
> Mr. Mixter
DUMP TRANSACTION database WITH NO_LOG
Hello there,
I am trying to learn about MS-SQL Server on my own and am faced with a
problem that requires action quickly, even though I would prefer to take my
time and dig out the answer from all the documentation available.
On the web site that I manage, some log files grow to be as large or larger
than the MDF files themselves, and I am concernced with preserving hard disk
space for obvious reasons. I asked the web hosting company's tech support
department for a way to reduce the size of these log files, and they pointed
me to this suggestion:
DUMP TRANSACTION database WITH NO_LOG
While I have used this command to reduce the log file size successfully, I
am concerned that I am actually truncating data that has not been committed
to the database yet. Am I worrying needlessly? Or am I actually losing data
when I run this command?
I have observed that when I run this query from the SQL Query Analyzer that
the log file does not instantly get smaller in size. Rather it gets
substantially smaller a few hours later. The MDF file is about 5.3 GB and
the LDF file is about 9 GB.
Thank you for whatever help you can offer.
Sincerely,
Mr. Mixter
Mick,
Don't worry. This command will truncate committed transactions from the
transaction log. If you don't have much control over your database in terms
of backups, etc, consider changing the recovery model to simple, this means
that committed transactions are removed from the transaction log, which will
stop it growing.
Once you have changed the recovery model and truncated the log, you will
need to shrink the log file using DBCC SHRINKFILE. Look that up in BOL.
Sounds like your hosting company admins need to go on a SQL Server course.
Regards,
Mark.
"Mick Keily" wrote:
> Hello there,
> I am trying to learn about MS-SQL Server on my own and am faced with a
> problem that requires action quickly, even though I would prefer to take my
> time and dig out the answer from all the documentation available.
> On the web site that I manage, some log files grow to be as large or larger
> than the MDF files themselves, and I am concernced with preserving hard disk
> space for obvious reasons. I asked the web hosting company's tech support
> department for a way to reduce the size of these log files, and they pointed
> me to this suggestion:
> DUMP TRANSACTION database WITH NO_LOG
> While I have used this command to reduce the log file size successfully, I
> am concerned that I am actually truncating data that has not been committed
> to the database yet. Am I worrying needlessly? Or am I actually losing data
> when I run this command?
> I have observed that when I run this query from the SQL Query Analyzer that
> the log file does not instantly get smaller in size. Rather it gets
> substantially smaller a few hours later. The MDF file is about 5.3 GB and
> the LDF file is about 9 GB.
> Thank you for whatever help you can offer.
> Sincerely,
> Mr. Mixter
I am trying to learn about MS-SQL Server on my own and am faced with a
problem that requires action quickly, even though I would prefer to take my
time and dig out the answer from all the documentation available.
On the web site that I manage, some log files grow to be as large or larger
than the MDF files themselves, and I am concernced with preserving hard disk
space for obvious reasons. I asked the web hosting company's tech support
department for a way to reduce the size of these log files, and they pointed
me to this suggestion:
DUMP TRANSACTION database WITH NO_LOG
While I have used this command to reduce the log file size successfully, I
am concerned that I am actually truncating data that has not been committed
to the database yet. Am I worrying needlessly? Or am I actually losing data
when I run this command?
I have observed that when I run this query from the SQL Query Analyzer that
the log file does not instantly get smaller in size. Rather it gets
substantially smaller a few hours later. The MDF file is about 5.3 GB and
the LDF file is about 9 GB.
Thank you for whatever help you can offer.
Sincerely,
Mr. Mixter
Mick,
Don't worry. This command will truncate committed transactions from the
transaction log. If you don't have much control over your database in terms
of backups, etc, consider changing the recovery model to simple, this means
that committed transactions are removed from the transaction log, which will
stop it growing.
Once you have changed the recovery model and truncated the log, you will
need to shrink the log file using DBCC SHRINKFILE. Look that up in BOL.
Sounds like your hosting company admins need to go on a SQL Server course.
Regards,
Mark.
"Mick Keily" wrote:
> Hello there,
> I am trying to learn about MS-SQL Server on my own and am faced with a
> problem that requires action quickly, even though I would prefer to take my
> time and dig out the answer from all the documentation available.
> On the web site that I manage, some log files grow to be as large or larger
> than the MDF files themselves, and I am concernced with preserving hard disk
> space for obvious reasons. I asked the web hosting company's tech support
> department for a way to reduce the size of these log files, and they pointed
> me to this suggestion:
> DUMP TRANSACTION database WITH NO_LOG
> While I have used this command to reduce the log file size successfully, I
> am concerned that I am actually truncating data that has not been committed
> to the database yet. Am I worrying needlessly? Or am I actually losing data
> when I run this command?
> I have observed that when I run this query from the SQL Query Analyzer that
> the log file does not instantly get smaller in size. Rather it gets
> substantially smaller a few hours later. The MDF file is about 5.3 GB and
> the LDF file is about 9 GB.
> Thank you for whatever help you can offer.
> Sincerely,
> Mr. Mixter
DUMP TRANSACTION database WITH NO_LOG
Hello there,
I am trying to learn about MS-SQL Server on my own and am faced with a
problem that requires action quickly, even though I would prefer to take my
time and dig out the answer from all the documentation available.
On the web site that I manage, some log files grow to be as large or larger
than the MDF files themselves, and I am concernced with preserving hard disk
space for obvious reasons. I asked the web hosting company's tech support
department for a way to reduce the size of these log files, and they pointed
me to this suggestion:
DUMP TRANSACTION database WITH NO_LOG
While I have used this command to reduce the log file size successfully, I
am concerned that I am actually truncating data that has not been committed
to the database yet. Am I worrying needlessly? Or am I actually losing data
when I run this command?
I have observed that when I run this query from the SQL Query Analyzer that
the log file does not instantly get smaller in size. Rather it gets
substantially smaller a few hours later. The MDF file is about 5.3 GB and
the LDF file is about 9 GB.
Thank you for whatever help you can offer.
Sincerely,
Mr. MixterMick,
Don't worry. This command will truncate committed transactions from the
transaction log. If you don't have much control over your database in terms
of backups, etc, consider changing the recovery model to simple, this means
that committed transactions are removed from the transaction log, which will
stop it growing.
Once you have changed the recovery model and truncated the log, you will
need to shrink the log file using DBCC SHRINKFILE. Look that up in BOL.
Sounds like your hosting company admins need to go on a SQL Server course.
Regards,
Mark.
"Mick Keily" wrote:
> Hello there,
> I am trying to learn about MS-SQL Server on my own and am faced with a
> problem that requires action quickly, even though I would prefer to take my
> time and dig out the answer from all the documentation available.
> On the web site that I manage, some log files grow to be as large or larger
> than the MDF files themselves, and I am concernced with preserving hard disk
> space for obvious reasons. I asked the web hosting company's tech support
> department for a way to reduce the size of these log files, and they pointed
> me to this suggestion:
> DUMP TRANSACTION database WITH NO_LOG
> While I have used this command to reduce the log file size successfully, I
> am concerned that I am actually truncating data that has not been committed
> to the database yet. Am I worrying needlessly? Or am I actually losing data
> when I run this command?
> I have observed that when I run this query from the SQL Query Analyzer that
> the log file does not instantly get smaller in size. Rather it gets
> substantially smaller a few hours later. The MDF file is about 5.3 GB and
> the LDF file is about 9 GB.
> Thank you for whatever help you can offer.
> Sincerely,
> Mr. Mixter
I am trying to learn about MS-SQL Server on my own and am faced with a
problem that requires action quickly, even though I would prefer to take my
time and dig out the answer from all the documentation available.
On the web site that I manage, some log files grow to be as large or larger
than the MDF files themselves, and I am concernced with preserving hard disk
space for obvious reasons. I asked the web hosting company's tech support
department for a way to reduce the size of these log files, and they pointed
me to this suggestion:
DUMP TRANSACTION database WITH NO_LOG
While I have used this command to reduce the log file size successfully, I
am concerned that I am actually truncating data that has not been committed
to the database yet. Am I worrying needlessly? Or am I actually losing data
when I run this command?
I have observed that when I run this query from the SQL Query Analyzer that
the log file does not instantly get smaller in size. Rather it gets
substantially smaller a few hours later. The MDF file is about 5.3 GB and
the LDF file is about 9 GB.
Thank you for whatever help you can offer.
Sincerely,
Mr. MixterMick,
Don't worry. This command will truncate committed transactions from the
transaction log. If you don't have much control over your database in terms
of backups, etc, consider changing the recovery model to simple, this means
that committed transactions are removed from the transaction log, which will
stop it growing.
Once you have changed the recovery model and truncated the log, you will
need to shrink the log file using DBCC SHRINKFILE. Look that up in BOL.
Sounds like your hosting company admins need to go on a SQL Server course.
Regards,
Mark.
"Mick Keily" wrote:
> Hello there,
> I am trying to learn about MS-SQL Server on my own and am faced with a
> problem that requires action quickly, even though I would prefer to take my
> time and dig out the answer from all the documentation available.
> On the web site that I manage, some log files grow to be as large or larger
> than the MDF files themselves, and I am concernced with preserving hard disk
> space for obvious reasons. I asked the web hosting company's tech support
> department for a way to reduce the size of these log files, and they pointed
> me to this suggestion:
> DUMP TRANSACTION database WITH NO_LOG
> While I have used this command to reduce the log file size successfully, I
> am concerned that I am actually truncating data that has not been committed
> to the database yet. Am I worrying needlessly? Or am I actually losing data
> when I run this command?
> I have observed that when I run this query from the SQL Query Analyzer that
> the log file does not instantly get smaller in size. Rather it gets
> substantially smaller a few hours later. The MDF file is about 5.3 GB and
> the LDF file is about 9 GB.
> Thank you for whatever help you can offer.
> Sincerely,
> Mr. Mixter
DUMP TRANSACTION
I am running SQL 2000 SP4. I see the DUMP TRANSACTION command in BOL and
other Microsoft documentation but it doesn't seem to work. Is it still
supported. The command I am using is DUMP TRANSACTION WITH NO_LOG.
Rom Reis,
If you want to truncate the inactive portion of the Transaction Log of your
database, in SQL SERVER 2000 you use the following statement:
Backup Log DBNAME with TRUNCATE_ONLY
The " Dump Transaction " is a 6.x SQL Version Command.
Best Regards,
Paulo Conde?a.
"Tom Reis" wrote:
> I am running SQL 2000 SP4. I see the DUMP TRANSACTION command in BOL and
> other Microsoft documentation but it doesn't seem to work. Is it still
> supported. The command I am using is DUMP TRANSACTION WITH NO_LOG.
>
>
|||"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:%23aWjbJZCHHA.4892@.TK2MSFTNGP04.phx.gbl...
>I am running SQL 2000 SP4. I see the DUMP TRANSACTION command in BOL and
>other Microsoft documentation but it doesn't seem to work. Is it still
>supported. The command I am using is DUMP TRANSACTION WITH NO_LOG.
DUMP TRANSACTION is still supported, but is deprecated. Define "doesn't
seem to work". Does the equivalent BACKUP command do anything differently?
sql
other Microsoft documentation but it doesn't seem to work. Is it still
supported. The command I am using is DUMP TRANSACTION WITH NO_LOG.
Rom Reis,
If you want to truncate the inactive portion of the Transaction Log of your
database, in SQL SERVER 2000 you use the following statement:
Backup Log DBNAME with TRUNCATE_ONLY
The " Dump Transaction " is a 6.x SQL Version Command.
Best Regards,
Paulo Conde?a.
"Tom Reis" wrote:
> I am running SQL 2000 SP4. I see the DUMP TRANSACTION command in BOL and
> other Microsoft documentation but it doesn't seem to work. Is it still
> supported. The command I am using is DUMP TRANSACTION WITH NO_LOG.
>
>
|||"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:%23aWjbJZCHHA.4892@.TK2MSFTNGP04.phx.gbl...
>I am running SQL 2000 SP4. I see the DUMP TRANSACTION command in BOL and
>other Microsoft documentation but it doesn't seem to work. Is it still
>supported. The command I am using is DUMP TRANSACTION WITH NO_LOG.
DUMP TRANSACTION is still supported, but is deprecated. Define "doesn't
seem to work". Does the equivalent BACKUP command do anything differently?
sql
Subscribe to:
Posts (Atom)