Note:Best viewed in Internet Explorer 6 and above
Implementation: Moving the msdb,model databases without SQL service start and stop.
Scenario: Lets say you are suppose to move the system databases(msdb,model) not tempdb and master to another location
without SQL service start and stop operations.
Below some steps will help you out for getting done above activities without failure.
Note:
1)Steps mentioned below are not recommended by Microsoft as a best practices.
For Best practices below is the link from Microsoft.....
https://msdn.microsoft.com/en-IN/library/ms345408.aspx
2)Tempdb and master databases movement are excluded from these steps.
3)Do not perform the same for any user databases since corruption may occur.
4)First test it on an UAT environment first.
Infrastructure: Below steps/operations have been tested successfully on SQL server 2005/2008/2008 R2/2012/2014.
Steps
Way 1:
1)First take the backup of system databases(master,model,msdb).We will need the backup of msdb database only.
Below is the actual location of msdb database files before moving.
2)Now open two new blank notepad and save as ‘msdbdata.mdf’ and ‘msdblog.ldf’ by selecting all files.
Save these files where you actually want to move your msdb database files coz these files only going to be your real msdb database files after movement.
3)Take two blank new notepad where you actually want to move your msdb files.
In above screen shot one notepad is highlighted only.Once you save those notepad as 'msdbdata.mdf' and 'msdblog.ldf' icon will come same as DB files.
Note: But initially the size of these files would be zero.
4)Now run the below 'ALTER MODIFY FILES' query for msdb files to make effective changes in catalogs.
Alter database msdb
modify file
(
name='msdbdata',
filename='D:\Dharmesh\msdbdata.mdf'
)
go
Alter database msdb
modify file
(
name='msdblog',
filename='D:\Dharmesh\msdblog.ldf'
)
go
5)Now restore the msdb database from backup with replace
6)Now run the below query to see the location of msdb database files where you have been moved it.
SELECT NAME,PHYSICAL_NAME AS 'PhysicalFilePath',
STATE_DESC AS 'DB Status' FROM SYS.MASTER_FILES
where name in('msdbdata', 'MSDBLog')
7)This is how you can move msdb(Only msdb) database to another location without service start and stop-without downtime.
Way 2:
By following this second way we can move msdb as well as model both DBs files to another location without downtime.
1)Take the Full backup of 'msdb' and 'model' databases and restore(with replace) it with another name say 'msdbtest' and 'modeltest' database using 'move to' command.
Mind the location where you actually want to keep our DBs files.Coz this Test databases files going to be our real msdb and model files.
2)
Now run the below 'ALTER MODIFY FILES' query for msdb files to make effective changes in catalogs.
:
Alter database msdb
modify file
(
name='msdbdata',
filename='D:\Dharmesh\msdbdata.mdf'
)
go
Alter database msdb
modify file
(
name='msdblog',
filename='D:\Dharmesh\msdblog.ldf'
)
go
For Model:
Alter database model
modify file
(
name='model',
filename='D:\Dharmesh\model.mdf'
)
go
Alter database model
modify file
(
name='modellog',
filename='D:\Dharmesh\modellog.ldf'
)
go
3)Now take the 'msdbtest'and 'modeltest' databases offline so that to rename its files(mdf and ldf) same as real msdb and model files.
4)Now you can delete your offline databases('msdbtest'and 'modeltest')..Thats done...
This is how you can move the msdb and model databases files to another location without need of SQL service start and stop or no downtime require.
Like this:
Like Loading...
You must be logged in to post a comment.