BOL says that sysfiles "contains one row for each file in a database".BOL also says that sysaltfiles "under special circumstances, contains rows corresponding to the files in a database"Now to my question...I am creating a stored procedure that will grab the last full backup of each of the user databases (well not pubs or Northwind of course) and restore it to another server. I am doing this to test out the backup. I need to use the WITH MOVE option in the RESTORE command since the paths will not be the same. In order to use the WITH MOVE option, I need to know what the logical file names are. To do this, you get the name column from sysfiles, well at least that's what I thought. But...Now I was under the impression that statement number one from above was true. Well I've got this development server with a bunch of databases on it. Only the master database information is in sysfiles. The other databases are in sysaltfiles (plus master actually). I can easily get the information from sysaltfiles if it doesn't exist in sysfiles, but what I want to know is why aren't the rows there on my dev server for each of the database. I also want to know when sysaltfiles is used since it says under special circumstances. The only thing that I can think of is that the databases were created from backups and that's when it happens. What do you think?Below is the code to get the last backup (doesn't include the call to sysfiles or sysaltfiles yet). The stored procedure will also have the KILL statements in case the database exists already and users are connected, and it will also do the restore.SELECT bs.database_name, MAX(bms.physical_device_name)FROM TESTRESTORE.msdb.dbo.backupset bsINNER JOIN TESTRESTORE.msdb.dbo.backupmediafamily bms ON bs.media_set_id = bms.media_set_idINNER JOIN TESTRESTORE.master.dbo.sysdatabases s ON bs.database_name = s.nameWHERE CONVERT(VARCHAR(20), bs.backup_finish_date, 101) = CONVERT(VARCHAR(20), GETDATE(), 101) AND s.name NOT IN ('master', 'msdb', 'model', 'pubs', 'Northwind')GROUP BY bs.database_nameDoes anyone else have code that does something similar?[EDIT] Forgot to mention that in my code that TESTRESTORE is a linked server that I setup for this. It will have a different name later though. The way that it is written right now is that the code needs to run on the server where the restore will occur. [/EDIT]TaraEdited by - tduggan on 06/11/2003 17:54:57
sysfiles system table
Posted - 2003-06-11 : 17:48:13
