Wednesday, March 28, 2012

Restoring using smo

I have built a VB.NET app that uses SMO to restore a database from a backup device to a database. This works great if I restore the backup into the original database. If, however, i try to restore the backup into ANOTHER database, it fails. My code looks like this (only important parts are included):

db1 = New Management.Smo.Database

db1.Name = Me.lstDatabase.SelectedItem

restore = New Management.Smo.Restore

restore.Action = Management.Smo.RestoreActionType.Database

restore.Database = db1.Name

backDeviceItem = New Management.Smo.BackupDeviceItem(Me.lstBackups.Text, Management.Smo.DeviceType.LogicalDevice)

restore.Devices.Add(backDeviceItem)

restore.ReplaceDatabase = True

restore.SqlRestore(SqlServerSelection)

In this example, the lstBackups contains the list of backup devices. lstDatabase contains the list of databases to restore into. I can successfully connect to the database, but the restore fails. Any ideas?

Thanks...

Scott

What privilege level is the user attempting to do the restore? While a db_owner user can back up a database, the restore is in essence a create database process, which requires sysadmin level privileges.|||Also the database has to be in restricted mode.

db1.DatabaseOptions.UserAccess = DatabseUserAccess.Restrictedsql

No comments:

Post a Comment