Automating Restoring of *.BAK files

I have set up a Maintenance Plans on a SQL Server 2000 SP3a on one server to
create flat file backup of full databases to *.BAK files nightly.
Is it possible to automate the restoring of such BAK files on another SQL
Server 2000 SP3a on another server (assume I have in place scripts for
copying the BAK files from the source server to the destination server)? If
so, how?http://msdn.microsoft.com/library/en-us/adminsql/ad_automate_42r7.asp
--
David Portas
SQL Server MVP
--|||Yes, I know how to create a job in general, but what exactly do I run to
restore a BAK file?
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1106572451.019746.272450@.f14g2000cwb.googlegroups.com...
> http://msdn.microsoft.com/library/en-us/adminsql/ad_automate_42r7.asp
> --
> David Portas
> SQL Server MVP
> --
>|||Use the RESTORE DATABASE command in a Transact SQL job step. See Books
Online for details of the RESTORE DATABASE command.
--
David Portas
SQL Server MVP
--|||Taking a step back, I am just wondering whether a flat-file backup-restore
would be the best way to synchronise 2 SQL Server 2000 databases? Or should
I go for a DTS package to export database on the source server to an Access
mdb file and import it on the other end? Sometimes, I find that the users
in an exported flat file, following an import on another server is not
"usable" even if the referenced user are already defined on the destination
server.
"Patrick" <patl@.reply.newsgroup.msn.com> wrote in message
news:%23CWx88hAFHA.2552@.TK2MSFTNGP09.phx.gbl...
> Yes, I know how to create a job in general, but what exactly do I run to
> restore a BAK file?
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1106572451.019746.272450@.f14g2000cwb.googlegroups.com...
> > http://msdn.microsoft.com/library/en-us/adminsql/ad_automate_42r7.asp
> > --
> > David Portas
> > SQL Server MVP
> > --
> >
>|||"Patrick" <patl@.reply.newsgroup.msn.com> wrote in message
news:%23S0tVOhAFHA.3416@.TK2MSFTNGP09.phx.gbl...
> I have set up a Maintenance Plans on a SQL Server 2000 SP3a on one server
to
> create flat file backup of full databases to *.BAK files nightly.
> Is it possible to automate the restoring of such BAK files on another SQL
> Server 2000 SP3a on another server (assume I have in place scripts for
> copying the BAK files from the source server to the destination server)?
If
> so, how?
>
Yes.
In my case I wrote a stored proc on the restoring server and called it from
the backing up server.
CREATE procedure restore_FOO as
declare @.backup_file as varchar(255)
select @.backup_file=physical_device_name from
nell.msdb.dbo.backupmediafamily where media_set_id in (select
max(media_set_id) from BAR.msdb.dbo.backupset where database_name='foo')
print @.backup_file
restore database FOO from disk=@.backup_file with
move 'SearchActivity_Data' to 'e:\sql_data\FOO_data.mdf',
move 'SearchActivity_Log' to 'f:\SQL_LOGs\FOO_log.ldf',
move 'SearchActivity_Index' to 'g:\sql_index\FOO_Index_Data.NDF',
replace
GO
>

Automating Restoring of *.BAK files

I have set up a Maintenance Plans on a SQL Server 2000 SP3a on one server to
create flat file backup of full databases to *.BAK files nightly.
Is it possible to automate the restoring of such BAK files on another SQL
Server 2000 SP3a on another server (assume I have in place scripts for
copying the BAK files from the source server to the destination server)? If
so, how?
http://msdn.microsoft.com/library/en...omate_42r7.asp
David Portas
SQL Server MVP
|||Yes, I know how to create a job in general, but what exactly do I run to
restore a BAK file?
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1106572451.019746.272450@.f14g2000cwb.googlegr oups.com...
> http://msdn.microsoft.com/library/en...omate_42r7.asp
> --
> David Portas
> SQL Server MVP
> --
>
|||Use the RESTORE DATABASE command in a Transact SQL job step. See Books
Online for details of the RESTORE DATABASE command.
David Portas
SQL Server MVP
|||"Patrick" <patl@.reply.newsgroup.msn.com> wrote in message
news:%23S0tVOhAFHA.3416@.TK2MSFTNGP09.phx.gbl...
> I have set up a Maintenance Plans on a SQL Server 2000 SP3a on one server
to
> create flat file backup of full databases to *.BAK files nightly.
> Is it possible to automate the restoring of such BAK files on another SQL
> Server 2000 SP3a on another server (assume I have in place scripts for
> copying the BAK files from the source server to the destination server)?
If
> so, how?
>
Yes.
In my case I wrote a stored proc on the restoring server and called it from
the backing up server.
CREATE procedure restore_FOO as
declare @.backup_file as varchar(255)
select @.backup_file=physical_device_name from
nell.msdb.dbo.backupmediafamily where media_set_id in (select
max(media_set_id) from BAR.msdb.dbo.backupset where database_name='foo')
print @.backup_file
restore database FOO from disk=@.backup_file with
move 'SearchActivity_Data' to 'e:\sql_data\FOO_data.mdf',
move 'SearchActivity_Log' to 'f:\SQL_LOGs\FOO_log.ldf',
move 'SearchActivity_Index' to 'g:\sql_index\FOO_Index_Data.NDF',
replace
GO

>
sql

Automating Restoring of *.BAK files

I have set up a Maintenance Plans on a SQL Server 2000 SP3a on one server to
create flat file backup of full databases to *.BAK files nightly.
Is it possible to automate the restoring of such BAK files on another SQL
Server 2000 SP3a on another server (assume I have in place scripts for
copying the BAK files from the source server to the destination server)? If
so, how?http://msdn.microsoft.com/library/e...tomate_42r7.asp
--
David Portas
SQL Server MVP
--|||Yes, I know how to create a job in general, but what exactly do I run to
restore a BAK file?
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1106572451.019746.272450@.f14g2000cwb.googlegroups.com...
> http://msdn.microsoft.com/library/e...tomate_42r7.asp
> --
> David Portas
> SQL Server MVP
> --
>|||Use the RESTORE DATABASE command in a Transact SQL job step. See Books
Online for details of the RESTORE DATABASE command.
David Portas
SQL Server MVP
--|||"Patrick" <patl@.reply.newsgroup.msn.com> wrote in message
news:%23S0tVOhAFHA.3416@.TK2MSFTNGP09.phx.gbl...
> I have set up a Maintenance Plans on a SQL Server 2000 SP3a on one server
to
> create flat file backup of full databases to *.BAK files nightly.
> Is it possible to automate the restoring of such BAK files on another SQL
> Server 2000 SP3a on another server (assume I have in place scripts for
> copying the BAK files from the source server to the destination server)?
If
> so, how?
>
Yes.
In my case I wrote a stored proc on the restoring server and called it from
the backing up server.
CREATE procedure restore_FOO as
declare @.backup_file as varchar(255)
select @.backup_file=physical_device_name from
nell.msdb.dbo.backupmediafamily where media_set_id in (select
max(media_set_id) from BAR.msdb.dbo.backupset where database_name='foo')
print @.backup_file
restore database FOO from disk=@.backup_file with
move 'SearchActivity_Data' to 'e:\sql_data\FOO_data.mdf',
move 'SearchActivity_Log' to 'f:\SQL_LOGs\FOO_log.ldf',
move 'SearchActivity_Index' to 'g:\sql_index\FOO_Index_Data.NDF',
replace
GO

>

automating data refresh

Frequently we get request of data refresh. Its curbersome process everytime
to take backup from prod and restore in development/test region. I am
thinking to automate this process using stored procedure. But I need to pass
various parameters to SP to tell where is source, destination, domain, etc.,
Anyone has better ideas/suggestions. Do you have any script reg.
Thanks,
RamuIf you’re using SQL 2005 there are more options obviously with data mirror
and then snapshot. If you want a straight backup and restore then use a
stored proc on the destination server dev or test to use a UNC to grab last
night’s backup file and copy it locally. Then do the restore. Do the copy
with a batch file or better yet a powershell script and then create another
proc that accepts the parameters you need for the restore. Then create a job
with no schedule and give the developers access to the job. The job would
have the step of executing the batch file then executing the restore. You ca
n
also use snapshot replication to refresh dev and test on a schedule when
things are slow, however if you did this make sure the either the dev or tes
t
system are the distributor to offline some of the over head of replication.
If that’s what you want.
John Vandervliet
"Ramu" wrote:

> Frequently we get request of data refresh. Its curbersome process everytim
e
> to take backup from prod and restore in development/test region. I am
> thinking to automate this process using stored procedure. But I need to pa
ss
> various parameters to SP to tell where is source, destination, domain, etc
.,
> Anyone has better ideas/suggestions. Do you have any script reg.
> Thanks,
> Ramu
>|||The "Copy SQL Server Objects Task" in DTS is your tool.
Quentin
"Ramu" <Ramu@.discussions.microsoft.com> wrote in message
news:EDCBFBCD-55C0-44D9-BB19-190EBFBFD7F4@.microsoft.com...
> Frequently we get request of data refresh. Its curbersome process
> everytime
> to take backup from prod and restore in development/test region. I am
> thinking to automate this process using stored procedure. But I need to
> pass
> various parameters to SP to tell where is source, destination, domain,
> etc.,
> Anyone has better ideas/suggestions. Do you have any script reg.
> Thanks,
> Ramu
>

automating data refresh

Frequently we get request of data refresh. Its curbersome process everytime
to take backup from prod and restore in development/test region. I am
thinking to automate this process using stored procedure. But I need to pass
various parameters to SP to tell where is source, destination, domain, etc.,
Anyone has better ideas/suggestions. Do you have any script reg.
Thanks,
Ramu
If you’re using SQL 2005 there are more options obviously with data mirror
and then snapshot. If you want a straight backup and restore then use a
stored proc on the destination server dev or test to use a UNC to grab last
night’s backup file and copy it locally. Then do the restore. Do the copy
with a batch file or better yet a powershell script and then create another
proc that accepts the parameters you need for the restore. Then create a job
with no schedule and give the developers access to the job. The job would
have the step of executing the batch file then executing the restore. You can
also use snapshot replication to refresh dev and test on a schedule when
things are slow, however if you did this make sure the either the dev or test
system are the distributor to offline some of the over head of replication.
If that’s what you want.
John Vandervliet
"Ramu" wrote:

> Frequently we get request of data refresh. Its curbersome process everytime
> to take backup from prod and restore in development/test region. I am
> thinking to automate this process using stored procedure. But I need to pass
> various parameters to SP to tell where is source, destination, domain, etc.,
> Anyone has better ideas/suggestions. Do you have any script reg.
> Thanks,
> Ramu
>
|||The "Copy SQL Server Objects Task" in DTS is your tool.
Quentin
"Ramu" <Ramu@.discussions.microsoft.com> wrote in message
news:EDCBFBCD-55C0-44D9-BB19-190EBFBFD7F4@.microsoft.com...
> Frequently we get request of data refresh. Its curbersome process
> everytime
> to take backup from prod and restore in development/test region. I am
> thinking to automate this process using stored procedure. But I need to
> pass
> various parameters to SP to tell where is source, destination, domain,
> etc.,
> Anyone has better ideas/suggestions. Do you have any script reg.
> Thanks,
> Ramu
>

automating data refresh

Frequently we get request of data refresh. Its curbersome process everytime
to take backup from prod and restore in development/test region. I am
thinking to automate this process using stored procedure. But I need to pass
various parameters to SP to tell where is source, destination, domain, etc.,
Anyone has better ideas/suggestions. Do you have any script reg.
Thanks,
RamuIf youâ're using SQL 2005 there are more options obviously with data mirror
and then snapshot. If you want a straight backup and restore then use a
stored proc on the destination server dev or test to use a UNC to grab last
nightâ's backup file and copy it locally. Then do the restore. Do the copy
with a batch file or better yet a powershell script and then create another
proc that accepts the parameters you need for the restore. Then create a job
with no schedule and give the developers access to the job. The job would
have the step of executing the batch file then executing the restore. You can
also use snapshot replication to refresh dev and test on a schedule when
things are slow, however if you did this make sure the either the dev or test
system are the distributor to offline some of the over head of replication.
If thatâ's what you want.
John Vandervliet
"Ramu" wrote:
> Frequently we get request of data refresh. Its curbersome process everytime
> to take backup from prod and restore in development/test region. I am
> thinking to automate this process using stored procedure. But I need to pass
> various parameters to SP to tell where is source, destination, domain, etc.,
> Anyone has better ideas/suggestions. Do you have any script reg.
> Thanks,
> Ramu
>|||The "Copy SQL Server Objects Task" in DTS is your tool.
Quentin
"Ramu" <Ramu@.discussions.microsoft.com> wrote in message
news:EDCBFBCD-55C0-44D9-BB19-190EBFBFD7F4@.microsoft.com...
> Frequently we get request of data refresh. Its curbersome process
> everytime
> to take backup from prod and restore in development/test region. I am
> thinking to automate this process using stored procedure. But I need to
> pass
> various parameters to SP to tell where is source, destination, domain,
> etc.,
> Anyone has better ideas/suggestions. Do you have any script reg.
> Thanks,
> Ramu
>

Automating backups with TSM

I was wondering if anyone can share the procedure(s) used in setting up an automated MS SQL database backup through tivoli Storage Manager
( 5.2.7 )........?I'd settle for getting Tivoli to back up files 50% or more of the time. I'd consider getting Tivoli to do reliable, repeatable backups to be a miracle.

-PatPsql

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

Automating a Backup and Restore across Servers

Hi,

I have been experiencing a problem with a job I am trying to automate. My team requires two instances of a db on development. Every week, db1 is detached and reattached as db1_copy, and a new copy of db1 from production is copied over. I have used cursors for the dropusers, addlogins, grantdbaccess, addroles, and addrolemembers aspects of the restore process. Also, we use sql authentication and not windows authentication. The issue is that when the agent encounters a minor issue, ie. a login that already exists in that db, or adding a rolemember to a role that is already there, I get an error. I have ensure that at that step, the job simply proceeds because it is a minor step in the process (it happens during the script execution when done manually but I can override this because I am present and just let it run in one step instead of many). Is there any way to do this without adding a multitude of steps?

Thanks,

MariaHi Maria,
try if putting the statement

SET XACT_ABORT OFF

in the TSQL-stream of the step solves your problem.

Automatics restore script

I am setting up a job to automatically restore the standby database.
This is to test our tape backups. I need to passon the latest full
backup and log file names to the tape system to extract the files. How
do I get the file names? I would like to store these file names in temp
table and pass on to tape system one by one to extract it to local
disk. Do I need to query the msdb of primary or backup directory?
Please suggest sql to achieve this.
Thanks
TramIf you do SQL Backups then you shouldn't need to know the file names because
the backup is referenced by database name. Are you doing file system
backups? That's not recommended unless you detach the database first.
Query the sysfiles system table for the physical file names.
--
David Portas
SQL Server MVP
--|||A I need to restore from third party tape subsyste, the enterprise
manager doen't recognize the restore fle anmes. I have to develop the
script to retiriev the latest backup and logs therafter.|||A I need to restore from third party tape subsystem, the enterprise
manager doen't recognize the restore fle anmes. I have to develop the
script to retiriev the latest backup and logs therafter.|||A I need to restore from third party tape subsystem, the enterprise
manager doen't recognize the restore fle anmes. I have to develop the
script to retiriev the latest backup and logs therafter.|||Hi
If it is 3rd party, ask the 3rd party vendor how you can script a restore
using their command line tools.
If SQL Server did not back the data up itself (to disk or a backup device
that it can control) it can't get the data back for you by itself.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"tram" <tram_e@.hotmail.com> wrote in message
news:1117887893.908844.130200@.f14g2000cwb.googlegroups.com...
>A I need to restore from third party tape subsystem, the enterprise
> manager doen't recognize the restore fle anmes. I have to develop the
> script to retiriev the latest backup and logs therafter.
>|||Thirty party vendor has command to resore the files. I have to passon
the filename as parameter, that is latest bak file and corresponding
trn files.|||Hi
Have you looked at the backupfile and backupset tables in msdb to see if it
has what you need?
John
"tram" <tram_e@.hotmail.com> wrote in message
news:1117889003.683361.325430@.g43g2000cwa.googlegroups.com...
> Thirty party vendor has command to resore the files. I have to passon
> the filename as parameter, that is latest bak file and corresponding
> trn files.
>sql

Automatically copying tables to a backup question...

Folks,
Hope you can help with this one please. I want to regulary copy the
contents of a particular table in a database to another database server
with an identical setup.
My question is: does SQL Server 2000 have the ability to do this
automatically?
Any comments/suggestions/user-experiences/things to watch out for
would me most appreciated.
Cheers,
Al.almurph@.altavista.com wrote:
> Folks,
>
> Hope you can help with this one please. I want to regulary copy the
> contents of a particular table in a database to another database server
> with an identical setup.
> My question is: does SQL Server 2000 have the ability to do this
> automatically?
> Any comments/suggestions/user-experiences/things to watch out for
> would me most appreciated.
> Cheers,
> Al.
DTS will be best option for this.
You can create a DTS Package and schedule it.
Look in BOL for more details.
Regards
Amish Shah
http://shahamishm.tripod.com|||almurph@.altavista.com wrote:
> Folks,
>
> Hope you can help with this one please. I want to regulary copy the
> contents of a particular table in a database to another database server
> with an identical setup.
> My question is: does SQL Server 2000 have the ability to do this
> automatically?
> Any comments/suggestions/user-experiences/things to watch out for
> would me most appreciated.
> Cheers,
> Al.
>
There are several options available to you:
- replication
- DTS
- a simple INSERT/SELECT over a linked server
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Automatically copying tables to a backup question...

Folks,
Hope you can help with this one please. I want to regulary copy the
contents of a particular table in a database to another database server
with an identical setup.
My question is: does SQL Server 2000 have the ability to do this
automatically?
Any comments/suggestions/user-experiences/things to watch out for
would me most appreciated.
Cheers,
Al.almurph@.altavista.com wrote:

> Folks,
>
> Hope you can help with this one please. I want to regulary copy the
> contents of a particular table in a database to another database server
> with an identical setup.
> My question is: does SQL Server 2000 have the ability to do this
> automatically?
> Any comments/suggestions/user-experiences/things to watch out for
> would me most appreciated.
> Cheers,
> Al.
DTS will be best option for this.
You can create a DTS Package and schedule it.
Look in BOL for more details.
Regards
Amish Shah
http://shahamishm.tripod.com|||almurph@.altavista.com wrote:
> Folks,
>
> Hope you can help with this one please. I want to regulary copy the
> contents of a particular table in a database to another database server
> with an identical setup.
> My question is: does SQL Server 2000 have the ability to do this
> automatically?
> Any comments/suggestions/user-experiences/things to watch out for
> would me most appreciated.
> Cheers,
> Al.
>
There are several options available to you:
- replication
- DTS
- a simple INSERT/SELECT over a linked server
Tracy McKibben
MCDBA
http://www.realsqlguy.comsql

automatically compress backups

I have a maintenance plan for handling backups. I need to be able to amend the jobs so that the latest backup can be compressed (e.g. command line compression utility) before it is copied off across the network to a DR machine.

The issue is that the Maintenance Plan produces data/time specific backup file names - mulitiple backups stored in the same directory. Therefore, within a job step, I'm finding it difficult to code it such that only the latest backup is compressed. The command line syntax of these compression utilities is somewhat limited...

e.g. wzzip zipname.zip dbbackup_*.bak

nb. The '*' represents the wild card for the date/time part of the backup files created by the maintenance plan.

The problem is that the above command would keep adding multiple backups to the same zip archive. What I want is to simply add only the latest backup to the archive but to achieve this I would have to know the name of the backup file (created by the maintenance plan) programatically within the job.

I would have thought this was a fairly common requirement/problem. Has anyone got some suggestions/solutions?

CliveRE:
Q1 Has anyone got some suggestions/solutions?

A1 I doubt you'll like hearing this, my suggestion is don't compress production backups if you can possibly avoid it in any production setting (especially certain kinds of software based compression).

I've seen too many failed validation restores that seem to have only the fact of having been compressed and decompressed in common to heavily rely on any compression of backup dumps. (Granted, it does work 'most' of the time, which is generally fine for many development environments. However, in some situations where compression is implemented with larger backups I've seen routine daily restore validations fail or restore with problems every few weeks.)
---

If you are intent on going ahead anyway, what Sql Server version you are running is important. (In 2k installs I've implemented UDF functions instead of stored procedures for extracting dump information.)

Unfortunately no version has provided particularly rich built in backup / maintenence procedures to address the information needs you would require. (There are lots of undocumented ones that are worth looking at, but they are as subject to unannounced changes as the underlying MSDB table structures. I think you may be stuck implementing and maintaining your own special functions / stored procedures). Current Maintenance Plan Procedures (ver 7 / 2k) include:
sp_add_maintenance_plan
sp_add_maintenance_plan_db
sp_add_maintenance_plan_job
sp_delete_maintenance_plan
sp_delete_maintenance_plan_db
sp_delete_maintenance_plan_job
sp_help_maintenance_plan

The following user special stored procedure (implemented on some ver. 7 installs) returns the last DB or TL dumpfor a specified DB and Full or Log Dump on 7.0 and 2k (but not 6.x) installs:

Use
Master
Go
DROP PROCEDURE sp_LastDump
go
CREATE PROCEDURE sp_LastDump
--Fully Qualified Select most recent DBDump or TLDump
--@.pBkpSetTyp = I, D, L, @.pDBNam = DB name,
-- sp parameters:
@.pDBNam VarChar(512) = 'Master',
@.pBkpSetTyp VarChar(50) = 'D'
AS
exec ('Set NoCount On')
SELECT MsDb..backupmediafamily.physical_device_name
FROM MsDb..backupmediafamily INNER JOIN
MsDb..backupset ON
MsDb..backupmediafamily.media_set_id = MsDb..backupset.media_set_id
WHERE MsDb..backupset.backup_finish_date =
(SELECT MAX(MsDb..backupset.backup_finish_date)
AS Mxbackup_finish_date
FROM MsDb..backupmediafamily INNER JOIN
MsDb..backupset ON
MsDb..backupmediafamily.media_set_id = MsDb..backupset.media_set_id
WHERE (MsDb..backupset.database_name = @.pDBNam) AND
(MsDb..backupset.type = @.pBkpSetTyp))

Note: If running 6.x or earlier, you'll need to create a proc referencing 6.x msdb tables instead e.g.(sysbackuphistory sysbackupdetail). If later upgrade to 7.0 or 2k, or later versions, you'll need to rdefine any special stored procs you create to reflect newer MSDB tables.|||How is the currently developed maintenance plan programmed - through the maintenance wizard / dts / stored procedure ... ? Which compression software package are you using ?

Automatically backup....?

//[Microsoft][ODBC SQL Server Driver][SQL Server]Repair
statement not processed. Database needs to be in single
user mode.
** Execution Time: 0 hrs, 0 mins, 1 secs **
End of maintenance plan 'Cybermurid maintenance' on
7/20/2004 5:55:00 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)//
How do I set automatically backup in SQL server?
Niky wrote:

> //[Microsoft][ODBC SQL Server Driver][SQL Server]Repair
> statement not processed. Database needs to be in single
> user mode.
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> End of maintenance plan 'Cybermurid maintenance' on
> 7/20/2004 5:55:00 AM
> SQLMAINT.EXE Process Exit Code: 1 (Failed)//
> How do I set automatically backup in SQL server?
Hi Niky,
you can create a new job (under SQL Agent, in Enterprise Manager):
- the activity you must add is a T-SQL script, with the instruction
"BACKUP DATABASE MyDb TO [DISK|TAPE]...".
- schedule it
David
|||Hello
I believe that you are running into the bug mentioned in the following
article :
290622 BUG: Database Maintenance Plan on System Databases Fails on Integrity
http://support.microsoft.com/?id=290622
The workaround is to disable the "Attempt to repair minor problems" option
in the Maintenance plan properties. This should stop generating the message
that you have posted.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Automatically backup....?

//[Microsoft][ODBC SQL Server Driver][SQL Server]Repair
statement not processed. Database needs to be in single
user mode.
** Execution Time: 0 hrs, 0 mins, 1 secs **
End of maintenance plan 'Cybermurid maintenance' on
7/20/2004 5:55:00 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)//
How do I set automatically backup in SQL server?Niky wrote:
> //[Microsoft][ODBC SQL Server Driver][SQL Server]Repair
> statement not processed. Database needs to be in single
> user mode.
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> End of maintenance plan 'Cybermurid maintenance' on
> 7/20/2004 5:55:00 AM
> SQLMAINT.EXE Process Exit Code: 1 (Failed)//
> How do I set automatically backup in SQL server?
Hi Niky,
you can create a new job (under SQL Agent, in Enterprise Manager):
- the activity you must add is a T-SQL script, with the instruction
"BACKUP DATABASE MyDb TO [DISK|TAPE]...".
- schedule it
David|||Hello
I believe that you are running into the bug mentioned in the following
article :
290622 BUG: Database Maintenance Plan on System Databases Fails on Integrity
http://support.microsoft.com/?id=290622
The workaround is to disable the "Attempt to repair minor problems" option
in the Maintenance plan properties. This should stop generating the message
that you have posted.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Automatically backup....?

//[Microsoft][ODBC SQL Server Driver][SQL Server]Repair
statement not processed. Database needs to be in single
user mode.
** Execution Time: 0 hrs, 0 mins, 1 secs **
End of maintenance plan 'Cybermurid maintenance' on
7/20/2004 5:55:00 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)//
How do I set automatically backup in SQL server?Niky wrote:

> //[Microsoft][ODBC SQL Server Driver][SQL Server]Repair
> statement not processed. Database needs to be in single
> user mode.
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> End of maintenance plan 'Cybermurid maintenance' on
> 7/20/2004 5:55:00 AM
> SQLMAINT.EXE Process Exit Code: 1 (Failed)//
> How do I set automatically backup in SQL server?
Hi Niky,
you can create a new job (under SQL Agent, in Enterprise Manager):
- the activity you must add is a T-SQL script, with the instruction
"BACKUP DATABASE MyDb TO [DISK|TAPE]...".
- schedule it
David|||Hello
I believe that you are running into the bug mentioned in the following
article :
290622 BUG: Database Maintenance Plan on System Databases Fails on Integrity
http://support.microsoft.com/?id=290622
The workaround is to disable the "Attempt to repair minor problems" option
in the Maintenance plan properties. This should stop generating the message
that you have posted.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.sql

automatic sql server backup to networked machine

Hi,
I am currently backing up to my machine on my localbox of the database
I want with enterprise manager.
I would like to do an automatic backup of my database running on a
schedule on a networked shared/remote machine?
How do I do that?
Thanks,
:DHRUV
1. Make sure the SQL Server service is running on a domain account.
2. use UNC name for the designation file, eg:
\\MYSERVER\MYSHARE\MyFolder\MyFile.bak.
3. Make sure the account in #1 has read/write privileges on the share.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Dhruv" <dmalhotr2001@.yahoo.com> wrote in message
news:b6d0b0b.0407090855.43690994@.posting.google.co m...
Hi,
I am currently backing up to my machine on my localbox of the database
I want with enterprise manager.
I would like to do an automatic backup of my database running on a
schedule on a networked shared/remote machine?
How do I do that?
Thanks,
:DHRUV
|||Hi,
Enterprise manager will only show the local disks not the mapped drives.
There are Few Pre requisites to do backup remotely;
1. You Should start SQL server using Domain user who got access to remote
machine Share
2. Should have share in the remote machine
3. If you need to schedule this as a job then SQL Agent should use the same
Domain user in which SQL server was started
4. Restart the services
Now you can execute the Backup script with UNC path
BACKUP Database <dbname> to disk='\\computername\sharename\dbname.bak' with
init
Note:
Backup to remote machine will not work if you start SQL server using Local
system account
How to schedule
Enterprise Manager - Management -- SQL Agent -- Jobs -- Right click and
create new job. Give a name to the Job and in Job step mention this above
backup script and schedule the job to be executed based on requirement
Thanks
Hari
MCDBA
"Dhruv" <dmalhotr2001@.yahoo.com> wrote in message
news:b6d0b0b.0407090855.43690994@.posting.google.co m...
> Hi,
> I am currently backing up to my machine on my localbox of the database
> I want with enterprise manager.
> I would like to do an automatic backup of my database running on a
> schedule on a networked shared/remote machine?
> How do I do that?
> Thanks,
> :DHRUV
|||Hi,
Enterprise manager will only show the local disks not the mapped drives.
There are Few Pre requisites to do backup remotely;
1. You Should start SQL server using Domain user who got access to remote
machine Share
2. Should have share in the remote machine
3. If you need to schedule this as a job then SQL Agent should use the same
Domain user in which SQL server was started
4. Restart the services
Now you can execute the Backup script with UNC path
BACKUP Database <dbname> to disk='\\computername\sharename\dbname.bak' with
init
Note:
Backup to remote machine will not work if you start SQL server using Local
system account
How to schedule
Enterprise Manager - Management -- SQL Agent -- Jobs -- Right click and
create new job. Give a name to the Job and in Job step mention this above
backup script and schedule the job to be executed based on requirement
Thanks
Hari
MCDBA
"Dhruv" <dmalhotr2001@.yahoo.com> wrote in message
news:b6d0b0b.0407090855.43690994@.posting.google.co m...
> Hi,
> I am currently backing up to my machine on my localbox of the database
> I want with enterprise manager.
> I would like to do an automatic backup of my database running on a
> schedule on a networked shared/remote machine?
> How do I do that?
> Thanks,
> :DHRUV
|||Just so happens there is a community article on that very subject.
HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?id=555128
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Dhruv" <dmalhotr2001@.yahoo.com> wrote in message
news:b6d0b0b.0407090855.43690994@.posting.google.co m...
> Hi,
> I am currently backing up to my machine on my localbox of the database
> I want with enterprise manager.
> I would like to do an automatic backup of my database running on a
> schedule on a networked shared/remote machine?
> How do I do that?
> Thanks,
> :DHRUV

automatic sql server backup to networked machine

Hi,
I am currently backing up to my machine on my localbox of the database
I want with enterprise manager.
I would like to do an automatic backup of my database running on a
schedule on a networked shared/remote machine?
How do I do that?
Thanks,
:DHRUV1. Make sure the SQL Server service is running on a domain account.
2. use UNC name for the designation file, eg:
\\MYSERVER\MYSHARE\MyFolder\MyFile.bak.
3. Make sure the account in #1 has read/write privileges on the share.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Dhruv" <dmalhotr2001@.yahoo.com> wrote in message
news:b6d0b0b.0407090855.43690994@.posting.google.com...
Hi,
I am currently backing up to my machine on my localbox of the database
I want with enterprise manager.
I would like to do an automatic backup of my database running on a
schedule on a networked shared/remote machine?
How do I do that?
Thanks,
:DHRUV|||Hi,
Enterprise manager will only show the local disks not the mapped drives.
There are Few Pre requisites to do backup remotely;
1. You Should start SQL server using Domain user who got access to remote
machine Share
2. Should have share in the remote machine
3. If you need to schedule this as a job then SQL Agent should use the same
Domain user in which SQL server was started
4. Restart the services
Now you can execute the Backup script with UNC path
BACKUP Database <dbname> to disk='\\computername\sharename\dbname.bak' with
init
Note:
Backup to remote machine will not work if you start SQL server using Local
system account
How to schedule
--
Enterprise Manager - Management -- SQL Agent -- Jobs -- Right click and
create new job. Give a name to the Job and in Job step mention this above
backup script and schedule the job to be executed based on requirement
Thanks
Hari
MCDBA
"Dhruv" <dmalhotr2001@.yahoo.com> wrote in message
news:b6d0b0b.0407090855.43690994@.posting.google.com...
> Hi,
> I am currently backing up to my machine on my localbox of the database
> I want with enterprise manager.
> I would like to do an automatic backup of my database running on a
> schedule on a networked shared/remote machine?
> How do I do that?
> Thanks,
> :DHRUV|||Hi,
Enterprise manager will only show the local disks not the mapped drives.
There are Few Pre requisites to do backup remotely;
1. You Should start SQL server using Domain user who got access to remote
machine Share
2. Should have share in the remote machine
3. If you need to schedule this as a job then SQL Agent should use the same
Domain user in which SQL server was started
4. Restart the services
Now you can execute the Backup script with UNC path
BACKUP Database <dbname> to disk='\\computername\sharename\dbname.bak' with
init
Note:
Backup to remote machine will not work if you start SQL server using Local
system account
How to schedule
--
Enterprise Manager - Management -- SQL Agent -- Jobs -- Right click and
create new job. Give a name to the Job and in Job step mention this above
backup script and schedule the job to be executed based on requirement
Thanks
Hari
MCDBA
"Dhruv" <dmalhotr2001@.yahoo.com> wrote in message
news:b6d0b0b.0407090855.43690994@.posting.google.com...
> Hi,
> I am currently backing up to my machine on my localbox of the database
> I want with enterprise manager.
> I would like to do an automatic backup of my database running on a
> schedule on a networked shared/remote machine?
> How do I do that?
> Thanks,
> :DHRUV|||Just so happens there is a community article on that very subject.
HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?id=555128
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Dhruv" <dmalhotr2001@.yahoo.com> wrote in message
news:b6d0b0b.0407090855.43690994@.posting.google.com...
> Hi,
> I am currently backing up to my machine on my localbox of the database
> I want with enterprise manager.
> I would like to do an automatic backup of my database running on a
> schedule on a networked shared/remote machine?
> How do I do that?
> Thanks,
> :DHRUV

automatic sql server backup to networked machine

Hi,
I am currently backing up to my machine on my localbox of the database
I want with enterprise manager.
I would like to do an automatic backup of my database running on a
schedule on a networked shared/remote machine?
How do I do that?
Thanks,
:DHRUV
1. Make sure the SQL Server service is running on a domain account.
2. use UNC name for the designation file, eg:
\\MYSERVER\MYSHARE\MyFolder\MyFile.bak.
3. Make sure the account in #1 has read/write privileges on the share.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Dhruv" <dmalhotr2001@.yahoo.com> wrote in message
news:b6d0b0b.0407090855.43690994@.posting.google.co m...
Hi,
I am currently backing up to my machine on my localbox of the database
I want with enterprise manager.
I would like to do an automatic backup of my database running on a
schedule on a networked shared/remote machine?
How do I do that?
Thanks,
:DHRUV
|||Hi,
Enterprise manager will only show the local disks not the mapped drives.
There are Few Pre requisites to do backup remotely;
1. You Should start SQL server using Domain user who got access to remote
machine Share
2. Should have share in the remote machine
3. If you need to schedule this as a job then SQL Agent should use the same
Domain user in which SQL server was started
4. Restart the services
Now you can execute the Backup script with UNC path
BACKUP Database <dbname> to disk='\\computername\sharename\dbname.bak' with
init
Note:
Backup to remote machine will not work if you start SQL server using Local
system account
How to schedule
Enterprise Manager - Management -- SQL Agent -- Jobs -- Right click and
create new job. Give a name to the Job and in Job step mention this above
backup script and schedule the job to be executed based on requirement
Thanks
Hari
MCDBA
"Dhruv" <dmalhotr2001@.yahoo.com> wrote in message
news:b6d0b0b.0407090855.43690994@.posting.google.co m...
> Hi,
> I am currently backing up to my machine on my localbox of the database
> I want with enterprise manager.
> I would like to do an automatic backup of my database running on a
> schedule on a networked shared/remote machine?
> How do I do that?
> Thanks,
> :DHRUV
|||Hi,
Enterprise manager will only show the local disks not the mapped drives.
There are Few Pre requisites to do backup remotely;
1. You Should start SQL server using Domain user who got access to remote
machine Share
2. Should have share in the remote machine
3. If you need to schedule this as a job then SQL Agent should use the same
Domain user in which SQL server was started
4. Restart the services
Now you can execute the Backup script with UNC path
BACKUP Database <dbname> to disk='\\computername\sharename\dbname.bak' with
init
Note:
Backup to remote machine will not work if you start SQL server using Local
system account
How to schedule
Enterprise Manager - Management -- SQL Agent -- Jobs -- Right click and
create new job. Give a name to the Job and in Job step mention this above
backup script and schedule the job to be executed based on requirement
Thanks
Hari
MCDBA
"Dhruv" <dmalhotr2001@.yahoo.com> wrote in message
news:b6d0b0b.0407090855.43690994@.posting.google.co m...
> Hi,
> I am currently backing up to my machine on my localbox of the database
> I want with enterprise manager.
> I would like to do an automatic backup of my database running on a
> schedule on a networked shared/remote machine?
> How do I do that?
> Thanks,
> :DHRUV
|||Just so happens there is a community article on that very subject.
HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?id=555128
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Dhruv" <dmalhotr2001@.yahoo.com> wrote in message
news:b6d0b0b.0407090855.43690994@.posting.google.co m...
> Hi,
> I am currently backing up to my machine on my localbox of the database
> I want with enterprise manager.
> I would like to do an automatic backup of my database running on a
> schedule on a networked shared/remote machine?
> How do I do that?
> Thanks,
> :DHRUV
sql

automatic sql server backup to networked machine

Hi,
I am currently backing up to my machine on my localbox of the database
I want with enterprise manager.
I would like to do an automatic backup of my database running on a
schedule on a networked shared/remote machine?
How do I do that?
Thanks,
:DHRUV1. Make sure the SQL Server service is running on a domain account.
2. use UNC name for the designation file, eg:
\\MYSERVER\MYSHARE\MyFolder\MyFile.bak.
3. Make sure the account in #1 has read/write privileges on the share.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Dhruv" <dmalhotr2001@.yahoo.com> wrote in message
news:b6d0b0b.0407090855.43690994@.posting.google.com...
Hi,
I am currently backing up to my machine on my localbox of the database
I want with enterprise manager.
I would like to do an automatic backup of my database running on a
schedule on a networked shared/remote machine?
How do I do that?
Thanks,
:DHRUV|||Hi,
Enterprise manager will only show the local disks not the mapped drives.
There are Few Pre requisites to do backup remotely;
1. You Should start SQL server using Domain user who got access to remote
machine Share
2. Should have share in the remote machine
3. If you need to schedule this as a job then SQL Agent should use the same
Domain user in which SQL server was started
4. Restart the services
Now you can execute the Backup script with UNC path
BACKUP Database <dbname> to disk='\\computername\sharename\dbname.bak' with
init
Note:
Backup to remote machine will not work if you start SQL server using Local
system account
How to schedule
--
Enterprise Manager - Management -- SQL Agent -- Jobs -- Right click and
create new job. Give a name to the Job and in Job step mention this above
backup script and schedule the job to be executed based on requirement
Thanks
Hari
MCDBA
"Dhruv" <dmalhotr2001@.yahoo.com> wrote in message
news:b6d0b0b.0407090855.43690994@.posting.google.com...
> Hi,
> I am currently backing up to my machine on my localbox of the database
> I want with enterprise manager.
> I would like to do an automatic backup of my database running on a
> schedule on a networked shared/remote machine?
> How do I do that?
> Thanks,
> :DHRUV|||Hi,
Enterprise manager will only show the local disks not the mapped drives.
There are Few Pre requisites to do backup remotely;
1. You Should start SQL server using Domain user who got access to remote
machine Share
2. Should have share in the remote machine
3. If you need to schedule this as a job then SQL Agent should use the same
Domain user in which SQL server was started
4. Restart the services
Now you can execute the Backup script with UNC path
BACKUP Database <dbname> to disk='\\computername\sharename\dbname.bak' with
init
Note:
Backup to remote machine will not work if you start SQL server using Local
system account
How to schedule
--
Enterprise Manager - Management -- SQL Agent -- Jobs -- Right click and
create new job. Give a name to the Job and in Job step mention this above
backup script and schedule the job to be executed based on requirement
Thanks
Hari
MCDBA
"Dhruv" <dmalhotr2001@.yahoo.com> wrote in message
news:b6d0b0b.0407090855.43690994@.posting.google.com...
> Hi,
> I am currently backing up to my machine on my localbox of the database
> I want with enterprise manager.
> I would like to do an automatic backup of my database running on a
> schedule on a networked shared/remote machine?
> How do I do that?
> Thanks,
> :DHRUV|||Just so happens there is a community article on that very subject.
HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?id=555128
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Dhruv" <dmalhotr2001@.yahoo.com> wrote in message
news:b6d0b0b.0407090855.43690994@.posting.google.com...
> Hi,
> I am currently backing up to my machine on my localbox of the database
> I want with enterprise manager.
> I would like to do an automatic backup of my database running on a
> schedule on a networked shared/remote machine?
> How do I do that?
> Thanks,
> :DHRUV