September 25, 2013 at 2:20 am
Hello,
I am a bit befuddled by a problem I have encountered.
The scenario is,
On Server A, database is backed up (full backup) , then transaction log backups every 15 minutes.
The backup file is then copied to Server B and restored.
The result is the database is apparently successfully restored to Server B, yet when digging a little deeper, it is apparent that some of the indexes that exist in User Tables in the original database (On Server A) do not exist in the restored version of the database (On Server B)
If the same backup is restored to a new database on Server A, there iis no problem and all the indexes exist.
Both servers are Microsoft SQL Server Standard Edition (64-bit), version 10.0.5500.0
September 25, 2013 at 2:28 am
Is this repeatable, or did it just happen once? What statement(s) are you using to restore the database, and what statements (if any) do you run after it?
You appear to have lost interest half way through the sentence that starts "If the same backup...". Please will you complete it?
John
September 25, 2013 at 2:33 am
Fishbarnriots (9/25/2013)
The backup file is then copied to Server B and restored.
hopefully you have restored all the required files including log files 😉
Pramod
SQL Server DBA | MCSE SQL Server 2012/2014
in.linkedin.com/in/pramodsingla/
http://pramodsingla.wordpress.com/
September 25, 2013 at 3:39 am
Hello, thank you for the responses. I have edited the original posting as requested.
This process is contained in a stored procedure, it is used to copy selected production databases from the production server(s) to our test environment (takes place weekly).
the process uses the following steps (each is dependant upon the success of the preceding step)
Step 1
Identify the backup device for the database
Step 2
EXEC xp_cmdshell 'copy \\[Production Server]\G$\SQL_Backup\[Backup file] \\[Test Server]\g$\SQLBackups '
Step 3
declare
@res INT
insert into #restoreheaderonly
exec sp_executesql N' Restore headeronly from disk = ''\\[Test Server]\g$\SQLBackups\[Backup file]'' set @res=@@error',N'@res int out', @res OUT
Step 4
declare
@res INT
insert into #FileList
exec sp_executesql N' Restore filelistonly from disk = ''\\[Test Server]\g$\SQLBackups\[Backup file]'' set @res=@@error',N'@res int out', @res OUT
Step 5
declare
@res INT,
@err int
EXEC @err =sp_executesql N' Restore verifyonly from disk = ''\\[Test Server]\g$\SQLBackups\[Backup file]''
with file = 1
,Move ''[Data file]'' To ''E:\Data\[DB Name]_Data_1.mdf''
,Move ''[Log File]'' To ''F:\Log\[DB Name]_Log_2.ldf''
;set @res=@@error',N'@res int out', @res OUT
insert into #verify([id],[Process], [comment])
select 1, 'Restore verifyonly from file 1', case when @res = 0 then 'Pass' else 'Fail' end
Step 6
declare
@res INT,
@err int
EXEC @err =sp_executesql N'Restore Database mc from disk = ''\\[Test Server]\g$\SQLBackups\[Backup file]''
with file = 1
,Move ''[Data file]'' To ''E:\Data\[DB Name]_Data_1.mdf''
,Move ''[Log File]'' To ''F:\Log\[DB Name]_Log_2.ldf''
,Replace
, Stats
, Recovery
;set @res=@@error',N'@res int out', @res OUT
insert into #verify([id],[Process], [comment])
select 1,
'Restore database from file 1',
case when @res = 0 then 'Pass' else 'Fail' end
Step 7
EXEC xp_cmdshell 'DEL /Q "\\[Test Server]\g$\SQLBackups\[Backup file]"'
Step 8 -- Space is at a premium in the test environment
DBCC SHRINKDATABASE ([DB name], 1, truncateonly)
September 25, 2013 at 3:44 am
Frustratingly, having had this problem for the last few days, I have just run the process again and all the indexes exist.
September 25, 2013 at 4:45 am
The backup process is a page by page copy of the database. So the restore process is the same. For any object to simply be missing means that it was missing in the backup originally. There's nothing about the backup/restore process that would allow for some objects or some data being missed. You must have had a backup that didn't have the indexes and that was the one that was used in the restore. Something to look for would be backup sets instead of individual backup files (stacking the backups inside of a single named file without an INIT statement).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 25, 2013 at 5:07 am
Does this happen every time, or did it just occur once? Do you have any jobs or triggers on Server B that might remove the indexes?
John
September 25, 2013 at 5:08 am
Hello, I am jsut checking the backup process to ensure it is all working as expected.
September 25, 2013 at 9:18 am
Looks like it was an error in the stored proc that processed the restore.
Thanks for the help.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply