MSSQL Shrink / Truncate transaction log

2 min read SQL Transaction logs allow you to restore a database to a specific point in time, and is a great option for a production database, but these logs must be backed up frequently enough to prevent them from filing up. However, you need to back up the transaction log itself, not just the database, if you want to use the Full or Bulk Logged recovery models. This article describes the right way to set this up: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/back-up-a-transaction-log-sql-server?view=sql-server-2017 

If you’ve already taken a full backup of your database, and find yourself in a crunch out of space, the following is a quick way of clearing the transaction log to recover space:

Log into Microsoft SQL Server Management Studio…

SQL Server hide database list from a particular user

< 1 min read One of the things I’ve been searching for is to hide the list of databases a user doesn’t have access to. By default, when a user connects to SQL Server, especially a shared instance, he sees all the databases available on the server, even though he doesn’t have access to view or access any of them. The goal is to only show a user the database(s) he should see. This isn’t fully supported yet by SQL, but there’s a way to solve the problem for the majority of cases. First,…

SQL Full Backup all databases

< 1 min read Backups are one of the most important tasks when maintaining a system, and as the complexity of your environment grows, you need tools to automate tasks. Using the SQL Agent, you can schedule the following stored procedure to back up every database and database log on your server (or modify it to your needs), excluding some stuff you can live without.

CREATE PROCEDURE BackupDatabases
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @name VARCHAR(50) /*database name*/
    DECLARE @dbpath VARCHAR(256) /*path for backup db files*/
    DECLARE @logpath VARCHAR(256) /*path for backup log files*/
    DECLARE @dbName VARCHAR(256) /*filename for backup*/
    DECLARE @logName VARCHAR(256) /*filename for backup*/
    DECLARE @fileDate VARCHAR(20) /*used for file name*/
    SET @dbpath = 'C:Database Backup'
    SET @logpath = 'C:Database Log Backup'
    SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
    DECLARE db_cursor CURSOR FOR
    SELECT name
    FROM master.dbo.sysdatabases
    WHERE name NOT IN ('master','model','msdb','tempdb') /* Exclude any databases you don't want to backup */
    OPEN db_cursor  
    FETCH NEXT FROM db_cursor INTO @name  
    WHILE @@FETCH_STATUS = 0  
    BEGIN  
           SET @dbName = @dbpath + @name + '_' + @fileDate + '.BAK'
           BACKUP DATABASE @name TO DISK = @dbName WITH FORMAT;
           SET @logName = @logpath + @name + '_' + @fileDate + '.BAK'
           BACKUP LOG @name TO DISK = @logName;
           FETCH NEXT FROM db_cursor INTO @name
    END
    CLOSE db_cursor
    DEALLOCATE db_cursor
END
GO

SQL Batch alter all columns

< 1 min read

Sometimes you need to make mass changes to a database, and either add or remove a column, or other table or data manipulations. The script below prints SQL statements you can later execute for whatever you need. It loops through each table in your database, and replaces ? with the table name.

EXEC sp_msforeachtable
'PRINT ALTER TABLE ? ADD LastModified DATETIME NOT NULL DEFAULT GETDATE();
ALTER TABLE ? ADD UserID int NULL;
ALTER TABLE ? ADD Deleted bit NOT NULL DEFAULT 0;'
EXEC sp_msforeachtable
    'PRINT Update ? SET SiteID=1'