Database Backups and Transaction Logs

Problem: Transaction log grew too big.
I took my database off line and then back online to ensure that no one was
connected to the database and then backed up the database.
I figured at this point I would be able to shrink the transaction log
because all of the 'pending' transactions would be clear - however I cannot.
Do I have to backup the transaction log? And in so doing will this solve my
problem?
Thanks
You must backup the log separately. Backing up the DB does NOT free up the
log. This is an online operation, so don't take the DB offline.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
Problem: Transaction log grew too big.
I took my database off line and then back online to ensure that no one was
connected to the database and then backed up the database.
I figured at this point I would be able to shrink the transaction log
because all of the 'pending' transactions would be clear - however I cannot.
Do I have to backup the transaction log? And in so doing will this solve my
problem?
Thanks
|||Stephen,
Production database? If so, backup the transaction log then shrink the
transaction log file using DBCC SHRINKFILE statment.
HTH
Jerry
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
> Problem: Transaction log grew too big.
> I took my database off line and then back online to ensure that no one was
> connected to the database and then backed up the database.
> I figured at this point I would be able to shrink the transaction log
> because all of the 'pending' transactions would be clear - however I
> cannot.
> Do I have to backup the transaction log? And in so doing will this solve
> my
> problem?
> Thanks
>
>
|||Stephen,
Also see:
http://www.support.microsoft.com/?id=272318
HTH
Jerry
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
> Problem: Transaction log grew too big.
> I took my database off line and then back online to ensure that no one was
> connected to the database and then backed up the database.
> I figured at this point I would be able to shrink the transaction log
> because all of the 'pending' transactions would be clear - however I
> cannot.
> Do I have to backup the transaction log? And in so doing will this solve
> my
> problem?
> Thanks
>
>
|||ok in the process of backing up the transaction log.
After that, I will run that command and then restrict the file growth.
Apparently the dba (which I am not nor am I a sql server dba) didn't set up
that functionality as part of a job. I will add a nightly backup. We have to
copy the database off that server to another box because there isn't
sufficient drive space available to do a backup.
Thanks for the quick response.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%232Cn$L2zFHA.720@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Stephen,
> Production database? If so, backup the transaction log then shrink the
> transaction log file using DBCC SHRINKFILE statment.
> HTH
> Jerry
> "Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
> news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
was
>
|||I suggest you read http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:emVLHa2zFHA.560@.TK2MSFTNGP12.phx.gbl...
> ok in the process of backing up the transaction log.
> After that, I will run that command and then restrict the file growth.
> Apparently the dba (which I am not nor am I a sql server dba) didn't set up
> that functionality as part of a job. I will add a nightly backup. We have to
> copy the database off that server to another box because there isn't
> sufficient drive space available to do a backup.
> Thanks for the quick response.
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%232Cn$L2zFHA.720@.TK2MSFTNGP15.phx.gbl...
> was
>

Database Backups and Transaction Logs

Problem: Transaction log grew too big.
I took my database off line and then back online to ensure that no one was
connected to the database and then backed up the database.
I figured at this point I would be able to shrink the transaction log
because all of the 'pending' transactions would be clear - however I cannot.
Do I have to backup the transaction log? And in so doing will this solve my
problem?
ThanksYou must backup the log separately. Backing up the DB does NOT free up the
log. This is an online operation, so don't take the DB offline.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
Problem: Transaction log grew too big.
I took my database off line and then back online to ensure that no one was
connected to the database and then backed up the database.
I figured at this point I would be able to shrink the transaction log
because all of the 'pending' transactions would be clear - however I cannot.
Do I have to backup the transaction log? And in so doing will this solve my
problem?
Thanks|||Stephen,
Production database? If so, backup the transaction log then shrink the
transaction log file using DBCC SHRINKFILE statment.
HTH
Jerry
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
> Problem: Transaction log grew too big.
> I took my database off line and then back online to ensure that no one was
> connected to the database and then backed up the database.
> I figured at this point I would be able to shrink the transaction log
> because all of the 'pending' transactions would be clear - however I
> cannot.
> Do I have to backup the transaction log? And in so doing will this solve
> my
> problem?
> Thanks
>
>|||Stephen,
Also see:
http://www.support.microsoft.com/?id=272318
HTH
Jerry
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
> Problem: Transaction log grew too big.
> I took my database off line and then back online to ensure that no one was
> connected to the database and then backed up the database.
> I figured at this point I would be able to shrink the transaction log
> because all of the 'pending' transactions would be clear - however I
> cannot.
> Do I have to backup the transaction log? And in so doing will this solve
> my
> problem?
> Thanks
>
>|||ok in the process of backing up the transaction log.
After that, I will run that command and then restrict the file growth.
Apparently the dba (which I am not nor am I a sql server dba) didn't set up
that functionality as part of a job. I will add a nightly backup. We have to
copy the database off that server to another box because there isn't
sufficient drive space available to do a backup.
Thanks for the quick response.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%232Cn$L2zFHA.720@.TK2MSFTNGP15.phx.gbl...
> Stephen,
> Production database? If so, backup the transaction log then shrink the
> transaction log file using DBCC SHRINKFILE statment.
> HTH
> Jerry
> "Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
> news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
was[vbcol=seagreen]
>|||I suggest you read http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:emVLHa2zFHA.560@.TK2MSFTNGP12.phx.gbl...
> ok in the process of backing up the transaction log.
> After that, I will run that command and then restrict the file growth.
> Apparently the dba (which I am not nor am I a sql server dba) didn't set u
p
> that functionality as part of a job. I will add a nightly backup. We have
to
> copy the database off that server to another box because there isn't
> sufficient drive space available to do a backup.
> Thanks for the quick response.
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%232Cn$L2zFHA.720@.TK2MSFTNGP15.phx.gbl...
> was
>

Database Backups and Transaction Logs

Problem: Transaction log grew too big.
I took my database off line and then back online to ensure that no one was
connected to the database and then backed up the database.
I figured at this point I would be able to shrink the transaction log
because all of the 'pending' transactions would be clear - however I cannot.
Do I have to backup the transaction log? And in so doing will this solve my
problem?
ThanksYou must backup the log separately. Backing up the DB does NOT free up the
log. This is an online operation, so don't take the DB offline.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
Problem: Transaction log grew too big.
I took my database off line and then back online to ensure that no one was
connected to the database and then backed up the database.
I figured at this point I would be able to shrink the transaction log
because all of the 'pending' transactions would be clear - however I cannot.
Do I have to backup the transaction log? And in so doing will this solve my
problem?
Thanks|||Stephen,
Production database? If so, backup the transaction log then shrink the
transaction log file using DBCC SHRINKFILE statment.
HTH
Jerry
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
> Problem: Transaction log grew too big.
> I took my database off line and then back online to ensure that no one was
> connected to the database and then backed up the database.
> I figured at this point I would be able to shrink the transaction log
> because all of the 'pending' transactions would be clear - however I
> cannot.
> Do I have to backup the transaction log? And in so doing will this solve
> my
> problem?
> Thanks
>
>|||Stephen,
Also see:
http://www.support.microsoft.com/?id=272318
HTH
Jerry
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
> Problem: Transaction log grew too big.
> I took my database off line and then back online to ensure that no one was
> connected to the database and then backed up the database.
> I figured at this point I would be able to shrink the transaction log
> because all of the 'pending' transactions would be clear - however I
> cannot.
> Do I have to backup the transaction log? And in so doing will this solve
> my
> problem?
> Thanks
>
>|||ok in the process of backing up the transaction log.
After that, I will run that command and then restrict the file growth.
Apparently the dba (which I am not nor am I a sql server dba) didn't set up
that functionality as part of a job. I will add a nightly backup. We have to
copy the database off that server to another box because there isn't
sufficient drive space available to do a backup.
Thanks for the quick response.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%232Cn$L2zFHA.720@.TK2MSFTNGP15.phx.gbl...
> Stephen,
> Production database? If so, backup the transaction log then shrink the
> transaction log file using DBCC SHRINKFILE statment.
> HTH
> Jerry
> "Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
> news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
> > Problem: Transaction log grew too big.
> >
> > I took my database off line and then back online to ensure that no one
was
> > connected to the database and then backed up the database.
> >
> > I figured at this point I would be able to shrink the transaction log
> > because all of the 'pending' transactions would be clear - however I
> > cannot.
> >
> > Do I have to backup the transaction log? And in so doing will this solve
> > my
> > problem?
> >
> > Thanks
> >
> >
> >
>|||I suggest you read http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:emVLHa2zFHA.560@.TK2MSFTNGP12.phx.gbl...
> ok in the process of backing up the transaction log.
> After that, I will run that command and then restrict the file growth.
> Apparently the dba (which I am not nor am I a sql server dba) didn't set up
> that functionality as part of a job. I will add a nightly backup. We have to
> copy the database off that server to another box because there isn't
> sufficient drive space available to do a backup.
> Thanks for the quick response.
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%232Cn$L2zFHA.720@.TK2MSFTNGP15.phx.gbl...
>> Stephen,
>> Production database? If so, backup the transaction log then shrink the
>> transaction log file using DBCC SHRINKFILE statment.
>> HTH
>> Jerry
>> "Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
>> news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
>> > Problem: Transaction log grew too big.
>> >
>> > I took my database off line and then back online to ensure that no one
> was
>> > connected to the database and then backed up the database.
>> >
>> > I figured at this point I would be able to shrink the transaction log
>> > because all of the 'pending' transactions would be clear - however I
>> > cannot.
>> >
>> > Do I have to backup the transaction log? And in so doing will this solve
>> > my
>> > problem?
>> >
>> > Thanks
>> >
>> >
>> >
>>
>

Database Backup

Hi,

I want to know how to take a database backup in sqlexpress from command line. Can some one help me?

Thanks.

At it's most simple, you'd use something like this:

SQLCmd -S .\SQLEXPRESS -E -Q "BACKUP DATABASE AdventureWorks TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\AdventureWorks.bak'"

This isn't teribly flexible or robust, so you're more likely going to want to write a backup script and call that script file rather than just typing in a simple T-SQL command. Read the BOL topic on BACKUP for information about the various options. You can find a general overview of database backup here.

Jasper Smith, one of the SQL Server MVPs, has put together a tool called ExpressMaint that will help you create a script to do maintenance tasks, including backup, check it out.

Mike

|||Thanks.

Data Type for multi line text?

Hi,
I'm using bulkload to import data from an xml file. The data has several
lines of text before the closing tag. After I bulkload the data all the text
is ran together.
I'm using data type of "Text" on my SQL2005 server, should I use something
else that will keep the formatting? The text can actuall be over several
thousand characters.
Thanks
Charles W
XML format:
<data> Line one with data
Line two with data, a line may be skipped
Fourth line with data.
</data>
SQL format:
Line one with dataLine two with data, a line may be skippedFourth line with
data.
Hello Charles,

> Hi,
> I'm using bulkload to import data from an xml file. The data has
> several
> lines of text before the closing tag. After I bulkload the data all
> the text
> is ran together.
> I'm using data type of "Text" on my SQL2005 server, should I use
> something
> else that will keep the formatting? The text can actuall be over
> several
> thousand characters.
> Thanks
> Charles W
> XML format:
> <data> Line one with data
> Line two with data, a line may be skipped
> Fourth line with data.
> </data>
> SQL format:
> Line one with dataLine two with data, a line may be skippedFourth line
> with
> data.
is nvarchar(max) an option?
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||"Charles W" <cwunderlich@.nospam.vrtlweb.com> wrote in message
news:eWjOBAsTGHA.4976@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I'm using bulkload to import data from an xml file. The data has several
> lines of text before the closing tag. After I bulkload the data all the
text
> is ran together.
> I'm using data type of "Text" on my SQL2005 server, should I use something
> else that will keep the formatting? The text can actuall be over several
> thousand characters.
>
> Thanks
> Charles W
>
> XML format:
> <data> Line one with data
> Line two with data, a line may be skipped
> Fourth line with data.
> </data>
> SQL format:
> Line one with dataLine two with data, a line may be skippedFourth line
with
> data.
>
How are you confirmin that the line feeds are actually being removed?
Note that XML will often replace any CRLF sequence with a simple LF.
Could it be that the LFs are there but what you are using to retreive and
display the value requires CRLFs?
Anthony.
|||I thought changing the field to nvarchar(4000) worked, but I ran into a size
problem when processing my files. It seems that some of the data is over the
4000 max.
Any other ideas?
Thanks
CW
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad741dc7f8c81cab6345f890@.news.microsoft.co m...
> Hello Charles,
>
> is nvarchar(max) an option?
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
|||"Charles W" <cwunderlich@.nospam.vrtlweb.com> wrote in message
news:OxLl6xbUGHA.5500@.TK2MSFTNGP12.phx.gbl...
> I thought changing the field to nvarchar(4000) worked, but I ran into a
size
> problem when processing my files. It seems that some of the data is over
the
> 4000 max.
> Any other ideas?
>
> Thanks
> CW
>
NText is the field type you need. Still doesn't eliminate the Line feed
issue which as already pointed out is not a function of the SQL data type
you are choosing but is just how XML works.
See:-
http://www.w3.org/TR/REC-xml/#sec-line-ends
Anthony

Data Type for multi line text?

Hi,
I'm using bulkload to import data from an xml file. The data has several
lines of text before the closing tag. After I bulkload the data all the text
is ran together.
I'm using data type of "Text" on my SQL2005 server, should I use something
else that will keep the formatting? The text can actuall be over several
thousand characters.
Thanks
Charles W
XML format:
<data> Line one with data
Line two with data, a line may be skipped
Fourth line with data.
</data>
SQL format:
Line one with dataLine two with data, a line may be skippedFourth line with
data.Hello Charles,

> Hi,
> I'm using bulkload to import data from an xml file. The data has
> several
> lines of text before the closing tag. After I bulkload the data all
> the text
> is ran together.
> I'm using data type of "Text" on my SQL2005 server, should I use
> something
> else that will keep the formatting? The text can actuall be over
> several
> thousand characters.
> Thanks
> Charles W
> XML format:
> <data> Line one with data
> Line two with data, a line may be skipped
> Fourth line with data.
> </data>
> SQL format:
> Line one with dataLine two with data, a line may be skippedFourth line
> with
> data.
is nvarchar(max) an option?
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||"Charles W" <cwunderlich@.nospam.vrtlweb.com> wrote in message
news:eWjOBAsTGHA.4976@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I'm using bulkload to import data from an xml file. The data has several
> lines of text before the closing tag. After I bulkload the data all the
text
> is ran together.
> I'm using data type of "Text" on my SQL2005 server, should I use something
> else that will keep the formatting? The text can actuall be over several
> thousand characters.
>
> Thanks
> Charles W
>
> XML format:
> <data> Line one with data
> Line two with data, a line may be skipped
> Fourth line with data.
> </data>
> SQL format:
> Line one with dataLine two with data, a line may be skippedFourth line
with
> data.
>
How are you confirmin that the line feeds are actually being removed?
Note that XML will often replace any CRLF sequence with a simple LF.
Could it be that the LFs are there but what you are using to retreive and
display the value requires CRLFs?
Anthony.|||I thought changing the field to nvarchar(4000) worked, but I ran into a size
problem when processing my files. It seems that some of the data is over the
4000 max.
Any other ideas?
Thanks
CW
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad741dc7f8c81cab6345f890@.news.microsoft.com...
> Hello Charles,
>
> is nvarchar(max) an option?
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>|||"Charles W" <cwunderlich@.nospam.vrtlweb.com> wrote in message
news:OxLl6xbUGHA.5500@.TK2MSFTNGP12.phx.gbl...
> I thought changing the field to nvarchar(4000) worked, but I ran into a
size
> problem when processing my files. It seems that some of the data is over
the
> 4000 max.
> Any other ideas?
>
> Thanks
> CW
>
NText is the field type you need. Still doesn't eliminate the Line feed
issue which as already pointed out is not a function of the SQL data type
you are choosing but is just how XML works.
See:-
http://www.w3.org/TR/REC-xml/#sec-line-ends
Anthony