restore the replication system

Database A and B are replicated to each other. Both db reside on the same box.
The box has been rebuild due to a system crash. All data base files have
been preserved.
NT SA restored all user databases by attaching the datafiles except
master and msdb. Now, the replication system is out of sync.
Can I restore the master and msdb using attach db to restore the
entire replication system?
Any other better or correct way to restore the entire system when there is a
system crash?
Appreciate any help.
Wen
Wen,
provided you also restore the distribution database this may be possible. It
partly depends on 2 factors:
firstly, is the server name the same as the original one? If not then the
recovery strategy won't work and you'll need to start again - recreate the
publications and initialize.
If the servername is identical, then it may be possible - have a look in BOL
for "replication, backup and restore operations".
The second question is what type of replication are you using? In most cases
it is ok but I'd mention that if the distribution database's backup has been
allowed to get ahead of the publisher's backup and you are using
transactional replication there are likely to be problems.
Anyway, the first port of call is the BOL reference above, and please post
back if you have any issues which arise from that.
Regards,
Paul Ibison
|||Paul.
1. Yes, the servername is the same as the original one. and the
Distribution db
has been restored.
2. We have transactional and merge replication.
I will attache back the master and msdb and see if it works.
Thanks for the help.
Wen
"Paul Ibison" wrote:

> Wen,
> provided you also restore the distribution database this may be possible. It
> partly depends on 2 factors:
> firstly, is the server name the same as the original one? If not then the
> recovery strategy won't work and you'll need to start again - recreate the
> publications and initialize.
> If the servername is identical, then it may be possible - have a look in BOL
> for "replication, backup and restore operations".
> The second question is what type of replication are you using? In most cases
> it is ok but I'd mention that if the distribution database's backup has been
> allowed to get ahead of the publisher's backup and you are using
> transactional replication there are likely to be problems.
> Anyway, the first port of call is the BOL reference above, and please post
> back if you have any issues which arise from that.
> Regards,
> Paul Ibison
>
>
|||Wen,
there should be no problem with the merge database restoration, and you
might want to synchronize with the most upto date subscriber after the
restore.
As for the transactional, there could be issues. Did the publisher have the
sync with backup option set to true? If not, the distribution backup could
be ahead of the publisher restore. After the restore you will get an error
from the Log Reader Agent because it will detect that the Distributor is
ahead of the Publisher. The recommendation is to run sp_replrestart in the
publication database with no parameters. and ensure that that the
distribution agent, which could now deliver duplicate rows to Subscribers,
can continue despite these failures. Choose the -SkipError Distribution
Agent profile, or you can manually add the -SkipError parameter to the
runtime parameters of the Distribution Agents and supply the errors you want
the Distribution Agents to ignore.
You'll need to use linked servers to fix the inconsistencies, or you could
of course resort to reinitializing.
HTH,
Paul Ibison
|||Paul, the sync with backup option is fairly useless unless you are shipping
the transaction logs to a standby server.
The sequence to get this to work is
1) use the sync with backup option on your publication database and possibly
your distribution database
2) restore the msdb, publication database, and distribution database backups
on the standby server with the keep_replication switch.
3) then ship the publication and distribution databases tlogs and without
the keep_replication switch.
4) when the publisher goes offline you rename the standby server with the
name of the original publisher, and then do a sp_replrestart,
and -skiperrors switch.
In the case of this user you can't really use any of these options as they
have not been shipping the database. With a new master database, the user
probably does not have any of the subscribers listed as remote servers and
restoring or attaching any databases will be highly problematic.
The user is best to drop all replication and create publications and
subscriptions from scratch as their data is likely out of sync.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uY4Vt9RfEHA.3916@.TK2MSFTNGP11.phx.gbl...
> Wen,
> there should be no problem with the merge database restoration, and you
> might want to synchronize with the most upto date subscriber after the
> restore.
> As for the transactional, there could be issues. Did the publisher have
the
> sync with backup option set to true? If not, the distribution backup could
> be ahead of the publisher restore. After the restore you will get an error
> from the Log Reader Agent because it will detect that the Distributor is
> ahead of the Publisher. The recommendation is to run sp_replrestart in the
> publication database with no parameters. and ensure that that the
> distribution agent, which could now deliver duplicate rows to Subscribers,
> can continue despite these failures. Choose the -SkipError Distribution
> Agent profile, or you can manually add the -SkipError parameter to the
> runtime parameters of the Distribution Agents and supply the errors you
want
> the Distribution Agents to ignore.
> You'll need to use linked servers to fix the inconsistencies, or you could
> of course resort to reinitializing.
> HTH,
> Paul Ibison
>
|||Hilary,
from a practical point of view I accept your point - log-shipping, the 'sync
with backup' option and keep_replication go well together to minimize the
latency involved if you ship every few minutes.
However in BOL
(mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL% 20Server\80\Tools\Books\re
plsql.chm::/replbackup_3js7.htm) the recommendation is different to your
implementation:
- they advise 'keep_replication' for the last log.
- and sp_replrestart for semi-synchronous mode only - ie when the 'sync with
backup' option is false.
Are these just alternative methods?
Regards,
Paul Ibison
|||BOL lies.
Try to restore a tlog with the keep_replication switch.
I have reported this doc bug to Microsoft.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23PFoxqifEHA.3520@.TK2MSFTNGP10.phx.gbl...
> Hilary,
> from a practical point of view I accept your point - log-shipping, the
'sync
> with backup' option and keep_replication go well together to minimize the
> latency involved if you ship every few minutes.
> However in BOL
>
(mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL% 20Server\80\Tools\Books\re
> plsql.chm::/replbackup_3js7.htm) the recommendation is different to your
> implementation:
> - they advise 'keep_replication' for the last log.
> - and sp_replrestart for semi-synchronous mode only - ie when the 'sync
with
> backup' option is false.
> Are these just alternative methods?
> Regards,
> Paul Ibison
>
|||Hilary,
thanks for pointing this out, and for posting up a bonafide working
sequence.
As far as I remember, when I tested this I followed a different methodology
where I used "WITH KEEP_REPLICATION, RECOVERY" on the last transaction log
and this seemed to work. Unfortunately I didn't make a proper note of it and
I really need 3 boxes to repeat the test. Later this week it might be
possible to set it up and I'll post back then.
Regards,
Paul Ibison

Restore SQL database from a network drive

Hi there,
I have a copy of a database on a network drive.
How could restore this database into my local test box without copy the
database down first?
I tried to use UNC and map a drive using net use but none of these work.
I always get the 'Cannot open backup device 'Z:\mapdrive\databasename.bak'.
Device error or device off-line. See the SQL Server error log for more
details. RESTORE DATABASE is terminating abnormally.'
I go to the SQL server Error Log and it said 'The system cannot find the
path specified (if I use the map drive syntax) or Access is denied (if I use
the UNC syntax like \\server\drive\dir\my backup.bak)
Any idea of how to do this?
Thanks.
Abel Chan
You must:
1) Use the UNC name
2) Have SQL Server running under a domain account - not Local System
3) Have read permission on the share for the domain account in #1 above
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"Abel Chan" <awong@.newsgroup.nospam> wrote in message
news:136DAF3F-B192-41C4-A819-D6BEC9CE1449@.microsoft.com...
Hi there,
I have a copy of a database on a network drive.
How could restore this database into my local test box without copy the
database down first?
I tried to use UNC and map a drive using net use but none of these work.
I always get the 'Cannot open backup device 'Z:\mapdrive\databasename.bak'.
Device error or device off-line. See the SQL Server error log for more
details. RESTORE DATABASE is terminating abnormally.'
I go to the SQL server Error Log and it said 'The system cannot find the
path specified (if I use the map drive syntax) or Access is denied (if I use
the UNC syntax like \\server\drive\dir\my backup.bak)
Any idea of how to do this?
Thanks.
Abel Chan
|||Tom,
Your solution works!!!
Thanks so mcuh Tom.
Abel
"Tom Moreau" wrote:

> You must:
> 1) Use the UNC name
> 2) Have SQL Server running under a domain account - not Local System
> 3) Have read permission on the share for the domain account in #1 above
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> ..
> "Abel Chan" <awong@.newsgroup.nospam> wrote in message
> news:136DAF3F-B192-41C4-A819-D6BEC9CE1449@.microsoft.com...
> Hi there,
> I have a copy of a database on a network drive.
> How could restore this database into my local test box without copy the
> database down first?
> I tried to use UNC and map a drive using net use but none of these work.
> I always get the 'Cannot open backup device 'Z:\mapdrive\databasename.bak'.
> Device error or device off-line. See the SQL Server error log for more
> details. RESTORE DATABASE is terminating abnormally.'
> I go to the SQL server Error Log and it said 'The system cannot find the
> path specified (if I use the map drive syntax) or Access is denied (if I use
> the UNC syntax like \\server\drive\dir\my backup.bak)
> Any idea of how to do this?
> Thanks.
> Abel Chan
>

Restore SQL database from a network drive

Hi there,
I have a copy of a database on a network drive.
How could restore this database into my local test box without copy the
database down first?
I tried to use UNC and map a drive using net use but none of these work.
I always get the 'Cannot open backup device 'Z:\mapdrive\databasename.bak'.
Device error or device off-line. See the SQL Server error log for more
details. RESTORE DATABASE is terminating abnormally.'
I go to the SQL server Error Log and it said 'The system cannot find the
path specified (if I use the map drive syntax) or Access is denied (if I use
the UNC syntax like \\server\drive\dir\my backup.bak)
Any idea of how to do this?
Thanks.
Abel ChanYou must:
1) Use the UNC name
2) Have SQL Server running under a domain account - not Local System
3) Have read permission on the share for the domain account in #1 above
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Abel Chan" <awong@.newsgroup.nospam> wrote in message
news:136DAF3F-B192-41C4-A819-D6BEC9CE1449@.microsoft.com...
Hi there,
I have a copy of a database on a network drive.
How could restore this database into my local test box without copy the
database down first?
I tried to use UNC and map a drive using net use but none of these work.
I always get the 'Cannot open backup device 'Z:\mapdrive\databasename.bak'.
Device error or device off-line. See the SQL Server error log for more
details. RESTORE DATABASE is terminating abnormally.'
I go to the SQL server Error Log and it said 'The system cannot find the
path specified (if I use the map drive syntax) or Access is denied (if I use
the UNC syntax like \\server\drive\dir\my backup.bak)
Any idea of how to do this?
Thanks.
Abel Chan|||Tom,
Your solution works!!!
Thanks so mcuh Tom.
Abel
"Tom Moreau" wrote:

> You must:
> 1) Use the UNC name
> 2) Have SQL Server running under a domain account - not Local System
> 3) Have read permission on the share for the domain account in #1 above
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> ..
> "Abel Chan" <awong@.newsgroup.nospam> wrote in message
> news:136DAF3F-B192-41C4-A819-D6BEC9CE1449@.microsoft.com...
> Hi there,
> I have a copy of a database on a network drive.
> How could restore this database into my local test box without copy the
> database down first?
> I tried to use UNC and map a drive using net use but none of these work.
> I always get the 'Cannot open backup device 'Z:\mapdrive\databasename.bak'
.
> Device error or device off-line. See the SQL Server error log for more
> details. RESTORE DATABASE is terminating abnormally.'
> I go to the SQL server Error Log and it said 'The system cannot find the
> path specified (if I use the map drive syntax) or Access is denied (if I u
se
> the UNC syntax like \\server\drive\dir\my backup.bak)
> Any idea of how to do this?
> Thanks.
> Abel Chan
>

Restore SQL database from a network drive

Hi there,
I have a copy of a database on a network drive.
How could restore this database into my local test box without copy the
database down first?
I tried to use UNC and map a drive using net use but none of these work.
I always get the 'Cannot open backup device 'Z:\mapdrive\databasename.bak'.
Device error or device off-line. See the SQL Server error log for more
details. RESTORE DATABASE is terminating abnormally.'
I go to the SQL server Error Log and it said 'The system cannot find the
path specified (if I use the map drive syntax) or Access is denied (if I use
the UNC syntax like \\server\drive\dir\my backup.bak)
Any idea of how to do this?
Thanks.
Abel ChanYou must:
1) Use the UNC name
2) Have SQL Server running under a domain account - not Local System
3) Have read permission on the share for the domain account in #1 above
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Abel Chan" <awong@.newsgroup.nospam> wrote in message
news:136DAF3F-B192-41C4-A819-D6BEC9CE1449@.microsoft.com...
Hi there,
I have a copy of a database on a network drive.
How could restore this database into my local test box without copy the
database down first?
I tried to use UNC and map a drive using net use but none of these work.
I always get the 'Cannot open backup device 'Z:\mapdrive\databasename.bak'.
Device error or device off-line. See the SQL Server error log for more
details. RESTORE DATABASE is terminating abnormally.'
I go to the SQL server Error Log and it said 'The system cannot find the
path specified (if I use the map drive syntax) or Access is denied (if I use
the UNC syntax like \\server\drive\dir\my backup.bak)
Any idea of how to do this?
Thanks.
Abel Chan|||Tom,
Your solution works!!!
Thanks so mcuh Tom.
Abel
"Tom Moreau" wrote:
> You must:
> 1) Use the UNC name
> 2) Have SQL Server running under a domain account - not Local System
> 3) Have read permission on the share for the domain account in #1 above
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> ..
> "Abel Chan" <awong@.newsgroup.nospam> wrote in message
> news:136DAF3F-B192-41C4-A819-D6BEC9CE1449@.microsoft.com...
> Hi there,
> I have a copy of a database on a network drive.
> How could restore this database into my local test box without copy the
> database down first?
> I tried to use UNC and map a drive using net use but none of these work.
> I always get the 'Cannot open backup device 'Z:\mapdrive\databasename.bak'.
> Device error or device off-line. See the SQL Server error log for more
> details. RESTORE DATABASE is terminating abnormally.'
> I go to the SQL server Error Log and it said 'The system cannot find the
> path specified (if I use the map drive syntax) or Access is denied (if I use
> the UNC syntax like \\server\drive\dir\my backup.bak)
> Any idea of how to do this?
> Thanks.
> Abel Chan
>sql

Restore SQL 2000 database onto a SQL 7 box

Is it possible to restore a SQL 2000 database backup onto a SQL 7 box?

I tried and it quite rightly complained about database structure versions are different.

Is there a way to backup SQL 2000 in such a way that only SQL 7 features are preserved? and thus can be restored on to a SQL 7 box.

The database it self is really quite simple. Just tables (plus data) and SPs.No you can't restore a 2K database onto a 7 box. You can use DTS to move data OR BCP the data out of the 2K box and into the 7 box.

restore MSSQL 7 backup to MSSQL 2000?

There is one lonely database sitting on a SQL Server 7 instance running on a
Windows 2000 server. I would like to retire that box and put the database on
the new Windows 2003 server running SQL Server 2000. Can I just copy a backup
of the SQL 7 database and restore it on the SQL 2000 instance? Thanks.
You can use BACKUP and RESTORE or sp_detach_db and sp_attach_db to move the
database. There is lots of "how to" information regarding these commands
available within Books Online.
Keith
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:101335D5-98F5-498F-8D03-7297BABCDBBE@.microsoft.com...
> There is one lonely database sitting on a SQL Server 7 instance running on
a
> Windows 2000 server. I would like to retire that box and put the database
on
> the new Windows 2003 server running SQL Server 2000. Can I just copy a
backup
> of the SQL 7 database and restore it on the SQL 2000 instance? Thanks.
|||Thank you - I have copied databases between servers before, but not between
different versions of SQL Server. I was just checking that I wouldn't have
problems migrating a 7 database to a 2000 version.
Thanks again!
"Keith Kratochvil" wrote:

> You can use BACKUP and RESTORE or sp_detach_db and sp_attach_db to move the
> database. There is lots of "how to" information regarding these commands
> available within Books Online.
> --
> Keith
>
> "J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
> news:101335D5-98F5-498F-8D03-7297BABCDBBE@.microsoft.com...
> a
> on
> backup
>
|||Migrating from 7.0 to 2000 is fully supported.
You cannot restore or attach a SQL Server 2000 databaes to SQL Server 7. If
you need to move your data from 2000 to 7.0 (or any previous version) you
will need to find another method such as DTS or BCP.
Keith
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:FFE80829-23E3-456F-AB95-A3B3761C6F96@.microsoft.com...
> Thank you - I have copied databases between servers before, but not
between[vbcol=seagreen]
> different versions of SQL Server. I was just checking that I wouldn't have
> problems migrating a 7 database to a 2000 version.
> Thanks again!
> "Keith Kratochvil" wrote:
the[vbcol=seagreen]
commands[vbcol=seagreen]
running on[vbcol=seagreen]
database[vbcol=seagreen]

restore MSSQL 7 backup to MSSQL 2000?

There is one lonely database sitting on a SQL Server 7 instance running on a
Windows 2000 server. I would like to retire that box and put the database on
the new Windows 2003 server running SQL Server 2000. Can I just copy a backu
p
of the SQL 7 database and restore it on the SQL 2000 instance? Thanks.You can use BACKUP and RESTORE or sp_detach_db and sp_attach_db to move the
database. There is lots of "how to" information regarding these commands
available within Books Online.
Keith
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:101335D5-98F5-498F-8D03-7297BABCDBBE@.microsoft.com...
> There is one lonely database sitting on a SQL Server 7 instance running on
a
> Windows 2000 server. I would like to retire that box and put the database
on
> the new Windows 2003 server running SQL Server 2000. Can I just copy a
backup
> of the SQL 7 database and restore it on the SQL 2000 instance? Thanks.|||Thank you - I have copied databases between servers before, but not between
different versions of SQL Server. I was just checking that I wouldn't have
problems migrating a 7 database to a 2000 version.
Thanks again!
"Keith Kratochvil" wrote:

> You can use BACKUP and RESTORE or sp_detach_db and sp_attach_db to move th
e
> database. There is lots of "how to" information regarding these commands
> available within Books Online.
> --
> Keith
>
> "J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
> news:101335D5-98F5-498F-8D03-7297BABCDBBE@.microsoft.com...
> a
> on
> backup
>|||Migrating from 7.0 to 2000 is fully supported.
You cannot restore or attach a SQL Server 2000 databaes to SQL Server 7. If
you need to move your data from 2000 to 7.0 (or any previous version) you
will need to find another method such as DTS or BCP.
Keith
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:FFE80829-23E3-456F-AB95-A3B3761C6F96@.microsoft.com...
> Thank you - I have copied databases between servers before, but not
between[vbcol=seagreen]
> different versions of SQL Server. I was just checking that I wouldn't have
> problems migrating a 7 database to a 2000 version.
> Thanks again!
> "Keith Kratochvil" wrote:
>
the[vbcol=seagreen]
commands[vbcol=seagreen]
running on[vbcol=seagreen]
database[vbcol=seagreen]

restore MSSQL 7 backup to MSSQL 2000?

There is one lonely database sitting on a SQL Server 7 instance running on a
Windows 2000 server. I would like to retire that box and put the database on
the new Windows 2003 server running SQL Server 2000. Can I just copy a backup
of the SQL 7 database and restore it on the SQL 2000 instance? Thanks.You can use BACKUP and RESTORE or sp_detach_db and sp_attach_db to move the
database. There is lots of "how to" information regarding these commands
available within Books Online.
--
Keith
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:101335D5-98F5-498F-8D03-7297BABCDBBE@.microsoft.com...
> There is one lonely database sitting on a SQL Server 7 instance running on
a
> Windows 2000 server. I would like to retire that box and put the database
on
> the new Windows 2003 server running SQL Server 2000. Can I just copy a
backup
> of the SQL 7 database and restore it on the SQL 2000 instance? Thanks.|||Thank you - I have copied databases between servers before, but not between
different versions of SQL Server. I was just checking that I wouldn't have
problems migrating a 7 database to a 2000 version.
Thanks again!
"Keith Kratochvil" wrote:
> You can use BACKUP and RESTORE or sp_detach_db and sp_attach_db to move the
> database. There is lots of "how to" information regarding these commands
> available within Books Online.
> --
> Keith
>
> "J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
> news:101335D5-98F5-498F-8D03-7297BABCDBBE@.microsoft.com...
> > There is one lonely database sitting on a SQL Server 7 instance running on
> a
> > Windows 2000 server. I would like to retire that box and put the database
> on
> > the new Windows 2003 server running SQL Server 2000. Can I just copy a
> backup
> > of the SQL 7 database and restore it on the SQL 2000 instance? Thanks.
>|||Migrating from 7.0 to 2000 is fully supported.
You cannot restore or attach a SQL Server 2000 databaes to SQL Server 7. If
you need to move your data from 2000 to 7.0 (or any previous version) you
will need to find another method such as DTS or BCP.
--
Keith
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:FFE80829-23E3-456F-AB95-A3B3761C6F96@.microsoft.com...
> Thank you - I have copied databases between servers before, but not
between
> different versions of SQL Server. I was just checking that I wouldn't have
> problems migrating a 7 database to a 2000 version.
> Thanks again!
> "Keith Kratochvil" wrote:
> > You can use BACKUP and RESTORE or sp_detach_db and sp_attach_db to move
the
> > database. There is lots of "how to" information regarding these
commands
> > available within Books Online.
> >
> > --
> > Keith
> >
> >
> > "J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
> > news:101335D5-98F5-498F-8D03-7297BABCDBBE@.microsoft.com...
> > > There is one lonely database sitting on a SQL Server 7 instance
running on
> > a
> > > Windows 2000 server. I would like to retire that box and put the
database
> > on
> > > the new Windows 2003 server running SQL Server 2000. Can I just copy a
> > backup
> > > of the SQL 7 database and restore it on the SQL 2000 instance? Thanks.
> >
> >

restore MSDB or transfer jobs?

I restored MSDB onto a new box and my jobs won't run now:
Unable to connect to SQL Server '(local)'. The step failed.
Am I better off to use the Transfer Jobs Task in DTS or is there a simple
answer to this? If so, what about moving the DTS Packages?
SQL2K SP3
TIA, ChrisR
I would scrip them out an apply on new server. But you might be able
to fix them on the new server
declare @.NewServerName varchar(50)
select @.NewServerName = cast(serverproperty ('ServerName') as
nvarchar(50))
update msdb..sysjobs
set originating_server = @.NewServerName
GO
checkpoint
GO
|||The query converts the @.NewServerName to nothing:
Server: Msg 515, Level 16, State 2, Line 3
Cannot insert the value NULL into column 'originating_server', table
'msdb.dbo.sysjobs'; column does not allow nulls. UPDATE fails.
The statement has been terminated.
declare @.NewServerName varchar(50)
select @.NewServerName = cast(serverproperty ('MyBox') as nvarchar(50))
print @.NewServerName
However, I did do an old fashioned:
Update SysJobs
set originating_server = 'MyBox'
but still I have the same problem.
If I did the script method, what would I do with my DTS Packages? Just save
to the new box? Would I be missing anything then from NOT restoring MSDB?
What do most of you do in this scenario?
"bert" <bertcord@.gmail.com> wrote in message
news:1106868585.875164.102430@.z14g2000cwz.googlegr oups.com...
> I would scrip them out an apply on new server. But you might be able
> to fix them on the new server
> declare @.NewServerName varchar(50)
> select @.NewServerName = cast(serverproperty ('ServerName') as
> nvarchar(50))
> update msdb..sysjobs
> set originating_server = @.NewServerName
> GO
> checkpoint
> GO
>
|||select serverproperty ('ServerName') returns null?
Sorry cant help you with the DTS jobs... I stay away from DTS.
did you stop and restart SQL
Bert
|||Have a look here: http://www.sqldts.com/default.aspx?204
Andrew J. Kelly SQL MVP
"ChrisR" <bla@.noemail.com> wrote in message
news:ub2Z2MMBFHA.2392@.TK2MSFTNGP14.phx.gbl...
>I restored MSDB onto a new box and my jobs won't run now:
> Unable to connect to SQL Server '(local)'. The step failed.
> Am I better off to use the Transfer Jobs Task in DTS or is there a simple
> answer to this? If so, what about moving the DTS Packages?
> --
> SQL2K SP3
> TIA, ChrisR
>
|||Not an option. Thanks Bert.
"bert" <bertcord@.gmail.com> wrote in message
news:1106878056.175663.124700@.z14g2000cwz.googlegr oups.com...
> select serverproperty ('ServerName') returns null?
> Sorry cant help you with the DTS jobs... I stay away from DTS.
> did you stop and restart SQL
> Bert
>
|||Thanks Andrew.
The "Save as" option suits me just fine. But assuming I do that in place of
Restoring MSDB, what about the jobs? Both the jobs to run these DTS Packages
and those that don't. I know the other options are to script them or to use
DTS to transfer them. Is anyone aware of the pros/ cons for either one? If I
do either of these methods, will I be missing something vital from NOT
Restoring MSDB? I don't think so based on past experience, but its been a
while.
TIA, ChrisR
"ChrisR" <bla@.noemail.com> wrote in message
news:ub2Z2MMBFHA.2392@.TK2MSFTNGP14.phx.gbl...
> I restored MSDB onto a new box and my jobs won't run now:
> Unable to connect to SQL Server '(local)'. The step failed.
> Am I better off to use the Transfer Jobs Task in DTS or is there a simple
> answer to this? If so, what about moving the DTS Packages?
> --
> SQL2K SP3
> TIA, ChrisR
>
|||I prefer to script the jobs myself but I don't know of any thing that says
that is better or worse than moving MSDB per say. Even the KB that talks
about moving DB's suggests moving jobs via scripting.
http://www.support.microsoft.com/?id=314546
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.noEmail.com> wrote in message
news:uQAGA5PBFHA.3524@.TK2MSFTNGP15.phx.gbl...
> Thanks Andrew.
> The "Save as" option suits me just fine. But assuming I do that in place
> of
> Restoring MSDB, what about the jobs? Both the jobs to run these DTS
> Packages
> and those that don't. I know the other options are to script them or to
> use
> DTS to transfer them. Is anyone aware of the pros/ cons for either one? If
> I
> do either of these methods, will I be missing something vital from NOT
> Restoring MSDB? I don't think so based on past experience, but its been a
> while.
> TIA, ChrisR
>
> "ChrisR" <bla@.noemail.com> wrote in message
> news:ub2Z2MMBFHA.2392@.TK2MSFTNGP14.phx.gbl...
>
|||Thanks again Andrew. I think Im going to change my strategy on this whole
thing. It seems like to "Save as" for the DTS Packages and Script the Jobs/
Operators/ and Alerts I could get everything that I would from Restoring
MSDB minus a few headaches.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:#AVGIwUBFHA.3924@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> I prefer to script the jobs myself but I don't know of any thing that says
> that is better or worse than moving MSDB per say. Even the KB that talks
> about moving DB's suggests moving jobs via scripting.
> http://www.support.microsoft.com/?id=314546
>
> --
> Andrew J. Kelly SQL MVP
>
> "ChrisR" <ChrisR@.noEmail.com> wrote in message
> news:uQAGA5PBFHA.3524@.TK2MSFTNGP15.phx.gbl...
If[vbcol=seagreen]
a[vbcol=seagreen]
simple
>

restore MSDB or transfer jobs?

I restored MSDB onto a new box and my jobs won't run now:
Unable to connect to SQL Server '(local)'. The step failed.
Am I better off to use the Transfer Jobs Task in DTS or is there a simple
answer to this? If so, what about moving the DTS Packages?
--
SQL2K SP3
TIA, ChrisRI would scrip them out an apply on new server. But you might be able
to fix them on the new server
declare @.NewServerName varchar(50)
select @.NewServerName = cast(serverproperty ('ServerName') as
nvarchar(50))
update msdb..sysjobs
set originating_server = @.NewServerName
GO
checkpoint
GO|||The query converts the @.NewServerName to nothing:
Server: Msg 515, Level 16, State 2, Line 3
Cannot insert the value NULL into column 'originating_server', table
'msdb.dbo.sysjobs'; column does not allow nulls. UPDATE fails.
The statement has been terminated.
declare @.NewServerName varchar(50)
select @.NewServerName = cast(serverproperty ('MyBox') as nvarchar(50))
print @.NewServerName
However, I did do an old fashioned:
Update SysJobs
set originating_server = 'MyBox'
but still I have the same problem.
If I did the script method, what would I do with my DTS Packages? Just save
to the new box? Would I be missing anything then from NOT restoring MSDB?
What do most of you do in this scenario?
"bert" <bertcord@.gmail.com> wrote in message
news:1106868585.875164.102430@.z14g2000cwz.googlegroups.com...
> I would scrip them out an apply on new server. But you might be able
> to fix them on the new server
> declare @.NewServerName varchar(50)
> select @.NewServerName = cast(serverproperty ('ServerName') as
> nvarchar(50))
> update msdb..sysjobs
> set originating_server = @.NewServerName
> GO
> checkpoint
> GO
>|||select serverproperty ('ServerName') returns null?
Sorry cant help you with the DTS jobs... I stay away from DTS.
did you stop and restart SQL
Bert|||Have a look here: http://www.sqldts.com/default.aspx?204
--
Andrew J. Kelly SQL MVP
"ChrisR" <bla@.noemail.com> wrote in message
news:ub2Z2MMBFHA.2392@.TK2MSFTNGP14.phx.gbl...
>I restored MSDB onto a new box and my jobs won't run now:
> Unable to connect to SQL Server '(local)'. The step failed.
> Am I better off to use the Transfer Jobs Task in DTS or is there a simple
> answer to this? If so, what about moving the DTS Packages?
> --
> SQL2K SP3
> TIA, ChrisR
>|||Thanks Andrew.
The "Save as" option suits me just fine. But assuming I do that in place of
Restoring MSDB, what about the jobs? Both the jobs to run these DTS Packages
and those that don't. I know the other options are to script them or to use
DTS to transfer them. Is anyone aware of the pros/ cons for either one? If I
do either of these methods, will I be missing something vital from NOT
Restoring MSDB? I don't think so based on past experience, but its been a
while.
TIA, ChrisR
"ChrisR" <bla@.noemail.com> wrote in message
news:ub2Z2MMBFHA.2392@.TK2MSFTNGP14.phx.gbl...
> I restored MSDB onto a new box and my jobs won't run now:
> Unable to connect to SQL Server '(local)'. The step failed.
> Am I better off to use the Transfer Jobs Task in DTS or is there a simple
> answer to this? If so, what about moving the DTS Packages?
> --
> SQL2K SP3
> TIA, ChrisR
>|||Not an option. Thanks Bert.
"bert" <bertcord@.gmail.com> wrote in message
news:1106878056.175663.124700@.z14g2000cwz.googlegroups.com...
> select serverproperty ('ServerName') returns null?
> Sorry cant help you with the DTS jobs... I stay away from DTS.
> did you stop and restart SQL
> Bert
>|||I prefer to script the jobs myself but I don't know of any thing that says
that is better or worse than moving MSDB per say. Even the KB that talks
about moving DB's suggests moving jobs via scripting.
http://www.support.microsoft.com/?id=314546
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.noEmail.com> wrote in message
news:uQAGA5PBFHA.3524@.TK2MSFTNGP15.phx.gbl...
> Thanks Andrew.
> The "Save as" option suits me just fine. But assuming I do that in place
> of
> Restoring MSDB, what about the jobs? Both the jobs to run these DTS
> Packages
> and those that don't. I know the other options are to script them or to
> use
> DTS to transfer them. Is anyone aware of the pros/ cons for either one? If
> I
> do either of these methods, will I be missing something vital from NOT
> Restoring MSDB? I don't think so based on past experience, but its been a
> while.
> TIA, ChrisR
>
> "ChrisR" <bla@.noemail.com> wrote in message
> news:ub2Z2MMBFHA.2392@.TK2MSFTNGP14.phx.gbl...
>> I restored MSDB onto a new box and my jobs won't run now:
>> Unable to connect to SQL Server '(local)'. The step failed.
>> Am I better off to use the Transfer Jobs Task in DTS or is there a simple
>> answer to this? If so, what about moving the DTS Packages?
>> --
>> SQL2K SP3
>> TIA, ChrisR
>>
>|||Thanks again Andrew. I think Im going to change my strategy on this whole
thing. It seems like to "Save as" for the DTS Packages and Script the Jobs/
Operators/ and Alerts I could get everything that I would from Restoring
MSDB minus a few headaches.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:#AVGIwUBFHA.3924@.TK2MSFTNGP10.phx.gbl...
> I prefer to script the jobs myself but I don't know of any thing that says
> that is better or worse than moving MSDB per say. Even the KB that talks
> about moving DB's suggests moving jobs via scripting.
> http://www.support.microsoft.com/?id=314546
>
> --
> Andrew J. Kelly SQL MVP
>
> "ChrisR" <ChrisR@.noEmail.com> wrote in message
> news:uQAGA5PBFHA.3524@.TK2MSFTNGP15.phx.gbl...
> > Thanks Andrew.
> >
> > The "Save as" option suits me just fine. But assuming I do that in place
> > of
> > Restoring MSDB, what about the jobs? Both the jobs to run these DTS
> > Packages
> > and those that don't. I know the other options are to script them or to
> > use
> > DTS to transfer them. Is anyone aware of the pros/ cons for either one?
If
> > I
> > do either of these methods, will I be missing something vital from NOT
> > Restoring MSDB? I don't think so based on past experience, but its been
a
> > while.
> >
> > TIA, ChrisR
> >
> >
> >
> > "ChrisR" <bla@.noemail.com> wrote in message
> > news:ub2Z2MMBFHA.2392@.TK2MSFTNGP14.phx.gbl...
> >> I restored MSDB onto a new box and my jobs won't run now:
> >>
> >> Unable to connect to SQL Server '(local)'. The step failed.
> >>
> >> Am I better off to use the Transfer Jobs Task in DTS or is there a
simple
> >> answer to this? If so, what about moving the DTS Packages?
> >>
> >> --
> >> SQL2K SP3
> >>
> >> TIA, ChrisR
> >>
> >>
> >
> >
>

restore MSDB or transfer jobs?

I restored MSDB onto a new box and my jobs won't run now:
Unable to connect to SQL Server '(local)'. The step failed.
Am I better off to use the Transfer Jobs Task in DTS or is there a simple
answer to this? If so, what about moving the DTS Packages?
SQL2K SP3
TIA, ChrisRI would scrip them out an apply on new server. But you might be able
to fix them on the new server
declare @.NewServerName varchar(50)
select @.NewServerName = cast(serverproperty ('ServerName') as
nvarchar(50))
update msdb..sysjobs
set originating_server = @.NewServerName
GO
checkpoint
GO|||The query converts the @.NewServerName to nothing:
Server: Msg 515, Level 16, State 2, Line 3
Cannot insert the value NULL into column 'originating_server', table
'msdb.dbo.sysjobs'; column does not allow nulls. UPDATE fails.
The statement has been terminated.
declare @.NewServerName varchar(50)
select @.NewServerName = cast(serverproperty ('MyBox') as nvarchar(50))
print @.NewServerName
However, I did do an old fashioned:
Update SysJobs
set originating_server = 'MyBox'
but still I have the same problem.
If I did the script method, what would I do with my DTS Packages? Just save
to the new box? Would I be missing anything then from NOT restoring MSDB?
What do most of you do in this scenario?
"bert" <bertcord@.gmail.com> wrote in message
news:1106868585.875164.102430@.z14g2000cwz.googlegroups.com...
> I would scrip them out an apply on new server. But you might be able
> to fix them on the new server
> declare @.NewServerName varchar(50)
> select @.NewServerName = cast(serverproperty ('ServerName') as
> nvarchar(50))
> update msdb..sysjobs
> set originating_server = @.NewServerName
> GO
> checkpoint
> GO
>|||select serverproperty ('ServerName') returns null?
Sorry cant help you with the DTS jobs... I stay away from DTS.
did you stop and restart SQL
Bert|||Have a look here: http://www.sqldts.com/default.aspx?204
Andrew J. Kelly SQL MVP
"ChrisR" <bla@.noemail.com> wrote in message
news:ub2Z2MMBFHA.2392@.TK2MSFTNGP14.phx.gbl...
>I restored MSDB onto a new box and my jobs won't run now:
> Unable to connect to SQL Server '(local)'. The step failed.
> Am I better off to use the Transfer Jobs Task in DTS or is there a simple
> answer to this? If so, what about moving the DTS Packages?
> --
> SQL2K SP3
> TIA, ChrisR
>|||Not an option. Thanks Bert.
"bert" <bertcord@.gmail.com> wrote in message
news:1106878056.175663.124700@.z14g2000cwz.googlegroups.com...
> select serverproperty ('ServerName') returns null?
> Sorry cant help you with the DTS jobs... I stay away from DTS.
> did you stop and restart SQL
> Bert
>|||Thanks Andrew.
The "Save as" option suits me just fine. But assuming I do that in place of
Restoring MSDB, what about the jobs? Both the jobs to run these DTS Packages
and those that don't. I know the other options are to script them or to use
DTS to transfer them. Is anyone aware of the pros/ cons for either one? If I
do either of these methods, will I be missing something vital from NOT
Restoring MSDB? I don't think so based on past experience, but its been a
while.
TIA, ChrisR
"ChrisR" <bla@.noemail.com> wrote in message
news:ub2Z2MMBFHA.2392@.TK2MSFTNGP14.phx.gbl...
> I restored MSDB onto a new box and my jobs won't run now:
> Unable to connect to SQL Server '(local)'. The step failed.
> Am I better off to use the Transfer Jobs Task in DTS or is there a simple
> answer to this? If so, what about moving the DTS Packages?
> --
> SQL2K SP3
> TIA, ChrisR
>|||I prefer to script the jobs myself but I don't know of any thing that says
that is better or worse than moving MSDB per say. Even the KB that talks
about moving DB's suggests moving jobs via scripting.
http://www.support.microsoft.com/?id=314546
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.noEmail.com> wrote in message
news:uQAGA5PBFHA.3524@.TK2MSFTNGP15.phx.gbl...
> Thanks Andrew.
> The "Save as" option suits me just fine. But assuming I do that in place
> of
> Restoring MSDB, what about the jobs? Both the jobs to run these DTS
> Packages
> and those that don't. I know the other options are to script them or to
> use
> DTS to transfer them. Is anyone aware of the pros/ cons for either one? If
> I
> do either of these methods, will I be missing something vital from NOT
> Restoring MSDB? I don't think so based on past experience, but its been a
> while.
> TIA, ChrisR
>
> "ChrisR" <bla@.noemail.com> wrote in message
> news:ub2Z2MMBFHA.2392@.TK2MSFTNGP14.phx.gbl...
>|||Thanks again Andrew. I think Im going to change my strategy on this whole
thing. It seems like to "Save as" for the DTS Packages and Script the Jobs/
Operators/ and Alerts I could get everything that I would from Restoring
MSDB minus a few headaches.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:#AVGIwUBFHA.3924@.TK2MSFTNGP10.phx.gbl...
> I prefer to script the jobs myself but I don't know of any thing that says
> that is better or worse than moving MSDB per say. Even the KB that talks
> about moving DB's suggests moving jobs via scripting.
> http://www.support.microsoft.com/?id=314546
>
> --
> Andrew J. Kelly SQL MVP
>
> "ChrisR" <ChrisR@.noEmail.com> wrote in message
> news:uQAGA5PBFHA.3524@.TK2MSFTNGP15.phx.gbl...
If[vbcol=seagreen]
a[vbcol=seagreen]
simple[vbcol=seagreen]
>

restore master to another box

Is it possible to Restore Master db from a backup to a box/ instance with
another name? Will it cause any problems?
sql2k sp3
TIA, ChrisR
It's possible, but kind of a risky proposition, and it really depends on what
you're trying to accomplish, why you're moving the database, other databases
involved, server and database configuration, etc. Check BOL "Restoring the
master Database from a Current Backup" and "Copying Databases to Other
Servers" for details.
You also might want to look at sqlservercentral.com - a couple of links I
found are
http://www.sqlservercentral.com/colu...toremaster.asp and
http://www.sqlservercentral.com/foru...messageid=8387
Personally, I install SQL Server on the destination server and detatch /
attatch the databases from old to new server. There used to be a Knowlege
Base article Q245133 that had code to generate scripts to recreate the
logins, but I couldn't find it. Let me know if you need it and I'll mail the
code to you - mstuart_spamtastesgood@.gates.com - drop the _spamtastesgood, of
course :-)
Mike
"ChrisR" wrote:

> Is it possible to Restore Master db from a backup to a box/ instance with
> another name? Will it cause any problems?
> --
> sql2k sp3
> TIA, ChrisR
>
>
|||For the moment Im only trying to do some Disaster Recovery planning.
"Mike" <mstuart_spamtastesgood@.gates.com> wrote in message
news:1BDC103A-A9E2-48F1-B93F-2C28C2118EA9@.microsoft.com...
> It's possible, but kind of a risky proposition, and it really depends on
what
> you're trying to accomplish, why you're moving the database, other
databases
> involved, server and database configuration, etc. Check BOL "Restoring
the
> master Database from a Current Backup" and "Copying Databases to Other
> Servers" for details.
> You also might want to look at sqlservercentral.com - a couple of links I
> found are
> http://www.sqlservercentral.com/colu...toremaster.asp and
>
http://www.sqlservercentral.com/foru...=24&messageid=
8387
> Personally, I install SQL Server on the destination server and detatch /
> attatch the databases from old to new server. There used to be a Knowlege
> Base article Q245133 that had code to generate scripts to recreate the
> logins, but I couldn't find it. Let me know if you need it and I'll mail
the
> code to you - mstuart_spamtastesgood@.gates.com - drop the _spamtastesgood,
of[vbcol=seagreen]
> course :-)
> Mike
> "ChrisR" wrote:
with[vbcol=seagreen]
|||First and foremost, backup all your databases and test your backups! There's
nothing worse (professionally, anyway) than thinking you've got good backups
and you don't when you need them. Ask me how I know...
Read BOL on recovering master and practice recovering. You should be able
to copy your environment onto a second server for testing purposes by:
1 - installing SQL Server and service packs on another server,
2 - stop SQL Server on the test server,
3 - copy the system data and log files to another location,
4 - stop SQL Server on your source server and copy all data and log files
from source to test environment,
5 - then restart SQL Server.
You'll also need backups of the other system databases (model and msdb).
Probably won't need to worry about pubs or Northwind unless you've got some
custom code in those databases you want to keep.
You'll also want to script out the logins and passwords - see the KB article
I referred to earlier. Depending on the number of changes to your login
accounts, you might even want to script a job to run and recreate the file
auto-magically.
Assuming you're not running earlier that SQL 7.0, you won't need to worry
about scripting out CREATE and ALTER database statements to add space to your
data and log files (I'm probably getting the terminology wrong - it's been a
very long time since SQL 4.2 and 6.5!!), but you'll want to know where you
created your database data, log and backup files so you don't have to go
searching everywhere when the pressure is on and the boss is looking over
your shoulder.
Finally, document on paper in clear, readable text with pictures (if
necessary) how to go about restoring your environment. You might even want
to have a coworker 'monkey test' it for you (can a monkey read your
directions and successfully do whatever you wrote down).
It sounds like extreme overkill, but at 2:00 in the AM and the payroll
server is down and you're the person they come to, you will definitely look
like a hero if you can pull it off, and you may lose your job if you can't.
Good luck!
Mike
"ChrisR" wrote:

> For the moment Im only trying to do some Disaster Recovery planning.
>
> "Mike" <mstuart_spamtastesgood@.gates.com> wrote in message
> news:1BDC103A-A9E2-48F1-B93F-2C28C2118EA9@.microsoft.com...
> what
> databases
> the
> http://www.sqlservercentral.com/foru...=24&messageid=
> 8387
> the
> of
> with
>
>
|||Mike I appreciate your response. But do all of your instructions hold true
for a box with another name? Also, your are referring to copying system db
files. Will that get me around the downfalls of restoring master to a box
with another name?
"Mike" <mstuart_spamtastesgood@.gates.com> wrote in message
news:4D09B9F2-B7D8-4824-9809-2188E93999BC@.microsoft.com...
> First and foremost, backup all your databases and test your backups!
There's
> nothing worse (professionally, anyway) than thinking you've got good
backups
> and you don't when you need them. Ask me how I know...
> Read BOL on recovering master and practice recovering. You should be able
> to copy your environment onto a second server for testing purposes by:
> 1 - installing SQL Server and service packs on another server,
> 2 - stop SQL Server on the test server,
> 3 - copy the system data and log files to another location,
> 4 - stop SQL Server on your source server and copy all data and log files
> from source to test environment,
> 5 - then restart SQL Server.
> You'll also need backups of the other system databases (model and msdb).
> Probably won't need to worry about pubs or Northwind unless you've got
some
> custom code in those databases you want to keep.
> You'll also want to script out the logins and passwords - see the KB
article
> I referred to earlier. Depending on the number of changes to your login
> accounts, you might even want to script a job to run and recreate the file
> auto-magically.
> Assuming you're not running earlier that SQL 7.0, you won't need to worry
> about scripting out CREATE and ALTER database statements to add space to
your
> data and log files (I'm probably getting the terminology wrong - it's been
a
> very long time since SQL 4.2 and 6.5!!), but you'll want to know where you
> created your database data, log and backup files so you don't have to go
> searching everywhere when the pressure is on and the boss is looking over
> your shoulder.
> Finally, document on paper in clear, readable text with pictures (if
> necessary) how to go about restoring your environment. You might even
want
> to have a coworker 'monkey test' it for you (can a monkey read your
> directions and successfully do whatever you wrote down).
> It sounds like extreme overkill, but at 2:00 in the AM and the payroll
> server is down and you're the person they come to, you will definitely
look
> like a hero if you can pull it off, and you may lose your job if you
can't.[vbcol=seagreen]
> Good luck!
> Mike
>
> "ChrisR" wrote:
on[vbcol=seagreen]
"Restoring[vbcol=seagreen]
links I[vbcol=seagreen]
and[vbcol=seagreen]
http://www.sqlservercentral.com/foru...=24&messageid=[vbcol=seagreen]
/[vbcol=seagreen]
Knowlege[vbcol=seagreen]
mail[vbcol=seagreen]
_spamtastesgood,[vbcol=seagreen]
|||Chris,
I guess I don't understand what you mean by the other box. If I understand
correctly, you have your server (we'll say production for sake of arguement)
that you want to develop a DR plan for. You need to set up an environment to
develop and test a plan against. So, you need to go through the steps I
outlined earlier - including the system .mdf and .ldf files.
One thing I did forget (yikes!) was that the master database .mdf and .ldf
files that you copy from the production server will still think that the name
of the SQL Server instance is the name of the production server. Run
'sp_dropserver' and 'sp_addserver' (see BOL 'Renaming a Server') to change
the instance name to the name of the development / test server.
This isn't exactly a restore of 'master' in the strictest sense of the word.
Your master database contains all of the information that allows the SQL
Server engine to see all of the databases installed on that instance of SQL
Server. You're just copying the master database, and all of the other
databases from the production instance to the development / test instance of
SQL Server.
If my directions are still unclear, email me @.
mstuart_spamtastesgood@.gates.com (drop the 'spamtastesgood') with you number
and I'll try to clarify things for you.
Mike

restore master to another box

Is it possible to Restore Master db from a backup to a box/ instance with
another name? Will it cause any problems?
--
sql2k sp3
TIA, ChrisRIt's possible, but kind of a risky proposition, and it really depends on what
you're trying to accomplish, why you're moving the database, other databases
involved, server and database configuration, etc. Check BOL "Restoring the
master Database from a Current Backup" and "Copying Databases to Other
Servers" for details.
You also might want to look at sqlservercentral.com - a couple of links I
found are
http://www.sqlservercentral.com/columnists/bknight/restoremaster.asp and
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=24&messageid=8387
Personally, I install SQL Server on the destination server and detatch /
attatch the databases from old to new server. There used to be a Knowlege
Base article Q245133 that had code to generate scripts to recreate the
logins, but I couldn't find it. Let me know if you need it and I'll mail the
code to you - mstuart_spamtastesgood@.gates.com - drop the _spamtastesgood, of
course :-)
Mike
"ChrisR" wrote:
> Is it possible to Restore Master db from a backup to a box/ instance with
> another name? Will it cause any problems?
> --
> sql2k sp3
> TIA, ChrisR
>
>|||For the moment Im only trying to do some Disaster Recovery planning.
"Mike" <mstuart_spamtastesgood@.gates.com> wrote in message
news:1BDC103A-A9E2-48F1-B93F-2C28C2118EA9@.microsoft.com...
> It's possible, but kind of a risky proposition, and it really depends on
what
> you're trying to accomplish, why you're moving the database, other
databases
> involved, server and database configuration, etc. Check BOL "Restoring
the
> master Database from a Current Backup" and "Copying Databases to Other
> Servers" for details.
> You also might want to look at sqlservercentral.com - a couple of links I
> found are
> http://www.sqlservercentral.com/columnists/bknight/restoremaster.asp and
>
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=24&messageid=
8387
> Personally, I install SQL Server on the destination server and detatch /
> attatch the databases from old to new server. There used to be a Knowlege
> Base article Q245133 that had code to generate scripts to recreate the
> logins, but I couldn't find it. Let me know if you need it and I'll mail
the
> code to you - mstuart_spamtastesgood@.gates.com - drop the _spamtastesgood,
of
> course :-)
> Mike
> "ChrisR" wrote:
> > Is it possible to Restore Master db from a backup to a box/ instance
with
> > another name? Will it cause any problems?
> >
> > --
> > sql2k sp3
> >
> > TIA, ChrisR
> >
> >
> >|||First and foremost, backup all your databases and test your backups! There's
nothing worse (professionally, anyway) than thinking you've got good backups
and you don't when you need them. Ask me how I know...
Read BOL on recovering master and practice recovering. You should be able
to copy your environment onto a second server for testing purposes by:
1 - installing SQL Server and service packs on another server,
2 - stop SQL Server on the test server,
3 - copy the system data and log files to another location,
4 - stop SQL Server on your source server and copy all data and log files
from source to test environment,
5 - then restart SQL Server.
You'll also need backups of the other system databases (model and msdb).
Probably won't need to worry about pubs or Northwind unless you've got some
custom code in those databases you want to keep.
You'll also want to script out the logins and passwords - see the KB article
I referred to earlier. Depending on the number of changes to your login
accounts, you might even want to script a job to run and recreate the file
auto-magically.
Assuming you're not running earlier that SQL 7.0, you won't need to worry
about scripting out CREATE and ALTER database statements to add space to your
data and log files (I'm probably getting the terminology wrong - it's been a
very long time since SQL 4.2 and 6.5!!), but you'll want to know where you
created your database data, log and backup files so you don't have to go
searching everywhere when the pressure is on and the boss is looking over
your shoulder.
Finally, document on paper in clear, readable text with pictures (if
necessary) how to go about restoring your environment. You might even want
to have a coworker 'monkey test' it for you (can a monkey read your
directions and successfully do whatever you wrote down).
It sounds like extreme overkill, but at 2:00 in the AM and the payroll
server is down and you're the person they come to, you will definitely look
like a hero if you can pull it off, and you may lose your job if you can't.
Good luck!
Mike
"ChrisR" wrote:
> For the moment Im only trying to do some Disaster Recovery planning.
>
> "Mike" <mstuart_spamtastesgood@.gates.com> wrote in message
> news:1BDC103A-A9E2-48F1-B93F-2C28C2118EA9@.microsoft.com...
> > It's possible, but kind of a risky proposition, and it really depends on
> what
> > you're trying to accomplish, why you're moving the database, other
> databases
> > involved, server and database configuration, etc. Check BOL "Restoring
> the
> > master Database from a Current Backup" and "Copying Databases to Other
> > Servers" for details.
> >
> > You also might want to look at sqlservercentral.com - a couple of links I
> > found are
> > http://www.sqlservercentral.com/columnists/bknight/restoremaster.asp and
> >
> http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=24&messageid=
> 8387
> >
> > Personally, I install SQL Server on the destination server and detatch /
> > attatch the databases from old to new server. There used to be a Knowlege
> > Base article Q245133 that had code to generate scripts to recreate the
> > logins, but I couldn't find it. Let me know if you need it and I'll mail
> the
> > code to you - mstuart_spamtastesgood@.gates.com - drop the _spamtastesgood,
> of
> > course :-)
> >
> > Mike
> >
> > "ChrisR" wrote:
> >
> > > Is it possible to Restore Master db from a backup to a box/ instance
> with
> > > another name? Will it cause any problems?
> > >
> > > --
> > > sql2k sp3
> > >
> > > TIA, ChrisR
> > >
> > >
> > >
>
>|||Mike I appreciate your response. But do all of your instructions hold true
for a box with another name? Also, your are referring to copying system db
files. Will that get me around the downfalls of restoring master to a box
with another name?
"Mike" <mstuart_spamtastesgood@.gates.com> wrote in message
news:4D09B9F2-B7D8-4824-9809-2188E93999BC@.microsoft.com...
> First and foremost, backup all your databases and test your backups!
There's
> nothing worse (professionally, anyway) than thinking you've got good
backups
> and you don't when you need them. Ask me how I know...
> Read BOL on recovering master and practice recovering. You should be able
> to copy your environment onto a second server for testing purposes by:
> 1 - installing SQL Server and service packs on another server,
> 2 - stop SQL Server on the test server,
> 3 - copy the system data and log files to another location,
> 4 - stop SQL Server on your source server and copy all data and log files
> from source to test environment,
> 5 - then restart SQL Server.
> You'll also need backups of the other system databases (model and msdb).
> Probably won't need to worry about pubs or Northwind unless you've got
some
> custom code in those databases you want to keep.
> You'll also want to script out the logins and passwords - see the KB
article
> I referred to earlier. Depending on the number of changes to your login
> accounts, you might even want to script a job to run and recreate the file
> auto-magically.
> Assuming you're not running earlier that SQL 7.0, you won't need to worry
> about scripting out CREATE and ALTER database statements to add space to
your
> data and log files (I'm probably getting the terminology wrong - it's been
a
> very long time since SQL 4.2 and 6.5!!), but you'll want to know where you
> created your database data, log and backup files so you don't have to go
> searching everywhere when the pressure is on and the boss is looking over
> your shoulder.
> Finally, document on paper in clear, readable text with pictures (if
> necessary) how to go about restoring your environment. You might even
want
> to have a coworker 'monkey test' it for you (can a monkey read your
> directions and successfully do whatever you wrote down).
> It sounds like extreme overkill, but at 2:00 in the AM and the payroll
> server is down and you're the person they come to, you will definitely
look
> like a hero if you can pull it off, and you may lose your job if you
can't.
> Good luck!
> Mike
>
> "ChrisR" wrote:
> > For the moment Im only trying to do some Disaster Recovery planning.
> >
> >
> > "Mike" <mstuart_spamtastesgood@.gates.com> wrote in message
> > news:1BDC103A-A9E2-48F1-B93F-2C28C2118EA9@.microsoft.com...
> > > It's possible, but kind of a risky proposition, and it really depends
on
> > what
> > > you're trying to accomplish, why you're moving the database, other
> > databases
> > > involved, server and database configuration, etc. Check BOL
"Restoring
> > the
> > > master Database from a Current Backup" and "Copying Databases to Other
> > > Servers" for details.
> > >
> > > You also might want to look at sqlservercentral.com - a couple of
links I
> > > found are
> > > http://www.sqlservercentral.com/columnists/bknight/restoremaster.asp
and
> > >
> >
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=24&messageid=
> > 8387
> > >
> > > Personally, I install SQL Server on the destination server and detatch
/
> > > attatch the databases from old to new server. There used to be a
Knowlege
> > > Base article Q245133 that had code to generate scripts to recreate the
> > > logins, but I couldn't find it. Let me know if you need it and I'll
mail
> > the
> > > code to you - mstuart_spamtastesgood@.gates.com - drop the
_spamtastesgood,
> > of
> > > course :-)
> > >
> > > Mike
> > >
> > > "ChrisR" wrote:
> > >
> > > > Is it possible to Restore Master db from a backup to a box/ instance
> > with
> > > > another name? Will it cause any problems?
> > > >
> > > > --
> > > > sql2k sp3
> > > >
> > > > TIA, ChrisR
> > > >
> > > >
> > > >
> >
> >
> >|||Chris,
I guess I don't understand what you mean by the other box. If I understand
correctly, you have your server (we'll say production for sake of arguement)
that you want to develop a DR plan for. You need to set up an environment to
develop and test a plan against. So, you need to go through the steps I
outlined earlier - including the system .mdf and .ldf files.
One thing I did forget (yikes!) was that the master database .mdf and .ldf
files that you copy from the production server will still think that the name
of the SQL Server instance is the name of the production server. Run
'sp_dropserver' and 'sp_addserver' (see BOL 'Renaming a Server') to change
the instance name to the name of the development / test server.
This isn't exactly a restore of 'master' in the strictest sense of the word.
Your master database contains all of the information that allows the SQL
Server engine to see all of the databases installed on that instance of SQL
Server. You're just copying the master database, and all of the other
databases from the production instance to the development / test instance of
SQL Server.
If my directions are still unclear, email me @.
mstuart_spamtastesgood@.gates.com (drop the 'spamtastesgood') with you number
and I'll try to clarify things for you.
Mike

restore master to another box

Is it possible to Restore Master db from a backup to a box/ instance with
another name? Will it cause any problems?
sql2k sp3
TIA, ChrisRIt's possible, but kind of a risky proposition, and it really depends on wha
t
you're trying to accomplish, why you're moving the database, other databases
involved, server and database configuration, etc. Check BOL "Restoring the
master Database from a Current Backup" and "Copying Databases to Other
Servers" for details.
You also might want to look at sqlservercentral.com - a couple of links I
found are
http://www.sqlservercentral.com/col...storemaster.asp and
[url]http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=24&messageid=8387[/
url]
Personally, I install SQL Server on the destination server and detatch /
attatch the databases from old to new server. There used to be a Knowlege
Base article Q245133 that had code to generate scripts to recreate the
logins, but I couldn't find it. Let me know if you need it and I'll mail th
e
code to you - mstuart_spamtastesgood@.gates.com - drop the _spamtastesgood, o
f
course :-)
Mike
"ChrisR" wrote:

> Is it possible to Restore Master db from a backup to a box/ instance with
> another name? Will it cause any problems?
> --
> sql2k sp3
> TIA, ChrisR
>
>|||For the moment Im only trying to do some Disaster Recovery planning.
"Mike" <mstuart_spamtastesgood@.gates.com> wrote in message
news:1BDC103A-A9E2-48F1-B93F-2C28C2118EA9@.microsoft.com...
> It's possible, but kind of a risky proposition, and it really depends on
what
> you're trying to accomplish, why you're moving the database, other
databases
> involved, server and database configuration, etc. Check BOL "Restoring
the
> master Database from a Current Backup" and "Copying Databases to Other
> Servers" for details.
> You also might want to look at sqlservercentral.com - a couple of links I
> found are
> http://www.sqlservercentral.com/col...storemaster.asp and
>
http://www.sqlservercentral.com/for...d=24&messageid=
8387
> Personally, I install SQL Server on the destination server and detatch /
> attatch the databases from old to new server. There used to be a Knowlege
> Base article Q245133 that had code to generate scripts to recreate the
> logins, but I couldn't find it. Let me know if you need it and I'll mail
the
> code to you - mstuart_spamtastesgood@.gates.com - drop the _spamtastesgood,
of[vbcol=seagreen]
> course :-)
> Mike
> "ChrisR" wrote:
>
with[vbcol=seagreen]|||First and foremost, backup all your databases and test your backups! There'
s
nothing worse (professionally, anyway) than thinking you've got good backups
and you don't when you need them. Ask me how I know...
Read BOL on recovering master and practice recovering. You should be able
to copy your environment onto a second server for testing purposes by:
1 - installing SQL Server and service packs on another server,
2 - stop SQL Server on the test server,
3 - copy the system data and log files to another location,
4 - stop SQL Server on your source server and copy all data and log files
from source to test environment,
5 - then restart SQL Server.
You'll also need backups of the other system databases (model and msdb).
Probably won't need to worry about pubs or Northwind unless you've got some
custom code in those databases you want to keep.
You'll also want to script out the logins and passwords - see the KB article
I referred to earlier. Depending on the number of changes to your login
accounts, you might even want to script a job to run and recreate the file
auto-magically.
Assuming you're not running earlier that SQL 7.0, you won't need to worry
about scripting out CREATE and ALTER database statements to add space to you
r
data and log files (I'm probably getting the terminology wrong - it's been a
very long time since SQL 4.2 and 6.5!!), but you'll want to know where you
created your database data, log and backup files so you don't have to go
searching everywhere when the pressure is on and the boss is looking over
your shoulder.
Finally, document on paper in clear, readable text with pictures (if
necessary) how to go about restoring your environment. You might even want
to have a coworker 'monkey test' it for you (can a monkey read your
directions and successfully do whatever you wrote down).
It sounds like extreme overkill, but at 2:00 in the AM and the payroll
server is down and you're the person they come to, you will definitely look
like a hero if you can pull it off, and you may lose your job if you can't.
Good luck!
Mike
"ChrisR" wrote:

> For the moment Im only trying to do some Disaster Recovery planning.
>
> "Mike" <mstuart_spamtastesgood@.gates.com> wrote in message
> news:1BDC103A-A9E2-48F1-B93F-2C28C2118EA9@.microsoft.com...
> what
> databases
> the
> [url]http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=24&messageid=[/ur
l]
> 8387
> the
> of
> with
>
>|||Mike I appreciate your response. But do all of your instructions hold true
for a box with another name? Also, your are referring to copying system db
files. Will that get me around the downfalls of restoring master to a box
with another name?
"Mike" <mstuart_spamtastesgood@.gates.com> wrote in message
news:4D09B9F2-B7D8-4824-9809-2188E93999BC@.microsoft.com...
> First and foremost, backup all your databases and test your backups!
There's
> nothing worse (professionally, anyway) than thinking you've got good
backups
> and you don't when you need them. Ask me how I know...
> Read BOL on recovering master and practice recovering. You should be able
> to copy your environment onto a second server for testing purposes by:
> 1 - installing SQL Server and service packs on another server,
> 2 - stop SQL Server on the test server,
> 3 - copy the system data and log files to another location,
> 4 - stop SQL Server on your source server and copy all data and log files
> from source to test environment,
> 5 - then restart SQL Server.
> You'll also need backups of the other system databases (model and msdb).
> Probably won't need to worry about pubs or Northwind unless you've got
some
> custom code in those databases you want to keep.
> You'll also want to script out the logins and passwords - see the KB
article
> I referred to earlier. Depending on the number of changes to your login
> accounts, you might even want to script a job to run and recreate the file
> auto-magically.
> Assuming you're not running earlier that SQL 7.0, you won't need to worry
> about scripting out CREATE and ALTER database statements to add space to
your
> data and log files (I'm probably getting the terminology wrong - it's been
a
> very long time since SQL 4.2 and 6.5!!), but you'll want to know where you
> created your database data, log and backup files so you don't have to go
> searching everywhere when the pressure is on and the boss is looking over
> your shoulder.
> Finally, document on paper in clear, readable text with pictures (if
> necessary) how to go about restoring your environment. You might even
want
> to have a coworker 'monkey test' it for you (can a monkey read your
> directions and successfully do whatever you wrote down).
> It sounds like extreme overkill, but at 2:00 in the AM and the payroll
> server is down and you're the person they come to, you will definitely
look
> like a hero if you can pull it off, and you may lose your job if you
can't.[vbcol=seagreen]
> Good luck!
> Mike
>
> "ChrisR" wrote:
>
on[vbcol=seagreen]
"Restoring[vbcol=seagreen]
links I[vbcol=seagreen]
and[vbcol=seagreen]
http://www.sqlservercentral.com/for...d=24&messageid=[vbcol=seagreen]
/[vbcol=seagreen]
Knowlege[vbcol=seagreen]
mail[vbcol=seagreen]
_spamtastesgood,[vbcol=seagreen]|||Chris,
I guess I don't understand what you mean by the other box. If I understand
correctly, you have your server (we'll say production for sake of arguement)
that you want to develop a DR plan for. You need to set up an environment t
o
develop and test a plan against. So, you need to go through the steps I
outlined earlier - including the system .mdf and .ldf files.
One thing I did forget (yikes!) was that the master database .mdf and .ldf
files that you copy from the production server will still think that the nam
e
of the SQL Server instance is the name of the production server. Run
'sp_dropserver' and 'sp_addserver' (see BOL 'Renaming a Server') to change
the instance name to the name of the development / test server.
This isn't exactly a restore of 'master' in the strictest sense of the word.
Your master database contains all of the information that allows the SQL
Server engine to see all of the databases installed on that instance of SQL
Server. You're just copying the master database, and all of the other
databases from the production instance to the development / test instance of
SQL Server.
If my directions are still unclear, email me @.
mstuart_spamtastesgood@.gates.com (drop the 'spamtastesgood') with you number
and I'll try to clarify things for you.
Mike