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
>
Showing posts with label sp3a. Show all posts
Showing posts with label sp3a. Show all posts
Thursday, March 29, 2012
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
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
>
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 restore
Hello,
I'm trying to automate a prod to test restore to run weekly on a SQL 2000
sp3a server. Its on the same machine so I can't use the copy db wizard.
Should i use DTS, replication or what. I don't want to deal with orphaned
users and the like. It should be as seamless as possible.
TIA,
jjjj
Have you looked at BACKUP\RESTORE commands?
"jj" <jeff_detoro@.urmc.rochester.edu> wrote in message
news:OmwojWWNFHA.1040@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I'm trying to automate a prod to test restore to run weekly on a SQL 2000
> sp3a server. Its on the same machine so I can't use the copy db wizard.
> Should i use DTS, replication or what. I don't want to deal with orphaned
> users and the like. It should be as seamless as possible.
> TIA,
> jj
>
I'm trying to automate a prod to test restore to run weekly on a SQL 2000
sp3a server. Its on the same machine so I can't use the copy db wizard.
Should i use DTS, replication or what. I don't want to deal with orphaned
users and the like. It should be as seamless as possible.
TIA,
jjjj
Have you looked at BACKUP\RESTORE commands?
"jj" <jeff_detoro@.urmc.rochester.edu> wrote in message
news:OmwojWWNFHA.1040@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I'm trying to automate a prod to test restore to run weekly on a SQL 2000
> sp3a server. Its on the same machine so I can't use the copy db wizard.
> Should i use DTS, replication or what. I don't want to deal with orphaned
> users and the like. It should be as seamless as possible.
> TIA,
> jj
>
Thursday, February 16, 2012
Auto Stats
Books Online does not explain how this runs for SQL Server 2000 (we are on
SP3a).
Does it take any database or other locks at all ? So that it could
contribute to a timeout/disconnect problem we are getting ?This is a multi-part message in MIME format.
--010006090901020804020402
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
I don't believe so. I think it's unlikely that that is contributing to
any timeout problem, but you can always disable that option (ALTER
DATABASE <dbname> SET AUTO_UPDATE_STATISTICS OFF) and see if the timeout
problem goes away (just do the statistics updates manually (EXEC
sp_updatestats) out of hours).
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Jim Trowbridge wrote:
>Books Online does not explain how this runs for SQL Server 2000 (we are on
>SP3a).
>Does it take any database or other locks at all ? So that it could
>contribute to a timeout/disconnect problem we are getting ?
>
>
--010006090901020804020402
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>I don't believe so. I think it's unlikely that that is
contributing to any timeout problem, but you can always disable that
option (ALTER DATABASE <dbname> SET AUTO_UPDATE_STATISTICS OFF</tt><tt>)
and see if the timeout problem goes away (just do the statistics
updates manually (EXEC sp_updatestats) out of hours).</tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2">blog:</font><font face="Tahoma" size="2"> <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Jim Trowbridge wrote:
<blockquote cite="mid92C390C7-7B08-412E-9053-16C8E295AFBD@.microsoft.com"
type="cite">
<pre wrap="">Books Online does not explain how this runs for SQL Server 2000 (we are on
SP3a).
Does it take any database or other locks at all ? So that it could
contribute to a timeout/disconnect problem we are getting ?
</pre>
</blockquote>
</body>
</html>
--010006090901020804020402--
SP3a).
Does it take any database or other locks at all ? So that it could
contribute to a timeout/disconnect problem we are getting ?This is a multi-part message in MIME format.
--010006090901020804020402
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
I don't believe so. I think it's unlikely that that is contributing to
any timeout problem, but you can always disable that option (ALTER
DATABASE <dbname> SET AUTO_UPDATE_STATISTICS OFF) and see if the timeout
problem goes away (just do the statistics updates manually (EXEC
sp_updatestats) out of hours).
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Jim Trowbridge wrote:
>Books Online does not explain how this runs for SQL Server 2000 (we are on
>SP3a).
>Does it take any database or other locks at all ? So that it could
>contribute to a timeout/disconnect problem we are getting ?
>
>
--010006090901020804020402
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>I don't believe so. I think it's unlikely that that is
contributing to any timeout problem, but you can always disable that
option (ALTER DATABASE <dbname> SET AUTO_UPDATE_STATISTICS OFF</tt><tt>)
and see if the timeout problem goes away (just do the statistics
updates manually (EXEC sp_updatestats) out of hours).</tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2">blog:</font><font face="Tahoma" size="2"> <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Jim Trowbridge wrote:
<blockquote cite="mid92C390C7-7B08-412E-9053-16C8E295AFBD@.microsoft.com"
type="cite">
<pre wrap="">Books Online does not explain how this runs for SQL Server 2000 (we are on
SP3a).
Does it take any database or other locks at all ? So that it could
contribute to a timeout/disconnect problem we are getting ?
</pre>
</blockquote>
</body>
</html>
--010006090901020804020402--
Subscribe to:
Posts (Atom)