WHAT I HAVE:
Visual Basic 2019, .NET Framework 4+, WinForms, Entity Framework 6+, SQL Server (version unsure)
I want my program to be able to make a copy of a database--with a new catalog, data file, and log file--to a destination on the same machine (and same server), while keeping the original database--along its with catalog, data file, and log file--intact and unmoved.
VS Help advises using BACKUP and RESTORE, and my run-time code executes SQL to copy the following sample database with catalog name "contactlist2.mdf 12/8/2021 6:13:57 pm", data file name "C:\ProgramData\Robert AlanGustafson II\Social Contacts Tracker\22.214.171.124\contactlist2.mdf", and log file name "C:\ProgramData\Robert AlanGustafson II\Social Contacts Tracker\126.96.36.199\contactlist2_log.ldf" to a database with catalog name "contactlist3.mdf 12/8/2021 6:13:57 pm", data file name "C:\ProgramData\Robert AlanGustafson II\Social Contacts Tracker\188.8.131.52\contactlist3.mdf", and log file name "C:\ProgramData\Robert AlanGustafson II\Social Contacts Tracker\184.108.40.206\contactlist3_log.ldf"--basically, copying all "conctactlist2"'s to "contactlist3"'s--using the following procedure:
''' <summary> ''' copy one database to another ''' </summary> ''' <param name="SourceCatalogName">catalog name of source database</param> ''' <param name="SourceFileName">file name of source database</param> ''' <param name="DestinationCatalogName">catalog name of destination database</param> ''' <param name="DestinationFileName">file name of destination database</param> ''' <returns>True if successful, False if cancelled</returns> ''' <remarks>Note the following:<br></br> ''' 1. If either database is the same as the currently open one (if any), if the ''' source database doesn't exist, or if an error occurs while making the copy, ''' an exception is thrown<br></br> ''' 2. If a database with the given destination catalog name and/or a file with the ''' same name as the desination primary file name already exists, then the ''' CatlogExists and/or DatabaseFileExists events will be fired to allow the user to ''' decide whether to cancel the operation or overwrite the catalog and/or file with ''' the database copy</remarks> Public Overridable Function Copy(ByVal SourceCatalogName As String, _ ByVal SourceFileName As String, _ ByVal DestinationCatalogName As String, ByVal DestinationFileName As String) As Boolean ' normalize names for and verify existence of source database CheckNamesForOpenExisting(SourceCatalogName, SourceFileName) ' normalize names for destination database CheckNamesForCreateNew(DestinationCatalogName, DestinationFileName) ' see if either database is already opened VerifyIsNotOpen(SourceCatalogName, SourceFileName) VerifyIsNotOpen(DestinationCatalogName, DestinationFileName) ' verify existence of source database Using Context As SocialContactsTracker1 = _ GetContextForExistingDatabase(SourceCatalogName, SourceFileName) ' do nothing End Using ' see if we need to overwrite destination database Using Context As SocialContactsTracker1 = _ GetContextForNewDatabase(DestinationCatalogName, DestinationFileName) If Context Is Nothing Then ' exit if destination database exists and is not to be overwritten Return False End If End Using ' copy database using backup and restore Using Context As SocialContactsTracker1 = GetPrototypeContext() Dim BackupFileName As String = _ SourceFileName.Substring(0, SourceFileName.Length - 3) & "bak" Dim BackupDeviceName As String = _ SourceCatalogName.Substring(0, SourceCatalogName.Length - 5) & "_TEMP" ' perform copy using backup and restore With Context.Database .ExecuteSqlCommand(Entity.TransactionalBehavior.DoNotEnsureTransaction, _ "BACKUP DATABASE """ & SourceCatalogName _ & """ TO DISK='" & BackupFileName & "'; " _ & "RESTORE FILELISTONLY FROM DISK='" & BackupFileName & "'; " _ & "RESTORE DATABASE """ & DestinationCatalogName _ & """ FROM DISK='" & BackupFileName _ & "' WITH MOVE '" & SourceCatalogName & "_Data' TO '" _ & DestinationFileName & "', " _ & "MOVE '" & SourceCatalogName & "_Log' TO '" _ & IO.Path.ChangeExtension(DestinationFileName, Nothing) & "_log.ldf'; ") End With If IO.File.Exists(BackupFileName) Then ' remove temporary backup file IO.File.Delete(BackupFileName) End If End Using ' flag operation successful Return True End Function
When I attempt to access sys.database_Files to get the correct logical data/log names, I'm stymied.
I've tried the following procedure:
Here's a code snippet, where Context is opened to an existing database with a given CatalogName and FileName, where I try to determine the logical/physical files and their types for the database:
Protected Function GetLogicalFiles(ByVal CatalogName As String, _ ByVal FileName As String) As List(Of LogicalFileEntry) Dim results As New List(Of LogicalFileEntry) Using Context As SocialContactsTracker1 = _ GetContextForExistingDatabase(CatalogName, FileName) ' get database list from master database results = _ Context.Database.SqlQuery(Of LogicalFileEntry) _ ("SELECT file_id AS " & NameOf(LogicalFileEntry.FileID) _ & ", type AS " & NameOf(LogicalFileEntry.FileType) _ & ", name AS " & NameOf(LogicalFileEntry.LogicalName) _ & ", physical_name AS " & NameOf(LogicalFileEntry.PhysicalName) _ & " FROM sys.database_files;").ToList() For Each LogicalName As LogicalFileEntry In results With LogicalName MessageBox.Show( _ .FileID & ControlChars.CrLf & .FileType _ & ControlChars.CrLf & """" & .LogicalName & """" _ & ControlChars.CrLf & """" & .PhysicalName & """") End With Next LogicalName Return results End Using End Function
where LogicalFileEntry is defined (within the larger class) as follows:
''' <summary> ''' This class returns the data that was retrieved in the ''' T-SQL script of the GetLogicalFiles method ''' </summary> Protected Class LogicalFileEntry Public FileID As Integer Public FileType As Byte Public LogicalName As String Public PhysicalName As String End Class
When I list the fields for each entry (there are 2), FileId and FileType return 0, while LogicalName and PhysicalName return "". When I try to qualify "sys,database_files" as CatalogName & ".sys.database_files", it tells me that the database doesn't exist. (That's wrong, because it does open,) What's wrong?! Is it the "sys.database_files" table name, the SQL syntax, the LogicalFileEntry field types?
Remember, I want everything copied, not merely moved!
As for the database catalog name, my program lets users create and open social-contact lists by specifying a name for a contact-list XML file--which contains the names of the catalog and .mdf database file that are subsequently created/opened by the program, both of which are derived from the contact-list name. (The user is allowed to create multiple contact lists, each with its own database. Don't ask why they might want to, but this makes copy functionality useful.) I need some system to give each database a unique catalog name. I use the .mdf file name plus the date and time the contact list is created to do this. When not trying to copy a database, this all works just fine in my program. As for the partial time stamp for the backup device, that's only because I derived its name from that of the backup file.
I sense that there's a problem with the syntax of the RESTORE DATABASE command. What is it?! Is there a way to "Intellisense" the generated SQL for syntax errors? I can't gleam from Help how to access the SQL editors and syntax-checkers.
PS. What's the best SQL for moving (not copying) a database to a new catalog, data file, and log file?
PPS. Remember, none of what I'm doing is across machines or across servers (although it could be in the future).
PPPS. I tried to add this new development to 1 of my previous posts on this issue, but the website won't do anything when I click "Submit." (I've had this problem before using the website after a lot of back-and-forth on a given issue!)