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

>

Automated Backup and restoring to a new machine

Hi all,
I am not sure if this is the correct place to ask this quesion. The problem
i am trying to solve is basically this. We do a nightly complete backup of
our database. The next day we copy the database to a different machine
restore it and run jobs on this database. I would like to automate this
process, but don't know the best solution.
We have 3 machines here. Server A is the main producation db machine, server
B is our backup machine and server C is where we restore the backed up DB and
run the jobs. Can someone please suggest what in theri opinion would be a
good solution for this. Thanks
Poorav
You might want to investigate log shipping for this functionality which
requires Enterprise Edition. However, you can use scripts off the Resource
Pack or off some internet sites to achieve the same effect if you only have
Standard Edition. The principle is not difficult - you have a backup job on
the principal server and you need a share to copy files to and a job on the
standby server to restore the database and subsequent log files('WITH NO
RECOVERY').
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)