Sql-server – Methods for automating SQL CU patching

I'm not sure I would advocate it as a good solution, neither performance wise nor as a best practice, but there actually is a possibility.

In short, the steps would be

1. Make a backup on a swap file on the server
2. Create a temporary table with a varbinary field
3. Insert the backup into the table
4. Fetch column data using ADO.NET
5. Save the stream in a file

The full source code is available on codeplex Transferring backup files from a remote SQL Server instance to a local machine without network shares, FTP, HTTP

The gist of the code is:

Step 1:

_sql = String.Format("BACKUP DATABASE {0} TO DISK " + 
       "= N'{1}\\{0}.bak' WITH FORMAT, COPY_ONLY, INIT, " + 
       "NAME = N'{0} - Full Database Backup', SKIP ", 
       _dbname, AremoteTempPath, _dbname);
_command.CommandText = _sql;
_command.ExecuteNonQuery();

Step 2:

_sql = String.Format("IF OBJECT_ID('tempdb..##{0}') IS " + 
                     "NOT NULL DROP TABLE ##{0}", temporaryTableName);
_command.CommandText = _sql;
_command.ExecuteNonQuery();
_sql = String.Format("CREATE TABLE ##{0} (bck VARBINARY(MAX))", 
                     temporaryTableName);
_command.CommandText = _sql;
_command.ExecuteNonQuery();

Step 3:

_sql = String.Format("INSERT INTO ##{0} SELECT bck.* FROM OPENROWSET" + 
       "(BULK '{1}\\{2}.bak',SINGLE_BLOB) bck", 
       temporaryTableName, AremoteTempPath, _dbname);
_command.CommandText = _sql;
_command.ExecuteNonQuery();

Step 4 & 5:

_sql = String.Format("SELECT bck FROM ##{0}", temporaryTableName);
SqlDataAdapter da = new SqlDataAdapter(_sql, _conn);
DataSet ds = new DataSet();
da.Fill(ds);
DataRow dr = ds.Tables[0].Rows[0];
byte[] backupFromServer = new byte[0];
backupFromServer = (byte[])dr["bck"];
int aSize = new int();
aSize = backupFromServer.GetUpperBound(0) + 1;

FileStream fs = new FileStream(String.Format("{0}\\{1}", 
                AlocalPath, fileName), FileMode.OpenOrCreate, FileAccess.Write);
fs.Write(backupFromServer, 0, aSize);
fs.Close();

The original code was written by Adrian Pasik under the bsd license and published on codeplex.

Instead of typing the entire list of column names, you can simply drag-and-drop the column list from the Object Explorer window in SQL Server Management Studio. In the image below, you drag-and-drop the highlighted section over to the query window:

However, if you insist on automatically creating the list of columns, you'll need to run the MERGE command using dynamic SQL. Erland Sommarskog has the definitive article on how to use dynamic SQL, along with a great set of advice on how not to do it, at http://www.sommarskog.se/dynamic_sql.html

Something like this:

DECLARE @cmd NVARCHAR(MAX);
SET @cmd = 'merge targ_table_name
using (select * from openquery(zdata,''select * from source_table_name'')) 
     as Tsource 
on (targ_table_name.ID = Tsource.ID) 
when matched then
Update set ' + @TargetToSourceColumns + '
when not matched by target then
insert values (' + @SourceColumns + ');';

EXEC sp_executesql @cmd;

The above statement is not syntactically correct, but essentially shows how you need to concatenate parts of the query that are dynamic. You'll need to compile the @TargetToSourceColumns and @SourceColumns variables as needed.