Can we move Resource database from one path to another? If yes,How can we?

Can we move Resource database from one path to another? If
yes,How can we?

Question Posted / vamsimadineni

1 Answers
6430 Views
Wipro, I also Faced
E-Mail Answers

Can we move Resource database from one path to another? If yes,How can we?..

Answer / sambashivareddy . m

Yes we can move one path to another by detaching.
The following example moves a database that is named mydb. This database contains one data file, Mydb.mdf, and one log file, Mydblog.ldf. If the database that you are moving has more data files or log files, specify the files in a comma-delimited list in the sp_attach_db stored procedure. The sp_detach_db procedure does not change regardless of how many files the database contains because the sp_detach_db procedure does not list the files.
Detach the database as follows:
use master
go
sp_detach_db 'mydb'
go
Next, copy the data files and the log files from the current location (D:\Mssql7\Data) to the new location (E:\Sqldata).
Re-attach the database. Point to the files in the new location as follows:
use master
go
sp_attach_db 'mydb','E:\Sqldata\mydbdata.mdf','E:\Sqldata\mydblog.ldf'
go
Verify the change in file locations by using the sp_helpfile stored procedure:
use mydb
go
sp_helpfile
go
The filename column values should reflect the new locations.

In SQL Server 2005 and in SQL Server 2000, you cannot detach system databases by using the sp_detach_db stored procedure. When you try to run the sp_detach_db 'model' statement, 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 the -c option, the -m option, and trace flag 3608. Trace flag 3608 prevents SQL Server from recovering 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 on SQL Server 2000, follow these steps:
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:
-c -m -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
After you add the -c option, the -m option, and trace flag 3608, follow these steps:
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 files from the D:\Mssql7\Data folder to the E:\Sqldata folder.
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 -c -m -T3608 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
Back to the top
Moving the MSDB database
SQL Server 7.0
Note If you are using this procedure while moving the msdb and model databases, you must reattach the model database first, and then reattach the msdb database. Follow these steps:
Make sure that the SQL Server Agent is not currently running.
Follow the same procedure for moving user databases.
Note If SQL Server Agent is running, the sp_detach_db stored procedure will not succeed and you will receive the following message:
Server: Msg 3702, Level 16, State 1, Line 0
Cannot drop the database 'msdb' because it is currently in use.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server 2005 and SQL Server 2000
To move the MSDB database, you must start SQL Server together with the -c option, the -m option, and trace flag 3608. Trace flag 3608 prevents SQL Server from recovering any database except the master database. To add the -c option, the -m option, and trace flag 3608, follow the steps in the "Moving the model database" section. After you add the -c option, the -m option and trace flag 3608, follow these steps:
Stop, and then restart SQL Server.
Make sure that the SQL Server Agent service is not currently running.
Detach the msdb database as follows:
use master
go
sp_detach_db 'msdb'
go
Move the Msdbdata.mdf and Msdblog.ldf files from the current location (D:\Mssql8\Data) to the new location (E:\Mssql8\Data).
Remove -c -m -T3608 from the startup parameters box in Enterprise Manager.
Stop and then restart SQL Server.

Note If you try to reattach the msdb database by starting SQL Server together with the -c option, the -m option, and trace flag 3608, you may receive the following error message:
Server: Msg 615, Level 21, State 1, Line 1
Could not find database table ID 3, name 'model'.
Reattach the msdb database as follows:
use master
go
sp_attach_db 'msdb','E:\Mssql8\Data\msdbdata.mdf','E:\Mssql8\Data\msdblog.ldf'
go
Note If you use this procedure together with moving the model database, you are trying to detach the msdb database while you detach the model database. When you do this, you must reattach the model database first, and then reattach the msdb database. If you reattach the msdb database first, you receive the following error message when you try to reattach the model database:
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
In this case, you must detach the msdb database, reattach the model database, and then reattach the msdb database,

After you move the msdb database, you may receive the following error message:
Error 229: EXECUTE permission denied on object 'ObjectName', database 'master', owner 'dbo'.
This problem occurs because the ownership chain has been broken. The database owners for the msdb database and for the master database are not the same. In this case, the ownership of the msdb database had been changed. To work around this problem, run the following Transact-SQL statements. You can do this by using the Osql.exe command-line utility (SQL Server 7.0 and SQL Server 2000) or the Sqlcmd.exe command-line utility (SQL Server 2005):
USE MSDB
Go
EXEC sp_changedbowner 'sa'
Go
For more information, click the following article number to view the article in the Microsoft Knowledge Base:
272424 Object ownership chain checking across databases depends on the login that is mapped to the object owners
Back to the top
Moving the master database
SQL Server 7.0 and SQL Server 2000
Change the path for the master data files and the master log files in SQL Server Enterprise Manager.

Note You may also change the location of the error log here.
Right-click the SQL Server in Enterprise Manager and then click Properties.
Click Startup Parameters to see the following entries:
-dD:\MSSQL7\data\master.mdf
-eD:\MSSQL7\log\ErrorLog
-lD:\MSSQL7\data\mastlog.ldf
-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 file.
Change these values as follows:
Remove the current entries for the Master.mdf and Mastlog.ldf files.
Add new entries specifying the new location:
-dE:\SQLDATA\master.mdf
-lE:\SQLDATA\mastlog.ldf
Stop SQL Server.
Copy the Master.mdf and Mastlog.ldf files to the new location (E:\Sqldata).
Restart SQL Server.
SQL Server 2005
For more information about moving the master database and the Resource database, visit the following MSDN Web site: http://msdn2.microsoft.com/en-us/library/ms345408.aspxYou may experience a failure when you move the master database and the Resource database. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
918695 You may experience a failure when you install SQL Server 2005 Service Pack 1 on an instance of SQL Server 2005
Back to the top
Moving the tempdb database
You can move tempdb files by using the ALTER DATABASE statement.
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:\Sqldata\tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = 'E:\Sqldata\templog.ldf')
go
You should receive the following messages that confirm the change:
Message 1
File 'tempdev' modified in sysaltfiles. Delete old file after restarting SQL Server.
Message 2
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.
Stop and then restart SQL Server.

Is This Answer Correct ?

1 Yes

0 No

Post New Answer