Get correct Logical File name.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Get correct Logical File name.

Author

Topic

patshaw
Posting Yak Master

177 Posts

Posted - 2008-09-17 : 09:52:51

Hi,

I am attempting to produce a script to perform a cross network restore of multiple databases onto a different server incorporating SQLBackup from Red Gate. The full backups on the source server were performed using SQLBackup. Once restored, the .mdf and .ldf's need to reside in a different directory on the destination server to the source server. To accomplish this I have incorporated WITH MOVE into the cursor.

The problem I have is that the logical filenames are inconsistent. Some are '_DAT', some are '_DATA' and some are just the same as the database name. So, when I run the code below:


declare @dbname sysname
declare @cmd varchar(2000)
declare csr cursor for
	select [name] from  catsql.master.dbo.sysdatabases where dbid>4
order by 1
open csr
fetch next from csr into @dbname
while @@fetch_status = 0
begin 

SET @cmd = 'exec master..sqlbackup ''-SQL "RESTORE DATABASE [' + @dbname + '] FROM DISK = [\\CATSQL\D$\backups\' + @dbname + '(FULL)_' + convert(varchar(8), getdate(), 112) + '.sqb] WITH NORECOVERY, MOVE [' + @dbname + '_dat] TO [F:\MSSQL.1\' + @dbname + '.mdf], MOVE [' + @dbname + '_log] TO [E:\MSSQL.1\' + @dbname + '.ldf]" '''

print @cmd
exec (@cmd)
	
	fetch next from csr into @dbname
end
close csr
deallocate csr

I ultimately get this error for certain databases:
SQL error 3234: SQL error 3234: Logical file 'MyDatabase' is not part of database 'MyDatabase'. Use RESTORE FILELISTONLY to list the logical file names.

How can I pull the correct file names for each database into the cursor?

Thanks,

Pat.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-09-17 : 10:08:40

What I have done is create a #table with the same structure as the output of restore filelistonly. Then insert into that table the results of that command. Then you build the restore command based on the file(s) in your #table.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources