Reposition Subtotals: resore

Showing posts with label resore. Show all posts

Friday, March 30, 2012

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:
>> 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.
> >
> >
> >
> >
> >
>
>|||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:
>> 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.
> >
> >
> >
> >
> >
>
>
>|||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?
>
> > 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:
> >> 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.
> > >
> > >
> > >
> > >
> > >
> >
> >
> >
>
>|||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,@.logicalLogFileName
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,@.logicalDataFileName
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,@.logicalDataFileName
--
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 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.
>|||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,@.logicalDataFileName
> --
> 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 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.
>sql

Resore all databases with the same logical filename in a loop

Resore .bak to SQL Express

Our vendor has provided us a copy of our data in a .bak files from SQL server. I want to run reports against this data. I have installed Express as well as Server management studio.

I presume I need to restore the .BAK file before I can run any reports? If so how does one do this?

Thanks in advance

In the UI right click on a database and select restore, or review the restore command in Books On Line;

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ra-rz_25rm.asp

Also here is a short example;

RESTORE DATABASE MyNwind
FROM MyNwind_1

Wednesday, March 7, 2012

reposting: bkup and resore strategy. Please reply

Hi,
My vb.net app will use database which will be deployed using MSDE on client
machines. I will provide UI to client in my app to backup and restore the d
atabase. My app will work with one database, for e.g. MyAppDB. My question
is what are the best pract
ices for doing this:
a) Should I choose Full or Simple mode. There is no mission critical data.
But as my clients will be using MSDE, will setting up the recovery mode as
Full cause the database and log size limits to cross the 2GB limit soon. Is
it better if I choose SIMP
LE recovery mode. What do you suggest.
b) Should I back up the system databases also, if yes then what should be th
e order of backing these up (and then in what order should the databases be
restored)
c) Does the MSDE login information also needs to be backed up. At max I wil
l have just one custom login and sa. I am using SQL Authentication.
d) Will I need to close my application and make sure that my apps' MSDE inst
ance is not running before backing up the databases.
d) I want to provide user the option to choose the backup location. How wil
l I figure out if they chose something like tape drive or zip drive. How sh
ould I handle this scenario.
e) Also how can I find out if there is sufficient space on the hard drive to
backup the database. Are there any standard sql functions to check that.
f) Should I do database log file backup too while backing up the database.
sorry for so many questions.
thanks
newbie
Print | Copy URL of this post
Expand All Collapse AllSee answers inline:
"newbie" <anonymous@.discussions.microsoft.com> wrote in message
news:3DFC5E47-73A3-4CFC-9161-0AF13E789AD5@.microsoft.com...
> Hi,
> My vb.net app will use database which will be deployed using MSDE on
client machines. I will provide UI to client in my app to backup and
restore the database. My app will work with one database, for e.g. MyAppDB.
My question is what are the best practices for doing this:
> a) Should I choose Full or Simple mode. There is no mission critical
data. But as my clients will be using MSDE, will setting up the recovery
mode as Full cause the database and log size limits to cross the 2GB limit
soon. Is it better if I choose SIMPLE recovery mode. What do you suggest.
I almost always use either full or bulk-logged, although there are a few
instances where simple is appropriate. Basically you need to determine in
the event of failure do you want to be able to recover to the point of
failure, or is it good engoug to recover from the last full backup? As for
size limits, you can control the size of the log by backing it up regularly.
Besides, I am pretty sure that the 2Gb size limit is on the data file, not
the log.

> b) Should I back up the system databases also, if yes then what should be
the order of backing these up (and then in what order should the databases
be restored)
Yes, at least master and msdb, but the order of backing them up is not
important. Master would be the first to be restored

> c) Does the MSDE login information also needs to be backed up. At max I
will have just one custom login and sa. I am using SQL Authentication.
The logins are stored in master.

> d) Will I need to close my application and make sure that my apps' MSDE
instance is not running before backing up the databases.
No, backups are an online operation.

> d) I want to provide user the option to choose the backup location. How
will I figure out if they chose something like tape drive or zip drive. How
should I handle this scenario.
That's an application issue, you stated that you were providing them a front
end UI to backup the databases. The destination of the backup files is just
one of the BACKUP command arguments. There are also several arguments that
apply only to tape backup operations that you might want to take into
consideration.

> e) Also how can I find out if there is sufficient space on the hard drive
to backup the database. Are there any standard sql functions to check that.
There isn't a function that will estimate the size of the backup, but you
can read the msdb..backupset table to get the size of the last backup (or an
average of the last several) and estimate the size based on that.

> f) Should I do database log file backup too while backing up the database.
Not at the same time, although it won't hurt anything, there's just no
reason to do them at the same time.

> sorry for so many questions.
> thanks
> newbie
>
> Print | Copy URL of this post
>
> Expand All Collapse All
>|||Thanks don. Here are few more concerns that I have:
1> Is there any way to zip the .bak files into just 1 file ising T-sql or wi
ll I have to resort to 3rd party solutions for compression (as .Net doesn't
have any compression lib)
2> What happens if one user chooses to backup while others are still working
on the same database (imagine a small office setup with few computers and m
y app on all of them and the db on one central machine). Will the backup wor
k fine in this situation or
will the users need to log off.
3> Also we are not aiming at schedule backups option right now. Our clients
will have to go to the UI screen and backup the database themselves when the
y want to. So in this situation should I restore system databases also when
the users choose to restore
my app's db from the backed up location. And do I need to backup the system
databases always when they choose to backup my apps db or just before any ma
jor important changes.
Thanks

reposting: bkup and resore strategy. Please reply

Hi,
My vb.net app will use database which will be deployed using MSDE on client machines. I will provide UI to client in my app to backup and restore the database. My app will work with one database, for e.g. MyAppDB. My question is what are the best pract
ices for doing this:
a) Should I choose Full or Simple mode. There is no mission critical data. But as my clients will be using MSDE, will setting up the recovery mode as Full cause the database and log size limits to cross the 2GB limit soon. Is it better if I choose SIMP
LE recovery mode. What do you suggest.
b) Should I back up the system databases also, if yes then what should be the order of backing these up (and then in what order should the databases be restored)
c) Does the MSDE login information also needs to be backed up. At max I will have just one custom login and sa. I am using SQL Authentication.
d) Will I need to close my application and make sure that my apps' MSDE instance is not running before backing up the databases.
d) I want to provide user the option to choose the backup location. How will I figure out if they chose something like tape drive or zip drive. How should I handle this scenario.
e) Also how can I find out if there is sufficient space on the hard drive to backup the database. Are there any standard sql functions to check that.
f) Should I do database log file backup too while backing up the database.
sorry for so many questions.
thanks
newbie
Print | Copy URL of this post
Expand All Collapse All
See answers inline:
"newbie" <anonymous@.discussions.microsoft.com> wrote in message
news:3DFC5E47-73A3-4CFC-9161-0AF13E789AD5@.microsoft.com...
> Hi,
> My vb.net app will use database which will be deployed using MSDE on
client machines. I will provide UI to client in my app to backup and
restore the database. My app will work with one database, for e.g. MyAppDB.
My question is what are the best practices for doing this:
> a) Should I choose Full or Simple mode. There is no mission critical
data. But as my clients will be using MSDE, will setting up the recovery
mode as Full cause the database and log size limits to cross the 2GB limit
soon. Is it better if I choose SIMPLE recovery mode. What do you suggest.
I almost always use either full or bulk-logged, although there are a few
instances where simple is appropriate. Basically you need to determine in
the event of failure do you want to be able to recover to the point of
failure, or is it good engoug to recover from the last full backup? As for
size limits, you can control the size of the log by backing it up regularly.
Besides, I am pretty sure that the 2Gb size limit is on the data file, not
the log.

> b) Should I back up the system databases also, if yes then what should be
the order of backing these up (and then in what order should the databases
be restored)
Yes, at least master and msdb, but the order of backing them up is not
important. Master would be the first to be restored

> c) Does the MSDE login information also needs to be backed up. At max I
will have just one custom login and sa. I am using SQL Authentication.
The logins are stored in master.

> d) Will I need to close my application and make sure that my apps' MSDE
instance is not running before backing up the databases.
No, backups are an online operation.

> d) I want to provide user the option to choose the backup location. How
will I figure out if they chose something like tape drive or zip drive. How
should I handle this scenario.
That's an application issue, you stated that you were providing them a front
end UI to backup the databases. The destination of the backup files is just
one of the BACKUP command arguments. There are also several arguments that
apply only to tape backup operations that you might want to take into
consideration.

> e) Also how can I find out if there is sufficient space on the hard drive
to backup the database. Are there any standard sql functions to check that.
There isn't a function that will estimate the size of the backup, but you
can read the msdb..backupset table to get the size of the last backup (or an
average of the last several) and estimate the size based on that.

> f) Should I do database log file backup too while backing up the database.
Not at the same time, although it won't hurt anything, there's just no
reason to do them at the same time.

> sorry for so many questions.
> thanks
> newbie
>
> Print | Copy URL of this post
>
> Expand All Collapse All
>
|||Thanks don. Here are few more concerns that I have:
1> Is there any way to zip the .bak files into just 1 file ising T-sql or will I have to resort to 3rd party solutions for compression (as .Net doesn't have any compression lib)
2> What happens if one user chooses to backup while others are still working on the same database (imagine a small office setup with few computers and my app on all of them and the db on one central machine). Will the backup work fine in this situation or
will the users need to log off.
3> Also we are not aiming at schedule backups option right now. Our clients will have to go to the UI screen and backup the database themselves when they want to. So in this situation should I restore system databases also when the users choose to restore
my app's db from the backed up location. And do I need to backup the system databases always when they choose to backup my apps db or just before any major important changes.
Thanks

Friday, March 30, 2012

Resore all databases with the same logical filename in a loop

Resore all databases with the same logical filename in a loop

Resore .bak to SQL Express

Wednesday, March 7, 2012

reposting: bkup and resore strategy. Please reply

reposting: bkup and resore strategy. Please reply

Reposition Subtotals

Blog Archive

About Me