Tag: T-SQL

  • T-SQL Stored Procedure to Get the UNC Path of Last Database Backup Location

    [sourcecode language=”text” wraplines=”false”] /* Script to get the last backup file location in the format of a UNC path for specified DB’s Last modified by Nick Xu on 2013-06-17 Usage: exec sp__GetLastDatabackupLocation ‘MDP’ */ use master go if exists (select * from sysobjects where name = ‘sp__GetLastDataBackupLocation’ and type = ‘P’) begin drop proc sp__GetLastDataBackupLocation…

  • T-SQL Script to Delete Older-Than-N-Days Backups in a Specific Folder

    I’ve noticed there are quite some hits on this post and the old format of the T-SQL code looked a bit messy, so I’ve done a makeover of the script on 2014-04-10. This script requires a not-too-strict security setting for sql server’s service account, because it will have to execute Windows batch commands to achieve…

  • T-SQL Script to Quickly Truncate the Log File of a Database

    Sometimes, as a DBA you are requested urgently by either users or your supervisors to quickly truncate a log file. It is not a very good practice first of all and you should have already avoided this situation if you have already standardized you DB’s backup and recovery plan. However besides the above reminder, this…

  • T-SQL Script Using sp_msforeachdb to Change All Databases’ Owner to ‘sa’ Account

    Oftentimes, some DB’s in our inventory are modified or restored or created by different user accounts, which leads to having various DB owners in an instance. And a good ‘best practice’ claimed by Microsoft is to align all DB owners to the ubiquitous ‘sa’ account and here goes the script to do so without offending…