Backing up MS SQL Server databases

Hello all,
using SQL Server 2000 sp4
What can Backup Exec. 9.1 do that I cannot do in scripts\jobs or sql
maintenance plans?
Is it normal out there to just have backups through MS SQL Server with
maintenance plans and jobs with dba own scripts? Or to also have 3rd party
backup solution
thanks
gv
"gv" <viator.gerry@.gmail.com> wrote in message
news:%23A4vsoHcHHA.4176@.TK2MSFTNGP02.phx.gbl...
> Hello all,
> using SQL Server 2000 sp4
> What can Backup Exec. 9.1 do that I cannot do in scripts\jobs or sql
> maintenance plans?
>
I'm not familiar with Backup Exec.

> Is it normal out there to just have backups through MS SQL Server with
> maintenance plans and jobs with dba own scripts? Or to also have 3rd party
> backup solution
Yes, very normal. And honestly, for the most part, I trust them more.
One of the few reasons I can think of to use a 3rd party solution is that
you can do a single backup operation to backup the entire server, including
the databases.

> thanks
> gv
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||thanks
gv
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:ON34WvHcHHA.596@.TK2MSFTNGP06.phx.gbl...
> "gv" <viator.gerry@.gmail.com> wrote in message
> news:%23A4vsoHcHHA.4176@.TK2MSFTNGP02.phx.gbl...
> I'm not familiar with Backup Exec.
>
> Yes, very normal. And honestly, for the most part, I trust them more.
> One of the few reasons I can think of to use a 3rd party solution is that
> you can do a single backup operation to backup the entire server,
> including the databases.
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com
> http://www.greenms.com/sqlserver.html
>

Backing up MS SQL Server databases

Hello all,
using SQL Server 2000 sp4
What can Backup Exec. 9.1 do that I cannot do in scripts\jobs or sql
maintenance plans?
Is it normal out there to just have backups through MS SQL Server with
maintenance plans and jobs with dba own scripts? Or to also have 3rd party
backup solution
thanks
gv"gv" <viator.gerry@.gmail.com> wrote in message
news:%23A4vsoHcHHA.4176@.TK2MSFTNGP02.phx.gbl...
> Hello all,
> using SQL Server 2000 sp4
> What can Backup Exec. 9.1 do that I cannot do in scripts\jobs or sql
> maintenance plans?
>
I'm not familiar with Backup Exec.
> Is it normal out there to just have backups through MS SQL Server with
> maintenance plans and jobs with dba own scripts? Or to also have 3rd party
> backup solution
Yes, very normal. And honestly, for the most part, I trust them more.
One of the few reasons I can think of to use a 3rd party solution is that
you can do a single backup operation to backup the entire server, including
the databases.
> thanks
> gv
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||thanks
gv
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:ON34WvHcHHA.596@.TK2MSFTNGP06.phx.gbl...
> "gv" <viator.gerry@.gmail.com> wrote in message
> news:%23A4vsoHcHHA.4176@.TK2MSFTNGP02.phx.gbl...
>> Hello all,
>> using SQL Server 2000 sp4
>> What can Backup Exec. 9.1 do that I cannot do in scripts\jobs or sql
>> maintenance plans?
> I'm not familiar with Backup Exec.
>
>> Is it normal out there to just have backups through MS SQL Server with
>> maintenance plans and jobs with dba own scripts? Or to also have 3rd
>> party backup solution
> Yes, very normal. And honestly, for the most part, I trust them more.
> One of the few reasons I can think of to use a 3rd party solution is that
> you can do a single backup operation to backup the entire server,
> including the databases.
>
>> thanks
>> gv
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com
> http://www.greenms.com/sqlserver.html
>

Backing up MS SQL Server databases

