The best or rather correct backup methodology

P

pachjo

Well-known member
Joined
Dec 12, 2006
Messages
370
Programming Experience
10+
HI, I am incorporating backup functionality into my application which consists of a single .mdf file.

From this site I found an SQL statement that will successfully back up the database even when it is open (by the application itself of course).

I came unstuck however when trying to use the restore statment as it complained rightly that the database was in use (by the application and by the connection the restore statement was attached too).

I then did some more searching and found something called SQLDMO which is a COM not .NET which confuses me a tad.

Could someone explain to be what they feel is the best approach of those listed below, or if they have a better method?

1. Create a seperate database that has no tables etc and use this as the connection to do a backup and restore using the SQL BACKUP and RESTORE staements. Closing the applications connection whilst doing so and then reconnect when done.

2. Create a SQLDMO instance of some sort and use this to backup and restore the application database, again closing the application connection first and then reconnecting.

3. Someone else has a much better, more professional approach?

Thanks in advance for any help
 
Sort by date Sort by votes
Hi,

this is the code I now have that makes a backup copy of my database and apparently restores it too but the restore does not reflect in my application at runtime or if I close and reopen?

Any help please? Thanks....

VB.NET:
Expand Collapse Copy 
[SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Function[/COLOR][/SIZE][SIZE=2] BackupRestore([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] strFilePath [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2], [/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] blnBackup [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Boolean[/COLOR][/SIZE][SIZE=2]) [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Boolean
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#008000]' the message to be displayed at the end of backup/restore
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] strMessage [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = [/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2].Empty
[/SIZE][SIZE=2][COLOR=#008000]' the title for the message above
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] strTitle [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = [/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2].Empty
[/SIZE][SIZE=2][COLOR=#008000]' this will be the connection to the sql server instance at runtime
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] cnSQLConnection [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] SqlClient.SqlConnection = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlClient.SqlConnection([/SIZE][SIZE=2][COLOR=#0000ff]My[/COLOR][/SIZE][SIZE=2].Settings.MB2007ConnectionString)
[/SIZE][SIZE=2][COLOR=#008000]' this will be the connection to the server 
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] scnServerConnection [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] ServerConnection = [/SIZE][SIZE=2][COLOR=#0000ff]Nothing
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#008000]' this will be our admin instance
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] srvAdminServer [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Server = [/SIZE][SIZE=2][COLOR=#0000ff]Nothing
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#008000]' this is the database to use 
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] dbTargetDatabase [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Database = [/SIZE][SIZE=2][COLOR=#0000ff]Nothing
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#008000]' this is a backup class 
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] bkBackup [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] Backup
[/SIZE][SIZE=2][COLOR=#008000]' this is a backup device item to use with the backup class
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] bdiBackup [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] BackupDeviceItem = [/SIZE][SIZE=2][COLOR=#0000ff]Nothing
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#008000]' this is a restore class
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] rsRestore [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] Restore
strFilePath &= [/SIZE][SIZE=2][COLOR=#a31515]"\MB.bak"
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#008000]' open a connection
[/COLOR][/SIZE][SIZE=2]cnSQLConnection.Open()
[/SIZE][SIZE=2][COLOR=#008000]' and connect
[/COLOR][/SIZE][SIZE=2]scnServerConnection = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] Microsoft.SqlServer.Management.Common.ServerConnection(cnSQLConnection)
[/SIZE][SIZE=2][COLOR=#008000]' set the admin server
[/COLOR][/SIZE][SIZE=2]srvAdminServer = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] Server(scnServerConnection)
[/SIZE][SIZE=2][COLOR=#008000]' name of the database we are interested in
[/COLOR][/SIZE][SIZE=2]dbTargetDatabase = srvAdminServer.Databases([/SIZE][SIZE=2][COLOR=#a31515]"MB2007"[/COLOR][/SIZE][SIZE=2])
[/SIZE][SIZE=2][COLOR=#008000]' add a BackupDeviceItem naming the output file and setting type of device as file
[/COLOR][/SIZE][SIZE=2]bdiBackup = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] BackupDeviceItem(strFilePath, DeviceType.File)
[/SIZE][SIZE=2][COLOR=#008000]' if true user wants to perform a backup, otherwise it is a restore
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] blnBackup.Equals([/SIZE][SIZE=2][COLOR=#0000ff]True[/COLOR][/SIZE][SIZE=2]) [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#008000]' we want to perform a backup action
[/COLOR][/SIZE][SIZE=2]bkBackup.Action = BackupActionType.Database
[/SIZE][SIZE=2][COLOR=#008000]' get the dynamically named database name at runtime
[/COLOR][/SIZE][SIZE=2]bkBackup.Database = cnSQLConnection.Database.ToString()
[/SIZE][SIZE=2][COLOR=#008000]' add device to the backup object
[/COLOR][/SIZE][SIZE=2]bkBackup.Devices.Add(bdiBackup)
[/SIZE][SIZE=2][COLOR=#008000]' name the backup set
[/COLOR][/SIZE][SIZE=2]bkBackup.BackupSetName = [/SIZE][SIZE=2][COLOR=#a31515]"MB2007Set"
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#008000]' set the time to now, then it will expire immediately allow the backup to run again without
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#008000]' complaining about using same file ... read help on backup object inialize method.
[/COLOR][/SIZE][SIZE=2]bkBackup.ExpirationDate = [/SIZE][SIZE=2][COLOR=#0000ff]Date[/COLOR][/SIZE][SIZE=2].Now
[/SIZE][SIZE=2][COLOR=#008000]' make this the first backup in set if exire date has passed, which it would have done above 
[/COLOR][/SIZE][SIZE=2]bkBackup.Initialize = [/SIZE][SIZE=2][COLOR=#0000ff]True
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#008000]' full backup required so set to false
[/COLOR][/SIZE][SIZE=2]bkBackup.Incremental = [/SIZE][SIZE=2][COLOR=#0000ff]False
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#008000]' truncate log after backup 
[/COLOR][/SIZE][SIZE=2]bkBackup.LogTruncation = BackupTruncateLogType.Truncate
[/SIZE][SIZE=2][COLOR=#008000]' call SqlBackup method to perform backup on the admin instance of SQL Server
[/COLOR][/SIZE][SIZE=2]bkBackup.SqlBackup(srvAdminServer)
[/SIZE][SIZE=2][COLOR=#008000]' create user message 
[/COLOR][/SIZE][SIZE=2]strMessage = [/SIZE][SIZE=2][COLOR=#a31515]"Database successfully backed up to the following file:"[/COLOR][/SIZE][SIZE=2] & _
System.Environment.NewLine & System.Environment.NewLine & _
strFilePath
[/SIZE][SIZE=2][COLOR=#008000]' create messagebox title 
[/COLOR][/SIZE][SIZE=2]strTitle = [/SIZE][SIZE=2][COLOR=#a31515]"Backup Confirmation"
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Else
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#008000]' we want to restore the database
[/COLOR][/SIZE][SIZE=2]rsRestore.Action = RestoreActionType.Database
rsRestore.Database = cnSQLConnection.Database.ToString()
rsRestore.Devices.Add(bdiBackup)
cnSQLConnection.ChangeDatabase([/SIZE][SIZE=2][COLOR=#a31515]"MASTER"[/COLOR][/SIZE][SIZE=2])
rsRestore.SqlRestore(srvAdminServer)
[/SIZE][SIZE=2][COLOR=#008000]' create user message 
[/COLOR][/SIZE][SIZE=2]strMessage = [/SIZE][SIZE=2][COLOR=#a31515]"Database has been successfully restored from the following file:"[/COLOR][/SIZE][SIZE=2] & _
System.Environment.NewLine & System.Environment.NewLine & _
strFilePath
strTitle = [/SIZE][SIZE=2][COLOR=#a31515]"Restore Confirmation"
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2]MessageBox.Show(strMessage, strTitle, MessageBoxButtons.OK, MessageBoxIcon.Information)
BackupRestore = [/SIZE][SIZE=2][COLOR=#0000ff]True
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#008000]
[/COLOR][/SIZE][SIZE=2]cnSQLConnection.Dispose()
scnServerConnection.Disconnect()
scnServerConnection = [/SIZE][SIZE=2][COLOR=#0000ff]Nothing
[/COLOR][/SIZE][SIZE=2]srvAdminServer = [/SIZE][SIZE=2][COLOR=#0000ff]Nothing
[/COLOR][/SIZE][SIZE=2]dbTargetDatabase = [/SIZE][SIZE=2][COLOR=#0000ff]Nothing
[/COLOR][/SIZE][SIZE=2]bkBackup = [/SIZE][SIZE=2][COLOR=#0000ff]Nothing
[/COLOR][/SIZE][SIZE=2]bdiBackup = [/SIZE][SIZE=2][COLOR=#0000ff]Nothing
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Function
[/COLOR][/SIZE]
 
Upvote 0 Downvote
HI, I think I did but will have to see if I can find out how I did it. This app was for home use and unfortunately I never finished it as work stuff took over.

I will see if I can find the project
 
Upvote 0 Downvote
In order to restore, you should not have any connections to the database when the restore is being done. If it is still not fully restoring (you open up Management Studio and it says it is "restoring...") then it is probably because you have multiple backups in one file. I had the latter problem. It keeps all the backups kind of in an array of backups in the file, but when you restore using SMO you cannot find what the latest backup was, but you can (if i remember correctly) specify the index to use. The way that I got around this was I delete the file, if it exists, before I do a backup, then I know it is always a fresh file and the restore completes with no problems.
 
Upvote 0 Downvote

Similar threads

A
  • Question Question
Question How Can I Implement a Pin Map Feature to Display User Locations?
Replies
1
Views
4K
Rythorian
Rythorian
manny cash
  • Question Question
Check if the database exists to delete, yes or no.
Replies
6
Views
3K
manny cash
manny cash
Rythorian
  • Question Question
Tip Key_Sentinal
Replies
0
Views
2K
Rythorian
Rythorian
Z
  • Question Question
The Application Is In Break Mode Error
Replies
2
Views
2K
zunebuggy65
Z
S
  • Question Question
Resolved Can't get ReportViewer working
Replies
1
Views
3K
zepter47
Z

Share this page

Latest posts

Back
Top