blocks|key|1746450|text|要从命令行备份单个数据库，请使用osql或sqlcmd。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1746451|"C:\Program+Files\Microsoft+SQL+Server\90\Tools\Binn\osql.exe"+
++++-E+-Q+"BACKUP+DATABASE+mydatabase+TO+DISK='C:\tmp\db.bak'+WITH+FORMAT"|code-block|syntax|javascript|1746452|您还需要阅读有关BACKUP、RESTORE和general+procedures的文档。|1746453|entityMap|0|LINK|mutability|MUTABLE|url|https://msdn.microsoft.com/en-us/library/ms162806.aspx|1|https://msdn.microsoft.com/en-us/library/ms162773.aspx|2|https://msdn.microsoft.com/en-us/library/ms186865.aspx|3|https://msdn.microsoft.com/en-us/library/ms190372.aspx|4|https://msdn.microsoft.com/en-us/library/ms187048.aspx^0|G|4|0|L|6|1|0|0|8|6|2|F|7|3|N|I|4|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@$A|11|B|12|1|13]|$A|14|B|15|1|16]]|C|$]]|$1|D|3|E|5|F|7|17|8|@]|9|@]|C|$G|H]]|$1|I|3|J|5|6|7|18|8|@]|9|@$A|19|B|1A|1|1B]|$A|1C|B|1D|1|1E]|$A|1F|B|1G|1|1H]]|C|$]]|$1|K|3|-4|5|6|7|1I|8|@]|9|@]|C|$]]]|L|$M|$5|N|O|P|C|$Q|R]]|S|$5|N|O|P|C|$Q|T]]|U|$5|N|O|P|C|$Q|V]]|W|$5|N|O|P|C|$Q|X]]|Y|$5|N|O|P|C|$Q|Z]]]]

To backup a single database from the command line, use <a href="https://msdn.microsoft.com/en-us/library/ms162806.aspx" rel="noreferrer">osql</a> or <a href="https://msdn.microsoft.com/en-us/library/ms162773.aspx" rel="noreferrer">sqlcmd</a>.

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

You'll also want to read the documentation on <a href="https://msdn.microsoft.com/en-us/library/ms186865.aspx" rel="noreferrer">BACKUP</a> and <a href="https://msdn.microsoft.com/en-us/library/ms190372.aspx" rel="noreferrer">RESTORE</a> and <a href="https://msdn.microsoft.com/en-us/library/ms187048.aspx" rel="noreferrer">general procedures</a>.

blocks|key|1531501|text|我使用ExpressMaint。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1531502|要备份我执行的所有用户数据库，例如：|1531503|C:\>ExpressMaint.exe+-S+(local)\sqlexpress+-D+ALL_USER+-T+DB+-BU+HOURS+-BV+1+-B+c:\backupdir\+-DS|code-block|syntax|javascript|1531504|entityMap|0|LINK|mutability|MUTABLE|url|http://www.codeplex.com/ExpressMaint^0|3|C|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@$A|T|B|U|1|V]]|C|$]]|$1|D|3|E|5|6|7|W|8|@]|9|@]|C|$]]|$1|F|3|G|5|H|7|X|8|@]|9|@]|C|$I|J]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|C|$]]]|L|$M|$5|N|O|P|C|$Q|R]]]]

I use <a href="http://www.codeplex.com/ExpressMaint" rel="nofollow noreferrer">ExpressMaint</a>.

To backup all user databases I do for example:

<pre><code>C:\&gt;ExpressMaint.exe -S (local)\sqlexpress -D ALL_USER -T DB -BU HOURS -BV 1 -B c:\backupdir\ -DS
</code></pre>

blocks|key|1531439|text|计划执行以下操作以备份所有数据库：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1531440|Use+Master

Declare+@ToExecute+VarChar(8000)

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

Execute(@ToExecute)|code-block|syntax|javascript|1531441|在我的博客上还有更多的细节:+how+to+Automate+SQL+Server+Express+Backups。|offset|length|1531442|entityMap|0|LINK|mutability|MUTABLE|url|http://gatekiller.co.uk/Post/Automate_SQL_Server_Express_Backups^0|0|0|M|Z|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@$I|V|J|W|1|X]]|A|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|R]]]]

Schedule the following to backup all Databases:

<pre><code>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)
</code></pre>

There are also more details on my blog: how to <a href="http://gatekiller.co.uk/Post/Automate_SQL_Server_Express_Backups" rel="noreferrer">Automate SQL Server Express Backups</a>.

blocks|key|1746534|text|我在Linux/UNIX基础设施上使用tsql来访问MSSQL数据库。下面是一个将表转储到文件的简单shell脚本：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1746535|#!/usr/bin/ksh
#
#.....
(
tsql+-S+{database}+-U+{user}+-P+{password}+<<EOF
select+*+from+{table}
go
quit
EOF
)+>{output_file.dump}|code-block|syntax|javascript|1746536|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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:

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

blocks|key|1531651|text|对于备份SQL+Express上的所有用户数据库，微软的答案是here|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1531652|这个过程是:复制、粘贴和执行他们的代码(见下文。我在顶部注释了一些奇怪的未注释行)作为数据库服务器上的查询。这意味着您应该首先安装SQL+Server+Management+Studio+(或者使用SSMS连接到数据库服务器)。此代码执行将在数据库服务器上创建一个存储过程。|1531653|创建一个批处理文件来执行存储过程，然后使用Task+Scheduler计划此批处理文件的定期(例如，夜间)运行。我的代码(可以工作)是他们第一个示例的稍微修改过的版本：|1531654|sqlcmd+-S+.\SQLEXPRESS+-E+-Q+"EXEC+sp_BackupDatabases+@backupLocation='E:\SQLBackups\',+@backupType='F'"+|code-block|syntax|javascript|1531655|这对我很有效，我很喜欢。每次运行它时，都会创建新的备份文件。您需要设计一种方法来定期删除旧的备份文件。我已经有一个做这类事情的例程，所以我将在磁盘上保留几天的备份(足够我的正常备份例程备份它们)，然后我将删除它们。换句话说，我手头总是有几天的备份，而不必从我的备份系统进行恢复。|1531656|我将把Microsoft的存储过程创建脚本粘贴到下面：|1531657|--//+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+=+'['%2B(SELECT+DBNAME+FROM+@DBs+WHERE+ID+=+@Loop)%2B']'


--+Set+the+current+date+and+time+n+yyyyhhmmss+format

++++++SET+@dateTime+=+REPLACE(CONVERT(VARCHAR,+GETDATE(),101),'/','')+%2B+'_'+%2B++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%2BREPLACE(REPLACE(@DBNAME,+'[',''),']','')%2B+'_FULL_'%2B+@dateTime%2B+'.BAK'
++++++ELSE+IF+@backupType+=+'D'
++++++++++++SET+@BackupFile+=+@backupLocation%2BREPLACE(REPLACE(@DBNAME,+'[',''),']','')%2B+'_DIFF_'%2B+@dateTime%2B+'.BAK'
++++++ELSE+IF+@backupType+=+'L'
++++++++++++SET+@BackupFile+=+@backupLocation%2BREPLACE(REPLACE(@DBNAME,+'[',''),']','')%2B+'_LOG_'%2B+@dateTime%2B+'.TRN'
+

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

++++++IF+@backupType+=+'F'
++++++++++++SET+@BackupName+=+REPLACE(REPLACE(@DBNAME,'[',''),']','')+%2B'+full+backup+for+'%2B+@dateTime

++++++IF+@backupType+=+'D'
++++++++++++SET+@BackupName+=+REPLACE(REPLACE(@DBNAME,'[',''),']','')+%2B'+differential+backup+for+'%2B+@dateTime

++++++IF+@backupType+=+'L'
++++++++++++SET+@BackupName+=+REPLACE(REPLACE(@DBNAME,'[',''),']','')+%2B'+log+backup+for+'%2B+@dateTime


--+Generate+the+dynamic+SQL+command+to+be+executed

+++++++IF+@backupType+=+'F'+
++++++++++++++++++BEGIN
+++++++++++++++SET+@sqlCommand+=+'BACKUP+DATABASE+'+%2B@DBNAME%2B++'+TO+DISK+=+'''%2B@BackupFile%2B+'''+WITH+INIT,+NAME=+'''+%2B@BackupName%2B''',+NOSKIP,+NOFORMAT'
++++++++++++++++++END

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

+++++++IF+@backupType+=+'L'+
++++++++++++++++++BEGIN
+++++++++++++++SET+@sqlCommand+=+'BACKUP+LOG+'+%2B@DBNAME%2B++'+TO+DISK+=+'''%2B@BackupFile%2B+'''+WITH+INIT,+NAME=+'''+%2B@BackupName%2B''',+NOSKIP,+NOFORMAT'++++++++
++++++++++++++++++END
+

--+Execute+the+generated+SQL+command

+++++++EXEC(@sqlCommand)

+
--+Goto+the+next+database

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

END​|1531658|entityMap|0|LINK|mutability|MUTABLE|url|https://support.microsoft.com/en-us/help/2019698/how-to-schedule-and-automate-backups-of-sql-server-databases-in-sql-se^0|V|4|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@$A|11|B|12|1|13]]|C|$]]|$1|D|3|E|5|6|7|14|8|@]|9|@]|C|$]]|$1|F|3|G|5|6|7|15|8|@]|9|@]|C|$]]|$1|H|3|I|5|J|7|16|8|@]|9|@]|C|$K|L]]|$1|M|3|N|5|6|7|17|8|@]|9|@]|C|$]]|$1|O|3|P|5|6|7|18|8|@]|9|@]|C|$]]|$1|Q|3|R|5|J|7|19|8|@]|9|@]|C|$K|L]]|$1|S|3|-4|5|6|7|1A|8|@]|9|@]|C|$]]]|T|$U|$5|V|W|X|C|$Y|Z]]]]

Microsoft's answer to backing up all user databases on SQL Express is <a href="https://support.microsoft.com/en-us/help/2019698/how-to-schedule-and-automate-backups-of-sql-server-databases-in-sql-se" rel="nofollow noreferrer">here</a>:
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:
<pre><code>sqlcmd -S .\SQLEXPRESS -E -Q &quot;EXEC sp_BackupDatabases @backupLocation='E:\SQLBackups\', @backupType='F'&quot; 
</code></pre>
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:
<pre><code>--// 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&gt;@Loop
 

END​
</code></pre>

blocks|key|1746597|text|您可以使用我为此目的编写的VB脚本：https://github.com/ezrarieben/mssql-backup-vbs/|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1746598|在"Task+Scheduler“中安排一个任务来执行脚本，它会将整个数据库备份到一个BAK文件中，并将其保存到您指定的任何位置。|1746599|entityMap|0|LINK|mutability|MUTABLE|url|https://github.com/ezrarieben/mssql-backup-vbs/^0|I|1B|0|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@$A|O|B|P|1|Q]]|C|$]]|$1|D|3|E|5|6|7|R|8|@]|9|@]|C|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|C|$]]]|G|$H|$5|I|J|K|C|$L|M]]]]

You could use a VB Script I wrote exactly for this purpose:
<a href="https://github.com/ezrarieben/mssql-backup-vbs/" rel="nofollow noreferrer">https://github.com/ezrarieben/mssql-backup-vbs/</a>

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.

blocks|key|1746604|text|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-------------------------'%2BCHAR(10)
SET+@COUNT+=+(SELECT+COUNT(*)+FROM+#back)
PRINT+'USE+MASTER'%2BCHAR(10)
WHILE(@COUNT+>+=+@RN)
BEGIN

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

+PRINT+'---------------------DIFF+BACKUP+SCRIPT-------------------------'%2BCHAR(10)

++SET++@COUNT++=0+SET++@RN++=1+SET+@SCRIPT+=''+SET+@DBNAME+=''
+SET+@COUNT+=+(SELECT+COUNT(*)+FROM+#back)
PRINT+'USE+MASTER'%2BCHAR(10)
WHILE(@COUNT+>+=+@RN)
BEGIN
SET+@DBNAME+=(SELECT+DatabaseName+FROM+#back+WHERE+RN=@RN)
SET+@SCRIPT+='BACKUP+DATABASE+'+%2B'['%2B@DBNAME%2B']'%2BCHAR(10)%2B'TO+DISK+=N'''%2B@PATH%2B@DBNAME%2B+N'_Backup_'
%2B+REPLACE+(+REPLACE+(+REPLACE+(+REPLACE+(+CAST+(+CAST+(+GETDATE+()+AS+DATETIME2+)+AS+VARCHAR+(+100+)),+'-'+,+'_'+),+'+'+,+'_'+),+'.'+,+'_'+),+':'+,+''+)%2B'.diff'''%2BCHAR(10)%2B'WITH+DIFFERENTIAL,+COMPRESSION,+STATS+=+10'%2BCHAR(10)%2B'GO'%2BCHAR(10)
PRINT+@SCRIPT
SET+@RN=@RN%2B1
END|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1746605|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>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 &gt; = @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 &gt; = @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
</code></pre>

blocks|key|1746719|text|当您在日常工作中处理停靠的mssql容器，并希望从表中快速转储数据时，这可能会很有帮助。当您频繁地重新构建数据库容器，并且不想在重新构建之后丢失测试数据时，我特别发现它很有用。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1746720|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>|code-block|syntax|javascript|1746721|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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.
<pre><code>Export data using bcp utility
/opt/mssql-tools/bin/bcp &lt;Table_Name&gt; out /tmp/MyData.bcp -d &lt;database_name&gt; -c -U &lt;user_name&gt; -P &quot;&lt;password&gt;&quot; -S &lt;server_name&gt;

Import data using bcp utility

/opt/mssql-tools/bin/bcp &lt;Table_Name&gt; IN /tmp/MyData.bcp -d &lt;database_name&gt; -c -U &lt;user_name&gt; -P &quot;&lt;password&gt;&quot; -S &lt;server_name&gt;
</code></pre>

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?

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

SQL数据库

SQL Server

数据库

服务器

容器

Linux

在我们的内部服务器上执行数据库备份时，我太松懈了。在SQL Server2005中，有没有简单的命令行程序可以用来备份某些数据库？或者有一个简单的VBScript？

问备份SQL server数据库的简单命令行程序或脚本是什么？
EN

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问备份SQL server数据库的简单命令行程序或脚本是什么？EN

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问备份SQL server数据库的简单命令行程序或脚本是什么？
EN