The text file is relative to the server, not your machine. So C:\Output.txt will be on the server's C:, not yours.

I have found that the copy database feature generally works...

Only a problem insofar as you have to actually know that the info is in HKLM\Software\Microsoft\MSSQLServer\MSSQLServer\BackupDirectory, as opposed to simply asking for the server object for it's default backup folder.

Here's the basic script I use to backup all DBs in each SQL Server instance on a machine, except tempdb.

This is saved as a Powershell.ps1 script, and run via...

When I need to create a new instance of our application's DB on several servers, nothing beats PoSh.

I have a script which:

restores to a new db from the current base...

Thanks Scott,

Very useful.

Scott,

I'm curious as well as to what the SQL would be to handle all columns at once.

Been thinking about it -- not too hard -- and coming up...

Although that does work nicely in the scenario I presented, that won't work in for my real-life case: there are other inserts into identity tables earlier in the procedure.

I should...

Hmm not quite: let's say there are x groups, and the parents' ids aren't always sequential.

Very clever.

Thanks again!

P

That's working in the back-end Eirikur, thank you!!

I have to dump the empty result set into a #tmp table so the front-end doesn't puke, but that's a minor detail.

Eirikur,

This is select @@version:

Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Jun 28 2012 08:36:30 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows...

DECLARE @code varchar(max)

SET @code = 'SELECT * from FROM sys.tables';

BEGIN TRY

EXEC ('SET PARSEONLY ON; ' + @code + '; SET PARSEONLY OFF;')

SELECT 'Success'

END TRY

BEGIN CATCH

SELECT 'Error'

END CATCH

This correctly gets...

Thanks for the reply Sigerson,

What I'm actually after is a T-SQL procedure or function to which I can pass the variable containing the generated code...

I tried a roll-your-own proc which...

One more question:

Let's assume I go with the certificate method.

Each night, we do database transfers to various target servers, using smo.Transfer.

Does the certificate/permissions granted by the cert travel...

My apologies Emil, you are correct.

In fact, Erland's article also covers the Execute As scenario. But I am looking at the Certificates section.