Is it possible to batch restore backups and remove users from databases in MSSQL 2008?

Question

We're having to do an emergency migration of all of our MSSQL databases from a dying box to a new shiny instance of SQL2008. Unfortunately, we're having to do it by restoring from nightly backups (.bak files).

Is there a way of:

Automating the restore of a batch of files so as to go:
- DatabaseName.bak >[restore to]> DatabaseName
Automating the removal of a specific user from the DB's security>users list?

Yell if you need more information

Kev, yes, it is. The permissions are the same across most DBs too — poolski, Jul 24 '12 at 08:14
consider posting database-related questions to dba.stackexchange.com — the-wabbit, Jul 24 '12 at 08:51

score 1 · Accepted Answer · answered Jul 24 '12 at 08:50

You probably would see a couple of T-SQL statements like

RESTORE DATABASE AdventureWorks2012
   FROM DISK = 'Z:\SQLServerBackups\AdventureWorks2012.bak'
   WITH FILE = 6
      NORECOVERY;

and

USE AdventureWorks2012;
DROP USER AbolrousHazem;
GO

scripted through something like sqlcmd fit your need.

Is it possible to batch restore backups and remove users from databases in MSSQL 2008?

1 Answers1