Backup SQL Server 2000 and 2005

Good morning,
I am with some questions concerning Backup of SQL Server 2000 and 2005.
First, I would like to know whether performing the backup Full, it is done
automatically backup of the Transaction Log? And if it is truncate it?
Or whether it is in fact necessary to perform a backup of the transaction
log specific?
Secondly, I would like to know about the differential backup. If after the
Full on Monday, I made a backup differential on Tuesday and on Wednesday, if
I have a problem on Wednesday to Thursday. It will be necessary to restore
the backup only Full of Monday and differential backup of Wednesday? Or will
it restore the backup of Differential Tuesday to restore the backup
differential of Wednesday?
Thank you,
Sincerely,
Ralph Haddad> First, I would like to know whether performing the backup Full, it is done
> automatically backup of the Transaction Log? And if it is truncate it?
TLOG is also being backuped ,however BACKUP does not truncate it

> Or whether it is in fact necessary to perform a backup of the transaction
> log specific?
You have to read about different RECOVERY models in the BOL to understand it

> Secondly, I would like to know about the differential backup. If after the
> Full on Monday, I made a backup differential on Tuesday and on Wednesday,
> if
> I have a problem on Wednesday to Thursday. It will be necessary to restore
> the backup only Full of Monday and differential backup of Wednesday? Or
> will
> it restore the backup of Differential Tuesday to restore the backup
> differential of Wednesday?
Please read BOL
"Ralph Haddad" <RalphHaddad@.discussions.microsoft.com> wrote in message
news:9FFC41F2-AF47-49D1-8E42-192BBA170A5B@.microsoft.com...
> Good morning,
> I am with some questions concerning Backup of SQL Server 2000 and 2005.
> First, I would like to know whether performing the backup Full, it is done
> automatically backup of the Transaction Log? And if it is truncate it?
> Or whether it is in fact necessary to perform a backup of the transaction
> log specific?
> Secondly, I would like to know about the differential backup. If after the
> Full on Monday, I made a backup differential on Tuesday and on Wednesday,
> if
> I have a problem on Wednesday to Thursday. It will be necessary to restore
> the backup only Full of Monday and differential backup of Wednesday? Or
> will
> it restore the backup of Differential Tuesday to restore the backup
> differential of Wednesday?
> Thank you,
> Sincerely,
> Ralph Haddad
>|||Answers are below:
Ekrem ?nsoy
"Ralph Haddad" <RalphHaddad@.discussions.microsoft.com> wrote in message
news:9FFC41F2-AF47-49D1-8E42-192BBA170A5B@.microsoft.com...
> Good morning,
> I am with some questions concerning Backup of SQL Server 2000 and 2005.
> First, I would like to know whether performing the backup Full, it is done
> automatically backup of the Transaction Log? And if it is truncate it?
> Or whether it is in fact necessary to perform a backup of the transaction
> log specific?
BACKUP DATABASE would not truncate T-Log. You need to perform a BACKUP LOG
to truncate T-Log. And to reduce your T-Log' s size you'll need to use
Shrinking. (don't use this unless necessary)

> Secondly, I would like to know about the differential backup. If after the
> Full on Monday, I made a backup differential on Tuesday and on Wednesday,
> if
> I have a problem on Wednesday to Thursday. It will be necessary to restore
> the backup only Full of Monday and differential backup of Wednesday? Or
> will
> it restore the backup of Differential Tuesday to restore the backup
> differential of Wednesday?
Restore the full backup you take on Monday and restore the lastest
differential backup only.

> Thank you,
> Sincerely,
> Ralph Haddad
>

Backup question

Hello All,
I have a database that I am currently performing backup and incremental
backups.
If I were to add an extra table to the database or modify an existing table
with a few columns, would that imply that I would need to perform a full
back.
ThanksOnly if you don't want to wait for the next backup - modifying a table
is like making any other change in the database. If you've made a large
number of changes which would take some time to redo manually, and if
your database isn't very big, it might be worth making a backup so that
in the worst case you don't have to reapply the changes by hand. But
apart from that there are no special considerations.

Simon

BackUp Quastions

Hi everyone
I have the folowing situations with my backups.
With Database Maintenance Plan is created backup strategy. Full Backup
is performing every day at 1.00 am. And Romove files older than: is
checked on 2 days, and it's working fine.

I am interested how can i perform this action with tsql, without using
DMPlan.
I tryed this:
BACKUP DATABASE TEST TO DISK 'C:...' WITH INIT, RETAINDAYS = 2

I put this statement into job but it is not working. I tryed after that
with (WITH NOINIT) but in that case .bak file grow and files older than
2 days are not deleted.

How can i delete files older then 2 days.
Thanks

acko

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!"acko bogicevic" <aconi2002@.yahoo.com> wrote in message
news:408ccc86$0$200$75868355@.news.frii.net...
> Hi everyone
> I have the folowing situations with my backups.
> With Database Maintenance Plan is created backup strategy. Full Backup
> is performing every day at 1.00 am. And Romove files older than: is
> checked on 2 days, and it's working fine.
> I am interested how can i perform this action with tsql, without using
> DMPlan.
> I tryed this:
> BACKUP DATABASE TEST TO DISK 'C:...' WITH INIT, RETAINDAYS = 2
> I put this statement into job but it is not working. I tryed after that
> with (WITH NOINIT) but in that case .bak file grow and files older than
> 2 days are not deleted.
> How can i delete files older then 2 days.
> Thanks
> acko
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

RETAINDAYS means that the backup set cannot be overwritten for that number
of days - it does not remove backups. One way to do this in SQL is using a
process something like this:

1. Generate a different backup filename for each day, based on the date -
BACKUP will accept a variable for the destination file name
2. After running BACKUP, execute 'dir /b' with xp_cmdshell to get a list of
the files in your backup folder into a temp table
3. Using a cursor, parse the filenames in the table to get the date of each
backup
4. If a file is older than @.x days (compare using DATEDIFF()), then use
xp_cmdshell again to delete it

But this is quite awkward to do in SQL, and may become complicated when you
have different backup types, different folders for each database's backups
etc. It would be probably easier to write an external script using Perl,
VBScript etc. which uses the SQLDMO objects to do everything. Or perhaps a
combination of both - use a multi-step job, where some steps are external
scripts, and some are SQL commands.

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:408d4a4e$1_2@.news.bluewin.ch...
> "acko bogicevic" <aconi2002@.yahoo.com> wrote in message
> news:408ccc86$0$200$75868355@.news.frii.net...
> > Hi everyone
> > I have the folowing situations with my backups.
> > With Database Maintenance Plan is created backup strategy. Full Backup
> > is performing every day at 1.00 am. And Romove files older than: is
> > checked on 2 days, and it's working fine.
> > I am interested how can i perform this action with tsql, without using
> > DMPlan.
> > I tryed this:
> > BACKUP DATABASE TEST TO DISK 'C:...' WITH INIT, RETAINDAYS = 2
> > I put this statement into job but it is not working. I tryed after that
> > with (WITH NOINIT) but in that case .bak file grow and files older than
> > 2 days are not deleted.
> > How can i delete files older then 2 days.
> > Thanks
> > acko
> > *** Sent via Developersdex http://www.developersdex.com ***
> > Don't just participate in USENET...get rewarded for it!
> RETAINDAYS means that the backup set cannot be overwritten for that number
> of days - it does not remove backups. One way to do this in SQL is using a
> process something like this:
> 1. Generate a different backup filename for each day, based on the date -
> BACKUP will accept a variable for the destination file name
> 2. After running BACKUP, execute 'dir /b' with xp_cmdshell to get a list
of
> the files in your backup folder into a temp table
> 3. Using a cursor, parse the filenames in the table to get the date of
each
> backup
> 4. If a file is older than @.x days (compare using DATEDIFF()), then use
> xp_cmdshell again to delete it
> But this is quite awkward to do in SQL, and may become complicated when
you
> have different backup types, different folders for each database's backups
> etc. It would be probably easier to write an external script using Perl,
> VBScript etc. which uses the SQLDMO objects to do everything. Or perhaps a
> combination of both - use a multi-step job, where some steps are external
> scripts, and some are SQL commands.

I'm going to suggest a much easier and in my mind more robust way.

Use the backupfile, backupset, etc tables in the msdb database.

This will let you find the proper filenames, dates, etc.

> Simon

BACKUP LOG WITH TRUNCATE_ONLY after DBCC SHRINKDATABASE

Is there any benefit to performing a BACKUP LOG MyDB WITH TRUNCATE_ONLY
after a DBCC SHRINKDATABASE (N'MyDB', 10, NOTRUNCATE) on a database where
the recovery model is simple?
Dave
Hi Dave
In SIMPLE mode, SQL Server performs the same operation automatically at
regular frequent intervals, so there would be practically no benefit to
doing it yourself.
But, a related question is, WHY are you shrinking the database? Most people
think they need to shrink in order to reclaim space, but with the NOTRUNCATE
option you're not even going to get that.
Please read this article:
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"DaveF" <dave@.aol.com> wrote in message news:5qIrj.2329$ip3.859@.trnddc07...
> Is there any benefit to performing a BACKUP LOG MyDB WITH TRUNCATE_ONLY
> after a DBCC SHRINKDATABASE (N'MyDB', 10, NOTRUNCATE) on a database where
> the recovery model is simple?
> Dave
>
|||Kalen,
We have a data warehouse load routine written by our software vendor that
actually performs 7 database shrinks over the course of the nightly run.
Believe it or not.
Are you saying these aren't necessary?
FYI. Our dev and test databases are in simple mode but our production
database is in full mode.
Dave
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23JPpvkCbIHA.5400@.TK2MSFTNGP03.phx.gbl...
> Hi Dave
> In SIMPLE mode, SQL Server performs the same operation automatically at
> regular frequent intervals, so there would be practically no benefit to
> doing it yourself.
> But, a related question is, WHY are you shrinking the database? Most
> people think they need to shrink in order to reclaim space, but with the
> NOTRUNCATE option you're not even going to get that.
> Please read this article:
> http://www.karaszi.com/sqlserver/info_dont_shrink.asp
>
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "DaveF" <dave@.aol.com> wrote in message
> news:5qIrj.2329$ip3.859@.trnddc07...
>
|||Did you read the article I pointed you to?
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"DaveF" <dave@.aol.com> wrote in message news:tYNrj.1262$r03.293@.trnddc08...
> Kalen,
> We have a data warehouse load routine written by our software vendor that
> actually performs 7 database shrinks over the course of the nightly run.
> Believe it or not.
> Are you saying these aren't necessary?
> FYI. Our dev and test databases are in simple mode but our production
> database is in full mode.
> Dave
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23JPpvkCbIHA.5400@.TK2MSFTNGP03.phx.gbl...
>
|||Time to get a new vendor.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"DaveF" <dave@.aol.com> wrote in message news:tYNrj.1262$r03.293@.trnddc08...
> Kalen,
> We have a data warehouse load routine written by our software vendor that
> actually performs 7 database shrinks over the course of the nightly run.
> Believe it or not.
> Are you saying these aren't necessary?
> FYI. Our dev and test databases are in simple mode but our production
> database is in full mode.
> Dave
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23JPpvkCbIHA.5400@.TK2MSFTNGP03.phx.gbl...
>

BACKUP LOG WITH TRUNCATE_ONLY after DBCC SHRINKDATABASE

Is there any benefit to performing a BACKUP LOG MyDB WITH TRUNCATE_ONLY
after a DBCC SHRINKDATABASE (N'MyDB', 10, NOTRUNCATE) on a database where
the recovery model is simple?
DaveHi Dave
In SIMPLE mode, SQL Server performs the same operation automatically at
regular frequent intervals, so there would be practically no benefit to
doing it yourself.
But, a related question is, WHY are you shrinking the database? Most people
think they need to shrink in order to reclaim space, but with the NOTRUNCATE
option you're not even going to get that.
Please read this article:
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"DaveF" <dave@.aol.com> wrote in message news:5qIrj.2329$ip3.859@.trnddc07...
> Is there any benefit to performing a BACKUP LOG MyDB WITH TRUNCATE_ONLY
> after a DBCC SHRINKDATABASE (N'MyDB', 10, NOTRUNCATE) on a database where
> the recovery model is simple?
> Dave
>|||Kalen,
We have a data warehouse load routine written by our software vendor that
actually performs 7 database shrinks over the course of the nightly run.
Believe it or not.
Are you saying these aren't necessary?
FYI. Our dev and test databases are in simple mode but our production
database is in full mode.
Dave
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23JPpvkCbIHA.5400@.TK2MSFTNGP03.phx.gbl...
> Hi Dave
> In SIMPLE mode, SQL Server performs the same operation automatically at
> regular frequent intervals, so there would be practically no benefit to
> doing it yourself.
> But, a related question is, WHY are you shrinking the database? Most
> people think they need to shrink in order to reclaim space, but with the
> NOTRUNCATE option you're not even going to get that.
> Please read this article:
> http://www.karaszi.com/sqlserver/info_dont_shrink.asp
>
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "DaveF" <dave@.aol.com> wrote in message
> news:5qIrj.2329$ip3.859@.trnddc07...
>> Is there any benefit to performing a BACKUP LOG MyDB WITH TRUNCATE_ONLY
>> after a DBCC SHRINKDATABASE (N'MyDB', 10, NOTRUNCATE) on a database
>> where the recovery model is simple?
>> Dave
>|||Did you read the article I pointed you to?
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"DaveF" <dave@.aol.com> wrote in message news:tYNrj.1262$r03.293@.trnddc08...
> Kalen,
> We have a data warehouse load routine written by our software vendor that
> actually performs 7 database shrinks over the course of the nightly run.
> Believe it or not.
> Are you saying these aren't necessary?
> FYI. Our dev and test databases are in simple mode but our production
> database is in full mode.
> Dave
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23JPpvkCbIHA.5400@.TK2MSFTNGP03.phx.gbl...
>> Hi Dave
>> In SIMPLE mode, SQL Server performs the same operation automatically at
>> regular frequent intervals, so there would be practically no benefit to
>> doing it yourself.
>> But, a related question is, WHY are you shrinking the database? Most
>> people think they need to shrink in order to reclaim space, but with the
>> NOTRUNCATE option you're not even going to get that.
>> Please read this article:
>> http://www.karaszi.com/sqlserver/info_dont_shrink.asp
>>
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "DaveF" <dave@.aol.com> wrote in message
>> news:5qIrj.2329$ip3.859@.trnddc07...
>> Is there any benefit to performing a BACKUP LOG MyDB WITH TRUNCATE_ONLY
>> after a DBCC SHRINKDATABASE (N'MyDB', 10, NOTRUNCATE) on a database
>> where the recovery model is simple?
>> Dave
>>
>|||Time to get a new vendor.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"DaveF" <dave@.aol.com> wrote in message news:tYNrj.1262$r03.293@.trnddc08...
> Kalen,
> We have a data warehouse load routine written by our software vendor that
> actually performs 7 database shrinks over the course of the nightly run.
> Believe it or not.
> Are you saying these aren't necessary?
> FYI. Our dev and test databases are in simple mode but our production
> database is in full mode.
> Dave
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23JPpvkCbIHA.5400@.TK2MSFTNGP03.phx.gbl...
>> Hi Dave
>> In SIMPLE mode, SQL Server performs the same operation automatically at
>> regular frequent intervals, so there would be practically no benefit to
>> doing it yourself.
>> But, a related question is, WHY are you shrinking the database? Most
>> people think they need to shrink in order to reclaim space, but with the
>> NOTRUNCATE option you're not even going to get that.
>> Please read this article:
>> http://www.karaszi.com/sqlserver/info_dont_shrink.asp
>>
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "DaveF" <dave@.aol.com> wrote in message
>> news:5qIrj.2329$ip3.859@.trnddc07...
>> Is there any benefit to performing a BACKUP LOG MyDB WITH TRUNCATE_ONLY
>> after a DBCC SHRINKDATABASE (N'MyDB', 10, NOTRUNCATE) on a database
>> where the recovery model is simple?
>> Dave
>>
>