Systemdatabases restore
-
I have come over a problem. It is when I try to make a restore of an SQL server and would like to place the systemdatabases on different locations then before the restore. For an example the systemdatabases was on G:\Program Files\Microsoft SQL……. But now I want to put them under E:\MSSQL…
I do the usual installation and when I restore the master database and the SQL service try to start again it says it could not find the locations for model and msdb devices. If I look in the eventlog I see it's complain about the location of these databases. The service would not start in any way. I have tyr sqlservr -c -m -f.
Is it possible to restor this way or do I have to restore to a configuration like the old one?
-
I believe that you can only restore master to the same location.
Hope this helps.
-
If you are restoring the master database and also the other system databases where on the different drive to the destination location SQL server wont start they have to be on the same location as the source .Users databases can be on different drive .
Cheers
--Ramesh
Ramesh -
Interesting. I just did this last weekend. I had 2 possible ways of accomplishing it. The first way was similar to what you are trying. Its a long drawn out process fraught with dangers. Ok, maybe not that bad, but its not great. I created a step by step document for this process if you would like to see it. The second way is to re-install SQL putting the system databases where you want them to be, then restoring. You need to use "with move" on your restore statements. Again, I created a step by step document for this procedure. If you would like to see either, let me know. (just respond here. I'll get an e-mail).
Steve
-
I wonder if the following is a possibility, for moving the system databases on the same server. Main thing to notice is Database restore process is not involved.
a. Shutdown SQL Server
b. Move the Master DB, and Master Trn Log, and Master Errorlog to the desired new location.
c. Startup, SQL Server from the command line, in single user mode and using the -f flag (minimal configuration), specifying the -d -e and -l options providing new locations for Master DB, Master Trn Log, and Master Errorlog files.
d. Detach Model and then Attach Model with MOVE option.
e. Detach MSDB and then Attach MSDB with MOVE option.
f. Following handles relocation of TempDB at next startup of SQL Server
Determine the logical file names for the tempdb database by using sp_helpfile
as follows:
use tempdb
go
sp_helpfile
go
The logical name for each file is contained in the name column. This example
uses the default file names of tempdev and templog.
Use the ALTER DATABASE statement, specifying the logical file name as follows:
use master
go
Alter database tempdb modify file (name = tempdev, filename =`e:\mssql7\data\tempdb.mdf`)
go
Alter database tempdb modify file (name = templog, filename = `e:\mssql7\data\tempdb.ldf`)
go
You should receive the following messages confirming the change:
File `tempdev` modified in sysaltfiles. Delete old file after restarting SQL Server.
File `templog` modified in sysaltfiles. Delete old file after restarting SQL Server.
Using sp_helpfile in tempdb will not confirm these changes until you restart SQL Server.
G. Shutdown SQL Server
H. Startup SQL Server normally.
This process has not been tested by me.
Any comments on this process would be appreciated.
GaryA
-
I though there is also a KB on this issue as well, at least my co-worker told me to dig for it.
I was in the same shoe last week and I didn't bother looking for the KB, I just hack master and bcp sysxlogins from old, delete some duplicate sysxlogins record and bcp it into the new server. Everything works just fine.
Of course I tried the master restored 3 or more time and it failed all the time because of not having the same directory structure.
mom
-
Gary,
Better option would be to change the startup options for the location of the master database files from enterprise manager BEFORE shutting down and moving the files. Then shut it down, move the files, restart.
Here's the document that I created last week for the move that I made:
Note that this is NOT the way that I chose to do it.
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
-
Here's the other method - the one that I chose to use:
1) Script security and configuration.
2) Backup master, msdb, and model
3) Stop SQL
4) Copy master, msdb and model files to DBASRV1 (backups also)
5) Make any necessary drive changes
6) Install SQL, placing System dbs on app/sysdb 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 and afDBA
11) Apply configuration
12) Apply security
much simpler and cleaner. Moving the full-text catalog folders and files from the first method I posted involves hacking the registry. Not a pretty picture.
Steve
-
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply