Backup SQL Server automatically

Question

I need to make backup copies of my database and store them on another server

I created this stored procedure for that task:

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)

Where to create a script:

// Sqlbackup.bat
/****************************************************************/
backup 
/***************************************************************/

sqlcmd -S DESKTOP -Q "EXEC sp_BackUpRecursosHumanos @backupLocation='C:\Users\dell\Documents\BackUp\', @databaseName='RecursosHumanos'"

Here is saving the copy internally, My problem is how I keep it on another server

Do you have any errors while backup copying to another server? have you tried xp_cmdshell? " EXEC sp_cmdshell 'copy C:\Users\dell\Documents\BackUp\.bak \\anotherserver\backupsharelocation' " you need to enable xp_cmdshell and should have access on \\anotherserver\backupsharelocation. — CR241
– CR241, Commented Dec 11, 2018 at 22:44
Side note: you should not use the sp_ prefix for your stored procedures. Microsoft has reserved that prefix for its own use (see Naming Stored Procedures), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoid sp_ and use something else as a prefix - or no prefix at all! — marc_s
– marc_s, Commented Dec 12, 2018 at 5:53

Jerry Hung · Accepted Answer · 2018-12-12 02:15:56Z

Also look into dbatools.io (PowerShell tool) to do it easily

Easier SQL Server Restores using DBATools - Stuart Moore

https://dbatools.io/commands/#Backup

https://dbatools.io/dr/

# 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\sql2017

Sreenivas Gurram · Accepted Answer · 2018-12-12 00:09:50Z

0

Here Create UNC Path of another server and check folder is accessible or not in sql server machine and specify path of unc in procedure.

answered Dec 12, 2018 at 0:09

Sreenivas Gurram

1364 bronze badges

Collectives™ on Stack Overflow

Backup SQL Server automatically

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related