将日期添加到 SQL 数据库备份文件名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2410674/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Add date to SQL database backup filename
提问by Cdl56
I'm using the below to backup a db from a SQL job. Can someone tell me how to add the current date to the output filename? Preferably in YYYYMMDD format.
我正在使用以下内容从 SQL 作业备份数据库。有人能告诉我如何将当前日期添加到输出文件名中吗？最好采用 YYYYMMDD 格式。
BACKUP DATABASE [myDB] TO? DISK = N'\myPath\myDB.bak' WITH NOFORMAT, INIT,? NAME = N'myDB', SKIP, REWIND, NOUNLOAD,? STATS = 10
GO
Thanks!
谢谢！
回答by JonH
DECLARE @MyFileName varchar(1000)
SELECT @MyFileName = (SELECT '\ServerToSave\Path\MyDB_' + convert(varchar(500),GetDate(),112) + '.bak')
BACKUP DATABASE [myDB] TO DISK=@MyFileName ...
回答by Juan Carlos Velez
If you want to include the date and time, so you can use:
如果要包含日期和时间，则可以使用：
DECLARE @MyFileName varchar(200)
SELECT @MyFileName='\ServerToSave\Path\MyDB_' + REPLACE(convert(nvarchar(20),GetDate(),120),':','-') + '.bak'
BACKUP DATABASE [myDB] TO DISK=@MyFileName ...
The 120 in the Convertgives you the yyyy-mm-dd hh:mi:ss(24h)
中的 120Convert为您提供 yyyy-mm-dd hh:mi:ss(24h)
The REPLACEfunction is necessary because the filename can not have the :character.
该REPLACE函数是必需的，因为文件名不能有:字符。
回答by CResults
Try this.
尝试这个。
DECLARE @MyFileName varchar(50)
SELECT '\ServerToSave\Path\MyDB_' + convert(nvarchar(20),GetDate(),112) + '.bak'
BACKUP DATABASE [myDB] TO DISK=@MyFileName ...
The 112 in the Convert gives you the YYYYMMDD format
Convert 中的 112 为您提供 YYYYMMDD 格式
回答by Raj More
Use the following
使用以下
DECLARE @BackupFileName varchar(20)
SELECT @BackupFileName = '\ServerName\SharedFolder\DatabaseName_' + CONVERT (VarChar, GetDate(), 112) + '.bak'
BACKUP DATABASE [myDB] TO DISK = @BackupFileName WITH NOFORMAT, INIT, NAME = N'myDB', SKIP, REWIND, NOUNLOAD, STATS = 10
Read up on Cast and Convert here http://msdn.microsoft.com/en-us/library/ms187928.aspx
在这里阅读 Cast 和 Convert http://msdn.microsoft.com/en-us/library/ms187928.aspx
回答by JorgeM
Maybe you want to use windows task, just put the code in a bat file and add to your Windows tasks:
也许你想使用 windows 任务，只需将代码放在一个 bat 文件中并添加到你的 Windows 任务中：
for /f "tokens=2 delims==" %%a in ('wmic OS Get localdatetime /value') do set "dt=%%a"
set "YY=%dt:~2,2%" & set "YYYY=%dt:~0,4%" & set "MM=%dt:~4,2%" & set "DD=%dt:~6,2%"
set "HH=%dt:~8,2%" & set "Min=%dt:~10,2%" & set "Sec=%dt:~12,2%"
set "fullstamp=%YYYY%%MM%%DD%-%HH%%Min%%Sec%"
set bkfile=dbname%fullstamp%.bak
set path_sqlcmd="C:\Program Files\Microsoft SQL Server\Client SDK\ODBC0\Tools\Binn\SQLCMD.exe"
%path_sqlcmd% -S .\SQLEXPRESS -E -Q "BACKUP DATABASE seteo TO DISK='D:\bk-sqlserver\%bkfile%' WITH FORMAT"
It's a bit long, but i think it's a practical solution, if you need backup sql server 2005 or oldie versions, just change sqlcmdby osql.
有点长，但我认为这是一个实用的解决方案，如果您需要备份 sql server 2005 或 oldie 版本，只需通过osql更改sqlcmd。
回答by AminGolmahalle
You can use any of them. These Are Sample For Use Date And DateTime In Backup Database:
您可以使用其中任何一个。这些是在备份数据库中使用日期和日期时间的示例：
SELECT 'TestDb' + '_'+CAST(CAST(GETDATE() AS datetime2) AS NVARCHAR(22))+
'_Log.trn';--TestDb_2019-09-28 13:54:42.54_Log.trn
SELECT 'TestDb' + '_'+CAST(CAST(GETDATE() AS DATE) AS NVARCHAR(MAX))+
'_Log.trn';--TestDb_2019-09-28_Log.trn
SELECT 'TestDb' + '_'+CAST(sysdatetime() as nvarchar(max))+ '_Log.trn';--TestDb_2019-09-28 13:54:42.5536994_Log.trn
SELECT 'TestDb'+'_' + convert(varchar(500),GETDATE(),120) + '_Log.trn';--TestDb_2019-09-28 13:54:42_Log.trn
SELECT 'TestDb'+'_' + convert(varchar(500),GETDATE(),112) + '_Log.trn';--TestDb_20190928_Log.trn
SELECT 'TestDb' + '_'+CAST(FORMAT(getdate(), N'yyyy-MM-ddThh:mm:ss') as nvarchar(max))+ '_Log.trn';--TestDb_2019-09-28T02:08:38_Log.trn
回答by Brad
DECLARE @var nvarchar(max) ='C:\Projects\myDatabase ' +replace(rtrim(convert(char,getdate())), ':',',')+'.bak';
BACKUP DATABASE myDatabase TO DISK = @var
output:
输出：
C:\Projects\myDatabase Jun 3 2015 6,33AM.bak
C:\Projects\myDatabase 2015 年 6 月 3 日 6,33AM.bak
