Archive

Posts Tagged ‘ALTER DATABASE’

SQL DBA – Move user Database (.mdf & .ldf files) to another drive

December 17, 2012 4 comments

 
Here in this post we will see how to move user created Database to an another drive from the default drive.
 

–> The first 2 ways I’ve discussed in my other blog posts, please check the links below:

1. Detach & Attach task, video

2. Backup & Restore task, link, video
 

3. By using ALTER command, Syntax below:

ALTER DATABASE SET OFFLINE;
GO

— Manually move the file(s) to the new location.

ALTER DATABASE Database name
MODIFY FILE (
NAME = ‘Database name’,
FILENAME = ‘New Location’
)
GO

ALTER DATABASE Database name SET ONLINE;
GO

 


 

–> Script used in above video:

Step #1. Create a new Database in SSMS with name “ManDB”.

Step #2. Check the current location of database:

select * from ManDB.sys.database_files

--ManDB		D:\Program Files (x86)\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\ManDB.mdf
--ManDB_log	D:\Program Files (x86)\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\ManDB_log.ldf

Step #3. Take the database offline, so that we can move files from one drive to another:

ALTER DATABASE ManDB SET OFFLINE;
GO

Step #4. Move the file(s) to the new location manually.

Step #5. Change the system catalog settings:

ALTER DATABASE ManDB
MODIFY FILE (
	NAME = 'ManDB',
	FILENAME = 'E:\SQLDBs\ManDB.mdf' 
)
GO

ALTER DATABASE ManDB
MODIFY FILE (
	NAME = 'ManDB_log',
	FILENAME = 'E:\SQLDBs\ManDB_log.ldf' 
)
GO

Step #6. Take the database back online:

ALTER DATABASE ManDB SET ONLINE;
GO

Step #7. Check the new location:

select * from ManDB.sys.database_files

--ManDB		E:\SQLDBs\ManDB.mdf
--ManDB_log	E:\SQLDBs\ManDB_log.ldf
Advertisement
Categories: DBA Stuff, SQL Basics Tags: ,

SQL DBA – Moved MASTER database by ALTER DATABASE statement? here’s the solution

October 8, 2012 4 comments

Have you also moved your MASTER DATABASE by using “ALTER DATABASE” statement just like you did for other system databases like MSDB, MODEL, TEMPDB & other databases?

If YES, then you are same nerdy DBA like me.

For quite some time I was observing very bad performance in one of our DEV servers. So today I thought to check it, I found that the C: Drive is almost full. Don’t know why do the DBA guys installed SQL Server on C: drive and put all system databases here to make it even worse. To get some room on C: drive I thought to move all four system databases (i.e. MASTER, MODEL, MSDB & TEMPDB) to another drive.

So, I created normal “ALTER DATABASE” scripts for all the 4 databases and executed them, as follows:

ALTER DATABASE master MODIFY FILE (
	NAME = 'tempdev' ,
	FILENAME = 'D:\MSSQL10_50\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\tempdb.mdf' )
ALTER DATABASE master MODIFY FILE (
	NAME = 'templog' ,
	FILENAME = 'D:\MSSQL10_50\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\templog.ldf' )

ALTER DATABASE master MODIFY FILE (
	NAME = 'modeldev' ,
	FILENAME = 'D:\MSSQL10_50\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\modeldev.mdf' )
ALTER DATABASE master MODIFY FILE (
	NAME = 'modellog' ,
	FILENAME = 'D:\MSSQL10_50\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\modellog.ldf' )

ALTER DATABASE master MODIFY FILE (
	NAME = 'MSDBData' ,
	FILENAME = 'D:\MSSQL10_50\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MSDBData.mdf' )
ALTER DATABASE master MODIFY FILE (
	NAME = 'MSDBLog' ,
	FILENAME = 'D:\MSSQL10_50\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MSDBLog.ldf' )

-- !!!! BEWARE DON'T RUN THIS !!!!
ALTER DATABASE master MODIFY FILE (
	NAME = 'master' ,
	FILENAME = 'D:\MSSQL10_50\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf' )
ALTER DATABASE master MODIFY FILE (
	NAME = 'mastlog' ,
	FILENAME = 'D:\MSSQL10_50\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf' )
-- !!!! BEWARE DON'T RUN THIS !!!!

-> Happily I Stopped the SQL Server service.

-> Now, to move the databases physically I moved the MDF & LDF files to the new location I used in “ALTER DATABASE” statements above.

-> After moving DB files I tried to Start the “SQL Server” service, but the service didn’t start and I was getting following error:
“The SQL Server service on [SERVER_NAME] started and then stopped. blah blah blah…”

I immediately thought that I’ve done something wrong, checked MS BOL, and found that I should not have moved the MASTER database by using “ALTER DATABASE” statement.

–> WORKAROUND:
Now when the wrong scripts are executed and there is no way to undo it, there should be some way to fix it.

SQL Server comes with a tool i.e. “SQL Server Configuration Manager” to manage the services associated with SQL Server. Like, for this case to configure startup options that will be used every time the Database Engine starts in SQL Server.

Open this tool from “Program Files -> SQL Server -> Configuration Tools”:

-> Select “SQL Server Services” on the left side navigation bar.

-> On the right side Right Click on SQL Server instance and select Properties.

-> On the Pop-Up select the “Startup Paramaters” tab. Here you can change the MASTER database’s MDF & LDF file’s location:
—> Parameter starting with “-dD” is for DATA file (MDF).
—> AND parameter starting with “-lD” is for LOG file (LDF).

-> Select both properties one by one and change the file location at the “Existing Parameters:” text box and click Update for both the files.

-> Now, Start the Services and yes it started without any issue.

-> Check the new location by issuing either of following 2 SQL queries:

select * from sys.sysdatabases
-- OR --
select * from sys.master_files

Not only this is a workaround to fix this issue, but you can also use this tool to move your MASTER database to a different Drive.

Categories: DBA Stuff, SQL Tips Tags: , , ,