August 2023 – Database Consultant

I have only had to do this a few times in a VM but want to capture the steps. Note that with Azure VM – The New datafiles will be the same size as the Old datafiles.

/* MovingTempDB.sql */
 
/* 1 save the current catalog paths to "rollback" (just-in-case) */
 
    USE master;
    GO
	select space(4) + 'USE master;' cmd
	union
	select space(4) + 'GO'
	union
    SELECT space(4) + 'ALTER DATABASE tempdb MODIFY FILE (NAME = [' + name + '], FILENAME = [' + physical_name + ']);'
    FROM sys.master_files 
    WHERE database_id = DB_ID(N'TempDB')
    ORDER BY cmd desc;

/* 2 manually create the "new" destination folder(s) */
  
/* 3 change the catalog */
 
    USE master;
    GO
    ALTER DATABASE tempdb MODIFY FILE (NAME = [tempdev], FILENAME = [D:\TempDB\tempdb.mdf]);
    ALTER DATABASE tempdb MODIFY FILE (NAME = [temp2], FILENAME = [D:\TempDB\tempdb_2.ndf]);
    ALTER DATABASE tempdb MODIFY FILE (NAME = [temp3], FILENAME = [D:\TempDB\tempdb_3.ndf]);
    ALTER DATABASE tempdb MODIFY FILE (NAME = [temp4], FILENAME = [D:\TempDB\tempdb_4.ndf]);
    ALTER DATABASE tempdb MODIFY FILE (NAME = [temp5], FILENAME = [D:\TempDB\tempdb_5.ndf]);
    ALTER DATABASE tempdb MODIFY FILE (NAME = [temp6], FILENAME = [D:\TempDB\tempdb_6.ndf]);
    ALTER DATABASE tempdb MODIFY FILE (NAME = [temp7], FILENAME = [D:\TempDB\tempdb_7.ndf]);
    ALTER DATABASE tempdb MODIFY FILE (NAME = [temp8], FILENAME = [D:\TempDB\tempdb_8.ndf]);
 
    /* confirmation message
    "The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started." */
   
/* 4 manually reboot the server */
 
/* 5 manually check the new files have been created */
  
/* 6 manually delete old files, and empty the recycle-bin */

/* 7 ideally pre-grow all tempdb data files */

/* x rollback if needed */

    USE master;
    GO
	ALTER DATABASE tempdb MODIFY FILE (NAME = [templog], FILENAME = [F:\SQLLogs\templog.ldf]);
	ALTER DATABASE tempdb MODIFY FILE (NAME = [tempdev], FILENAME = [H:\TempDB\tempdb.mdf]);
	ALTER DATABASE tempdb MODIFY FILE (NAME = [temp2], FILENAME = [H:\TempDB\tempdb_2.ndf]);
	ALTER DATABASE tempdb MODIFY FILE (NAME = [temp3], FILENAME = [H:\TempDB\tempdb_3.ndf]);
	ALTER DATABASE tempdb MODIFY FILE (NAME = [temp4], FILENAME = [H:\TempDB\tempdb_4.ndf]);
	ALTER DATABASE tempdb MODIFY FILE (NAME = [temp5], FILENAME = [H:\TempDB\tempdb_5.ndf]);
	ALTER DATABASE tempdb MODIFY FILE (NAME = [temp6], FILENAME = [H:\TempDB\tempdb_6.ndf]);
	ALTER DATABASE tempdb MODIFY FILE (NAME = [temp7], FILENAME = [H:\TempDB\tempdb_7.ndf]);
	ALTER DATABASE tempdb MODIFY FILE (NAME = [temp8], FILENAME = [H:\TempDB\tempdb_8.ndf]);

--AzureDatabaseBackups.sql SELECT @@SERVERNAME server_name, DB_NAME() database_name, backup_start_date, backup_finish_date, CASE backup_type WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Transaction Log' ELSE '' END backup_type FROM sys.dm_database_backups WHERE in_retention = 1 /* Available */ ORDER BY backup_start_date DESC;

Month: August 2023

Moving TempDB

Azure database backups

Expensive Spaghetti