Automating Backups

Is there a way to automate backups through MSDE? I have been trying to find
a way I can automate a backup of a SQL database through a network. Please
help!
Thanks in advance
You can automate backups using some OSQL scripts and the scheduler on the
machine where MSDE is installed.
Jim
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:74FB91B8-1111-49F5-A25C-F14E8EFC84FC@.microsoft.com...
> Is there a way to automate backups through MSDE? I have been trying to
> find
> a way I can automate a backup of a SQL database through a network. Please
> help!
> Thanks in advance
|||I kind of figured that but what would be the syntax to connect to the remote
PC?
"Jim Young" wrote:

> You can automate backups using some OSQL scripts and the scheduler on the
> machine where MSDE is installed.
> Jim
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:74FB91B8-1111-49F5-A25C-F14E8EFC84FC@.microsoft.com...
>
>
|||hi Paul,
Paul wrote:[vbcol=seagreen]
> I kind of figured that but what would be the syntax to connect to the
> remote PC?
> "Jim Young" wrote:
you can even use the SQL Server Agent to provide this kind of feature..
you have to create a Job and relative schedule, and add a job step including
a T-SQL command like
BACKUP DATABASE ...
if you have to access network shares, the account running SQL Server agent
will require enought privileges on those shares..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Ok ... I must be sleep deprived or just plain not too smart. I have access
to the PC I am trying to connect to. DO I have to add a mapped drive? I
have been able to backup to any other driver letter but my mapped drive.
"Andrea Montanari" wrote:

> hi Paul,
> Paul wrote:
> you can even use the SQL Server Agent to provide this kind of feature..
> you have to create a Job and relative schedule, and add a job step including
> a T-SQL command like
> BACKUP DATABASE ...
> if you have to access network shares, the account running SQL Server agent
> will require enought privileges on those shares..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>
|||hi Paul,
Paul wrote:
> Ok ... I must be sleep deprived or just plain not too smart. I have
> access to the PC I am trying to connect to. DO I have to add a
> mapped drive? I have been able to backup to any other driver letter
> but my mapped drive.
>
I use UNC notation...
just
BACKUP DATABASE [a]
TO DISK = N'\\Roberto\documenti\a.bak' WITH INIT
, NAME = N'a BackUp'
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thankyou that was a big help. I did have kind of a related question. I
found a knowledge base documant for writing a backup script. It is 241397.
I modified the text to fit my database and moved in the stored procedures etc
but now I get an error "could not find stored procedure
'dinerware.dbo.xp_sqlagent_is_starting'. When I try to add that stored
procedure it tell me that "sp_addextendedproc can only be executed by the
master database. Would you know what I am doing wrong. Sorry if thses
questions are dumb. It has been about a 24 hour strestch for me.
"Andrea Montanari" wrote:

> hi Paul,
> Paul wrote:
> I use UNC notation...
> just
> BACKUP DATABASE [a]
> TO DISK = N'\\Roberto\documenti\a.bak' WITH INIT
> , NAME = N'a BackUp'
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>
|||hi Paul,
Paul wrote:
> Thankyou that was a big help. I did have kind of a related question.
> I found a knowledge base documant for writing a backup script. It is
> 241397. I modified the text to fit my database and moved in the
> stored procedures etc but now I get an error "could not find stored
> procedure 'dinerware.dbo.xp_sqlagent_is_starting'. When I try to add
> that stored procedure it tell me that "sp_addextendedproc can only be
> executed by the master database. Would you know what I am doing
> wrong. Sorry if thses questions are dumb. It has been about a 24
> hour strestch for me.
>
can you please post the script?
BTW you ca not move sp_add_jobXXX procedures (if that is what you mean) as
they reside in msdb database..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Here is the script. There is a scheduling section after. Maybe I am trying
to do this all wrong. I am not sure. The sites use MSDE but I have SQL
Server 2K developer. I need to set up a backup and restore accross a network
to another instance of MSDE for redundancy purposes.
-- Create job.
-- You may specify an e-mail address, commented below, and/or pager, etc.
-- For more details about this option or others, see SQL Server Books Online.
USE msdb
EXEC sp_add_job @.job_name = 'myTestBackupJob',
@.enabled = 1,
@.description = 'myTestBackupJob',
@.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 = 'myTestBackupJob',
@.step_name = 'Backup msdb Data',
@.subsystem = 'TSQL',
@.command = 'BACKUP DATABASE msdb TO DISK = ''c:\msdb.dat_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 = 'myTestBackupJob',
@.step_name = 'Backup msdb Log',
@.subsystem = 'TSQL',
@.command = 'BACKUP LOG msdb TO DISK = ''c:\msdb.log_bak''',
@.on_success_action = 1,
@.retry_attempts = 5,
@.retry_interval = 5
go
-- Add the target servers.
USE msdb
EXEC sp_add_jobserver @.job_name = 'myTestBackupJob', @.server_name = N'(local)'
-- Run job. Starts the job immediately.
USE msdb
EXEC sp_start_job @.job_name = 'myTestBackupJob'
I would need this to backup a database called dinerware. Thanks again for
your help
"Andrea Montanari" wrote:

> hi Paul,
> Paul wrote:
> can you please post the script?
> BTW you ca not move sp_add_jobXXX procedures (if that is what you mean) as
> they reside in msdb database..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>
|||hi Paul,
Paul wrote:
> Here is the script. There is a scheduling section after. Maybe I am
> trying to do this all wrong. I am not sure. The sites use MSDE but
> I have SQL Server 2K developer. I need to set up a backup and
> restore accross a network to another instance of MSDE for redundancy
> purposes.
the script is fine but the part that perform the BACKUP LOG as msdb is in
simple recovery model so that no backup log is allowed...
the only things you have to change are
-- Add job step (backup data).
USE msdb
EXEC sp_add_jobstep @.job_name = 'myTestBackupJob',
@.step_name = 'Backup msdb Data', -- change step name to match your db
name
@.subsystem = 'TSQL',
@.command = 'BACKUP DATABASE [database_name] TO DISK =
''c:\msdb.dat_bak''', -- change this to point
-- to your actual database
@.on_success_action = 3,
@.retry_attempts = 5,
@.retry_interval = 5
go
-- Add job step (backup log).
USE msdb
EXEC sp_add_jobstep @.job_name = 'myTestBackupJob',
@.step_name = 'Backup msdb Log', -- change step name to match your db
name
@.subsystem = 'TSQL',
@.command = 'BACKUP LOG [database_name] TO DISK =
''c:\msdb.log_bak''', -- change this to point
-- to your actual database log
@.on_success_action = 1,
@.retry_attempts = 5,
@.retry_interval = 5
go
but they always will reside in msdb database..
as regard the destination, you can change
TO DISK = ''c:\msdb.log_bak'''
with
TO DISK = ''\\ComputerName\Share\msdb.log_bak'''
as long as the SQL Server Agent has enought privileges (read/write) to that
share..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply