Restore errors
-
I have the following QA code to restore a database that is made up of 1 MDF file, 5 NDF files, and 1 LDF file:
restore database dia2 FILEGROUP = 'PRIMARY'
from disk = 'e:\diabackup\PRIMARY_2007_08_31_02_35_22.BAK'
with norecovery
restore database dia2
FILEGROUP = 'DIA_DLY'
from disk = 'e:\diabackup\DIA_DLY_2007_08_31_02_11_31.BAK'
with norecovery
restore database dia2
FILEGROUP = 'DIA_DI090'
from disk = 'e:\diabackup\DIA_DI090_2007_08_31_02_22_35.BAK'
with norecovery
restore database dia2
FILEGROUP = 'DIA_DI010'
from disk = 'e:\diabackup\DIA_DI010_2007_08_31_02_23_32.BAK'
with norecovery
restore database dia2
FILEGROUP = 'DIA_DI030'
from disk = 'e:\diabackup\DIA_DI030_2007_08_31_02_33_56.BAK'
with norecovery
restore database dia2
FILEGROUP = 'DIA_MTHLY'
from disk = 'e:\diabackup\DIA_MTHLY_2007_08_31_02_41_05.BAK'
with norecovery
restore log DIA2
--filegroup = 'dia_log'
from disk = 'e:\diabackup\DIA_LOG_2007_08_31_02_53_34.BAK'
with recovery
--********************************************************
When I run it, this is the result:
Processed 435664 pages for database 'dia2', file 'dia2_Data' on file 1.
RESTORE DATABASE successfully processed 435664 pages in 66.252 seconds (53.869 MB/sec).
Processed 315120 pages for database 'dia2', file 'DIA2_DLYDATA' on file 1.
RESTORE DATABASE successfully processed 315120 pages in 46.482 seconds (55.536 MB/sec).
Processed 78480 pages for database 'dia2', file 'DIA2_DI090' on file 1.
RESTORE DATABASE successfully processed 78480 pages in 14.090 seconds (45.628 MB/sec).
Processed 1182152 pages for database 'dia2', file 'DIA2_DI010' on file 1.
RESTORE DATABASE successfully processed 1182152 pages in 215.525 seconds (44.933 MB/sec).
Processed 169296 pages for database 'dia2', file 'DIA2_DI030' on file 1.
RESTORE DATABASE successfully processed 169296 pages in 26.604 seconds (52.130 MB/sec).
Processed 988936 pages for database 'dia2', file 'DIA2_MTHLY' on file 1.
RESTORE DATABASE successfully processed 988936 pages in 178.520 seconds (45.380 MB/sec).
Processed 11 pages for database 'DIA2', file 'dia2_Log' on file 1.
Server: Msg 3624, Level 20, State 1, Line 30
Location: filemgr.cpp:1764
Expression: IS_ON (FCB_LOG_DEVICE, saflStatus) || IS_ON (FCB_DROPPED, saflStatus)
SPID: 51
Process ID: 3240
Connection Broken
It seems to get hung up on my log file.
Any ideas would be appreciated.
Thanks!!!
-
Try this:
/*
Script made by Lester A. Policarpio September 03, 2007
Any questions or clarifications feel free to email me at:
NOTE: This script can be run instantly or by using job schedule
NOTE: Please change the path/directory to your corresponding path/directory
NOTE: Read carefully the choices given in the comment below the word "READ"
NOTE: This script is only applicable in .BAK files other files such as flat file,.TRN,etc are not applicable
*/
if exists (select name from sysobjects where name = 'migration')
DROP TABLE migration
if exists (select name from sysobjects where name = 'cmdshell')
DROP TABLE cmdshell
CREATE TABLE cmdshell
(
fentry varchar(1000)
)
CREATE TABLE migration
(
LogicalName varchar(1024),
PhysicalName varchar(4000),
type char(1),
FileGroupName varchar(50),
size real,
MaxSize real
)
DECLARE @path varchar(1024)
DECLARE @restore varchar(1024)
DECLARE @index int
DECLARE @date varchar(200)
DECLARE @db varchar(1024)
DECLARE @cmd2 varchar(1024)
DECLARE @restoredb varchar(2000)
DECLARE @migrate2 varchar(1024)
DECLARE @extension varchar(1024)
DECLARE @pathension varchar(1024)
DECLARE @newpath varchar(1024)
SET @newpath = 'D:\lester\' --new path wherein you will put the mdf/ldf
SET @path = 'D:\lester1\' --Path of the Backup File
SET @extension = 'BAK'
SET @pathension = 'dir /OD '+@Path+'*.'+@Extension
INSERT INTO cmdshell exec master..xp_cmdshell @pathension
DELETE FROM cmdshell WHERE FEntry NOT LIKE '%BAK%'
DELETE FROM cmdshell WHERE FEntry is NULL
DECLARE @migrate varchar(1024)
DECLARE migrate CURSOR FOR
select substring(FEntry,40,50) as 'FEntry',substring(Fentry,40,
charindex('.BAK',fentry)-56) as 'FEntry2' --56 is the character position of your backup depends upon the name of your backupfile
from cmdshell
OPEN migrate
FETCH NEXT FROM migrate INTO @migrate,@migrate2
WHILE (@@FETCH_STATUS = 0)
BEGIN
print @db
set @restore = 'RESTORE FILELISTONLY FROM DISK = '+''''+@path+@migrate+''''
INSERT INTO migration EXEC (@restore)
UPDATE migration SET physicalname = @newpath+substring(physicalname,15,50)
--@@@@@@@@@@@@@@@@@@@@
SET @restoredb = 'RESTORE DATABASE '+@migrate2+' FROM DISK = '+ ''''+@path+@migrate+''''
+' WITH MOVE '+''''+ (select logicalname from migration where type = 'D')+''''+
' TO '+ ''''+( select physicalname from migration WHERE physicalname like '%mdf%')+''''+
', MOVE '+''''+ (select logicalname from migration where type = 'L')+''''+
' TO '+''''+( select physicalname from migration WHERE physicalname like '%ldf%')+''''
--@@@ @@@ @@@@ @@@@
--@ @ @@ @ @ @ @
--@ @ @ @@@@ @ @
--@ @ @@@ @ @ @ @@
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
--EXEC (@restoredb) --to view the location of the restore script make this a comment first
--then if ready to execute run EXEC (@restoredb)
print @restoredb
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
DELETE FROM migration
FETCH NEXT FROM migrate INTO @migrate,@migrate2
END
CLOSE migrate
DEALLOCATE migrate
--@@@@@@@@@@@@@@@@@@@
DROP TABLE migration
DROP TABLE cmdshell
Change the path where your ackup resides and path where you want to save your mdf and ldf. In ('.BAK',fentry)-56) change the number where it will satisfy the name of your backup file.
"-=Still Learning=-"
Lester Policarpio
-
@Lester - how does this script solve the problem?
@Jon - are there any additonal error messages in the SQL Server Error log? Windows Application Event Log?
--------------------
Colt 45 - the original point and click interface
-
Jon, Also, what version and SP level is the server you're restoring on. Are the backup files from a database on this server, or a different server. If they're from a different server, what version and SP level is that server.
--------------------
Colt 45 - the original point and click interface -
Phil, I am running SP4, and the backup is on the same server. I actually got it to work. I switched some of the files around, then rebuilt the log file, and I had success. However, when I did a CHECKDB, I got this message several times:
database ID <id> is marked allocated in the GAM, but no SGAM or IAM has allocated it.
When I go into the Properties section of the database, the Data Files tab only shows the MDF file. None of the NDF's show up on the list, but the data is all there and can be accessed.
Thanks,
Jon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply