July 28, 2004 at 4:00 am
I've inherited a selection of SQL servers all of which have been built slightly differently and this includes installation paths. As and when we rebuild them I'd like to standardize the install locations.
The problems occur when I try to restore the master database onto a server that has SQL installed in a different location. It throws up horrible errors about master and msdb database paths not found.
Now I understand exactly why these errors appear but I haven't been able to find a clean and definitive way to restore a master database to a different location.
Thanks
July 28, 2004 at 6:16 am
You need to set some startup parameters before the move....check out http://support.microsoft.com/default.aspx?scid=kb;en-us;224071
July 28, 2004 at 7:55 am
Perfect! Exactly what I was looking for.
Thanks
July 28, 2004 at 10:23 am
There are a few "gotchas" in manually moving the system folders. If you're only moving the databases, some of the gotchas go away. Here's a little "step by step" that I came up with a while back when I needed to do this. Incidentally, I didn't do it this way, I reinstalled SQL and restored the system databases. I'll include my "step by step" for that, too. Note: Step 12 in the manual move for moving the full text catalog folder and files was enough to cause me to do the reinstall version!
Steve
Database Moving Procedure (Plan A, reinstall SQL Server)
1) Script security and configuration.
2) Backup master, msdb, and model
3) Stop SQL
4) Copy master, msdb and model files to test server (backups also)
5) Make any necessary drive changes
6) Install SQL, placing System dbs on application/systemdb drive (E:\)
7) Start SQL in Single User Mode (startup parameter = –m)
8) Restore master database
9) Restore msdb and model (if necessary) dbs
10) Attach user dbs
11) Apply configuration
12) Apply security
Database Moving Procedure (Plan B, manual move)
1) Detach user databases
2) Move the files to their new locations (Pubs and Northwind to MSSQL)
3) Reattach dbs from step 1.
4) Add new Startup Parameter: -T3608, stop, restart SQL.
5) Ensure that SQL Agent is NOT running.
6) Detach Model, detach MSDB, move files, reattach Model, reattach MSDB (in this order!!!)
7) Move tempdb:
a. ALTER DATABASE TEMPDB MODIFY FILE (name = tempdev, filename = ‘F:\MSSQL\DATA\tempdb.mdf.
b. repeat for log
c. optionally – disable autogrow on tempdb files, create new files on data/log drives, allow new files to grow.
8) Remove –T3608 from startup parameters
9) Restart SQL. Delete old tempdb files.
10) Move the Master database:
a. Remove startup parameters for master.mdf, mastlog.ldf, ERRORLOG
b. Add new startup parameters with new paths for master.mdf, mastlog.ldf, ERRORLOG.
i. –dF:\MSSQL\DATA\master.mdf
ii. –eF:\MSSQL\LOG\ERRORLOG
iii. –lF:\MSSQL\DATA\mastlog.ldf
c. Stop SQL.
d. Copy master.mdf, mastlog.ldf, and error log files to their new locations.
11) Restart SQL.
12) Follow Microsoft provided directions for moving Full-Text Catalog Folders and Files
http://support.microsoft.com/default.aspx?scid=kb;EN-US;240867
July 29, 2004 at 12:17 pm
I'd like to suggest that in your Plan A, you not only copy the backup files to a test server, but you take the additional step of restoring them on that test server. If that restore fails, that could indicate potential problems when you go to restore on the production server.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply