question

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

I can't seem to find the right SQL for my program to COPY 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-runtimedotnet-entity-framework
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.

Bruce-SqlWork avatar image
0 Votes"
Bruce-SqlWork answered RobertGustafson-1682 edited

sqlserver uses single quotes for literals, double quotes (or []) are to use reserved words as column or table names.


· 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.

Are you saying SQL Server is confused by my choice of delimiters? I thought it didn't differentiate between single and double quotes, provided the same mark is used on both sides of the literal.

0 Votes 0 ·
DanGuzman avatar image
1 Vote"
DanGuzman answered ErlandSommarskog commented

Use single quotes instead of double-quotes to enclose values like file names. I also suggest you ditch the device (unless you have special needs not evident here) and use a DISK= specification instead. The resultant SQL script should be like:

 BACKUP DATABASE [contactlist2.mdf 12/8/2021 6:13:57 pm] 
  TO DISK = 'C:\ProgramData\Robert Alan Gustafson II\Social Contacts Tracker\1.0.0.0\ContactList2.bak';
 RESTORE FILELISTONLY 
  FROM DISK = 'C:\ProgramData\Robert Alan Gustafson II\Social Contacts Tracker\1.0.0.0\ContactList2.bak';
 RESTORE DATABASE [contactlist3.mdf 5/20/2022 3:35:54 am] 
  FROM DISK = 'C:\ProgramData\Robert Alan Gustafson II\Social Contacts Tracker\1.0.0.0\ContactList2.bak'
  WITH MOVE 'contactlist2.mdf 12/8/2021 6:13:57 pm_Data' TO 'C:\ProgramData\Robert Alan Gustafson II\Social Contacts Tracker\1.0.0.0\ContactList3.mdf'
  , MOVE 'contactlist2.mdf 12/8/2021 6:13:57 pm_Log' TO 'C:\ProgramData\Robert Alan Gustafson II\Social Contacts Tracker\1.0.0.0\ContactList3_log.ldf';

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

Backup/restore is is a good solution. IMHO. For your future need, to copy to a remote server be aware that you would need to setup a share and permissions such that the SQL Server service account has the necessary rights. An alternative if your database isn't particular large is a bacpac export/import, in which case the account the app runs under needs permissions to the share.


· 5
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.

I just use a logical device to save text (by avoiding repeated references to the disk file); besides, a great many Help examples rely on using one. Are you saying that I shouldn't create a temporary logical device unless I plan to use it for several actions at a time?

As for the quotes, are you saying that double quotes for literals (typical in most coding scenarios) are confusing SQL Server?

As for the last paragraph, is that just about the PS's or about the whole post? And "to copy to remove" looks like a typo; did you mean "to copy or to remove"? And how difficult/complex is it for my program to set up permissions? I don't want to have to perform a lot of extra code/SQL just to make copy of a database (on the same machine and server)!

Finally, will the above code establish 2 (initially identical) versions of the database, each with its own catalog, data file, and log file? That's important if the 2 databases are to be edited independently henceforth. And what about moving (not copying) the database along with its catalog and files? What SQL do I need there?

I have a feeling the solution should be fairly simple provided the SQL instructions and syntax are correct.




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

Regarding the device, I think using one for this task adds unneeded complexity. I stopped using them 25 years ago but that's just my personal preference. Do as you choose.

Enclosing string literals in single quotes is standard ISO SQL. Enclosing identifiers (e.g. database name) in double quotes is also an ISO SQL standard but SQL Server allows square brackets instead as Erland mentioned. Identifier enclosures are only needed when the name does not follow [regular identifier naming rules][1], such as the database name in this script. Using double-quotes instead of single quotes where a literal is expected will confuse SQL Server.

I think backup/restore is easiest for both copy and move use cases. For a move, just restore to same name as the original database but with new file locations and specify the REPLACE option. There are other ways to copy databases and move database files (detach/attach) but those require moving database files programmatically and dealing with file permission issues.

A database restored to a different name and file locations will be completely independent of the source db.

p.s. The word "removed" in my answer should have been "remote". I corrected it.

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

Regarding the device, I think using one for this task adds unneeded complexity. I stopped using them 25 years ago but that's just my personal preference. Do as you choose.

Enclosing string literals in single quotes (e.g. the logical file name and file path) is standard ISO SQL. Enclosing identifiers (e.g. database name) in double quotes is also an ISO SQL standard but SQL Server allows square brackets instead as Erland mentioned. Identifier enclosures are only needed when the name does not follow [regular identifier naming rules][1], such as the database name in this script. Using double-quotes instead of single quotes where a literal is expected will confuse SQL Server.

I think backup/restore is easiest for both copy and move use cases. For a move, just restore to same name as the original database but with new file locations and specify the REPLACE option. There are other ways to copy databases and move database files (detach/attach) but those require moving database files programmatically and dealing with file permission issues.

A database restored to a different name and file locations will be completely independent of the source db.

p.s. The word "removed" in my answer should have been "remote". I corrected it.

0 Votes 0 ·

Regarding moving (not copying) using the REPLACE option, are you saying a should use "REPLACE" instead "MOVE" in the RESTORE statement, or do mean something different. I need to be sure of the move SQL as well as the copy SQL (which you just explained).

Also, re-check original post; I've updated it.

0 Votes 0 ·
Show more comments
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered RobertGustafson-1682 commented

In addition to what Dan and Bruce said, these statements are OK:

USE master;
EXEC sp_addumpdevice "disk", "contactlist2.mdf 12/8/2021 6:13:_TEMP", "C:\ProgramData\Robert Alan Gustafson II\Social Contacts Tracker\1.0.0.0\ContactList2.bak";
BACKUP DATABASE "contactlist2.mdf 12/8/2021 6:13:57 pm" TO "contactlist2.mdf 12/8/2021 6:13:_TEMP";
RESTORE FILELISTONLY FROM "contactlist2.mdf 12/8/2021 6:13:_TEMP";

But I am not surprised if Dan or Bruce were confused; my initial reaction was the same. Backup devices are a very old feature in SQL Server, but they are very little used today, but I have one environment where I use one or two out of habit.

However this statement is incorrect:

RESTORE DATABASE "contactlist3.mdf 5/20/2022 3:35:54 am" FROM "contactlist2.mdf 12/8/2021 6:13:_TEMP" 
    WITH MOVE "contactlist2.mdf 12/8/2021 6:13:57 pm_Data" TO "C:\ProgramData\Robert Alan Gustafson II\Social Contacts Tracker\1.0.0.0\ContactList3.mdf", 
         MOVE "contactlist2.mdf 12/8/2021 6:13:57 pm_Log" TO "C:\ProgramData\Robert Alan Gustafson II\Social Contacts Tracker\1.0.0.0\ContactList3_log.ldf";

The MOVE and TO clauses can not be followed by identifiers, they must be followed by string literals.

As Dan and Bruce pointed out single and double quotes are not interchangeable in SQL, just like they are not interchangeable in many other languages. In SQL the single quote ' serves as a string delimiter, whereas the double quote " quotes identifiers. Thereby, you can have table name that includes spaces or other special characters. Or column names that are reserved keywords. For for that matter dump devices with slashes in their names.

In SQL Server, you can also use the brackets [] for the same purpose. While the double quote is ANSI standards, and the brackets are proprietary, the brackets are far more widely used in the SQL Server world.


· 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.

So you're saying that SQL Server expects literals in single quotes, unlike most languages, which expect them in double quotes. I assume that the only part of the RESTORE statement that is wrong is the usual of double, instead of single, quotes for the file names, right?

Is there anything else I should change, add, or remove? And are logical devices a formally "deprecated" feature?




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

So you're saying that SQL Server expects literals in single quotes, unlike most languages,

Let's see, what did I say?

In SQL the single quote ' serves as a string delimiter,

Yes, it seems that you are correct. I did say that. I can add that this not unique to SQL Server, but it applies to SQL in general.

unlike most languages, which expect them in double quotes

Dunno. I grew up with Fortran and Pascal, both use the single quote as I recall. Many languages use both, but with different meaning. These two print statements will return different results, in Perl:

print '$str';
print "$str";


A piece of curio: originally the single and double quote was entirely interchangeable in T-SQL, but this changed when Microsoft wanted T-SQL to be more ANSI-compatible. There is actually a setting to change back to the old ways, but it is a purely legacy setting and don't touch it.

And are logical devices a formally "deprecated" feature?

Not really. They're just "funny". In your example there is little point, since you generate the suite of commands anyway. And in practice, they are of little interest, because in most cases you want to back up to a unique file name every time. I used logical backup devices for a dev database where I was content with have a single backup, so I always used the same file name.

0 Votes 0 ·

I'm still having problems. Re-check original post for changes I've made.

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

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

You have this code:

              & "RESTORE DATABASE """ & DestinationCatalogName _
                 & """ FROM DISK='" & BackupFileName _
                     & "' WITH MOVE '" & SourceCatalogName & "_Data' TO '" _

That is, you assume that the logical name of the data file is the same as the database name with _Data tacked on. This assumption is somewhat brave.

If you simply say:

CREATE DATABASE MyDB

The logical name of the data file will be MyDbB and the log file will have the name MyDB_log. If you use the UI in SSMS it may use different defaults. If you restore the database under a different name, the logical names will not change. Thus, it is precarious to make assumptions about the name.

Use sys.database_files to find out what the names actually are.

If you want to play really safe, you should not assume that there are exactly two files. Then again, for smaller databases it is quite common that there are only two files.

· 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.

So I should just look at the DDL that's created when I use EF to "Generate Database from Model"? There is a slight problem: My program creates databases a run-time, using a connection string to specify the mdf file and catalog names when instantiating the model for opening or creating a database. So is there a rule for determining the logical file names from the mdf file and catalog names (assuming no other customizations)? I really don't want to have to do additional queries; if that's unavoidable, then please show me how (in VB.NET with EF): that is, how do I query sys,database_files for the mdf and log file names? (Full disclosure: I'm not too experienced at working with the "meta-databases" that SQL Server uses to oversee "regular" databases [i.e., "master"].)

0 Votes 0 ·