Home/ Groups/MS SQL

Group rules

Full backups of all databases in the local named instance of SQLEXPRESS by using Windows Authentication Example 1: Full backups of See more

Full backups of all databases in the local named instance of SQLEXPRESS by using Windows Authentication

Example 1: Full backups of all databases in the local named instance of SQLEXPRESS by using Windows Authentication

sqlcmd -S .\SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @backupType='F'"

Example 2: Differential backups of all databases in the local named instance of SQLEXPRESS by using a SQLLogin and its password

sqlcmd -U <YourSQLLogin> -P <StrongPassword> -S .\SQLEXPRESS -Q "EXEC sp_BackupDatabases @backupLocation ='D:\SQLBackups', @BackupType='D'"

Example 3: Log backups of all databases in the local named instance of SQLEXPRESS by using Windows Authentication

sqlcmd -S .\SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\',@backupType='L'"

Example 4: Full backups of the database USERDB in the local named instance of SQLEXPRESS by using Windows Authentication

sqlcmd -S .\SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\',@backupType='F'"

Create a Full Database Backup MS Sql Sever Script

How to create a scheduled backup in SQL Express

You have to follow these four steps to back up your SQL Server databases by using Windows Task Scheduler:

Step 1: Create a stored procedure to back up your databases

Connect to your SQL express instance and create sp_BackupDatabases stored procedure in your master database using the script at the following location:

Step 2: Download the SQLCMD client utility

The sqlcmd utility lets you enter Transact-SQL statements, system procedures, and script files. In SQL Server 2014 and lower versions, the utility is shipped as part of the product. Starting with SQL Server 2016, sqlcmd utility is offered as a separate download. For more information, review sqlcmd Utility.

USE [master]  
GO  


SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  


CREATE PROCEDURE [dbo].[sp_BackupDatabases]   
            @databaseName sysname = null, 
            @backupType CHAR(1), 
            @backupLocation nvarchar(200)  
AS  
       SET NOCOUNT ON;  
            DECLARE @DBs TABLE 
            ( 
                  ID int IDENTITY PRIMARY KEY, 
                  DBNAME nvarchar(500) 
            ) 
            
            INSERT INTO @DBs (DBNAME) 
            SELECT Name FROM master.sys.databases 
            where state=0 
            AND name= ISNULL(@databaseName ,name)
            ORDER BY Name
           
            IF @backupType='F' 
                  BEGIN 
                  DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','AdventureWorks') 
                  END 
            ELSE IF @backupType='D' 
                  BEGIN 
                  DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks') 
                  END 
            ELSE IF @backupType='L' 
                  BEGIN 
                  DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks') 
                  END 
            ELSE 
                  BEGIN 
                  RETURN 
                  END 
           
            DECLARE @BackupName nvarchar(100) 
            DECLARE @BackupFile nvarchar(300) 
            DECLARE @DBNAME nvarchar(300) 
            DECLARE @sqlCommand NVARCHAR(1000)  
        DECLARE @dateTime NVARCHAR(20) 
            DECLARE @Loop int                   
           
            SELECT @Loop = min(ID) FROM @DBs 
      WHILE @Loop IS NOT NULL 
      BEGIN 


      SET @DBNAME = '['+(SELECT DBNAME FROM @DBs WHERE ID = @Loop)+']' 


      SET @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),101),'/','') + '_' +  REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')   


      IF @backupType = 'F' 
            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_FULL_'+ @dateTime+ '.BAK' 
      ELSE IF @backupType = 'D' 
            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_DIFF_'+ @dateTime+ '.BAK' 
      ELSE IF @backupType = 'L' 
            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_LOG_'+ @dateTime+ '.TRN' 


      IF @backupType = 'F' 
            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' full backup for '+ @dateTime 
      IF @backupType = 'D' 
            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' differential backup for '+ @dateTime 
      IF @backupType = 'L' 
            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' log backup for '+ @dateTime 


       IF @backupType = 'F'  
                  BEGIN 
               SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT' 
                  END 
       IF @backupType = 'D' 
                  BEGIN 
               SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH DIFFERENTIAL, INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'         
                  END 
       IF @backupType = 'L'  
                  BEGIN 
               SET @sqlCommand = 'BACKUP LOG ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'         
                  END 


       EXEC(@sqlCommand) 


SELECT @Loop = min(ID) FROM @DBs where ID>@Loop 
END 

Step 3: Create a batch file using a text editor

In a text editor, create a batch file that is named Sqlbackup.bat, and then copy the text from one of the following examples into that file, depending on your scenario:

  • All the scenarios below use D:\SQLBackups as a place holder. The script needs to be adjusted to the right drive and Backup folder location in your environment.
  • If you are using SQL authentication, ensure that access to the folder is restricted to authorized users as the passwords are stored in clear text.

Clear The SQL Server transaction log

Managing SQL Server Transaction Logs: A Step-by-Step Guide

Effective management of transaction logs is crucial for maintaining the performance and storage efficiency of your SQL Server databases. In this post, we’ll walk through a practical example using the AdventureWorks2008R2 database, covering how to change the recovery model, truncate the log, and shrink the log file.

Why Manage Transaction Logs?

Transaction logs are essential for:

  • Ensuring data integrity and recovery.
  • Tracking all transactions and database modifications.
  • Controlling the amount of disk space consumed.

However, these logs can grow significantly, especially under a FULL recovery model, leading to storage challenges.

USE AdventureWorks2008R2;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks2008R2
SET RECOVERY SIMPLE;
GO explain in points
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks2008R2_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE AdventureWorks2008R2
SET RECOVERY FULL;
GO
  • SIMPLE Model: Truncates log and frees space; no point-in-time recovery.
  • Shrink Operation: Reduces the log file size on disk.
  • FULL Model: Allows for comprehensive recovery options; log management is necessary.
 See less

There are no posts yet.