自动备份SQL Server
自动备份SQL Server
提问于 2018-12-12 06:29:35
我需要制作数据库的备份副本，并将其存储在另一台服务器上
我为该任务创建了这个存储过程：
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_BackUpRecursosHumanos]
@backupLocation NVARCHAR(200),
@databaseName SYSNAME = NULL
AS
DECLARE @BackupName VARCHAR(100)
DECLARE @BackupFile VARCHAR(100)
DECLARE @DBNAME VARCHAR(300)
DECLARE @sqlCommand NVARCHAR(1000)
DECLARE @dateTime NVARCHAR(20)
--DECLARE @Loop INT
--DECLARE @backupLocation NVARCHAR(200)
SET @DBNAME = @databaseName
SET @backupLocation = @backupLocation
SET @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),101),'/','') + '_' + REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')
SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_FULL_'+ @dateTime+ '.BAK'
SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' full backup for '+ @dateTime
BEGIN
SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+ ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'
END
EXEC(@sqlCommand)创建脚本的位置：
// Sqlbackup.bat
/****************************************************************/
backup
/***************************************************************/
sqlcmd -S DESKTOP -Q "EXEC sp_BackUpRecursosHumanos @backupLocation='C:\Users\dell\Documents\BackUp\', @databaseName='RecursosHumanos'" 这是在内部保存副本，My problem是我在另一台服务器上保存副本的方式
回答 2
Stack Overflow用户
发布于 2018-12-12 10:15:57
此外，还可以查看dbatools.io (PowerShell工具)来轻松完成此任务
Easier SQL Server Restores using DBATools - Stuart Moore
https://dbatools.io/commands/#Backup
# What if you just want to script out your restore? Invoke Backup-DbaDatabase or your Maintenance Solution job
# Let's create a FULL, DIFF, LOG, LOG, LOG
Start-DbaAgentJob -SqlInstance localhost\sql2016 -Job 'DatabaseBackup - SYSTEM_DATABASES - FULL','DatabaseBackup - USER_DATABASES - FULL'
Get-DbaRunningJob -SqlInstance localhost\sql2016
Start-DbaAgentJob -SqlInstance localhost\sql2016 -Job 'DatabaseBackup - USER_DATABASES - DIFF'
Get-DbaRunningJob -SqlInstance localhost\sql2016
Start-DbaAgentJob -SqlInstance localhost\sql2016 -Job 'DatabaseBackup - USER_DATABASES - LOG'
Get-DbaRunningJob -SqlInstance localhost\sql2016
Start-DbaAgentJob -SqlInstance localhost\sql2016 -Job 'DatabaseBackup - USER_DATABASES - LOG'
Get-DbaRunningJob -SqlInstance localhost\sql2016
Start-DbaAgentJob -SqlInstance localhost\sql2016 -Job 'DatabaseBackup - USER_DATABASES - LOG'
Get-DbaRunningJob -SqlInstance localhost\sql2016
# Now export the restores to disk
Get-ChildItem -Directory '\\localhost\backups\WORKSTATION$SQL2016' | Restore-DbaDatabase -SqlInstance localhost\sql2017 -OutputScriptOnly -WithReplace | Out-File -Filepath c:\temp\restore.sql
Invoke-Item c:\temp\restore.sql
# Speaking of Ola, use his backup script? We can restore an *ENTIRE INSTANCE* with just one line
Get-ChildItem -Directory \\workstation\backups\sql2012 | Restore-DbaDatabase -SqlInstance localhost\sql2017Stack Overflow用户
发布于 2018-12-12 08:09:51
在这里创建另一台服务器UNC路径，并检查sql server计算机中的文件夹是否可访问，并在过程中指定unc的路径。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/53733328
复制相关文章
相似问题