Hello all,
using SQL Server 2000 sp4
What can Backup Exec. 9.1 do that I cannot do in scripts\jobs or sql
maintenance plans?
Is it normal out there to just have backups through MS SQL Server with
maintenance plans and jobs with dba own scripts? Or to also have 3rd party
backup solution
thanks
gv"gv" <viator.gerry@.gmail.com> wrote in message
news:%23A4vsoHcHHA.4176@.TK2MSFTNGP02.phx.gbl...
> Hello all,
> using SQL Server 2000 sp4
> What can Backup Exec. 9.1 do that I cannot do in scripts\jobs or sql
> maintenance plans?
>
I'm not familiar with Backup Exec.

> Is it normal out there to just have backups through MS SQL Server with
> maintenance plans and jobs with dba own scripts? Or to also have 3rd party
> backup solution
Yes, very normal. And honestly, for the most part, I trust them more.
One of the few reasons I can think of to use a 3rd party solution is that
you can do a single backup operation to backup the entire server, including
the databases.

> thanks
> gv
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||thanks
gv
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:ON34WvHcHHA.596@.TK2MSFTNGP06.phx.gbl...
> "gv" <viator.gerry@.gmail.com> wrote in message
> news:%23A4vsoHcHHA.4176@.TK2MSFTNGP02.phx.gbl...
> I'm not familiar with Backup Exec.
>
> Yes, very normal. And honestly, for the most part, I trust them more.
> One of the few reasons I can think of to use a 3rd party solution is that
> you can do a single backup operation to backup the entire server,
> including the databases.
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com
> http://www.greenms.com/sqlserver.html
>

Backing up DTS and SQL jobs....

I know If I store the DTS packages in SQL Server it will be stored in the
msdb database.
Also all the job scheduling information is stored in the msdb database.
Even though I am taking backup of system databases, I would like to know if
is there any way I can export all the DTS packages and jobs in any different
way. This is only an additional extra precaution incase If I am not able to
restore the system database in the event of disaster.
I am able to open the DTS packages individually and store as .dts file. It
is a time consuming process.
I am using SQL 2K.
Thanks,
J Smith
For DTS backup, I use a neat little program called, "DTS Backup 2000." It's
freeware.
"Jessy Martin" <Jessy_Smith79@.hotmail.com> wrote in message
news:uMDTg4gZFHA.3048@.TK2MSFTNGP14.phx.gbl...
>I know If I store the DTS packages in SQL Server it will be stored in the
> msdb database.
> Also all the job scheduling information is stored in the msdb database.
> Even though I am taking backup of system databases, I would like to know
> if
> is there any way I can export all the DTS packages and jobs in any
> different
> way. This is only an additional extra precaution incase If I am not able
> to
> restore the system database in the event of disaster.
> I am able to open the DTS packages individually and store as .dts file. It
> is a time consuming process.
> I am using SQL 2K.
> Thanks,
> J Smith
>
|||Free Backup Software from Darren Green is available at www.sqldts.com
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jessy Martin" <Jessy_Smith79@.hotmail.com> wrote in message
news:uMDTg4gZFHA.3048@.TK2MSFTNGP14.phx.gbl...
>I know If I store the DTS packages in SQL Server it will be stored in the
> msdb database.
> Also all the job scheduling information is stored in the msdb database.
> Even though I am taking backup of system databases, I would like to know
> if
> is there any way I can export all the DTS packages and jobs in any
> different
> way. This is only an additional extra precaution incase If I am not able
> to
> restore the system database in the event of disaster.
> I am able to open the DTS packages individually and store as .dts file. It
> is a time consuming process.
> I am using SQL 2K.
> Thanks,
> J Smith
>
sql

Backing up DTS and SQL jobs....

I know If I store the DTS packages in SQL Server it will be stored in the
msdb database.
Also all the job scheduling information is stored in the msdb database.
Even though I am taking backup of system databases, I would like to know if
is there any way I can export all the DTS packages and jobs in any different
way. This is only an additional extra precaution incase If I am not able to
restore the system database in the event of disaster.
I am able to open the DTS packages individually and store as .dts file. It
is a time consuming process.
I am using SQL 2K.
Thanks,
J SmithFor DTS backup, I use a neat little program called, "DTS Backup 2000." It's
freeware.
"Jessy Martin" <Jessy_Smith79@.hotmail.com> wrote in message
news:uMDTg4gZFHA.3048@.TK2MSFTNGP14.phx.gbl...
>I know If I store the DTS packages in SQL Server it will be stored in the
> msdb database.
> Also all the job scheduling information is stored in the msdb database.
> Even though I am taking backup of system databases, I would like to know
> if
> is there any way I can export all the DTS packages and jobs in any
> different
> way. This is only an additional extra precaution incase If I am not able
> to
> restore the system database in the event of disaster.
> I am able to open the DTS packages individually and store as .dts file. It
> is a time consuming process.
> I am using SQL 2K.
> Thanks,
> J Smith
>|||Free Backup Software from Darren Green is available at www.sqldts.com
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jessy Martin" <Jessy_Smith79@.hotmail.com> wrote in message
news:uMDTg4gZFHA.3048@.TK2MSFTNGP14.phx.gbl...
>I know If I store the DTS packages in SQL Server it will be stored in the
> msdb database.
> Also all the job scheduling information is stored in the msdb database.
> Even though I am taking backup of system databases, I would like to know
> if
> is there any way I can export all the DTS packages and jobs in any
> different
> way. This is only an additional extra precaution incase If I am not able
> to
> restore the system database in the event of disaster.
> I am able to open the DTS packages individually and store as .dts file. It
> is a time consuming process.
> I am using SQL 2K.
> Thanks,
> J Smith
>

Backing up DTS and SQL jobs....

I know If I store the DTS packages in SQL Server it will be stored in the
msdb database.
Also all the job scheduling information is stored in the msdb database.
Even though I am taking backup of system databases, I would like to know if
is there any way I can export all the DTS packages and jobs in any different
way. This is only an additional extra precaution incase If I am not able to
restore the system database in the event of disaster.
I am able to open the DTS packages individually and store as .dts file. It
is a time consuming process.
I am using SQL 2K.
Thanks,
J SmithFor DTS backup, I use a neat little program called, "DTS Backup 2000." It's
freeware.
"Jessy Martin" <Jessy_Smith79@.hotmail.com> wrote in message
news:uMDTg4gZFHA.3048@.TK2MSFTNGP14.phx.gbl...
>I know If I store the DTS packages in SQL Server it will be stored in the
> msdb database.
> Also all the job scheduling information is stored in the msdb database.
> Even though I am taking backup of system databases, I would like to know
> if
> is there any way I can export all the DTS packages and jobs in any
> different
> way. This is only an additional extra precaution incase If I am not able
> to
> restore the system database in the event of disaster.
> I am able to open the DTS packages individually and store as .dts file. It
> is a time consuming process.
> I am using SQL 2K.
> Thanks,
> J Smith
>|||Free Backup Software from Darren Green is available at www.sqldts.com
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jessy Martin" <Jessy_Smith79@.hotmail.com> wrote in message
news:uMDTg4gZFHA.3048@.TK2MSFTNGP14.phx.gbl...
>I know If I store the DTS packages in SQL Server it will be stored in the
> msdb database.
> Also all the job scheduling information is stored in the msdb database.
> Even though I am taking backup of system databases, I would like to know
> if
> is there any way I can export all the DTS packages and jobs in any
> different
> way. This is only an additional extra precaution incase If I am not able
> to
> restore the system database in the event of disaster.
> I am able to open the DTS packages individually and store as .dts file. It
> is a time consuming process.
> I am using SQL 2K.
> Thanks,
> J Smith
>

back up job not backing up properly?

