Unanswered: restore backup from one database to another database
Here's the situation : I have a production database - PRODDB
I have a report and a test database - REPTDB and TESTDB respectively
After the backup maintenance plan for PRODDB runs - I want to take the backup and apply it to the test and report databases. I use the following script.
***start of script:***
declare @openfile as char(84)
-- pre-initialize production backup file
set @openfile = 'd:\program files\microsoft sql server\mssql\backup\proddb\proddb_db_yyyymmddhhmm. bak'
-- OK here it is in a nutshell
-- the object is to replace the yyyymmddhhmm string in the openfile variable with
-- the correct info so that restore will use the correct backup file for the run date.
-- The select will return the correct message field which contains the file path for
-- the last run production database backup - it's contends are :
-- 'Backup Destination: [d:\program files\microsoft sql server\mssql\backup\proddb\proddb_db_200208150202. bk]'
-- Once you have above you have to replace the yyymmddhhmm with the 200208150202 (this changes daily)
-- Then you can do the restores once you build the openfile variable
set @openfile = replace(@openfile, 'yyyymmddhhmm',right((select message
where convert(char(11),start_time) = convert(char(11), getdate())
and cast(message as char(18)) = 'Backup Destination') ,17))
-- restore test database with production backup
restore database testdb
-- Restore report database with production backup
restore database reptdb
*** End script***
****the messages/logs I get is this:****
Processed 236888 pages for database 'testdb', file 'Base' on file 1.
Processed 2 pages for database 'testdb', file 'Base_log' on file 1.
RESTORE DATABASE successfully processed 236890 pages in 270.579 seconds (7.172 MB/sec).
Processed 236888 pages for database 'reptdb', file 'Base' on file 1.
Processed 2 pages for database 'reptdb', file 'Base_log' on file 1.
RESTORE DATABASE successfully processed 236890 pages in 276.522 seconds (7.017 MB/sec).
**** end message****
The problem I have is that the REPTDB and TESTDB are left in a (loading...) state when you look in Enterprise Manager. It say's that they are in a restore process.
Can anyone tell me what's going on and how to correct this?