SQL DBA - Delete OLD Backup files Using TSQL - SQL server 2005

Step 1 :

            USE MASTER

GO

Step 2 :

EXEC SP_CONFIGURE 'show advanced options',1

GO

Step 3 :

EXEC SP_CONFIGURE 'XP_CMDSHELL',1

GO

RECONFIGURE

GO

Step 4:

TSQL TO Delete All the Backup Files

DECLARE @DBbackuppath varchar(8000)

DECLARE @DBName varchar(255)

DECLARE @Command varchar(255)

DECLARE @Backup_finish_date datetime

DECLARE DATABASES_CURSOR CURSOR FOR

SELECT Physical_device_name,Backup_finish_date,database_name FROM msdb.dbo.backupmediafamily

INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id

WHERE msdb..backupset.type = 'D' -- add a condition if u need a condition on Backup_finish_date

OPEN DATABASES_CURSOR

FETCH NEXT FROM DATABASES_CURSOR INTO @DBbackuppath,@Backup_finish_date,@DBName

WHILE @@FETCH_STATUS = 0

BEGIN

      FETCH NEXT FROM DATABASES_CURSOR INTO @DBbackuppath,@Backup_finish_date,@DBName

      set @DBbackuppath = 'del '+@DBbackuppath

      exec master..xp_cmdshell  @DBbackuppath

      END

CLOSE DATABASES_CURSOR

DEALLOCATE DATABASES_CURSOR

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)