I had created 7 jobs to back up our database every day, the files were
created, and the modified dates have been updating on the files that
were created, so I never bothered to check the data, as it was only a
temporary solution anyway. However, somebody at work recently deleted a
lot of data, so we went to restore last nights back-up, however the
back-ups apparently weren't working as intended. The data is old, here
is one of my scripts. Does anyone notice anything in there that might
'cause the files not to be updated properly with fresh data?
USE msdb
EXEC sp_add_job @.job_name = 'Tuesday_MagStats_Backup',
@.enabled = 1,
@.description = 'Tuesday_MagStats_Backup',
@.owner_login_name = 'sa',
@.notify_level_eventlog = 2,
@.notify_level_email = 2,
@.notify_level_netsend =2,
@.notify_level_page = 2
-- @.notify_email_operator_name = 'email name'
go
-- Add job step (backup data).
USE msdb
EXEC sp_add_jobstep @.job_name = 'Tuesday_MagStats_Backup',
@.step_name = 'Tuesday_MagStats_Backup',
@.subsystem = 'TSQL',
@.command = 'BACKUP DATABASE magstats TO DISK = ''c:\backups\Tuesday_databasebackup.bak''',
@.on_success_action = 3,
@.retry_attempts = 5,
@.retry_interval = 5
go
-- Add job step (backup log).
USE msdb
EXEC sp_add_jobstep @.job_name = 'Tuesday_MagStats_Backup',
@.step_name = 'Tuesday_Magstats_Log_Backup',
@.subsystem = 'TSQL',
@.command = 'BACKUP LOG magstats TO DISK = ''c:\backups\Tuesday_log_databasebackup.log''',
@.on_success_action = 1,
@.retry_attempts = 5,
@.retry_interval = 5
go
-- Add the target servers.
USE msdb
EXEC sp_add_jobserver @.job_name = 'Tuesday_MagStats_Backup',
@.server_name = N'(local)'
-- Schedule job.
USE msdb
EXEC sp_add_jobschedule @.job_name = 'Tuesday_MagStats_Backup',
@.name = 'ScheduledBackup_MagStats_DB',
@.freq_type = 8, --weekly
@.freq_interval = 4, --Tuesday
@.freq_recurrence_factor = 1, --weekly inbetween
@.active_start_time = '043000' -- (043000 = 4:30 AM) 24hr HHMMSS.
goApparently, this method will keep many back-ups within one file, and
when trying to restore through command line with osql, it restores the
frist one on the file. I was able to restore the correct database using
MS SQL Server Management Studio Express.|||If you don't use the WITH INIT option of the BACKUP command all new backups
are appended to the existing backup device. In order to restore from this
you need to tell SQL Server which backup in the device you need. Please
refer to RESTORE in BOL for more details.
--
Andrew J. Kelly SQL MVP
"KBuser" <sakred@.element9media.com> wrote in message
news:1137002233.526102.65220@.o13g2000cwo.googlegroups.com...
>I had created 7 jobs to back up our database every day, the files were
> created, and the modified dates have been updating on the files that
> were created, so I never bothered to check the data, as it was only a
> temporary solution anyway. However, somebody at work recently deleted a
> lot of data, so we went to restore last nights back-up, however the
> back-ups apparently weren't working as intended. The data is old, here
> is one of my scripts. Does anyone notice anything in there that might
> 'cause the files not to be updated properly with fresh data?
> USE msdb
> EXEC sp_add_job @.job_name = 'Tuesday_MagStats_Backup',
> @.enabled = 1,
> @.description = 'Tuesday_MagStats_Backup',
> @.owner_login_name = 'sa',
> @.notify_level_eventlog = 2,
> @.notify_level_email = 2,
> @.notify_level_netsend =2,
> @.notify_level_page = 2
> -- @.notify_email_operator_name = 'email name'
> go
> -- Add job step (backup data).
> USE msdb
> EXEC sp_add_jobstep @.job_name = 'Tuesday_MagStats_Backup',
> @.step_name = 'Tuesday_MagStats_Backup',
> @.subsystem = 'TSQL',
> @.command = 'BACKUP DATABASE magstats TO DISK => ''c:\backups\Tuesday_databasebackup.bak''',
> @.on_success_action = 3,
> @.retry_attempts = 5,
> @.retry_interval = 5
> go
> -- Add job step (backup log).
> USE msdb
> EXEC sp_add_jobstep @.job_name = 'Tuesday_MagStats_Backup',
> @.step_name = 'Tuesday_Magstats_Log_Backup',
> @.subsystem = 'TSQL',
> @.command = 'BACKUP LOG magstats TO DISK => ''c:\backups\Tuesday_log_databasebackup.log''',
> @.on_success_action = 1,
> @.retry_attempts = 5,
> @.retry_interval = 5
> go
> -- Add the target servers.
> USE msdb
> EXEC sp_add_jobserver @.job_name = 'Tuesday_MagStats_Backup',
> @.server_name = N'(local)'
> -- Schedule job.
> USE msdb
> EXEC sp_add_jobschedule @.job_name = 'Tuesday_MagStats_Backup',
> @.name = 'ScheduledBackup_MagStats_DB',
> @.freq_type = 8, --weekly
> @.freq_interval = 4, --Tuesday
> @.freq_recurrence_factor = 1, --weekly inbetween
> @.active_start_time = '043000' -- (043000 = 4:30 AM) 24hr HHMMSS.
> go
>

back up job not backing up properly?

I had created 7 jobs to back up our database every day, the files were
created, and the modified dates have been updating on the files that
were created, so I never bothered to check the data, as it was only a
temporary solution anyway. However, somebody at work recently deleted a
lot of data, so we went to restore last nights back-up, however the
back-ups apparently weren't working as intended. The data is old, here
is one of my scripts. Does anyone notice anything in there that might
'cause the files not to be updated properly with fresh data?
USE msdb
EXEC sp_add_job @.job_name = 'Tuesday_MagStats_Backup',
@.enabled = 1,
@.description = 'Tuesday_MagStats_Backup',
@.owner_login_name = 'sa',
@.notify_level_eventlog = 2,
@.notify_level_email = 2,
@.notify_level_netsend =2,
@.notify_level_page = 2
-- @.notify_email_operator_name = 'email name'
go
-- Add job step (backup data).
USE msdb
EXEC sp_add_jobstep @.job_name = 'Tuesday_MagStats_Backup',
@.step_name = 'Tuesday_MagStats_Backup',
@.subsystem = 'TSQL',
@.command = 'BACKUP DATABASE magstats TO DISK =
''c:\backups\Tuesday_databasebackup.bak''',
@.on_success_action = 3,
@.retry_attempts = 5,
@.retry_interval = 5
go
-- Add job step (backup log).
USE msdb
EXEC sp_add_jobstep @.job_name = 'Tuesday_MagStats_Backup',
@.step_name = 'Tuesday_Magstats_Log_Backup',
@.subsystem = 'TSQL',
@.command = 'BACKUP LOG magstats TO DISK =
''c:\backups\Tuesday_log_databasebackup.log''',
@.on_success_action = 1,
@.retry_attempts = 5,
@.retry_interval = 5
go
-- Add the target servers.
USE msdb
EXEC sp_add_jobserver @.job_name = 'Tuesday_MagStats_Backup',
@.server_name = N'(local)'
-- Schedule job.
USE msdb
EXEC sp_add_jobschedule @.job_name = 'Tuesday_MagStats_Backup',
@.name = 'ScheduledBackup_MagStats_DB',
@.freq_type = 8, --weekly
@.freq_interval = 4, --Tuesday
@.freq_recurrence_factor = 1, --weekly inbetween
@.active_start_time = '043000' -- (043000 = 4:30 AM) 24hr HHMMSS.
go
Apparently, this method will keep many back-ups within one file, and
when trying to restore through command line with osql, it restores the
frist one on the file. I was able to restore the correct database using
MS SQL Server Management Studio Express.
|||If you don't use the WITH INIT option of the BACKUP command all new backups
are appended to the existing backup device. In order to restore from this
you need to tell SQL Server which backup in the device you need. Please
refer to RESTORE in BOL for more details.
Andrew J. Kelly SQL MVP
"KBuser" <sakred@.element9media.com> wrote in message
news:1137002233.526102.65220@.o13g2000cwo.googlegro ups.com...
>I had created 7 jobs to back up our database every day, the files were
> created, and the modified dates have been updating on the files that
> were created, so I never bothered to check the data, as it was only a
> temporary solution anyway. However, somebody at work recently deleted a
> lot of data, so we went to restore last nights back-up, however the
> back-ups apparently weren't working as intended. The data is old, here
> is one of my scripts. Does anyone notice anything in there that might
> 'cause the files not to be updated properly with fresh data?
> USE msdb
> EXEC sp_add_job @.job_name = 'Tuesday_MagStats_Backup',
> @.enabled = 1,
> @.description = 'Tuesday_MagStats_Backup',
> @.owner_login_name = 'sa',
> @.notify_level_eventlog = 2,
> @.notify_level_email = 2,
> @.notify_level_netsend =2,
> @.notify_level_page = 2
> -- @.notify_email_operator_name = 'email name'
> go
> -- Add job step (backup data).
> USE msdb
> EXEC sp_add_jobstep @.job_name = 'Tuesday_MagStats_Backup',
> @.step_name = 'Tuesday_MagStats_Backup',
> @.subsystem = 'TSQL',
> @.command = 'BACKUP DATABASE magstats TO DISK =
> ''c:\backups\Tuesday_databasebackup.bak''',
> @.on_success_action = 3,
> @.retry_attempts = 5,
> @.retry_interval = 5
> go
> -- Add job step (backup log).
> USE msdb
> EXEC sp_add_jobstep @.job_name = 'Tuesday_MagStats_Backup',
> @.step_name = 'Tuesday_Magstats_Log_Backup',
> @.subsystem = 'TSQL',
> @.command = 'BACKUP LOG magstats TO DISK =
> ''c:\backups\Tuesday_log_databasebackup.log''',
> @.on_success_action = 1,
> @.retry_attempts = 5,
> @.retry_interval = 5
> go
> -- Add the target servers.
> USE msdb
> EXEC sp_add_jobserver @.job_name = 'Tuesday_MagStats_Backup',
> @.server_name = N'(local)'
> -- Schedule job.
> USE msdb
> EXEC sp_add_jobschedule @.job_name = 'Tuesday_MagStats_Backup',
> @.name = 'ScheduledBackup_MagStats_DB',
> @.freq_type = 8, --weekly
> @.freq_interval = 4, --Tuesday
> @.freq_recurrence_factor = 1, --weekly inbetween
> @.active_start_time = '043000' -- (043000 = 4:30 AM) 24hr HHMMSS.
> go
>

back up job not backing up properly?

I had created 7 jobs to back up our database every day, the files were
created, and the modified dates have been updating on the files that
were created, so I never bothered to check the data, as it was only a
temporary solution anyway. However, somebody at work recently deleted a
lot of data, so we went to restore last nights back-up, however the
back-ups apparently weren't working as intended. The data is old, here
is one of my scripts. Does anyone notice anything in there that might
'cause the files not to be updated properly with fresh data?
USE msdb
EXEC sp_add_job @.job_name = 'Tuesday_MagStats_Backup',
@.enabled = 1,
@.description = 'Tuesday_MagStats_Backup',
@.owner_login_name = 'sa',
@.notify_level_eventlog = 2,
@.notify_level_email = 2,
@.notify_level_netsend =2,
@.notify_level_page = 2
-- @.notify_email_operator_name = 'email name'
go
-- Add job step (backup data).
USE msdb
EXEC sp_add_jobstep @.job_name = 'Tuesday_MagStats_Backup',
@.step_name = 'Tuesday_MagStats_Backup',
@.subsystem = 'TSQL',
@.command = 'BACKUP DATABASE magstats TO DISK =
''c:\backups\Tuesday_databasebackup.bak''',
@.on_success_action = 3,
@.retry_attempts = 5,
@.retry_interval = 5
go
-- Add job step (backup log).
USE msdb
EXEC sp_add_jobstep @.job_name = 'Tuesday_MagStats_Backup',
@.step_name = 'Tuesday_Magstats_Log_Backup',
@.subsystem = 'TSQL',
@.command = 'BACKUP LOG magstats TO DISK =
''c:\backups\Tuesday_log_databasebackup.log''',
@.on_success_action = 1,
@.retry_attempts = 5,
@.retry_interval = 5
go
-- Add the target servers.
USE msdb
EXEC sp_add_jobserver @.job_name = 'Tuesday_MagStats_Backup',
@.server_name = N'(local)'
-- Schedule job.
USE msdb
EXEC sp_add_jobschedule @.job_name = 'Tuesday_MagStats_Backup',
@.name = 'ScheduledBackup_MagStats_DB',
@.freq_type = 8, --weekly
@.freq_interval = 4, --Tuesday
@.freq_recurrence_factor = 1, --weekly inbetween
@.active_start_time = '043000' -- (043000 = 4:30 AM) 24hr HHMMSS.
goApparently, this method will keep many back-ups within one file, and
when trying to restore through command line with osql, it restores the
frist one on the file. I was able to restore the correct database using
MS SQL Server Management Studio Express.|||If you don't use the WITH INIT option of the BACKUP command all new backups
are appended to the existing backup device. In order to restore from this
you need to tell SQL Server which backup in the device you need. Please
refer to RESTORE in BOL for more details.
Andrew J. Kelly SQL MVP
"KBuser" <sakred@.element9media.com> wrote in message
news:1137002233.526102.65220@.o13g2000cwo.googlegroups.com...
>I had created 7 jobs to back up our database every day, the files were
> created, and the modified dates have been updating on the files that
> were created, so I never bothered to check the data, as it was only a
> temporary solution anyway. However, somebody at work recently deleted a
> lot of data, so we went to restore last nights back-up, however the
> back-ups apparently weren't working as intended. The data is old, here
> is one of my scripts. Does anyone notice anything in there that might
> 'cause the files not to be updated properly with fresh data?
> USE msdb
> EXEC sp_add_job @.job_name = 'Tuesday_MagStats_Backup',
> @.enabled = 1,
> @.description = 'Tuesday_MagStats_Backup',
> @.owner_login_name = 'sa',
> @.notify_level_eventlog = 2,
> @.notify_level_email = 2,
> @.notify_level_netsend =2,
> @.notify_level_page = 2
> -- @.notify_email_operator_name = 'email name'
> go
> -- Add job step (backup data).
> USE msdb
> EXEC sp_add_jobstep @.job_name = 'Tuesday_MagStats_Backup',
> @.step_name = 'Tuesday_MagStats_Backup',
> @.subsystem = 'TSQL',
> @.command = 'BACKUP DATABASE magstats TO DISK =
> ''c:\backups\Tuesday_databasebackup.bak''',
> @.on_success_action = 3,
> @.retry_attempts = 5,
> @.retry_interval = 5
> go
> -- Add job step (backup log).
> USE msdb
> EXEC sp_add_jobstep @.job_name = 'Tuesday_MagStats_Backup',
> @.step_name = 'Tuesday_Magstats_Log_Backup',
> @.subsystem = 'TSQL',
> @.command = 'BACKUP LOG magstats TO DISK =
> ''c:\backups\Tuesday_log_databasebackup.log''',
> @.on_success_action = 1,
> @.retry_attempts = 5,
> @.retry_interval = 5
> go
> -- Add the target servers.
> USE msdb
> EXEC sp_add_jobserver @.job_name = 'Tuesday_MagStats_Backup',
> @.server_name = N'(local)'
> -- Schedule job.
> USE msdb
> EXEC sp_add_jobschedule @.job_name = 'Tuesday_MagStats_Backup',
> @.name = 'ScheduledBackup_MagStats_DB',
> @.freq_type = 8, --weekly
> @.freq_interval = 4, --Tuesday
> @.freq_recurrence_factor = 1, --weekly inbetween
> @.active_start_time = '043000' -- (043000 = 4:30 AM) 24hr HHMMSS.
> go
>