0

I'm super lazy so I'm spending hours writing a batch file to handle restoring a database to a sql server and making a whole bunch of changes to it after that happens.

Different people send me the databases that I'm using.

I only need the database and the transaction log, skipping any other files. Currently the following works for most of the databases I have.

This part is in the batch.

SQLCMD -S %SName% -U %UName% -P %Pwd% -v varDBName=%DBName% varDBPath=%1 varSQLLogPath=%SQLLogPath% varSQLDataPath=%SQLDataPath% -i "%RunningPath%\RestoreScript.sql" -o "%RunningPath%\Restore_Log.txt"

This is the restore script I'm calling.

RESTORE DATABASE [$(varDBName)] FROM DISK = N'$(varDBPath)' WITH FILE = 1, MOVE N'MQ1CS_Data' TO N'$(varSQLDataPath)\$(varDBName).MDF', MOVE N'MQ1CS_Log' TO N'$(varSQLLogPath)\$(varDBName).LDF',  NOUNLOAD, REPLACE, STATS = 10

The problem I just ran into is that the database I'm trying to restore has a third file, something called sysFT_OpenIssuesFT. I'm guessing the filename on this changes depending on how the DBA sets it up. Is there a way to only restore the above two files? Will the database work without this third full text file?

How can I write that SQL script so that only the data and log files get restored up and nothing else?

indiv
  • 17,306
  • 6
  • 61
  • 82
Konraden
  • 35
  • 4
  • Yes, it is possible but we don't want this as you'll loose full text search catalog. – bjnr Dec 03 '13 at 21:07
  • I don't need the full-text search catalog. I'm only hunting for certain problems in the data itself and the catalog isn't important for me to do this. – Konraden Dec 04 '13 at 14:14

1 Answers1

1

There can be multiple data files and even log file for each database. But even if you only have 1 data and 1 log file than the logical name of the file can still be different for each database.

You can use the following code to retrieve all the files from a backup file:

CREATE TABLE #filelist
(
    LogicalName NVARCHAR(128),
    PhysicalName NVARCHAR(260),
    Type CHAR(1),
    FileGroupName NVARCHAR(128),
    Size NUMERIC(20,0),
    MaxSize NUMERIC(20,0),
    FileId TINYINT,
    CreateLSN NUMERIC(25,0),
    DropLSN NUMERIC(25, 0),
    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)
);

INSERT INTO #filelist
    EXEC('RESTORE FILELISTONLY  FROM  DISK = N''$(varDBPath)''');

SELECT * FROM #filelist;

DROP TABLE #filelist;

With the file names available in the temp table #filelist you should be able to create the correct restore statement.

JodyT
  • 4,324
  • 2
  • 19
  • 31