💡 What Does This Stored Procedure Do?
This procedure:
1️⃣ Creates a .BAK backup file for the entire database locally.
- Filename includes timestamp:
MyDatabase_ddMMyyyy_HH_mm.bak
2️⃣ Loops through all user tables in the database.
- For each table:
- Checks if the remote server has a table named
TableName_Suffix.
- If it doesn't exist, → creates it (matching schema).
- If it exists, → clears existing data.
- Then → inserts all rows from the local table into the remote one.
✅ No need to specify each table manually.
✅ Supports SQL Authentication to the remote server.
✅ Handles schema differences automatically.
📌 Why Use It?
If you're managing data pipelines or DR (disaster recovery) scenarios where:
- You want local backups for restore points
- You also want to push all tables' data to another server (e.g., for reporting, BI, or standby)
- And you want to version the table names with suffixes (like a server or environment tag)
→ This single procedure automates the entire process!
🧭 Example Usage
✅ Backup MyDatabase to D:\SQL_Backups
✅ Copy all tables to the remote server 192.168.1.100, database RemoteDB
✅ Add suffix kartik to remote table names.
EXEC dbo.usp_BackupDatabaseAndAllTablesToRemote
@DatabaseName = 'MyDatabase',
@ServerSuffix = 'kartik',
@LocalBackupPath = 'D:\SQL_Backups\',
@RemoteServerName = '192.168.1.100',
@RemoteDatabase = 'RemoteDB',
@RemoteUser = 'sa',
@RemotePassword = 'YourPassword'
✅ The Complete T-SQL Procedure.
Here’s the full script you can deploy on your SQL Server:
CREATE OR ALTER PROCEDURE dbo.usp_BackupDatabaseAndAllTablesToRemote
@DatabaseName NVARCHAR(255),
@ServerSuffix NVARCHAR(100),
@LocalBackupPath NVARCHAR(500),
@RemoteServerName NVARCHAR(255),
@RemoteDatabase NVARCHAR(255),
@RemoteUser NVARCHAR(100) = NULL,
@RemotePassword NVARCHAR(100) = NULL
AS
BEGIN
SET NOCOUNT ON;
-- Step 1: Generate timestamped .bak filename
DECLARE @DatePart NVARCHAR(50) = REPLACE(CONVERT(CHAR(8), GETDATE(), 103), '/', '')
DECLARE @TimePart NVARCHAR(5) = REPLACE(CONVERT(CHAR(5), GETDATE(), 108), ':', '_')
DECLARE @FileName NVARCHAR(500) = @DatabaseName + '_' + @DatePart + '_' + @TimePart + '.bak'
DECLARE @FullBackupPath NVARCHAR(1000) = @LocalBackupPath + @FileName
-- Step 2: Backup the database to disk
DECLARE @BackupSQL NVARCHAR(MAX) = 'BACKUP DATABASE [' + @DatabaseName + '] TO DISK = N''' + @FullBackupPath + ''' WITH INIT'
EXEC (@BackupSQL)
PRINT '✔ Database backed up to: ' + @FullBackupPath
-- Step 3: Loop through all user tables
DECLARE @TableName NVARCHAR(255)
DECLARE table_cursor CURSOR FOR
SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG = @DatabaseName
OPEN table_cursor
FETCH NEXT FROM table_cursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @TargetTable NVARCHAR(255) = REPLACE(@TableName, '.', '_') + '_' + @ServerSuffix
DECLARE @CreateTable NVARCHAR(MAX) = ''
DECLARE @Sql NVARCHAR(MAX) = ''
-- Build CREATE TABLE statement
SELECT @CreateTable = 'CREATE TABLE ' + QUOTENAME(@TargetTable) + ' ('
SELECT @CreateTable = @CreateTable + CHAR(10) +
QUOTENAME(c.name) + ' ' +
t.name +
CASE
WHEN t.name IN ('varchar', 'char', 'nvarchar', 'nchar') THEN '(' +
CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length AS VARCHAR) END + ')'
WHEN t.name IN ('decimal', 'numeric') THEN '(' + CAST(c.precision AS VARCHAR) + ',' + CAST(c.scale AS VARCHAR) + ')'
ELSE ''
END + ','
FROM sys.columns c
JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE c.object_id = OBJECT_ID(@TableName)
SET @CreateTable = LEFT(@CreateTable, LEN(@CreateTable)-1) + ')'
-- Step 3a: Create remote table if it doesn't exist
SET @Sql = '
IF NOT EXISTS (
SELECT 1 FROM OPENROWSET(
''SQLNCLI'',
''Server=' + @RemoteServerName + ';' +
ISNULL('UID=' + @RemoteUser + ';PWD=' + @RemotePassword + ';', '') + ''',
''SELECT TABLE_NAME FROM ' + @RemoteDatabase + '.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''''' + @TargetTable + ''''''')
)
BEGIN
DECLARE @c NVARCHAR(MAX) = N''' + REPLACE(@CreateTable, '''', '''''') + '''
EXEC (''INSERT INTO OPENROWSET(''''SQLNCLI'''', ''''Server=' + @RemoteServerName + ';' +
ISNULL('UID=' + @RemoteUser + ';PWD=' + @RemotePassword + ';', '') + '''',
''''SET FMTONLY OFF; EXEC('''''''' + @c + '''''''') ON ' + @RemoteDatabase + '''') SELECT 1'')
END
'
EXEC(@Sql)
-- Step 3b: Delete existing remote data
SET @Sql = '
EXEC (''DELETE FROM OPENROWSET(''''SQLNCLI'''', ''''Server=' + @RemoteServerName + ';' +
ISNULL('UID=' + @RemoteUser + ';PWD=' + @RemotePassword + ';', '') + '''',
''''SELECT * FROM ' + @RemoteDatabase + '.dbo.' + @TargetTable + '''')'')'
EXEC(@Sql)
-- Step 3c: Insert local data into remote table
SET @Sql = '
INSERT INTO OPENROWSET(
''SQLNCLI'',
''Server=' + @RemoteServerName + ';' +
ISNULL('UID=' + @RemoteUser + ';PWD=' + @RemotePassword + ';', '') + ''',
''SELECT * FROM ' + @RemoteDatabase + '.dbo.' + @TargetTable + ''')
SELECT * FROM ' + @TableName
EXEC(@Sql)
PRINT '✔ Copied: ' + @TableName + ' → ' + @TargetTable
FETCH NEXT FROM table_cursor INTO @TableName
END
CLOSE table_cursor
DEALLOCATE table_cursor
PRINT '✅ All tables copied successfully.'
END
✅ Benefits
- Fully automated – no need to list tables manually
- Disaster recovery ready – local .bak backup for safe restores
- Remote replication – easy table-level replication to another server
- Flexible naming – append environment/server suffix for clarity
If you found this useful, feel free to share or comment, and let's discuss more SQL Server automation ideas!