What is a simple command line program or script to backup SQL server databases?

Sql Server Command Line Scripting Backup

Sql Server Problem Overview

I've been too lax with performing DB backups on our internal servers.

Is there a simple command line program that I can use to backup certain databases in SQL Server 2005? Or is there a simple VBScript?

Sql Server Solutions

Solution 1 - Sql Server

To backup a single database from the command line, use osql or sqlcmd.

"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\osql.exe" 
    -E -Q "BACKUP DATABASE mydatabase TO DISK='C:\tmp\db.bak' WITH FORMAT"

You'll also want to read the documentation on BACKUP and RESTORE and general procedures.

Solution 2 - Sql Server

I use ExpressMaint.

To backup all user databases I do for example:

C:\>ExpressMaint.exe -S (local)\sqlexpress -D ALL_USER -T DB -BU HOURS -BV 1 -B c:\backupdir\ -DS

Solution 3 - Sql Server

Schedule the following to backup all Databases:

Use Master

Declare @ToExecute VarChar(8000)

Select @ToExecute = Coalesce(@ToExecute + 'Backup Database ' + [Name] + ' To Disk =     ''D:\Backups\Databases\' + [Name]   + '.bak'' With Format;' + char(13),'')
From
Master..Sysdatabases
Where
[Name] Not In ('tempdb')
and databasepropertyex ([Name],'Status') = 'online'

Execute(@ToExecute)

There are also more details on my blog: how to Automate SQL Server Express Backups.

Solution 4 - Sql Server

I found this on a Microsoft Support page http://support.microsoft.com/kb/2019698.

It works great! And since it came from Microsoft, I feel like it's pretty legit.

Basically there are two steps.

Create a stored procedure in your master db. See msft link or if it's broken try here: http://pastebin.com/svRLkqnq

Schedule the backup from your task scheduler. You might want to put into a .bat or .cmd file first and then schedule that file.

sqlcmd -S YOUR_SERVER_NAME\SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation='C:\SQL_Backup\', @backupType='F'"  1>c:\SQL_Backup\backup.log

Obviously replace YOUR_SERVER_NAME with your computer name or optionally try .\SQLEXPRESS and make sure the backup folder exists. In this case it's trying to put it into c:\SQL_Backup

Solution 5 - Sql Server

You can use the backup application by ApexSQL. Although it’s a GUI application, it has all its features supported in CLI. It is possible to either perform one-time backup operations, or to create a job that would back up specified databases on the regular basis. You can check the switch rules and exampled in the articles:

Solution 6 - Sql Server

I'm using tsql on a Linux/UNIX infrastructure to access MSSQL databases. Here's a simple shell script to dump a table to a file:

#!/usr/bin/ksh
#
#.....
(
tsql -S {database} -U {user} -P {password} <<EOF
select * from {table}
go
quit
EOF
) >{output_file.dump}

Solution 7 - Sql Server

Eventual if you don't have a trusted connection as the –E switch declares

Use following command line

"[program dir]\[sql server version]\Tools\Binn\osql.exe" -Q "BACKUP DATABASE mydatabase TO DISK='C:\tmp\db.bak'" -S [server] –U [login id] -P [password]

Where

[program dir] is the directory where the osql.exe exists

On 32bit OS c:\Program Files\Microsoft SQL Server</pre>
On 64bit OS c:\Program Files (x86)\Microsoft SQL Server</pre>
[sql server version] your sql server version 110 or 100 or 90 or 80 begin with the largest number
[server] your servername or server ip
[login id] your ms-sql server user login name
[password] the required login password

Solution 8 - Sql Server

Microsoft's answer to backing up all user databases on SQL Express is here:

The process is: copy, paste, and execute their code (see below. I've commented some oddly non-commented lines at the top) as a query on your database server. That means you should first install the SQL Server Management Studio (or otherwise connect to your database server with SSMS). This code execution will create a stored procedure on your database server.

Create a batch file to execute the stored procedure, then use Task Scheduler to schedule a periodic (e.g. nightly) run of this batch file. My code (that works) is a slightly modified version of their first example:

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

This worked for me, and I like it. Each time you run it, new backup files are created. You'll need to devise a method of deleting old backup files on a routine basis. I already have a routine that does that sort of thing, so I'll keep a couple of days' worth of backups on disk (long enough for them to get backed up by my normal backup routine), then I'll delete them. In other words, I'll always have a few days' worth of backups on hand without having to restore from my backup system.

I'll paste Microsoft's stored procedure creation script below:

--// Copyright © Microsoft Corporation.  All Rights Reserved.
--// This code released under the terms of the
--// Microsoft Public License (MS-PL, http://opensource.org/licenses/ms-pl.html.)

USE [master] 
GO 

/****** Object:  StoredProcedure [dbo].[sp_BackupDatabases] ******/ 

SET ANSI_NULLS ON 
GO 

SET QUOTED_IDENTIFIER ON 
GO 

 
-- ============================================= 
-- Author: Microsoft 
-- Create date: 2010-02-06
-- Description: Backup Databases for SQLExpress
-- Parameter1: databaseName 
-- Parameter2: backupType F=full, D=differential, L=log
-- Parameter3: backup file location
-- =============================================

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)
            )
           
             -- Pick out only databases which are online in case ALL databases are chosen to be backed up

             -- If specific database is chosen to be backed up only pick that out from @DBs

            INSERT INTO @DBs (DBNAME)
            SELECT Name FROM master.sys.databases
            where state=0
            AND name=@DatabaseName
            OR @DatabaseName IS NULL
            ORDER BY Name

 
           -- Filter out databases which do not need to backed up
 
           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 variables

            DECLARE @BackupName varchar(100)
            DECLARE @BackupFile varchar(100)
            DECLARE @DBNAME varchar(300)
            DECLARE @sqlCommand NVARCHAR(1000) 
        DECLARE @dateTime NVARCHAR(20)
            DECLARE @Loop int                  
                       
            -- Loop through the databases one by one

            SELECT @Loop = min(ID) FROM @DBs
       WHILE @Loop IS NOT NULL
      BEGIN
 
-- Database Names have to be in [dbname] format since some have - or _ in their name

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


-- Set the current date and time n yyyyhhmmss format

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

-- Create backup filename in path\filename.extension format for full,diff and log backups

      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'
 

-- Provide the backup a name for storing in the media

      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


-- Generate the dynamic SQL command to be executed

       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
 

-- Execute the generated SQL command

       EXEC(@sqlCommand)

 
-- Goto the next database

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

END

Solution 9 - Sql Server

Here is an example one, it will take backup database, compress using 7zip and delete backup file so issue related to storage also solved. In this example I use 7zip, which is free

@echo off

CLS

echo Running dump ...

sqlcmd -S SERVER\SQLEXPRESS -U username -P password -Q "BACKUP DATABASE master TO DISK='D:\DailyDBBackup\DB_master_%date:~-10,2%%date:~-7,2%%date:~-4,4%.bak'"

echo Zipping ...

"C:\Program Files\7-Zip\7z.exe" a -tzip "D:\DailyDBBackup\DB_master_%date:~-10,2%%date:~-7,2%%date:~-4,4%_%time:~0,2%%time:~3,2%%time:~6,2%.bak.zip" "D:\DailyDBBackup\DB_master_%date:~-10,2%%date:~-7,2%%date:~-4,4%.bak"

echo Deleting the SQL file ...

del "D:\DailyDBBackup\DB_master_%date:~-10,2%%date:~-7,2%%date:~-4,4%.bak"

echo Done!

Save this as sqlbackup.bat and schedule it to be run everyday.

If you just want to take backup only then you can create script without zipping and deleting.

Solution 10 - Sql Server

You could use a VB Script I wrote exactly for this purpose: https://github.com/ezrarieben/mssql-backup-vbs/

Schedule a task in the "Task Scheduler" to execute the script as you like and it'll backup the entire DB to a BAK file and save it wherever you specify.

Solution 11 - Sql Server

SET NOCOUNT ON;
declare @PATH VARCHAR(200)='D:\MyBackupFolder\'
 -- path where you want to take backups
IF OBJECT_ID('TEMPDB..#back') IS NOT NULL

DROP TABLE #back

CREATE TABLE #back
(
RN INT IDENTITY (1,1),
DatabaseName NVARCHAR(200)

)

INSERT INTO #back 
SELECT       'MyDatabase1'
UNION SELECT 'MyDatabase2'
UNION SELECT 'MyDatabase3'
UNION SELECT 'MyDatabase4'

-- your databases List

DECLARE @COUNT INT =0 ,  @RN INT =1, @SCRIPT NVARCHAR(MAX)='',  @DBNAME VARCHAR(200)

PRINT '---------------------FULL BACKUP SCRIPT-------------------------'+CHAR(10)
SET @COUNT = (SELECT COUNT(*) FROM #back)
PRINT 'USE MASTER'+CHAR(10)
WHILE(@COUNT > = @RN)
BEGIN

SET @DBNAME =(SELECT DatabaseName FROM #back WHERE RN=@RN)
SET @SCRIPT ='BACKUP DATABASE ' +'['+@DBNAME+']'+CHAR(10)+'TO DISK =N'''+@PATH+@DBNAME+ N'_Backup_'
+ REPLACE ( REPLACE ( REPLACE ( REPLACE ( CAST ( CAST ( GETDATE () AS DATETIME2 ) AS VARCHAR ( 100 )), '-' , '_' ), ' ' , '_' ), '.' , '_' ), ':' , '' )+'.bak'''+CHAR(10)+'WITH COMPRESSION, STATS = 10'+CHAR(10)+'GO'+CHAR(10)
PRINT @SCRIPT
SET @RN=@RN+1
END
 
 PRINT '---------------------DIFF BACKUP SCRIPT-------------------------'+CHAR(10)

  SET  @COUNT  =0 SET  @RN  =1 SET @SCRIPT ='' SET @DBNAME =''
 SET @COUNT = (SELECT COUNT(*) FROM #back)
PRINT 'USE MASTER'+CHAR(10)
WHILE(@COUNT > = @RN)
BEGIN
SET @DBNAME =(SELECT DatabaseName FROM #back WHERE RN=@RN)
SET @SCRIPT ='BACKUP DATABASE ' +'['+@DBNAME+']'+CHAR(10)+'TO DISK =N'''+@PATH+@DBNAME+ N'_Backup_'
+ REPLACE ( REPLACE ( REPLACE ( REPLACE ( CAST ( CAST ( GETDATE () AS DATETIME2 ) AS VARCHAR ( 100 )), '-' , '_' ), ' ' , '_' ), '.' , '_' ), ':' , '' )+'.diff'''+CHAR(10)+'WITH DIFFERENTIAL, COMPRESSION, STATS = 10'+CHAR(10)+'GO'+CHAR(10)
PRINT @SCRIPT
SET @RN=@RN+1
END

Solution 12 - Sql Server

This can be helpful when you are dealing with dockerised mssql container in your day to day work and want to take a quick dump of the data from table. I have specially found it useful when you are re-building the db container quite frequently and don't want to loose the test data after the re-build.

Export data using bcp utility
/opt/mssql-tools/bin/bcp <Table_Name> out /tmp/MyData.bcp -d <database_name> -c -U <user_name> -P "<password>" -S <server_name>

Import data using bcp utility

/opt/mssql-tools/bin/bcp <Table_Name> IN /tmp/MyData.bcp -d <database_name> -c -U <user_name> -P "<password>" -S <server_name>

Solution 13 - Sql Server

If you can find the DB files... "cp DBFiles backup/"

Almost for sure not advisable in most cases, but it's simple as all getup.

Content Type	Original Author	Original Content on Stackoverflow
Question	Frank Krueger	View Question on Stackoverflow
Solution 1 - Sql Server	Craig Trader	View Answer on Stackoverflow
Solution 2 - Sql Server	Martin Meixger	View Answer on Stackoverflow
Solution 3 - Sql Server	GateKiller	View Answer on Stackoverflow
Solution 4 - Sql Server	John W.	View Answer on Stackoverflow
Solution 5 - Sql Server	P.Thompson	View Answer on Stackoverflow
Solution 6 - Sql Server	Ira C	View Answer on Stackoverflow
Solution 7 - Sql Server	George Vrynios	View Answer on Stackoverflow
Solution 8 - Sql Server	CraigD	View Answer on Stackoverflow
Solution 9 - Sql Server	Bhadresh Patel	View Answer on Stackoverflow
Solution 10 - Sql Server	ezrarieben	View Answer on Stackoverflow
Solution 11 - Sql Server	Sagar Mahajan	View Answer on Stackoverflow
Solution 12 - Sql Server	Ashutosh Ranjan	View Answer on Stackoverflow
Solution 13 - Sql Server	BCS	View Answer on Stackoverflow