Hi ,
I am having some issues restoring the following databases
i tried to follow the steps in the articles -->
http://support.microsoft.com/kb/224071/en-us
what i have done :
- i am trying to restore all the databases back to the same location after
changing to a new disk
- Have reinstall SQL Server 2000 with SP3a and is i have copied the
PREVIOUS system db .mdf & .ldf files
Ques 1 : What are the sequence in restoring the system db files ?
Ques 2 : do i need to restore tempdb the location that i have installed is
my original location
Ques 3 : i followed the steps of moving 'model' database
SQL Server 2000 and SQL Server 2005
In SQL Server 2000 and in SQL Server 2005, system databases cannot be
detached by using the sp_detach_db stored procedure. Running the sp_detach_db
'model' statement does not work, and you receive the following error message:
Server: Msg 7940, Level 16, State 1, Line 1
System databases master, model, msdb, and tempdb cannot be detached.
To move the model database, you must start SQL Server together with trace
flag 3608 so SQL Server does not recover any database except the master
database.
Note You will not be able to access any user databases after you do this. You
must not perform any operations, other than the following steps, while you
use this trace flag. To add trace flag 3608 as a SQL Server startup parameter,
follow these steps: 1. In SQL Server Enterprise Manager, right-click the
server name, and then click Properties.
2. On the General tab, click Startup Parameters.
3. Add the following new parameter:
-T3608
If you are using SQL Server 2005, you can use SQL Server Configuration
Manager to change the startup parameters of the SQL Server service. For more
information about how to change the startup parameters, visit the following
Microsoft Developer Network (MSDN) Web site:
http://msdn2.microsoft.com/en-us/library/ms190737.aspx (
http://msdn2.microsoft.com/en-us/library/ms190737.aspx)
After you add trace flag 3608, follow these steps: 1. Stop and then restart
SQL Server.
2. Detach the model database by using the following commands:use master
go
sp_detach_db 'model'
go
3. Move the Model.mdf and Modellog.ldf files from the D:\Mssql7\Data folder
to the E:\Sqldata folder.
4. Reattach the model database by using the following commands:use master
go
sp_attach_db 'model','E:\Sqldata\model.mdf','E:\Sqldata\modellog.ldf'
go
at step 4 , when i try to reattach i got the following error something like
"Connection Broken" and i could not continue
i'll apprecaite someone can give me the exact steps of restoring a System DBs
tks & rdgs
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1Hi All ,
I have managed to successfully restore my System Database with extracts from
the 2 articles and hopefully it'll be useful to those who might use it.
if you use the article in whole from Microsoft , somehow it gives error after
moving the "master" db
Step 1 : Moving ?master? database
First, right-click on SQL Server in Enterprise Manager (EM) and choose
Properties. Next,
click the Startup Parameters as shown in Figure A. As you can see in Figure A,
the
following parameters appear in
-d is the fully qualified path for the master database data file.
-e is the fully qualified path for the error log file.
-l is the fully qualified path for the master database log
- Now, if you want to move your files you would remove the current entry and
recreate
your new entry with the correct path. For example, I am going to move the
Master database log to C:\test\.
- I would delete the -l [path] by highlighting the old parameter and choosing
remove
- Next, I would add the following entry, -l C:\test\mastlog.ldf and click OK
twice. Now, you will stop SQL Server and move the mastlog.ldf to its new
location.
Note: Please make sure you move the mastlog.ldf to the location you
configured in your
startup parameters. If you move it to a location that is not specified in the
startup
parameters, SQL Server WILL NOT start.
- Finally, start SQL Server
Step 2 : Moving ?tempdb? database
- login to Query Analyzer
- run the following scripts where 'E:\Sqldata\tempdb.mdf' is the path of the
data file and 'E:\Sqldata\templog.ldf' is the path of the txn log file. Note :
the files can be in different location
use master
go
Alter database tempdb modify file (name = tempdev, filename = 'E:\Sqldata\
tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = 'E:\Sqldata\
templog.ldf')
Go
- Stop & ReStart SQL Server
The above steps(1 & 2) are taken from this link ?
http://www.databasejournal.com/features/mssql/article.php/3379901
Step 3 : Moving ?model? database
- In SQL Server Enterprise Manager, right-click the server name, and then
click Properties.
- On the General tab, click Startup Parameters.
- Add the following new parameter: -T3608
- Stop and then restart SQL Server.
- Detach the model database by using the following commands:
use master
go
sp_detach_db 'model'
go
- Move the Model.mdf and Modellog.ldf from its original location to the new
location
- Reattach the model database by using the following commands:
use master
go
sp_attach_db 'model','E:\Sqldata\model.mdf','E:\Sqldata\modellog.ldf'
go
- Remove the -T3608 trace flag from the startup parameters in SQL
Server Enterprise Manager or in SQL
Server Configuration Manager.
- Stop and then restart SQL Server. You can verify the change in file
locations by using the sp_helpfile
stored procedure. For example, use the following command:
use model
go
sp_helpfile
go
Step 4 : Moving ?msdb? database
- In SQL Server Enterprise Manager, right-click the server name, and then
click Properties.
- On the General tab, click Startup Parameters.
- Add the following new parameter: -T3608
- Stop and then restart SQL Server.
- Detach the model database by using the following commands:
use master
go
sp_detach_db 'msdb'
go
- Move the Msdbdata.mdf and msdblog.ldf from its original location to the new
location
- Reattach the model database by using the following commands:
use master
go
sp_attach_db 'msdb','d:\Sqldata\msdb.mdf','E:\Sqldata\msdblog.ldf'
go
- Remove the -T3608 trace flag from the startup parameters in SQL
Server Enterprise Manager or in SQL
Server Configuration Manager.
- Stop and then restart SQL Server. You can verify the change in file
locations by using the sp_helpfile
stored procedure. For example, use the following command:
use msdb
go
sp_helpfile
go
Note : To check if the db is correct you may use ? sp_helpdb in Query
Analyzer take note that the dbid for ?model? MUST be 3 else there could be
some error !!!
The above steps ( 3 & 4) are taken from ?
http://support.microsoft.com/default.aspx?scid=kb;en-us;224071
tks & rdgs
maxzsim wrote:
>Hi ,
> I am having some issues restoring the following databases
> i tried to follow the steps in the articles -->
>http://support.microsoft.com/kb/224071/en-us
>what i have done :
>- i am trying to restore all the databases back to the same location after
>changing to a new disk
> - Have reinstall SQL Server 2000 with SP3a and is i have copied the
>PREVIOUS system db .mdf & .ldf files
>Ques 1 : What are the sequence in restoring the system db files ?
>Ques 2 : do i need to restore tempdb the location that i have installed is
>my original location
>Ques 3 : i followed the steps of moving 'model' database
>SQL Server 2000 and SQL Server 2005
>In SQL Server 2000 and in SQL Server 2005, system databases cannot be
>detached by using the sp_detach_db stored procedure. Running the sp_detach_db
>'model' statement does not work, and you receive the following error message:
>Server: Msg 7940, Level 16, State 1, Line 1
>System databases master, model, msdb, and tempdb cannot be detached.
>To move the model database, you must start SQL Server together with trace
>flag 3608 so SQL Server does not recover any database except the master
>database.
>Note You will not be able to access any user databases after you do this. You
>must not perform any operations, other than the following steps, while you
>use this trace flag. To add trace flag 3608 as a SQL Server startup parameter,
>follow these steps: 1. In SQL Server Enterprise Manager, right-click the
>server name, and then click Properties.
>2. On the General tab, click Startup Parameters.
>3. Add the following new parameter:
>-T3608
>If you are using SQL Server 2005, you can use SQL Server Configuration
>Manager to change the startup parameters of the SQL Server service. For more
>information about how to change the startup parameters, visit the following
>Microsoft Developer Network (MSDN) Web site:
>http://msdn2.microsoft.com/en-us/library/ms190737.aspx (
>http://msdn2.microsoft.com/en-us/library/ms190737.aspx)
>After you add trace flag 3608, follow these steps: 1. Stop and then restart
>SQL Server.
>2. Detach the model database by using the following commands:use master
> go
> sp_detach_db 'model'
> go
>3. Move the Model.mdf and Modellog.ldf files from the D:\Mssql7\Data folder
>to the E:\Sqldata folder.
>4. Reattach the model database by using the following commands:use master
> go
> sp_attach_db 'model','E:\Sqldata\model.mdf','E:\Sqldata\modellog.ldf'
> go
>
>at step 4 , when i try to reattach i got the following error something like
>"Connection Broken" and i could not continue
>i'll apprecaite someone can give me the exact steps of restoring a System DBs
>tks & rdgs
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200606/1
