May 19, 2004 at 5:35 am
I have a Development PC with SQL Server 2000 Developer. I am really struggling to try and restore a copy our Production Server on to a new instance.
I want to use this new instance to test change scripts.
Would appreciate the steps needed and the correct order to restore everything: Master, msdb, model, user databases.
I succeeded in the past restoring onto the default instance but can't seem able to get this working on a new instance.
Help
May 19, 2004 at 6:02 am
There's a way that requires a brief shutdown of the prod server, and another that does not, but may be more involved in syntax. If shutdown is not an option, then the first won't work.
Common assumption: since you seem to want a 'clone', I assume that your dev instance 'server' is partitioned the same as the prod box - ie the same drive letters exists.
If yes, then -
1) install the new instance exactly the same way as the prod base (ie same paths for system dbs etc)
2) shut down dev server
3) shut down prod server
4) copy *all* database files (*.mdf, *.ldf etc, including master, msdb) from the prod box to the dev box and place all files on the same paths as prod, overwriting the existsing system db's on the dev box.
5) start prod server when copy is done
6) start dev server. It should now start and look/feel the same as the prod box. You may have name issues, though, if your dev comp have another name.
The other way is to just restore backups of all the user db's and system db's too. Can't really see what the difference should be between a default and a named instance.. Each instance lives in it's own directory structure.
Anyway, RESTORE with MOVE might be what you want.
Look up RESTORE in BOL (Books on Line), there are many variations on the RESTORE comand depending on the situation.
The system dbs also requires some special treatment to restore. BOL has info on this too.
Hope it helps some?
=;o)
/Kenneth
May 19, 2004 at 6:17 am
Thanks for the reply.
I can not stop the Production server so have to use the second option.
The default instance on the development server is a copy of production so I have to restore to different paths.
Which order should I restore? Master, msdb, model, user databases?
Currently I try restoring Master first but then I am unable start the default and new instance at the same time. Only one will run at a time.
May 20, 2004 at 1:57 am
i might be being a little fick here, but have you tried just restoring your database to the server ?
does sql not link up your new database automatically when you do that ?
if you have your own custom sps etc in the other databases, script them on your box then run the script on the production server - if you are allowed access to those databases.
May 20, 2004 at 9:27 am
I wish it was as easy as just restoring the database.
In view of the struggle I am having in restoring Master to a new instance and lack of an answer I guess I will do this the safer way and just restore the user databases keeping everything else in sync manually and using Redgate tools to check this.
May 25, 2004 at 5:06 am
I guess I had two problems
Firstly the default instance of SQL Server used the machine name rather than the DNS name.
I solved this by getting the machine name renamed to be the same as the DNS name.
My second problem was that the Development version on my development PC used the same file structure as the Production version on my Production Server.
I wanted to create a new instance on my Development server so that I could test releases against the new instance. Because the development instance already replicated the Production path I had to use a new file structure for the new instance.
This has caused me nightmares, cold sweats etc. and I still have not successfully got this to work. I have had some anxious moments also when I thought I had lost my development databases and overwritten the backups.
As I have not been successful in restoring the Production system and user databases to a new instance in a different location I have gone down a different route pending a future article on this site which gives a step by step idiots guide.
I have moved all the development databases to a new location so that the paths are different to Production. And will now try and restore the test version of Production onto the new instance using the same paths as Production. Thinking about it this actually makes sense.
I will in the future however want to refresh my development version from Production so I am hoping that Steve Jones series of articles "Keeping QA Up To Date" will cover this in enough detail for me to get it right.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply