Resore all databases with the same logical filename in a loop

I have about 500 databases. They all have the same logical filenames
"application_db_data" and "application_db_log". I am moving them from
SQL 2000 to SQL 2005 on a new server, keeping all logical filenames.
I have a table that stores all database parameters needed for RESTORE:
database_name, backup_file_location, current_Data_logical_file,
current_Log_logical_file, new_Data_logical_file, new_Log_logical_file
As I said, current_Data_logical_file="application_db_data" for all DBs,
current_log_logical_file="application_db_log" for all DBs.
I created a SP called "PROC_RESTORE_ALL_DBS". Inside it there is a loop
through the table (using cursor) and run the following restore statement:
RESTORE DATABASE @.database_name
FROM DISK = backup_file_location
WITH
MOVE @.current_Data_logical_file TO @.new_Data_logical_file,
MOVE @.current_Log_logical_file TO @.new_Log_logical_file,
REPLACE
--
Unfortunately, I can only restore One database (the first one) for each
run. There are error messages for the resore of the second database and
the ones after it:
Msg 3234, Level 16, State 2, Procedure PROC_RESTORE_ALL_DBS, Line 70
Logical file 'application_db_log' is not part of database 'App_DB1'. Use
RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Procedure PROC_RESTORE_ALL_DBS, Line 70
RESTORE DATABASE is terminating abnormally.
--
Why is this happening? Is it because the loop is running too fast and
the logical file is still being held? Why is the error on the
'application_db_log', not on 'application_db_data'?
Anyway to get around it? I am running the SP on SQL 2005.I'm not sure what the problem might be. I successfully restored multiple
databases using the cursor example below. In any case, you might find it
easier to generate and execute a single script using the SQL 2005
varchar(MAX):
--script example
DECLARE @.RestoreStatements nvarchar(MAX)
SET @.RestoreStatements = ''
SELECT
@.RestoreStatements = @.RestoreStatements + '
DATABASE [' + database_name + ']
FROM DISK=''' + backup_file_location + '''
WITH
MOVE ''' + current_Data_logical_file +
''' TO ' + '''' + new_Data_logical_file + ''',
MOVE ''' + current_Log_logical_file +
''' TO ''' + new_Log_logical_file + ''''
FROM DatabaseNames
EXEC sp_executesql @.RestoreStatements
GO
--cursor example
DECLARE
@.database_name sysname,
@.backup_file_location varchar(255),
@.current_Data_logical_file sysname,
@.current_Log_logical_file sysname,
@.new_Data_logical_file varchar(255),
@.new_Log_logical_file varchar(255)
DECLARE DatabaseNames
CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
SELECT
database_name,
backup_file_location,
current_Data_logical_file,
current_Log_logical_file,
new_Data_logical_file,
new_Log_logical_file
FROM DatabaseNames
OPEN DatabaseNames
WHILE 1 = 1
BEGIN
FETCH NEXT FROM DatabaseNames INTO
@.database_name,
@.backup_file_location,
@.current_Data_logical_file,
@.current_Log_logical_file,
@.new_Data_logical_file,
@.new_Log_logical_file
IF @.@.FETCH_STATUS = -1 BREAK
RESTORE DATABASE @.database_name
FROM DISK=@.backup_file_location
WITH
MOVE @.current_Data_logical_file TO @.new_Data_logical_file,
MOVE @.current_Log_logical_file TO @.new_Log_logical_file
END
CLOSE DatabaseNames
DEALLOCATE DatabaseNames
Hope this helps.
Dan Guzman
SQL Server MVP
"q_test" <jluost1@.yahoo.com> wrote in message
news:443FC542.6050509@.yahoo.com...
>I have about 500 databases. They all have the same logical filenames
>"application_db_data" and "application_db_log". I am moving them from SQL
>2000 to SQL 2005 on a new server, keeping all logical filenames.
> I have a table that stores all database parameters needed for RESTORE:
> database_name, backup_file_location, current_Data_logical_file,
> current_Log_logical_file, new_Data_logical_file, new_Log_logical_file
> As I said, current_Data_logical_file="application_db_data" for all DBs,
> current_log_logical_file="application_db_log" for all DBs.
> I created a SP called "PROC_RESTORE_ALL_DBS". Inside it there is a loop
> through the table (using cursor) and run the following restore statement:
> --
> RESTORE DATABASE @.database_name
> FROM DISK = backup_file_location
> WITH
> MOVE @.current_Data_logical_file TO @.new_Data_logical_file,
> MOVE @.current_Log_logical_file TO @.new_Log_logical_file,
> REPLACE
> --
> Unfortunately, I can only restore One database (the first one) for each
> run. There are error messages for the resore of the second database and
> the ones after it:
> --
> Msg 3234, Level 16, State 2, Procedure PROC_RESTORE_ALL_DBS, Line 70
> Logical file 'application_db_log' is not part of database 'App_DB1'. Use
> RESTORE FILELISTONLY to list the logical file names.
> Msg 3013, Level 16, State 1, Procedure PROC_RESTORE_ALL_DBS, Line 70
> RESTORE DATABASE is terminating abnormally.
> --
> Why is this happening? Is it because the loop is running too fast and the
> logical file is still being held? Why is the error on the
> 'application_db_log', not on 'application_db_data'?
> Anyway to get around it? I am running the SP on SQL 2005.
>
>
>|||I am using almost exact code as you have laid out. Since you didn't have
any problem, maybe the problem is in my backup file.
So I ran the following as instructed in error message:
RESTORE FILELISTONLY from disk='D:\Database\Backup\App_DB1.bak'
and here is what I see:
there are two files:
LogicalName, Type, FileGroupName, FileSize, FileID, isPresent
application_db_Data, D, Primary, 232343, 1, 1
application_db_log, L, NULL, 34433, 2, 0
Note the the second file's isPresent=0. Is it causing the error of
RESTORE? Why isPresent=0 since size>0? How can I get around?
Thank you very much on this.
Dan Guzman wrote:
> I'm not sure what the problem might be. I successfully restored multiple
> databases using the cursor example below. In any case, you might find it
> easier to generate and execute a single script using the SQL 2005
> varchar(MAX):
> --script example
> DECLARE @.RestoreStatements nvarchar(MAX)
> SET @.RestoreStatements = ''
> SELECT
> @.RestoreStatements = @.RestoreStatements + '
> DATABASE [' + database_name + ']
> FROM DISK=''' + backup_file_location + '''
> WITH
> MOVE ''' + current_Data_logical_file +
> ''' TO ' + '''' + new_Data_logical_file + ''',
> MOVE ''' + current_Log_logical_file +
> ''' TO ''' + new_Log_logical_file + ''''
> FROM DatabaseNames
> EXEC sp_executesql @.RestoreStatements
> GO
> --cursor example
> DECLARE
> @.database_name sysname,
> @.backup_file_location varchar(255),
> @.current_Data_logical_file sysname,
> @.current_Log_logical_file sysname,
> @.new_Data_logical_file varchar(255),
> @.new_Log_logical_file varchar(255)
> DECLARE DatabaseNames
> CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
> SELECT
> database_name,
> backup_file_location,
> current_Data_logical_file,
> current_Log_logical_file,
> new_Data_logical_file,
> new_Log_logical_file
> FROM DatabaseNames
> OPEN DatabaseNames
> WHILE 1 = 1
> BEGIN
> FETCH NEXT FROM DatabaseNames INTO
> @.database_name,
> @.backup_file_location,
> @.current_Data_logical_file,
> @.current_Log_logical_file,
> @.new_Data_logical_file,
> @.new_Log_logical_file
> IF @.@.FETCH_STATUS = -1 BREAK
> RESTORE DATABASE @.database_name
> FROM DISK=@.backup_file_location
> WITH
> MOVE @.current_Data_logical_file TO @.new_Data_logical_file,
> MOVE @.current_Log_logical_file TO @.new_Log_logical_file
> END
> CLOSE DatabaseNames
> DEALLOCATE DatabaseNames
>
"q_test" <jluost1@.yahoo.com> wrote in message
news:443FC542.6050509@.yahoo.com...

>I have about 500 databases. They all have the same logical filenames
>"application_db_data" and "application_db_log". I am moving them from SQL
>2000 to SQL 2005 on a new server, keeping all logical filenames.
> I have a table that stores all database parameters needed for RESTORE:
> database_name, backup_file_location, current_Data_logical_file,
> current_Log_logical_file, new_Data_logical_file, new_Log_logical_file
> As I said, current_Data_logical_file="application_db_data" for all DBs,
> current_log_logical_file="application_db_log" for all DBs.
> I created a SP called "PROC_RESTORE_ALL_DBS". Inside it there is a loop
> through the table (using cursor) and run the following restore statement:
> --
> RESTORE DATABASE @.database_name
> FROM DISK = backup_file_location
> WITH
> MOVE @.current_Data_logical_file TO @.new_Data_logical_file,
> MOVE @.current_Log_logical_file TO @.new_Log_logical_file,
> REPLACE
> --
> Unfortunately, I can only restore One database (the first one) for each
> run. There are error messages for the resore of the second database and
> the ones after it:
> --
> Msg 3234, Level 16, State 2, Procedure PROC_RESTORE_ALL_DBS, Line 70
> Logical file 'application_db_log' is not part of database 'App_DB1'. Use
> RESTORE FILELISTONLY to list the logical file names.
> Msg 3013, Level 16, State 1, Procedure PROC_RESTORE_ALL_DBS, Line 70
> RESTORE DATABASE is terminating abnormally.
> --
> Why is this happening? Is it because the loop is running too fast and
the
> logical file is still being held? Why is the error on the
> 'application_db_log', not on 'application_db_data'?
> Anyway to get around it? I am running the SP on SQL 2005.
>
>
>|||> Note the the second file's isPresent=0. Is it causing the error of
> RESTORE? Why isPresent=0 since size>0? How can I get around?
Was this a full backup that completed successfully? I haven't seen
IsPresent = 0 before.
In your initial post, you mentioned that you can successfully restore the
first backup from 'each run'. Does this mean that when you later skip the
one that restored successfully, the second database then restores
successfully but the subsequent ones fail?
Hope this helps.
Dan Guzman
SQL Server MVP
"q_test" <jluost1@.yahoo.com> wrote in message
news:443FDD53.2090600@.yahoo.com...
>I am using almost exact code as you have laid out. Since you didn't have
>any problem, maybe the problem is in my backup file.
> So I ran the following as instructed in error message:
> RESTORE FILELISTONLY from disk='D:\Database\Backup\App_DB1.bak'
> and here is what I see:
> there are two files:
> LogicalName, Type, FileGroupName, FileSize, FileID, isPresent
> application_db_Data, D, Primary, 232343, 1, 1
> application_db_log, L, NULL, 34433, 2, 0
> Note the the second file's isPresent=0. Is it causing the error of
> RESTORE? Why isPresent=0 since size>0? How can I get around?
> Thank you very much on this.
> Dan Guzman wrote:
> "q_test" <jluost1@.yahoo.com> wrote in message
> news:443FC542.6050509@.yahoo.com...
>
> the
>
>|||Yes, it is a complete backup. Yes, the first one is always restored
successfully but the subsequent ones failed.
In my table, if one is restored successfully, it is flagged. So the next
run will skip it.
I can get each one restored by run the SP 500 times. But I want it done
with one run...
From the error message I found that, starting from the second database,
it is trying to find the physical directory of the logical file on the
server, which doesn't exist any more. The logical file's physical
directory is like "C:\MSSQL$instance1\data\app_db1.mdb". In the new
server, it is like "C:\MSSQL05_instance1\data\app_db1.mdb".
A revised code is more like:
RESTORE DATABASE @.database_name
FROM DISK=@.backup_file_location
WITH
MOVE @.current_Data_logical_file TO @.new_Data_file_LOCATION,
MOVE @.current_Log_logical_file TO @.new_Log_file_LOCATION
END
--
Why is the old physical location used? And only from the second database?

> Note the the second file's isPresent=0. Is it causing the error of
> RESTORE? Why isPresent=0 since size>0? How can I get around?
Was this a full backup that completed successfully? I haven't seen
IsPresent = 0 before.
In your initial post, you mentioned that you can successfully restore the
first backup from 'each run'. Does this mean that when you later skip the
one that restored successfully, the second database then restores
successfully but the subsequent ones fail?
-- Hope this helps. Dan Guzman SQL Server MVP "q_test"
<jluost1@.yahoo.com> wrote in message news:443FDD53.2090600@.yahoo.com...

>I am using almost exact code as you have laid out. Since you didn't have
>any problem, maybe the problem is in my backup file.
> So I ran the following as instructed in error message:
> RESTORE FILELISTONLY from disk='D:\Database\Backup\App_DB1.bak'
> and here is what I see:
> there are two files:
> LogicalName, Type, FileGroupName, FileSize, FileID, isPresent
> application_db_Data, D, Primary, 232343, 1, 1
> application_db_log, L, NULL, 34433, 2, 0
> Note the the second file's isPresent=0. Is it causing the error of
> RESTORE? Why isPresent=0 since size>0? How can I get around?
> Thank you very much on this.
> Dan Guzman wrote:

multiple[vbcol=seagreen]
find it[vbcol=seagreen]
[vbcol=seagreen]
> "q_test" <jluost1@.yahoo.com> wrote in message
> news:443FC542.6050509@.yahoo.com...
>

from SQL[vbcol=seagreen]
'App_DB1'. Use[vbcol=seagreen]
[vbcol=seagreen]
> the

[vbcol=seagreen]
>
>|||It seems like SQL Server isn't recognizing the 'MOVE' and/or logical file
names on the subsequent restores. Can you post your actual script? I'd
like to try to reproduce your problem.
Hope this helps.
Dan Guzman
SQL Server MVP
"q_test" <jluost1@.yahoo.com> wrote in message
news:443FEB36.2090408@.yahoo.com...
> Yes, it is a complete backup. Yes, the first one is always restored
> successfully but the subsequent ones failed.
> In my table, if one is restored successfully, it is flagged. So the next
> run will skip it.
> I can get each one restored by run the SP 500 times. But I want it done
> with one run...
> From the error message I found that, starting from the second database, it
> is trying to find the physical directory of the logical file on the
> server, which doesn't exist any more. The logical file's physical
> directory is like "C:\MSSQL$instance1\data\app_db1.mdb". In the new
> server, it is like "C:\MSSQL05_instance1\data\app_db1.mdb".
> A revised code is more like:
> --
> RESTORE DATABASE @.database_name
> FROM DISK=@.backup_file_location
> WITH
> MOVE @.current_Data_logical_file TO @.new_Data_file_LOCATION,
> MOVE @.current_Log_logical_file TO @.new_Log_file_LOCATION
> END
> --
> Why is the old physical location used? And only from the second database?
>
>
> Was this a full backup that completed successfully? I haven't seen
> IsPresent = 0 before.
> In your initial post, you mentioned that you can successfully restore the
> first backup from 'each run'. Does this mean that when you later skip the
> one that restored successfully, the second database then restores
> successfully but the subsequent ones fail?
> -- Hope this helps. Dan Guzman SQL Server MVP "q_test" <jluost1@.yahoo.com>
> wrote in message news:443FDD53.2090600@.yahoo.com...
>
>
> multiple
> find it
>
>
> from SQL
> 'App_DB1'. Use
>
>
>
>
>|||Here is the script for SP, table, insert data and exec SP statements:
CREATE PROCEDURE PROC_RESTORE_ALL_DBS
AS
/*
Purpose: upgrate from SQL 2000 to SQL 2005 by restoring
*/
set nocount on
declare @.oneWebDB varchar(75)
declare @.datapath varchar(100)
declare @.logicalDataFileName varchar(100),@.logicalLogFileName varchar(100)
declare @.DestPath varchar(100)
DECLARE @.err int
declare @.updQ varchar(300)
declare @.back_db_name varchar(75),@.diskname varchar(75)
declare @.full_bk_data_file varchar(75), @.full_bk_log_file varchar(75),
@.full_data_file varchar(75), @.full_log_file varchar(75),
@.logicalFileName varchar(100)
set @.datapath = 'D:\Migration\Backup\DEFAULT'
set @.DestPath = 'D:\MSSQL05_DEFAULT'
declare all_web_db cursor
LOCAL
READ_ONLY
for
select db_name_used
,logicalDataFileName
,logicalLogFileName
from migration_table_info
where is_not_active=0
and is_migrated=0
order by db_name_used
open all_web_db
fetch next from all_web_db
into @.oneWebDB,@.logicalDataFileName,@.logicalL
ogFileName
while @.@.FETCH_STATUS = 0 begin
begin
print '-- Restore database ' + @.oneWebDB + '
--'
set @.back_db_name = @.oneWebDB + '.bak'
set @.diskname = @.datapath +@.back_db_name
set @.full_bk_data_file =@.logicalDataFileName
set @.full_bk_log_file = @.logicalLogFileName
set @.full_data_file = @.DestPath + 'data' + @.oneWebDB + '_data.mdf'
set @.full_log_file = @.DestPath + 'Log' + @.oneWebDB + '_log.ldf'
print ' ' + @.diskname + ' '
print ' ' + @.full_data_file + ' '
print ' ' + @.full_log_file + ' '
--RESTORE FILELISTONLY
--FROM DISK = @.diskname
RESTORE DATABASE @.oneWebDB
FROM DISK = @.diskname
WITH
MOVE @.full_bk_data_file TO @.full_data_file,
MOVE @.full_bk_log_file TO @.full_log_file
--,REPLACE
-- update [migration_table_info] is_migrated=1
SELECT @.err = @.@.error
IF (@.err = 0)
BEGIN
set @.updQ = 'UPDATE migration_table_info SET is_migrated=1 WHERE
db_name_used = ''' + @.oneWebDB + ''''
execute(@.updQ)
END
end
fetch next from all_web_db into
@.oneWebDB,@.logicalDataFileName,@.logicalD
ataFileName
end
close all_web_db
deallocate all_web_db
set nocount off
return 1
GO
Here is the script for database table:
CREATE TABLE migration_table_info (
db_name_used varchar(75) primary key,
logicalDataFileName varchar(100) null,
logicalLogFileName varchar(100) null,
is_not_active bit null,
is_migrated bit null
)
--
Here is two example of database info:
--
INSERT INTO migration_table_info VALUES ('m7E','app_Data','app_Log',0,0)
INSERT INTO migration_table_info VALUES ('m7F','app_Data','app_Log',0,0)
--
Use the following to run the SP:
exec PROC_RESTORE_ALL_DBS
--
Hope that helps...
Dan Guzman wrote:
> It seems like SQL Server isn't recognizing the 'MOVE' and/or logical file
> names on the subsequent restores. Can you post your actual script? I'd
> like to try to reproduce your problem.
>|||I created 100 SQL 2000 databases, backup these up and then ran your script
under SQL 2005 SP1 CTP. I changed only the folder names and database names.
All 100 databases restored without problems.
From your initial post:
Msg 3234, Level 16, State 2, Procedure PROC_RESTORE_ALL_DBS, Line 70
Logical file 'application_db_log' is not part of database 'App_DB1'. Use
RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Procedure PROC_RESTORE_ALL_DBS, Line 70
RESTORE DATABASE is terminating abnormally.
--
And in a later post:
---
From the error message I found that, starting from the second database,
it is trying to find the physical directory of the logical file on the
server, which doesn't exist any more. The logical file's physical
directory is like "C:\MSSQL$instance1\data\app_db1.mdb". In the new
server, it is like "C:\MSSQL05_instance1\data\app_db1.mdb".
---
Why do you say that subsequent restores are looking for the physical
directory of the original server? I don't see that in the error you posted.
Hope this helps.
Dan Guzman
SQL Server MVP
"q_test" <jluost1@.yahoo.com> wrote in message
news:443FF33D.70204@.yahoo.com...
> Here is the script for SP, table, insert data and exec SP statements:
>|||Dan Guzman,
You asked the right question. I looked at my code again and found a very
stupid mistake on the line below:
fetch next from all_web_db into
@.oneWebDB,@.logicalDataFileName,@.logicalD
ataFileName
--
The last one should be: @.logicalLogFileName instead of @.logicalDataFileName.
It is working fine now.
Thank you for all of your time and effort. I really really appreciate it.
Dan Guzman wrote:
> I created 100 SQL 2000 databases, backup these up and then ran your script
> under SQL 2005 SP1 CTP. I changed only the folder names and database name
s.
> All 100 databases restored without problems.
> From your initial post:
> --
> Msg 3234, Level 16, State 2, Procedure PROC_RESTORE_ALL_DBS, Line 70
> Logical file 'application_db_log' is not part of database 'App_DB1'. Use
> RESTORE FILELISTONLY to list the logical file names.
> Msg 3013, Level 16, State 1, Procedure PROC_RESTORE_ALL_DBS, Line 70
> RESTORE DATABASE is terminating abnormally.
> --
> And in a later post:
> ---
> From the error message I found that, starting from the second database,
> it is trying to find the physical directory of the logical file on the
> server, which doesn't exist any more. The logical file's physical
> directory is like "C:\MSSQL$instance1\data\app_db1.mdb". In the new
> server, it is like "C:\MSSQL05_instance1\data\app_db1.mdb".
> ---
> Why do you say that subsequent restores are looking for the physical
> directory of the original server? I don't see that in the error you poste
d.
>|||I'm glad you were able to get it sorted out.
Dan Guzman
SQL Server MVP
"q_test" <jluost1@.yahoo.com> wrote in message
news:4443A02D.8040704@.yahoo.com...
> Dan Guzman,
> You asked the right question. I looked at my code again and found a very
> stupid mistake on the line below:
> --
> fetch next from all_web_db into
> @.oneWebDB,@.logicalDataFileName,@.logicalD
ataFileName
> --
> The last one should be: @.logicalLogFileName instead of
> @.logicalDataFileName.
> It is working fine now.
> Thank you for all of your time and effort. I really really appreciate it.
> Dan Guzman wrote:
>