Search This Blog

Tuesday, September 27, 2011

SQL 2005 - How to move System DB in SQL 2005

Moving System Database in SQL Server 2005

1. Ensure the Destination Drive is having enough space then the Current one.

2. For Example :-
       Current Sys DB path : C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
       To Move                   : D:\System DB

3    Moving Model DB
           
            a.  Goto SQL Server Configuration Manager and open.

            b.  Select the SQL Sever 2005 Services.

            c.  Right click the SQL Server Service and Stop.

            d.  Open a Command prompt ( Run -> cmd)

            e.  Go to the SQLserver installation path ie C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn

            f.  Type Sqlservr -c -m -T3608 and run.( This will Start the SQL Server in Single user mode).

            g.  Open the Microsoft SQLServer Management Studio ( While opening it will ask the username and pwd..
                 Dont give any details, Just open SSMS)

            h.  Select the New Query Option ( Now you give the Username and password)   .

            i.  Run this Query
                       
            SELECT name, physical_name AS CurrentLocation, state_desc
            FROM sys.master_files
            WHERE database_id = DB_ID(N'model')             
                        This will provide the current location of your Model DB.

            j. Use this Query to Detach the Model db.
                        use master
            go
            sp_detach_db 'model'
            go

      k. Move the Model DB files (.mdf & .ldf) file the Destination Folder
                        Ex : C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data  ---> D:\System DB
                       
            l. Press Ctrl + c in your opened cmd prompt . it will ask Do you wish to Shutdown the SQL Server <Y/N>

            m.  Press Y . then the SQL Server will Shut down

            n.  Goto the SQL Server Configuration Manager  and Restart the MSSQL server.

            l.  Run this Query
                        use master
            go
            sp_attach_db 'model','D:\System DB\model.mdf','D:\System DB\modellog.ldf'
            go

            p. Rerun this Query
                       
            SELECT name, physical_name AS CurrentLocation, state_desc
            FROM sys.master_files
            WHERE database_id = DB_ID(N'model')             
                        This will provide the current location of your Model DB.
                        ( I am Sure now the Model DB is moved to Destination Place)

4    Moving MSDB
           
            a.  Goto SQL Server Configuration Manager and open.

            b.  Select the SQL Sever 2005 Services.

            c.  Right click the SQL Server Service and Stop.

            d.  Open a Command prompt ( Run -> cmd)

            e.  Go to the SQLserver installation path ie C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn

            f.  Type Sqlservr -c -m -T3608 and run.( This will Start the SQL Server in Single user mode).

            g.  Open the Microsoft SQLServer Management Studio ( While opening it will ask the username and pwd..
                 Dont give any details, Just open SSMS)

            h.  Select the New Query Option ( Now you give the Username and password)   .

            i.  Run this Query
                       
            SELECT name, physical_name AS CurrentLocation, state_desc
            FROM sys.master_files
            WHERE database_id = DB_ID(N'msdb')               
                        This will provide the current location of your Model DB.

            j. Use this Query to Detach the Model db.
                        use master
            go
            sp_detach_db 'msdb'
            go

      k. Move the MSDB files (.mdf & .ldf) file the Destination Folder
                        Ex : C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data  ---> D:\System DB
                       
            l. Press Ctrl + c in your opened cmd prompt . it will ask Do you wish to Shutdown the SQL Server <Y/N>

            m.  Press Y . then the SQL Server will Shut down

            n.  Goto the SQL Server Configuration Manager  and Restart the MSSQL server.

            o.  Run this Query
                        use master
            go
            sp_attach_db 'msdb','D:\System DB\msdbdata.mdf','D:\System DB\msdblog.ldf'
            go
     
            p. Rerun this Query
                        SELECT name, physical_name AS CurrentLocation, state_desc
            FROM sys.master_files
            WHERE database_id = DB_ID(N'msdb')               
                        This will provide the current location of your MSDB.
                        ( I am Sure now the MSDB is moved to Destination Place)

            q . Run this Query
                        USE MSDB
            Go
            EXEC sp_changedbowner 'sa'
            Go


4    Moving Master DB

            a.  Goto SQL Server Configuration Manager and open.

            b.  Select the SQL Sever 2005 Services.

            c.  Right click the SQL Server Service and Stop.

            d. Move the Master DB files (.mdf & .ldf) file the Destination Folder
                        Ex : C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data  ---> D:\System DB
           
            e. Goto SQL Server Configuration Manager and open.

            f.  Select the SQL Sever 2005 Services.

            g.  Right click the SQL Server Service --> Properties --> Advanced (tab) --> Select the Startup Parameters.

            h.  Old Entry will be

                        -dC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\master.mdf;
                        -eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ERRORLOG;
                        -lC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mastlog.ldf

                        Replace to

                        -dD:\System DB\master.mdf;
                        -eD:\System DB\ERRORLOG;
                        -lD:\System DB\mastlog.ldf

            i. goto cmd prompt and Type Sqlservr -f -T3608 and run          

            j.Run this Query
                        SELECT name, physical_name AS CurrentLocation, state_desc
            FROM sys.master_files
            WHERE database_id = DB_ID(N'master')                       
                        This will provide the current location of your master.

            k. Press Ctrl + c in your opened cmd prompt . it will ask Do you wish to Shutdown the SQL Server <Y/N>

            l.  Press Y . then the SQL Server will Shut down

            m.  Goto the SQL Server Configuration Manager  and Restart the MSSQL server.

5    Moving mssqlsystemresource DB
           
            a.  Goto SQL Server Configuration Manager and open.

            b.  Select the SQL Sever 2005 Services.

            c.  Right click the SQL Server Service and Stop.
           
            d. Move the mssqlsystemresource DB files (.mdf & .ldf) file the Destination Folder
                        Ex : C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data  ---> D:\System DB

            e.  Open a Command prompt ( Run -> cmd)

            f.  Go to the SQLserver installation path ie C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn

            g.  Type Sqlservr -c -m -T3608 and run.( This will Start the SQL Server in Single user mode).

            h.  Open the Microsoft SQLServer Management Studio ( While opening it will ask the username and pwd..
                 Dont give any details, Just open SSMS)

            i.  Select the New Query Option  ( Now you give the Username and password)   .

            j.  Run this Query
                       
                        use master
            ALTER DATABASE mssqlsystemresource
            MODIFY FILE (NAME=data, FILENAME= 'D:\System DB\mssqlsystemresource.mdf');
            GO
            ALTER DATABASE mssqlsystemresource
            MODIFY FILE (NAME=log, FILENAME= 'D:\System DB\mssqlsystemresource.ldf');    
            GO

            k. Press Ctrl + c in your opened cmd prompt . it will ask Do you wish to Shutdown the SQL Server <Y/N>

            l.  Press Y . then the SQL Server will Shut down

            m.  Goto the SQL Server Configuration Manager  and Restart the MSSQL server.

6    Moving TEMPDB

            a.  Goto SQL Server Configuration Manager and open.

            b.  Select the SQL Sever 2005 Services.

            c.  Right click the SQL Server Service and Stop.

            d.  Open a Command prompt ( Run -> cmd)

            e.  Go to the SQLserver installation path ie C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn

            f.  Type Sqlservr -c -m -T3608 and run.( This will Start the SQL Server in Single user mode).

            g.  Open the Microsoft SQLServer Management Studio ( While opening it will ask the username and pwd..
                 Dont give any details, Just open SSMS)

            h.  Select the New Query Option ( Now you give the Username and password)   .

            i.  Run this Query
                       
                        USE master;
            GO
            ALTER DATABASE tempdb
            MODIFY FILE (NAME = tempdev, FILENAME = 'D:\System DB\tempdb.mdf');
            GO
            ALTER DATABASE tempdb
            MODIFY FILE (NAME = templog, FILENAME = 'D:\System DB\templog.ldf');
            GO
            j. Press Ctrl + c in your opened cmd prompt . it will ask Do you wish to Shutdown the SQL Server <Y/N>

            k.  Press Y . then the SQL Server will Shut down

            l.  Goto the SQL Server Configuration Manager  and Restart the MSSQL server.

            m. Run this Query

                          SELECT name, physical_name AS CurrentLocation, state_desc
             FROM sys.master_files
             WHERE database_id = DB_ID(N'tempdb')

            ( I am Sure now the TEMPDB is moved to Destination Place)