Restore system dbs from SP3 to SP3a?

Can system databases (master, model, msdb) backed up from SQL2k SP3 be restored to an instance of SP3a? Can they be restored from SP3a to SP3? TIA!Not going to happen buddy. try installing a fresh copy of sql server somewhere else upgrade to sp3, restore your system db's there, upgrade to sp3a then move the system db's to the live server. hope this helps.|||Thanks - that's what I thought. However, how can it be determined which SP (3 or 3a) a given instance is running? @.@.version returns 8.00.760 for both of them, & Properties in EM show SP3 for both. I remember there being a way to find the build number, but I don't know how to get it. TIA.|||Check with MS website at the SP3/SP3a download pages. They usually have a section that tells you how to determine which SP you're currently running under.|||Even better: http://www.sqlteam.com/item.asp?ItemID=8318

Restore SQL2000 database using vbscript

Hello,
I am trying to use a VBscript thru installshield to restore a sql2000 database. the script (below) worked fine for default instance of database. but with a specific named instance I get error 3201. any ideas? thx - Prasanna
-----------
code:

dim sql
dim sqlRest

on error resume next
Set sql = CreateObject("SQLDMO.SQLServer")
sql.LoginSecure = True
sql.Connect ".\TEST2000"

Set sqlRest = CreateObject("SQLDMO.Restore")
sqlRest.Files = "c:\testdb.bak"
sqlRest.Database = "TESTDB"
sqlRest.Action = SQLDMORestore_Database
sqlRest.ReplaceDatabase = True
sqlRest.SQLRestore sql
msgbox err.numberFound a solution:

The backup was created using default instance of SQL2000 and once I used a backup created using the 'TEST2000' instance the script worked. I think you can use the RelocateFiles property of the SQLDMO Restore object to set the location of the data and log files but due to time constraints I could not try that solution.

-Prasanna

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 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

restore master database not working

When I restored the master database from another server I immediately get
the message that the restore succeeded and the (single user) instance of SQL
server ends. When I attempt to start server again in single or multi-user
mode, it looks like it is going to start, but then I just get a bunch of
errors. Most of them are refering to the database files that I didn't copy
over (and they are in a different drive on the original server anyway). Then
the server just stops again. I tried using the enterprise manager and
changing the restore to location to match the new server install, and I also
used the following T-SQL:
RESTORE DATABASE master
FROM disk = 'e:\mssql\backup\master_bk.bak'
MOVE 'master' TO 'e:\mssql\data\master.mdf','mastlog' TO
'e:\mssql\data\mastlog.ldf'
go
We have done it before on a server with the same data path. I used the move
statement. Why doesn't this work?Hi,
This problem is not because of Master database restore. After restore, the
filename column for otherdatabases
(MSDB, Model, Tempdb...and user databases) in master..sysdatabases table may
be refrerring to some other
physical folder. Due to this all the databases failed tro recover and which
will cause the SQL Service to
come down. So look into the sysdatabases table and based on that copy the
files into appropriate folders,
before the restoring Master database.
Thanks
Hari
MCDBA
"rschatz" <@.> wrote in message news:uPtz449$DHA.3452@.TK2MSFTNGP11.phx.gbl...
> When I restored the master database from another server I immediately get
> the message that the restore succeeded and the (single user) instance of
SQL
> server ends. When I attempt to start server again in single or multi-user
> mode, it looks like it is going to start, but then I just get a bunch of
> errors. Most of them are refering to the database files that I didn't copy
> over (and they are in a different drive on the original server anyway).
Then
> the server just stops again. I tried using the enterprise manager and
> changing the restore to location to match the new server install, and I
also
> used the following T-SQL:
> RESTORE DATABASE master
> FROM disk = 'e:\mssql\backup\master_bk.bak'
> MOVE 'master' TO 'e:\mssql\data\master.mdf','mastlog' TO
> 'e:\mssql\data\mastlog.ldf'
> go
> We have done it before on a server with the same data path. I used the
move
> statement. Why doesn't this work?
>|||U have restored master, however your other system databases are not being
initialized because the info in master points to another physical location,
you might want to consider rebuildm utility to help you
get all system databases in correct location. . . .once this is done, you
can go on to the other server and perform sp_detach_db (check BOL) for the
user databases, then copy the *.ldf and *.mdf files into the correct
location on the new server and then run sp_attach_db(check BOL) for all the
user databases.
moving on from this, if you wish to restore your SQL agent Jobs then you can
just resotre the msdb database from the other server with the
REPLACE/MOVE(check BOL) options
--
Olu Adedeji
"rschatz" <@.> wrote in message news:uPtz449$DHA.3452@.TK2MSFTNGP11.phx.gbl...
> When I restored the master database from another server I immediately get
> the message that the restore succeeded and the (single user) instance of
SQL
> server ends. When I attempt to start server again in single or multi-user
> mode, it looks like it is going to start, but then I just get a bunch of
> errors. Most of them are refering to the database files that I didn't copy
> over (and they are in a different drive on the original server anyway).
Then
> the server just stops again. I tried using the enterprise manager and
> changing the restore to location to match the new server install, and I
also
> used the following T-SQL:
> RESTORE DATABASE master
> FROM disk = 'e:\mssql\backup\master_bk.bak'
> MOVE 'master' TO 'e:\mssql\data\master.mdf','mastlog' TO
> 'e:\mssql\data\mastlog.ldf'
> go
> We have done it before on a server with the same data path. I used the
move
> statement. Why doesn't this work?
>

restore master database not working

When I restored the master database from another server I immediately get
the message that the restore succeeded and the (single user) instance of SQL
server ends. When I attempt to start server again in single or multi-user
mode, it looks like it is going to start, but then I just get a bunch of
errors. Most of them are refering to the database files that I didn't copy
over (and they are in a different drive on the original server anyway). Then
the server just stops again. I tried using the enterprise manager and
changing the restore to location to match the new server install, and I also
used the following T-SQL:
RESTORE DATABASE master
FROM disk = 'e:\mssql\backup\master_bk.bak'
MOVE 'master' TO 'e:\mssql\data\master.mdf','mastlog' TO
'e:\mssql\data\mastlog.ldf'
go
We have done it before on a server with the same data path. I used the move
statement. Why doesn't this work?Hi,
This problem is not because of Master database restore. After restore, the
filename column for otherdatabases
(MSDB, Model, Tempdb...and user databases) in master..sysdatabases table may
be refrerring to some other
physical folder. Due to this all the databases failed tro recover and which
will cause the SQL Service to
come down. So look into the sysdatabases table and based on that copy the
files into appropriate folders,
before the restoring Master database.
Thanks
Hari
MCDBA
"rschatz" <@.> wrote in message news:uPtz449$DHA.3452@.TK2MSFTNGP11.phx.gbl...
> When I restored the master database from another server I immediately get
> the message that the restore succeeded and the (single user) instance of
SQL
> server ends. When I attempt to start server again in single or multi-user
> mode, it looks like it is going to start, but then I just get a bunch of
> errors. Most of them are refering to the database files that I didn't copy
> over (and they are in a different drive on the original server anyway).
Then
> the server just stops again. I tried using the enterprise manager and
> changing the restore to location to match the new server install, and I
also
> used the following T-SQL:
> RESTORE DATABASE master
> FROM disk = 'e:\mssql\backup\master_bk.bak'
> MOVE 'master' TO 'e:\mssql\data\master.mdf','mastlog' TO
> 'e:\mssql\data\mastlog.ldf'
> go
> We have done it before on a server with the same data path. I used the
move
> statement. Why doesn't this work?
>|||U have restored master, however your other system databases are not being
initialized because the info in master points to another physical location,
you might want to consider rebuildm utility to help you
get all system databases in correct location. . . .once this is done, you
can go on to the other server and perform sp_detach_db (check BOL) for the
user databases, then copy the *.ldf and *.mdf files into the correct
location on the new server and then run sp_attach_db(check BOL) for all the
user databases.
moving on from this, if you wish to restore your SQL agent Jobs then you can
just resotre the msdb database from the other server with the
REPLACE/MOVE(check BOL) options
Olu Adedeji
"rschatz" <@.> wrote in message news:uPtz449$DHA.3452@.TK2MSFTNGP11.phx.gbl...
> When I restored the master database from another server I immediately get
> the message that the restore succeeded and the (single user) instance of
SQL
> server ends. When I attempt to start server again in single or multi-user
> mode, it looks like it is going to start, but then I just get a bunch of
> errors. Most of them are refering to the database files that I didn't copy
> over (and they are in a different drive on the original server anyway).
Then
> the server just stops again. I tried using the enterprise manager and
> changing the restore to location to match the new server install, and I
also
> used the following T-SQL:
> RESTORE DATABASE master
> FROM disk = 'e:\mssql\backup\master_bk.bak'
> MOVE 'master' TO 'e:\mssql\data\master.mdf','mastlog' TO
> 'e:\mssql\data\mastlog.ldf'
> go
> We have done it before on a server with the same data path. I used the
move
> statement. Why doesn't this work?
>

Restore master database from 64bit sql server to 32 bit server

Hi,
We are planning to create a prod environment on our Dev server, by
creating a new named instance on DEV. Our prod server is running on
sql enterprise edition 64bit and dev on SQl enterprise 32bit.
Could we copy all the databases from prod server including
Master,msdb,model onto our dev server, and bring up all the databases?
Does Master store information about the version of SQl server?

Thanks for your help
GGIt would probably be best not to copy the system databases (except
perhaps model) - master and msdb have lots of information about the
instance, including name, backup history etc which could create
problems if you restore onto another instance. Typically, those
databases are only restored if they become corrupted, or as part of a
complete server recovery.

Simon