Create databases with powershell transactionally

Question

I'm creating a set of databases on a SQL Server 2008R2 instance using powershell, like this:

foreach ($database in $databases) {
    # Create DB
    $db = New-Object Microsoft.SqlServer.Management.SMO.Database
    $db.Parent = Get-Item "SQLSERVER:\SQL\$DBServer\$Instance"
    $db.Name = $database
    $db.Create()

    #
    # Create tables, fill in data, etc
    #
}

Here I'm assuming that none of the databases to be created already exist. If it turns out that one does, I want to abort and rollback. In SQL I would just wrap this in a BEGIN TRAN and ROLLBACK or COMMIT, but can I do this in Powershell? There is a cmdlet Start-Transaction, but it requires support from the commands used in the transactions, and $db.Create() does not.

Is there a way in the SMO object to do transactions? I could of course just check all the databases in advance, but the entire script is quite complicated, and I feel it would be more understandable if I can have the logic closer to the creation.

Remus Rusanu · Accepted Answer · 2012-05-18 14:16:40Z

You couldn't wrap this in SQL in a transaction either, since CREATE DATABASE cannot be enrolled in a transaction:

The CREATE DATABASE statement must run in autocommit mode (the default transaction management mode) and is not allowed in an explicit or implicit transaction.

So the capability cannot be achieved in PS, SMO nor any other way. In lack of transactions you need to handle errors carefully. It would be very difficult to achieve atomic behavior for 'all or none' databases to be created. If you hit an error, you are not guaranteed that you can drop the databases already created.

If possible, is better to thrive for idempotency: if you run the script and hit an error on database 5, next time you run it it skips databases 1-4 which were already created. Or, alternatively, it drops the databases already created if exist and create them again (this is risky, as the script may end up dropping the wrong DBs due to operator error).

Diego · Accepted Answer · 2012-05-18 12:55:19Z

1

cant you run a

if not exists(select 1 from sys.databases where name like ='your_db_name') begin
 create
end else begin
  log?
end

answered May 18, 2012 at 12:55

Diego

36.5k21 gold badges94 silver badges138 bronze badges

2 Comments

carlpett Over a year ago

The idea here is doing it within Powershell, if possible, since there are quite a lot of other things being done within the same task.

Chad Miller Over a year ago

You could use a try/catch block in powershell and add some logic in your catch block to "undo"-- delete/drop any databases created.

Collectives™ on Stack Overflow

Create databases with powershell transactionally

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related