SMO Restore remove newly added tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SMO Restore remove newly added tables

Author

Topic

jazzyb
Starting Member

20 Posts

Posted - 2011-06-13 : 10:32:42

Dear Friends,
Hi,

I am working on a small prject using VB.net and SQL Express. I have created forms to Backup and Restore data by using SMO. These are working correctly execpt only one problem:

Now I have added 2 more tables in mydatabase to expand my project, but when I restore data from earlier bakup file. It removes my newly created tables and newly added columns in existing tables.

How can I restore only data in tables of mydatabase from .bak file, and retain the new schema of tables and newly added tables.

My SMO Restore commands are as following:

Dim strFileName As String
            Dim rstDb As New Restore
            Dim srv As Server
            srv = New Server("(local)\SQLEXPRESS")
            Dim db As Database
            db = srv.Databases("mydatabasename")
           strFileName = txtrestore.Text
            rstDb.Database = db.Name
            rstDb.Action = RestoreActionType.Database
            rstDb.Devices.Add(New Smo.BackupDeviceItem(strFileName, DeviceType.File))
            rstDb.UnloadTapeAfter = True
            rstDb.ReplaceDatabase = True

And my SMO backup commands are as following:

Dim strFileName As String
        Dim bkdb As New Backup
        Dim srv As Server
        srv = New Server("(local)\SQLEXPRESS")
        Dim db As Database
        db = srv.Databases("mydatabasename")
        strFileName = txtbackup.Text
        bkdb.Database = db.Name
        bkdb.Action = BackupActionType.Database
        bkdb.BackupSetName = "Backup " & db.Name & Date.Now
        bkdb.Devices.Add(New Smo.BackupDeviceItem(strFileName, DeviceType.File))
        bkdb.Initialize = True
        bkdb.SkipTapeHeader = True
        bkdb.UnloadTapeAfter = True

How can I restore the data only from my backup file and retain newly added tables in my database and retain newly added columns in my tables.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-13 : 11:13:31

You can't. It's a full restore - i.e. it replaces the database with what's in the backup.
You could restore to a new databse then copy the tables over.

You could try paying around with filegroup or page restores or putting the new tables in another database but I doubt if you want to get into that.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources