什么是备份 SQL 服务器数据库的简单命令行程序或脚本？

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.

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.

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.

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

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}

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.

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

2. 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

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\

On 64bit OS c:\Program Files (x86)\Microsoft SQL Server\

[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

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:

• ApexSQL Backup CLI support
• ApexSQL Backup CLI examples

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.

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

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​

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>

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.