Use a variable in an ALTER DATABASE command

Note:  Best viewed in Internet Explorer 6 and above

Implementation: How to use a variable in an ALTER database command.

Scenario: Lets say,you want to use a variable in an ALTER DATABASE for putting all or selected DBs offline/online/attach/detach purpose.

Infrastructure:The below query/procedure has been run/tested successfully on sql server 2005,2008,2008 R2 and 2012.

Kindly find the below link for the same.

https://app.box.com/s/dtp8qdc582o44tpo9u7b8p9g0k7np7fh

 

Move MSDB & MODEL databases to another location without downtime [without start\stop SQL service]

 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.
msdblocation_before migration
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.
notepad_msdb
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.