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…
-
T-SQL Script to Return the Service Accounts of SQL Server and SQL Agent Services
/* This script reads out the names of sql server service account and sql server agent service account Author:Ning Xu Date: 2010-09-16 Applied version: 2000, 2005, 2008 */ DECLARE @serviceaccount as varchar(50) EXECUTE master.dbo.xp_instance_regread N’HKEY_LOCAL_MACHINE’, N’SYSTEM\CurrentControlSet\Services\MSSQLSERVER’, N’ObjectName’, @serviceAccount OUTPUT, N’no_output’ SELECT @Serviceaccount as SQLServer_ServiceAccount EXECUTE master.dbo.xp_instance_regread N’HKEY_LOCAL_MACHINE’, N’SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT’, N’ObjectName’, @serviceAccount OUTPUT, N’no_output’ SELECT @serviceaccount as…