Help with backup (Full, differential and transactional) process

Hi everyone, i was hoping to obtain some help with my backup process.
Ideally I wish to make a weekly full backup, with a differential at the end
of each day, and transactionals every hour during business hours. My code is
below
note, each backup process (full, diff and trans) will be placed in a
separate job
--
-- Full backup scheduled on Monday at 2:00 AM
DECLARE @.str varchar(200)
SET @.str = 'BACKUP DATABASE ZestLive TO DISK=''R:\BACKUP\ZestFull\ZestLive'
+ RTRIM(CONVERT(varchar, GETDATE(), 112))
+ '_'
+ STUFF(SUBSTRING(RIGHT(RTRIM(CONVERT(varchar, GETDATE(), 113)), 12), 1, 5),
3, 1, '')
+ '.BAK'' '
EXEC (@.str)
--
-- Transactional backup scheduled every hour between 6AM and 11PM inclusive
DECLARE @.str varchar(200)
SET @.str = 'BACKUP LOG ZestLive TO DISK=''R:\BACKUP\ZestTLog\ZestLive'
+ RTRIM(CONVERT(varchar, GETDATE(), 112))
+ '_'
+ STUFF(SUBSTRING(RIGHT(RTRIM(CONVERT(varchar, GETDATE(), 113)), 12), 1, 5),
3, 1, '')
+ '.TRN'' '
EXEC (@.str)
--
-- Differential backup scheduled Tue, Wed, Thu, Fri, Sat, Sun at 2:00AM
DECLARE @.str varchar(200)
SET @.str = 'BACKUP DATABASE ZestLive TO DISK=''R:\BACKUP\ZestDiff\ZestLive'
+ RTRIM(CONVERT(varchar, GETDATE(), 112))
+ '_'
+ STUFF(SUBSTRING(RIGHT(RTRIM(CONVERT(varchar, GETDATE(), 113)), 12), 1, 5),
3, 1, '')
+ '.DIFF'' WITH DIFFERENTIAL'
EXEC (@.str)
--
My questions are:
1. I have noticed that my transaction log keeps growing. How do i keep it to
size with the above backup process. i.e. does the log shrink after taking a
transactional backup?
2. I noticed that there are other flags that can be appended to the above
backup commands. Do i really need them? i.e. INIT, NOINIT, STAT etc...
3. Will the database lock whilst performing transactional backups? I figure
the database will be locked during a full and differential backup.
Any help most appreciated.
Many thanks,
cheers, steve
*/If you are running these backups, then your log shouldn't really be growing
any more. Are you sure the job is executing successfully? You're probably
fine without any other flags. The database does nock lock while backups are
in progress.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"steve" <steve@.nospam.com> wrote in message
news:%23GD1eZvNGHA.2920@.TK2MSFTNGP10.phx.gbl...
Hi everyone, i was hoping to obtain some help with my backup process.
Ideally I wish to make a weekly full backup, with a differential at the end
of each day, and transactionals every hour during business hours. My code is
below
note, each backup process (full, diff and trans) will be placed in a
separate job
--
-- Full backup scheduled on Monday at 2:00 AM
DECLARE @.str varchar(200)
SET @.str ='BACKUP DATABASE ZestLive TO DISK=''R:\BACKUP\ZestFull\ZestLive'
+ RTRIM(CONVERT(varchar, GETDATE(), 112))
+ '_'
+ STUFF(SUBSTRING(RIGHT(RTRIM(CONVERT(varchar, GETDATE(), 113)), 12), 1, 5),
3, 1, '')
+ '.BAK'' '
EXEC (@.str)
--
-- Transactional backup scheduled every hour between 6AM and 11PM inclusive
DECLARE @.str varchar(200)
SET @.str ='BACKUP LOG ZestLive TO DISK=''R:\BACKUP\ZestTLog\ZestLive'
+ RTRIM(CONVERT(varchar, GETDATE(), 112))
+ '_'
+ STUFF(SUBSTRING(RIGHT(RTRIM(CONVERT(varchar, GETDATE(), 113)), 12), 1, 5),
3, 1, '')
+ '.TRN'' '
EXEC (@.str)
--
-- Differential backup scheduled Tue, Wed, Thu, Fri, Sat, Sun at 2:00AM
DECLARE @.str varchar(200)
SET @.str ='BACKUP DATABASE ZestLive TO DISK=''R:\BACKUP\ZestDiff\ZestLive'
+ RTRIM(CONVERT(varchar, GETDATE(), 112))
+ '_'
+ STUFF(SUBSTRING(RIGHT(RTRIM(CONVERT(varchar, GETDATE(), 113)), 12), 1, 5),
3, 1, '')
+ '.DIFF'' WITH DIFFERENTIAL'
EXEC (@.str)
--
My questions are:
1. I have noticed that my transaction log keeps growing. How do i keep it to
size with the above backup process. i.e. does the log shrink after taking a
transactional backup?
2. I noticed that there are other flags that can be appended to the above
backup commands. Do i really need them? i.e. INIT, NOINIT, STAT etc...
3. Will the database lock whilst performing transactional backups? I figure
the database will be locked during a full and differential backup.
Any help most appreciated.
Many thanks,
cheers, steve
*/|||Just to extend Tom's comments a bit,
I wouldn't worry about backups. They are just a means. What you need to do
is create a restore and recovery strategy. Determine how much data loss and
down time during possible recovery scenarios (full server loss, data
corruption, multiple hard disk failure, etc.) is acceptable. Plan backups
(full, differential, and transaction log) to support your recovery strategy.
Read the BOL section on database backup and restore so you will understand
how SQL backup works. It is not the same as a file system backup. Treating
it the same for restore purposes will lead to disappointment when you
actually have to use a backup.
Finally, test your restore/recovery plans, especially the actual backup
files. Until you test the process, you have a hope, not a plan.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"steve" <steve@.nospam.com> wrote in message
news:%23GD1eZvNGHA.2920@.TK2MSFTNGP10.phx.gbl...
> Hi everyone, i was hoping to obtain some help with my backup process.
> Ideally I wish to make a weekly full backup, with a differential at the
> end of each day, and transactionals every hour during business hours. My
> code is below
> note, each backup process (full, diff and trans) will be placed in a
> separate job
> --
> -- Full backup scheduled on Monday at 2:00 AM
> DECLARE @.str varchar(200)
> SET @.str => 'BACKUP DATABASE ZestLive TO DISK=''R:\BACKUP\ZestFull\ZestLive'
> + RTRIM(CONVERT(varchar, GETDATE(), 112))
> + '_'
> + STUFF(SUBSTRING(RIGHT(RTRIM(CONVERT(varchar, GETDATE(), 113)), 12), 1,
> 5), 3, 1, '')
> + '.BAK'' '
> EXEC (@.str)
> --
> -- Transactional backup scheduled every hour between 6AM and 11PM
> inclusive
> DECLARE @.str varchar(200)
> SET @.str => 'BACKUP LOG ZestLive TO DISK=''R:\BACKUP\ZestTLog\ZestLive'
> + RTRIM(CONVERT(varchar, GETDATE(), 112))
> + '_'
> + STUFF(SUBSTRING(RIGHT(RTRIM(CONVERT(varchar, GETDATE(), 113)), 12), 1,
> 5), 3, 1, '')
> + '.TRN'' '
> EXEC (@.str)
> --
> -- Differential backup scheduled Tue, Wed, Thu, Fri, Sat, Sun at 2:00AM
> DECLARE @.str varchar(200)
> SET @.str => 'BACKUP DATABASE ZestLive TO DISK=''R:\BACKUP\ZestDiff\ZestLive'
> + RTRIM(CONVERT(varchar, GETDATE(), 112))
> + '_'
> + STUFF(SUBSTRING(RIGHT(RTRIM(CONVERT(varchar, GETDATE(), 113)), 12), 1,
> 5), 3, 1, '')
> + '.DIFF'' WITH DIFFERENTIAL'
> EXEC (@.str)
> --
> My questions are:
> 1. I have noticed that my transaction log keeps growing. How do i keep it
> to size with the above backup process. i.e. does the log shrink after
> taking a transactional backup?
> 2. I noticed that there are other flags that can be appended to the above
> backup commands. Do i really need them? i.e. INIT, NOINIT, STAT etc...
> 3. Will the database lock whilst performing transactional backups? I
> figure the database will be locked during a full and differential backup.
> Any help most appreciated.
> Many thanks,
> cheers, steve
> */
>