Corruption when restoring/renaming databases

Question

Post reply

Corruption when restoring/renaming databases

spencepa

Ten Centuries

Points: 1042
More actions
February 15, 2017 at 9:15 am

#335295

Hi i am trying to move databases to new SSD drives but some have dozens of logical file and are up to 2tb. I have a script i use to restore a DB so have modified to include name, physical name from sys.master_files plus various variables to restore it to a new location. EG
RESTORE DATABASE [object_new] FROM DISK = '\\share1\SQLBackups\FS1STGPRD01\object\FS1STGPRD01_object_FULL_20170212_223444.bak' WITH MOVE 'object' TO 'H:\SQLDB\object\object.mdf', MOVE 'object_log' TO 'H:\SQLDB\object\object_log.LDF', NORECOVERY
The last steps are take diff backup, restore this to the "new" db. Then set old db single user, rename dbs, set old db to offline, run dbcc checkdb on new db. All good - i end up with new db "object" on SSD and old db "object_old(offline)" However, I went into one of the prod db's i have done and ran a select for sanity (see image):
SELECT TOP 1000 * FROM [object].[dbo].[event_history]
Database 'object_old' cannot be opened as it is offline
There seems to be some kind of pointer/corruption/bug when i select data from the restored/renamed db - its trying to access the old, renamed, offline copy!
If i run select under 'use master' its a problem
If i run select "use object" it runs ok
all the DB's logical files are correct (on new H drive)
Ive run dbcc checkdb and no errors returned
Ive dropped the DB, run migration process again same issue! I cant replicate it in the old live db with correct name. Im stumped and concerend it might be a problem for 2 other DB's ive done(i cant replicate it luckily).... Or it might be a problem with the DB being called "object"!! :crazy: (this is a sql 2008 ent cluster)
February 15, 2017 at 8:34 pm

This was removed by the editor as SPAM
February 20, 2017 at 11:12 pm

This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 1

Interesting.

What results do you get when running

select name, database_id from sys.databases
and when focused on 'object'
select db_name(), db_id()

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

Are there any synonyms in the database?

SELECT name, base_object_name FROM sys.synonyms

John

spencepa Ten Centuries Points: 1042 More actions · Answer 3

thanks for replys:

in master:
name database_id
object 32
Object_old 5

in object:
object 32

Nothing in sys.synonyms...

spencepa Ten Centuries Points: 1042 More actions · Answer 4

yes i used a script to take them off and online

I did some testing and restored to a 2014 server but couldnt replicate. Interestingly it caused the available db list to crash and show "no databases available" inc all the sys dbs!!! I cant replicatet that either and i dont have another 2104 server handy

spencepa Ten Centuries Points: 1042 More actions · Answer 5

JasonClark - Monday, February 20, 2017 11:12 PM
If you don't have any other backup you may try to recover your data using the .mdf and .ndf file.

Im trying to migrate a live working db called "object" via backup/restore to a new SSD drive using "with move". IMO Its the fact the db is called "object" that is the issue. I wanted to know if anyone else has seen similar as i cant find anything googling

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

Have you tried renaming the old database before you do the restore? Then you don't need to rename the new database, and if this problem is being caused by the rename, this might resolve it.

John