1

So I have a script that almost does what I want but for one server.I need it to work "Cross-Server" ex.I have a database in one server and I need to pass it to other server and deleting if it exists on second server.

What I have now does almost the same but in one instance ->

USE master;

DECLARE
    @SourceDatabaseName AS SYSNAME = 'ABCBRAGA', 
    @TargetDatabaseName AS SYSNAME = 'TEST'



-- ============================================
-- Define path where backup will be saved
-- ============================================
IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE name = @SourceDatabaseName)
    RAISERROR ('Variable @SourceDatabaseName is not set correctly !', 20, 1) WITH LOG       

DECLARE @SourceBackupFilePath varchar(2000)
SELECT @SourceBackupFilePath = BMF.physical_device_name
FROM
    msdb.dbo.backupset B
    JOIN msdb.dbo.backupmediafamily BMF ON B.media_set_id = BMF.media_set_id
WHERE B.database_name = @SourceDatabaseName
ORDER BY B.backup_finish_date DESC

SET @SourceBackupFilePath = REPLACE(@SourceBackupFilePath, '.bak', '_clone.bak')



-- ============================================
-- Backup source database
-- ============================================
DECLARE @Sql NVARCHAR(MAX) 
SET @Sql = 'BACKUP DATABASE @SourceDatabaseName TO DISK = ''@SourceBackupFilePath'''
SET @Sql = REPLACE(@Sql, '@SourceDatabaseName', @SourceDatabaseName)
SET @Sql = REPLACE(@Sql, '@SourceBackupFilePath', @SourceBackupFilePath)
SELECT 'Performing backup...', @Sql as ExecutedSql
EXEC (@Sql)



-- ============================================
-- Automatically compose database files (.mdf and .ldf) paths
-- ============================================
DECLARE
          @LogicalDataFileName as NVARCHAR(MAX)
        , @LogicalLogFileName as NVARCHAR(MAX)
        , @TargetDataFilePath as NVARCHAR(MAX)
        , @TargetLogFilePath as NVARCHAR(MAX)

SELECT
    @LogicalDataFileName = name,
    @TargetDataFilePath = SUBSTRING(physical_name,1,LEN(physical_name)-CHARINDEX('\',REVERSE(physical_name))) + '\' + @TargetDatabaseName + '.mdf'
FROM sys.master_files
WHERE
    database_id = DB_ID(@SourceDatabaseName)        
    AND type = 0            -- datafile file

SELECT
    @LogicalLogFileName = name,
    @TargetLogFilePath = SUBSTRING(physical_name,1,LEN(physical_name)-CHARINDEX('\',REVERSE(physical_name))) + '\' + @TargetDatabaseName + '.ldf'
FROM sys.master_files
WHERE
    database_id = DB_ID(@SourceDatabaseName)        
    AND type = 1            -- log file     



-- ============================================
-- Restore target database
-- ============================================
IF EXISTS (SELECT 1 FROM sys.databases WHERE name = @TargetDatabaseName)
BEGIN
SET @Sql = 'DROP DATABASE ' + @TargetDatabaseName
EXEC (@Sql)
END

SET @Sql = 'RESTORE DATABASE @TargetDatabaseName
FROM DISK = ''@SourceBackupFilePath'' 
WITH MOVE ''@LogicalDataFileName'' TO ''@TargetDataFilePath'',
MOVE ''@LogicalLogFileName'' TO ''@TargetLogFilePath''' 
SET @Sql = REPLACE(@Sql, '@TargetDatabaseName', @TargetDatabaseName)
SET @Sql = REPLACE(@Sql, '@SourceBackupFilePath', @SourceBackupFilePath)
SET @Sql = REPLACE(@Sql, '@LogicalDataFileName', @LogicalDataFileName)
SET @Sql = REPLACE(@Sql, '@TargetDataFilePath', @TargetDataFilePath)
SET @Sql = REPLACE(@Sql, '@LogicalLogFileName', @LogicalLogFileName)
SET @Sql = REPLACE(@Sql, '@TargetLogFilePath', @TargetLogFilePath)
SELECT 'Restoring...', @Sql as ExecutedSql
EXEC (@Sql)

What I need it to is to pass a database from one server to other.

  • 1
    This would seem better to do outside of SQL, in some ways then. You can't change connection to the instance in the middle of a batch; meaning you can't BACKUP on one instance and RESTORE on another in the same batch. Something like Powershell with Backup-SqlDatabase and Restore-SqlDatabase might be useful. – Larnu Feb 27 at 14:28
  • Any example/Documentation I can follow? – Megaluk Feb 27 at 14:30
1

Use dbatools powershell module. There are numerous commands to help all sorts of administrations tasks.

Here is a migrate database example.

copy-dbadatabase -Source oldserver -Destination newserver -BackupRestore -SharedPath \\server\backup\ -Database databasename -SetSourceOffline
| improve this answer | |
1

1) Setup Server2 as linked server on Server1

2) Backup database to the network share visible from both servers

3) Restore database from the network share by executing SQL statement on linked Server2

| improve this answer | |
1

Yes, you can do it but that solution isn't perfect.

You will need the sqlpackage utility and also allow the SQL server to execute the xp_cmdshell procedure

Run this script on the receiver server. In the script, you need to specify information for connecting to both the databases.

declare @sql varchar(300)

--export bacpac from source server
set @sql = 'c:\utils\sqlpackage.exe /TargetFile:"c:\temp\temp.bacpac" /Action:Export /SourceServerName:"my-source-server.com" /SourceDatabaseName:"source-database" /SourceUser:"sa" /SourcePassword:"my-password"'

EXEC xp_cmdshell @sql

IF EXISTS (SELECT name FROM sys.databases WHERE name = N'target-database')
    DROP DATABASE [target-database]
GO

declare @sql varchar(300)
--import bacpac to that server
set @sql = 'c:\utils\sqlpackage.exe /SourceFile:"c:\temp\temp.bacpac" /Action:Import /TargetServerName:"." /TargetDatabaseName:"target-database" /TargetUser:"sa" /TargetPassword:"my-password"'

EXEC xp_cmdshell @sql

That way isn't perfect for two reasons:

  1. Allowing startup for xp_cmdshell is bad from a security point of view

  2. sqlpackage - loses in speed in comparison with the BACKUP DATABASE

@Joe Schmoe advised you a good method: run BACKUP DATABASE on the source server and RESTORE DATABASE on the destination server using share folder.

If your architecture does not allow you to use a network folder, you may want to find a way to map an FTP site as either a network path or drive.

Also, paid cloud backup services such as ibm cloud backup or sqlbak can transfer and restore .bak files through various cloud storage.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.