Hi Guys...I'm new to SQL and have been mostly an Oracle DBA. I'm starting to support SQL at work and what I am basically trying to do is to restore my SQL Server 2000 to another server. I am assuming this is a practical thing to do.
Scenario: serverA is our production SQL server. I take full backups of the master, msdb, model, and all the user databases to tape. serverA burns down. We setup serverB with the same configuration with the exception of the file structure not being the same...otherwise - memory, CPU, etc are all the same. We installed a fresh copy of SQL Server 2000 on serverB and patched it to the same level as before (SP3).
Based on a doc from MS support, this is what I've done so far:
1.) Shutdown the newly installed SQL server (on serverB).
2.) Start SQL server in single-user mode (with sqlservr -c -m ).
3.) Connect with Query Analyzer and issue the following command:
restore database master from disk = 'C:\MSSQL\backups\New Folder\masterBackup.bak'
move 'master' to 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\master.mdf' ,
move 'mastlog' to 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\mastlog.ldf' ,
(This execute successfully and SQL server shutsdown automatically as expected).
4.) Start SQL server (with sqlservr -c -f -T3608)
5.) Connect with Query Analyzer and issue the following command(s):
update sysdatabases set filename='C:\Program Files\Microsoft SQL Server\MSSQL\Data\tempdb.mdf' where name='tempdb'
(This execute successfully)
alter database tempdb modify file (name = tempdev, filename = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\tempdb.mdf')
alter database tempdb modify file (name = templog, filename = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\templog.ldf')
(This is were I get the error
[Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionCheckForData (CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
When I looked in the ERRORLOG file for SQL server, I find that it tries to start the model database when I issue the last command (alter database...). And of course, the master database now thinks the model database datafiles are located in a physical location that does not exist in this server...and thus, errors out.
I've been trying to get support in this issue and not even Premier Support from MS can help. It's been 1 week and 4 different technicians from MS.
I'm starting to realize that something as practical as this: to be able to restore the entire database server and databases to another machine - is not as practical/easy in SQL server as one would think. However, I still refuse to believe that this is not something that can be easily on SQL databases....it just seems so basic and
Can you confirm this impression I am starting to have. And if it is something done often in practice by most SQL DBAs, then could someone shed some light on this for me.