question

RobertGustafson-1682 avatar image
0 Votes"
RobertGustafson-1682 asked RobertGustafson-1682 published

I'm having trouble COPYING a database

WHAT I HAVE:
Visual Basic 2019, .NET Framework 4+, WinForms, Entity Framework 6+, SQL Server (version unsure)

MY PROBLEM:
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\1.0.0.0\contactlist2.mdf", and log file name "C:\ProgramData\Robert AlanGustafson II\Social Contacts Tracker\1.0.0.0\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\1.0.0.0\contactlist3.mdf", and log file name "C:\ProgramData\Robert AlanGustafson II\Social Contacts Tracker\1.0.0.0\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

My code throws the following exception:

Logical file 'contactlist2.mdf 12/8/2021 6:13:57 pm_Data' is not part of database 'contactlist3.mdf 5/22/2022 3:29:32 am'. Use RESTORE FILELISTONLY to list the logical file names.
RESTORE DATABASE is terminating abnormally.
Processed 352 pages for database 'contactlist2.mdf 12/8/2021 6:13:57 pm', file 'ContactList2.mdf' on file 9.
Processed 2 pages for database 'contactlist2.mdf 12/8/2021 6:13:57 pm', file 'ContactList2_log.ldf' on file 9.
BACKUP DATABASE successfully processed 354 pages in 0.431 seconds (6.416 MB/sec).

What is wrong with my syntax (since it's in my run-time code, I can't Intellisense-check it)?! How am I supposed to know the logical file names? I didn't formally define them in BACKUP! (Is there a way to specify them during BACKUP?) And somehow I don't think FILELISTONLY will help if I want to keep my VB.NET code simple.

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.

Source code of procedure producing exception (There are references to other parts of the program omitted here; just focus on the SQL.):


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!)















sql-server-generaldotnet-visual-basicwindows-formsdotnet-entity-framework
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Isn't SMO old-school? I"m using EM here. Besides, whatever it is has to be done at run-time with code, as I want my program to give the user the ability to copy a given (user-created) database of my specific type.

PS. Check the original post again; I've modified it to provide more info in the light of feedback. (I also removed the "GO"'s from my app's SQL.)


0 Votes 0 ·
karenpayneoregon avatar image karenpayneoregon RobertGustafson-1682 ·

I don't see SMO as old school, it's less prone to errors than using T-SQL. See an example.


0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered RobertGustafson-1682 edited

Incorrect syntax near 'GO'.

GO isn't a valid SQL command, it's a utility command interpreded by tools like SSMS or SqlCmd.
You can't use it in a SQL statement send from an application; remove it.
[See SQL Server Utilities Statements - GO][1]
"! that are not Transact-SQL statements, but are recognized by the sqlcmd and osql utilities"


[1]: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/sql-server-utilities-statements-go?view=sql-server-ver15

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you for the "GO" bit, but why the other issue? And before I added the "GO"'s, the system seemed to expect SQL statements to end where I didn't intend them too, and told me about missing ";"'s that weren't actually missing.

And is there a way to "Intellisense" the SQL syntax for errors from the environment or a related VS tool?

PS. Check the original post again; I've modified it to provide more info in the light of feedback. (I also removed the "GO"'s from my app's SQL.)

0 Votes 0 ·
BjoernPeters avatar image
0 Votes"
BjoernPeters answered RobertGustafson-1682 edited

The first issue might be (not based on the technical topic) WHY are you using such crazy names?


spaces and another special character in a database name are not a good idea!

It's not bad to have a "timestamp" within your dbname but please use a "-" order "_" => "contactlist2_12082021_061357pm" that makes troubleshooting even easier.
The same thing for your backup device => "backup2disk" might be easier to use, and as you create/drop the device during your execution... it is not necessary to have a timestamp in there.


For your error message "Incorrect syntax near "GO"
Just remove those "GO"s in your code, as you execute it all together and close your commands with a ";".

and from another forum as a really good explanation:
The GO keyword is not T-SQL, but a SQL Server Management Studio artifact that allows you to separate the execution of a script file in multiple batches.I.e. when you run a T-SQL script file in SSMS, the statements are run in batches separated by the GO keyword.
More details can be found here: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/sql-server-utilities-statements-go?redirectedfrom=MSDN&view=sql-server-ver15


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you about the "GO" think; I was originally inclined to exclude it anyway. But what's with the other error?

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. My overall programming strategy is irrelevant; iust get me SQL that'll effectively copy my databases!
PPS. Also, check the original post again; I've modified it to provide more info in the light of feedback. (I also removed the "GO"'s from my app's SQL.)


0 Votes 0 ·
NaomiNNN avatar image
0 Votes"
NaomiNNN answered

When I changed double quotes to single quotes in your command, I was able to parse it (I changed only in the WITH MOVE part).

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.