question

RobertGustafson-1682 avatar image
0 Votes"
RobertGustafson-1682 asked PatrickHurst-1147 answered

I STILL can't find the right SQL syntax to COPY my 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

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












































sql-server-generaldotnet-visual-basicwindows-formsdotnet-runtimedotnet-entity-framework
· 2
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 do it only because 1) I don't get responses to posts that are more than a few days old, and 2) this site sometimes makes it hard to add additional replies or comments back-and-forth at some point. I try to avoid double-posting when I think it's possible.

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.

· 12
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 ·
ErlandSommarskog avatar image ErlandSommarskog RobertGustafson-1682 ·

The query you need is

SELECT name FROM sys.database_files

If you want to make it complicated and do this with EF6, I'm afraid I can't help. I'm an SQL Server guy, and I don't use EF6. They say that the purpose of ORMs is remove the object/relational impedance mismatch, but it doesn't. It just moves out of the code and instead we get these conversations between application developers and database people. No one understands what the other person is talking about.

Yeah, you can also do some reverse engineering to figure out what names when you create the database, but it safer to look in sys.database_files, in case your program now runs at this copy instead of the original.

I like to imagine that running a query directly through ADO .NET without EF6 should not be too tricky.

0 Votes 0 ·

How does one look in sys.database_files for the logical files corresponding the source (to-be-copied) database's catalog--particularly assuming one can do it using the same Context.ExecuteSQL technique that I rely on for the rest of my procedure's SQL? If you don't know, find someone who does to respond here.

It's not a terribly complex thing to, in an ORM manner, to perform the query above you mentioned, but I think it requires more than just one line in order to get all the logical-file names, and only those, corresponding to a specific catalog name. My code has other procedures that use EF together with queries against "master" to get the mdf file corresponding to a catalog and vice versa. It's not convoluted, but it requires getting multiple fields and a WHERE clause--which I imagine is true here. If you just give the full query--which I imagine getting logical-file fields when screening by catalog name--I can pick it up from there. (I sense we're close to a solution.)

0 Votes 0 ·
Show more comments

At some point, I have difficulty posting new replies and comments--especially when I try to include code snippets. This is why I periodically have to start new initial posts. Why is this? It seems like a quirk in the website.

0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered ErlandSommarskog commented

Logical file 'contactlist2.mdf 12/8/2021 6:13:57 pm_Data' is not

On Windows machines a slash (/) is not allowed in a file name., also not double points (:) and some more characters.
· 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.

Yeah, but this is the logical name which is stored inside SQL Server. Here you can have anything.

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

you need to understand logical files. a sqlserver database can be spread across multiple drives/files. when a database is created a logical name and file path is given to each file used by the database (the default is a log file and database file, but you can use more).

when you restore a database, the restore need to know the physical path for each logical file name. you can skip this step if restoring to the same database as the backup was made from, because the logical names and paths are stored in the database. if you restore to a new database, then you must specify the path for each logical file. this is done with the move clause in the restore.

if you are restore a database and do not know the logical names, then you can query the actual backup file for the logical name list.

RESTORE FILELISTONLY
FROM DISK = N'C:\AdventureWorks-FullBackup.bak';
GO

this will return a result set with all the information for each logical drive, name. physical path, and the file group name.

with this information you can restore a copy. you will need a new file path for each logical because the old files are in use.

RESTORE DATABASE AdventureWorksCopy
FROM DISK = N'C:\AdventureWorks-FullBackup.bak'
WITH
MOVE 'AdventureWorks_Data' TO 'c:\mssql\data\AdventureWorksCopy_Data.mdf',
MOVE 'AdventureWorks_Log' TO 'c:\mssql\log\AdventureWorksCopy_Log.ldf'

to do this pragmatically, you will need a way to assign new filenames. this will depend on how you want them placed. do you want the copies in the same folders with different names, or different folders, same name, or just a random name (typically the filename has the database name as a prefix).


· 2
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 gives a very good explanation of the process.

I just like to make one note about RESTORE FILELISTONLY since I've been talking about sys.database_files. They both return the relevant information for the case. If all you have is the backup, RESTORE FILELISTONLY is the only option, but since Robert wants to copy an existing database, I figured that sys.database_files was a little more straightforward.

0 Votes 0 ·

Is it possible, when querying sys.database_files, to differentiate the logical names for data (mdf) files from those for log files, just using EF and T-SQL? I need to figure all this out--programmatically--as simply as possible. (A sample code snippet--based on my code snippet--would be helpful.) Re-check my original post; it contains something I've tried.



0 Votes 0 ·
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

I suggest you read this. Specifically "GetDatabaseFileList".

https://blog.devgenius.io/backup-and-restore-sql-server-database-bak-using-c-net-4f3a848d15d

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.

RobertGustafson-1682 avatar image
0 Votes"
RobertGustafson-1682 answered

To get the logical and physical files:

 Protected Function GetDatabaseFiles(ByVal CatalogName As String, _
     ByVal FileName As String) As DatabaseFiles
     Const LogType As Byte = 1, TypeName As String = "type", _
         LogicalName As String = "name", PhysicalName As String = "physical_name"

     Dim df As DatabaseFiles = New DatabaseFiles()
     Using Context As SocialContactsTracker1 = _
             GetContextForExistingDatabase(CatalogName, FileName)
         Using c As SqlConnection = _
                 New SqlConnection(Context.Database.Connection.ConnectionString)
             c.Open()
             Using command As SqlCommand = New SqlCommand( _
                     "SELECT * FROM """ & CatalogName & """.sys.database_files;", c)
                 Using r As SqlDataReader = command.ExecuteReader()
                     Do While r.Read()
                         If r.GetByte(r.GetOrdinal(TypeName)) = LogType Then
                             df.LogicalLogFile = r.GetString(r.GetOrdinal(LogicalName))
                             df.PhysicalLogFile = r.GetString(r.GetOrdinal(PhysicalName))
                          Else
                             df.LogicalDataFile = r.GetString(r.GetOrdinal(LogicalName))
                             df.PhysicalDataFile = r.GetString(r.GetOrdinal(PhysicalName))
                         End If
                     Loop
                 End Using
             End Using
         End Using
     End Using
     Return df
 End Function

Defined within the above method's class:

 ''' <summary>
 ''' This class returns the data that was retrieved in the GetDataFiles method 
 ''' </summary>
 Protected Class DatabaseFiles
     Public LogicalDataFile As String    
     Public PhysicalDataFile As String
     Public LogicalLogFile As String
     Public PhysicalLogFile As String
 End Class

The orginal method then becomes:

 ''' <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)
     '   get files of source database
     Dim SourceFiles As DatabaseFiles = _
         GetDatabaseFiles(SourceCatalogName, SourceFileName)
     '   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 DATABASE """ & DestinationCatalogName _
                     & """ FROM DISK='" & BackupFileName _
                         & "' WITH MOVE '" & SourceFiles.LogicalDataFile & "' TO '" _
                         & DestinationFileName & "', " _
                     & "MOVE '" & SourceFiles.LogicalLogFile & "' 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








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.

PatrickHurst-1147 avatar image
0 Votes"
PatrickHurst-1147 answered

The give away here is in the error you received:

Use RESTORE FILELISTONLY to list the logical file names

 DECLARE @baseBackup NVARCHAR(200) = '\\myBackupPath\Backup.bak' 
 DECLARE @DatabaseName SYSNAME = 'MyNewDatabase', @DataPath NVARCHAR(100) = 'D:\Data\MyNewDatabase.mdf', @LogPath NVARCHAR(100) = 'F:\TLOG\MyNewDatabase_log.ldf'
    
     
 DECLARE @fileList TABLE (LogicalName NVARCHAR(128), PhysicalName NVARCHAR(260), Type CHAR(1), FileGroupName NVARCHAR(128), Size NUMERIC(20,0), MaxSize NUMERIC(20,0), FileId TINYINT, CreateLSN NUMERIC(20,0), DropLSN INT, UniqueID UNIQUEIDENTIFIER, ReadOnlyLSN NUMERIC(25,0),
                          ReadWriteLSN NUMERIC(25,0), BackupSizeInBytes BIGINT, SourceBlockSize INT, FileGroupId INT, LogGroupGUID UNIQUEIDENTIFIER, DifferentialBaseLSN NUMERIC(25,0), DifferentialBaseGUID UNIQUEIDENTIFIER, IsReadOnly BIT, IsPresent BIT, 
                          TDEThumbprint VARBINARY(32), SnapshotUrl NVARCHAR(336));
 INSERT INTO @fileList 
 EXEC ('RESTORE FILELISTONLY FROM '+'DISK'+'= N'''+@baseBackup+''' WITH  FILE = 1')
 ;WITH moves AS (
 SELECT ' MOVE '''+ LogicalName+''' TO '''+ 
 STUFF(CASE WHEN Type = 'D' THEN CAST(@DataPath AS NVARCHAR(MAX)) WHEN Type = 'L' THEN CAST(@logPath AS NVARCHAR(MAX)) END,CHARINDEX('.',@DataPath),0,
 CASE WHEN ROW_NUMBER() OVER (PARTITION BY type ORDER BY fileID) =  1 THEN '' ELSE CAST(ROW_NUMBER() OVER (PARTITION BY type ORDER BY fileID) AS NVARCHAR)
 END) AS moveString
   FROM @fileList
 )
    
 SELECT 'RESTORE DATABASE ['+@DatabaseName+'] FROM DISK '''+@baseBackup+''' WITH '+ STRING_AGG(moveString,',')
   FROM moves


You can supply the values to the variables in this script and it'll generate the restore statement for you. You may wish to alter it to add other options (NORECOVERY for example, if you intend to also restore subsequent log backups).

If you also need to find the most recent full backup, you could do something like:

 DECLARE @ExistingDatabaseName SYSNAME = 'MyTemplateDatabase'
 SELECT TOP 1 @baseBackup= bmf.physical_device_name
   FROM msdb.dbo.backupset bs
     INNER JOIN msdb.dbo.backupmediafamily bmf
       ON bs.media_set_id = bmf.media_set_id
  WHERE bs.type = 'd'
    AND @ExistingDatabaseName = bs.database_name
  ORDER BY bs.backup_start_date DESC

beforehand, to populate the baseBackup variable.

Hope this helps!

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.