March 17, 2004 at 11:17 am
I accidently deatched both the model and MSDB databases from the SQL server, I was in the process of moving the files over to a different drive. Now my SQL does not start for me to attach the files. Is there any way to reattach the same. Any help on this is appreciated
Rama
March 17, 2004 at 11:31 am
I would like to get the scenario correct.
a. SQL Server was executing
b. Model and MSDB were detached
c. SQL server was shutdown
d. Now SQL Server will not startup.
How you tried starting up SQL Server using SQLServr from command prompt in single user mode (I don't know if this is possible)? Then tried attaching Model and MSDB?
GaryA
March 17, 2004 at 12:20 pm
You were in the process of moving WHAT files? And how were you trying to move the files?
-SQLBill
March 17, 2004 at 12:44 pm
The background is as follows:
I started SQl server, went into properties by right clicking on the server and enter an entry for -T3608. Once this was done, I restarted the SQl server
Then I went into ISQLW and did the following
sp_detach_db 'msdb'
sp_detach_db 'model'
Then I stopped the server and then moved the the .mdf and .ldf files to the new location.
After this I was planning to reattach the same from ISQLW but could not get SQL started.
The reason i was trying all this was we wanted to move the databases to a new Raid group created on our SAN.
Any help is highly appreciated.
Regards
Rama
March 17, 2004 at 1:32 pm
I don't know if the following procedure is the best procedure, but it may work.
1. Rebuild the master file.
2. Put Model and MSDB .mdf & .ndf & .ldf files back to their original location.
3. Start-up SQL (maybe from command line in single user mode without any trace flags).
4. Restore master file from current backup.
This may get you back to where you were.
GaryA
March 17, 2004 at 1:47 pm
Here are two articles to read on moving MSDB and MODEL. They talk about using Enterprise Manager, but the facts are the same.
http://support.microsoft.com/default.aspx?scid=KB;en-us;224071&
http://qa.sqlservercentral.com/columnists/sjones/movingmsdb.asp
Basically, you need to set the trace flag -T3608 (which you did). Stop and restart SQL Server (which you did not say you did). Make sure SQL Server Agent is stopped (again, you didn't say you did this). Detach the databases. Move them. Attach them using the path to the new location.
If you haven't tried Gary's suggestion, you might try moving the files BACK to where they were originally and then following the directions for moving them.
-SQLBill
March 17, 2004 at 1:59 pm
I did exactly what was told in the document, but now reading the document closely it looks like you should not detach model databased and stop SQL.
I have copied the files back to their location but still I can't get them up, the server itself does not start
March 17, 2004 at 3:05 pm
I am grasping at straws, but maybe it can be started from the command prompt using the -f switch (Starts an instance of SQL Server with minimal configuration).
If this gets it started, then maybe the situtation can be resolved.
You may want to wait for SQLBill's response, before attempting this course of action.
Best Wishes
GaryA
March 18, 2004 at 3:14 am
I did this and recovered from it.
If anyone else is thinking of moving Model and MSBD, one important thing is to detach and attach the databases one at a time. If you have both detached at the same time things can get tricky...
The post from SQLBill giving some Microsoft links should help.
The important thing is to get SQL Server running with the -T3608 flag. This flag must be set to detach these databases, but if you remove this flag and try to restart SQL it will not start.
If you think the -T3608 flag is missing, you have to edit the registry direct. Navigate to HKLM\Software\Microsoft\MSSQLServer\MSSQLServer\Parameters and add the missing flag. SQL Should now start.
You should now attach the databases. Make sure you attach them in the correct order, as SQL Server is hard-coded to expect Master as dbid 1, tempdb as 2, model as 3 and msdb as 4. If you attach msdb before model the dbid numbers will be wrong and SQL Server will get upset when you restart without -T3608.
If you ever manage to detach Tempdb, you will never be able to attach it as dbid 2. The only way out of this I found to work was to start SQL Server in minimal mode using the -f switch, then update master..sysdatabases to correct the dbid number.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
March 18, 2004 at 5:20 am
Here is another option for getting SQL Server started again, assuming you have a backup of the master db to restore.
1. Set SQL Server to single user mode. Open a command prompt and navigate to the directory that contains sqlservr.exe. Note: Be sure that MSSQLServer and SQLAgent services are stopped. Enter the following command:
sqlservr.exe -c -m
This will start SQL Server in single user mode. Be sure to leave the DOS window open.
2. Rebuild the master database using the rebuildm utility. Pasting from Books online.
How to rebuild the master database (Rebuild Master utility)
To rebuild the master database
1. Shutdown Microsoft® SQL Server™ 2000, and then run Rebuildm.exe. This is located in the Program Files\Microsoft SQL Server\80\Tools\Binn directory.
2. In the Rebuild Master dialog box, click Browse.
3. In the Browse for Folder dialog box, select the \Data folder on the SQL Server 2000 compact disc or in the shared network directory from which SQL Server 2000 was installed, and then click OK.
4. Click Settings. In the Collation Settings dialog box, verify or change settings used for the master database and all other databases.
Initially, the default collation settings are shown, but these may not match the collation selected during setup. You can select the same settings used during setup or select new collation settings. When done, click OK.
5. In the Rebuild Master dialog box, click Rebuild to start the process.
The Rebuild Master utility reinstalls the master database.
3. For step 3 in above instructions, above instead of using the CD, copy the files over to a folder on a local drive and remove the readonly access of all the files and folders of the CD otherwise you may run into the issue below:-
273572 BUG: Rebuildm.exe Utility Stops Responding When Source Directory is on a
http://support.microsoft.com/?id=273572
4. Open SQL Enterprise Manager or SQL Query Analyzer, be sure to only open one since SQL Server is in Single User Mode, it will show as stopped but you can open and it move around. Following the normal SQL Server Enterprise Manager / SQL Query Analyzer routine for restoring the Master database.
March 18, 2004 at 7:29 am
I have moved MSDB and model database at the same time on more than a dozen servers recently. The first time I ran into the same issued by following the MS documentation. The trick is to set the start up parameter -T3608, then re-start SQL. Make sure the SQL server agent if NOT running. In this Order; Detach MSDB and then Model databases. Move data files to new location and then re-attach databases. Attach Model first and then MSDB. Remove the startup flag (-T3608) and re-start SQL.
Hope this helps
Dan
March 18, 2004 at 2:24 pm
Phew, that was close, I realised my mistake and I was able to get my databases back. This is some of my learning, hopefully it will help someone someday. Thanks for everyone providing suggestions.
Database Mystery
Following are the steps I did for moving the system databases which resulted in the fiasco.
1) I followed the Microsoft Knowledge Base Article- 224071, this article is good, but it does not explain the moving of Model and MSDB databases properly, it is confusing.
2) I started the SQL server and went to enterprise manager and rightclicked on the server properties and in the General Tab went to the Start-up parameters and added the parameter ‘-T3608’
3) I stopped the SQL server and restarted it.
4) I then detached both model and MSDB through the sp_detach_db statement.
5) I went into the Start-up properties and removed the parameter which I added.
6) I stopped the server and tried restarting and it DID NOT START.
What did I do to restart the server
1) I edited the registry through regedt32.Go to HKLM\Software\Microsoft\MSSQLServer\MSSQLServer\Parameters and add the missing flag.
2) Once the Missing flag was added the SQl server started, but gave a message that it could not connect to the server, which I feel was because it was not able to see any databases. This is fine.
3) Then I opened Query Analyser and attached the model database first ( This is very important because if you attached MSDB first there will be problem with the database id being issued)
4) Then I went to the start-up parameters of the server and removed the parameter which I had added through the resgistry.
5) I stopped SQL server and started it again.
6) I then attached the MSDB back through the sp_attach_db statement.
7) Once I did the above my server came up with all the databases intact.
Mistakes I did
The main mistake is that after I added the parameter in the Start-up parameters I detached both MSDB and model at the same time and then removed the parameter and stopped SQL. That was really bad because SQL requires Model to start in a normal way, it can start without model only with the parameter in.
The Correct steps to do the above
First Do model
1) Go to the properties on the server , and put in the parameter ‘-T3608’
2) Restart SQL server
3) Detach model db from the current file
4) Move the files over to the new place where you want it.
5) Attach the model again through sp_attach statement to the new location of the file.
6) Remove the parameter.
7) Restart SQL
8) You will see the change in the path through sp_helpfile
Now do MSDB
1) Go to the parameters again
2) Introduce the parameter
3) Restart SQL
4) Detach MSDB
5) Move the files to the new location
6) Remove the parameter.
7) Restart SQL
8) Reattach MSDB again to the new location
Please note the above is my experience and there is no guarantee that it will work for everyone, anyone trying to use the same please consult expert resources for more information on the said issue..
DO NOT TOTALLY RELY ON THE ABOVE
Rama
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply