Odd! Objects Shown Up in Master after DB Restoration

Question

Odd! Objects Shown Up in Master after DB Restoration

Michelle-138172

Hall of Fame

Points: 3234
More actions
July 18, 2016 at 2:52 pm

#315248

I created a script to restore multiple databases in one shot. What's weird is, I often see objects from the dbs being restored show up in Master. They can be tables, procs, etc. The DBs themselves seem to be properly restored somehow.
It's very annoying to have to clean up the master afterwards. What is happening here?

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Michelle-138172 (7/18/2016)
I created a script to restore multiple databases in one shot. What's weird is, I often see objects from the dbs being restored show up in Master. They can be tables, procs, etc. The DBs themselves seem to be properly restored somehow.
It's very annoying to have to clean up the master afterwards. What is happening here?

I'd check your script. Just restoring databases should be creating objects in the master database.

Jeff Moden SSC Guru Points: 1003925 More actions · Answer 2

Lynn Pettis (7/18/2016)
Michelle-138172 (7/18/2016)
I created a script to restore multiple databases in one shot. What's weird is, I often see objects from the dbs being restored show up in Master. They can be tables, procs, etc. The DBs themselves seem to be properly restored somehow.
It's very annoying to have to clean up the master afterwards. What is happening here?
I'd check your script. Just restoring databases should be creating objects in the master database.

Should not be?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Michelle-138172 Hall of Fame Points: 3234 More actions · Answer 3

Those were auto generated scripts that I ran repeatedly over time. Sometimes I would have trouble, sometimes no. It really doesn't make any sense to me. I only noticed the problem in master by accident.

Here's part of the script for 3 of the DBs - this time some objects from DB2 got into Master:

USE [master]

RESTORE DATABASE [DB1] FROM DISK = N'G:\Backups\OneTimeBackup\Prod1\User\DB1_backup.bak'

WITH FILE = 1, MOVE N'DB1_Data' TO N'E:\Data\DB1_Data.mdf', MOVE N'DB1_Data2' TO N'E:\Data\DB1_Data2.mdf', MOVE N'DB1_Log' TO N'F:\Log\DB1_Log.ldf', NOUNLOAD, REPLACE, STATS = 5

GO

USE [master]

RESTORE DATABASE [DB2] FROM DISK = N'G:\Backups\OneTimeBackup\Prod1\User\DB2_backup.bak' WITH FILE = 1, MOVE N'DB2' TO N'D:\Data\User\DB2.mdf', MOVE N'DB2_index' TO N'D:\Data\User\DB2_1.ndf', MOVE N'DB2_log' TO N'F:\Log\DB2_2.LDF', NOUNLOAD, REPLACE, STATS = 5

GO

USE [master]

RESTORE DATABASE [DB3] FROM DISK = N'G:\Backups\OneTimeBackup\Prod1\User\DB3_backup.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 5

GO

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Jeff Moden (7/18/2016)
Lynn Pettis (7/18/2016)
Michelle-138172 (7/18/2016)
I created a script to restore multiple databases in one shot. What's weird is, I often see objects from the dbs being restored show up in Master. They can be tables, procs, etc. The DBs themselves seem to be properly restored somehow.
It's very annoying to have to clean up the master afterwards. What is happening here?
I'd check your script. Just restoring databases should be creating objects in the master database.
Should not be?

Really need to read my posts backwards before hitting Post.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Michelle-138172 (7/19/2016)
Those were auto generated scripts that I ran repeatedly over time. Sometimes I would have trouble, sometimes no. It really doesn't make any sense to me. I only noticed the problem in master by accident.
Here's part of the script for 3 of the DBs - this time some objects from DB2 got into Master:
USE [master]
RESTORE DATABASE [DB1] FROM DISK = N'G:\Backups\OneTimeBackup\Prod1\User\DB1_backup.bak'
WITH FILE = 1, MOVE N'DB1_Data' TO N'E:\Data\DB1_Data.mdf', MOVE N'DB1_Data2' TO N'E:\Data\DB1_Data2.mdf', MOVE N'DB1_Log' TO N'F:\Log\DB1_Log.ldf', NOUNLOAD, REPLACE, STATS = 5
GO
GO
USE [master]
RESTORE DATABASE [DB2] FROM DISK = N'G:\Backups\OneTimeBackup\Prod1\User\DB2_backup.bak' WITH FILE = 1, MOVE N'DB2' TO N'D:\Data\User\DB2.mdf', MOVE N'DB2_index' TO N'D:\Data\User\DB2_1.ndf', MOVE N'DB2_log' TO N'F:\Log\DB2_2.LDF', NOUNLOAD, REPLACE, STATS = 5
GO
USE [master]
RESTORE DATABASE [DB3] FROM DISK = N'G:\Backups\OneTimeBackup\Prod1\User\DB3_backup.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 5
GO

Are you sure that this issue is a result of the restores? Does not make sense that a restore of a database will result in the creation of objects in master.

Michelle-138172 Hall of Fame Points: 3234 More actions · Answer 6

100% sure. Doesn't make any sense to me, either. That's why I'm very puzzled. Out of a dozen DBs restored this way, only one got into Master this time. Sometimes it can be fragments from several DBs. Totally bizarre!

Lynn Pettis (7/19/2016)
Are you sure that this issue is a result of the restores? Does not make sense that a restore of a database will result in the creation of objects in master.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Is there by any chance a DDL trigger on that instance?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Michelle-138172 Hall of Fame Points: 3234 More actions · Answer 8

No. If there was, it should happen consistently as well, but it didn't.

GilaMonster (7/19/2016)
Is there by any chance a DDL trigger on that instance?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

Michelle-138172 (7/19/2016)
Those were auto generated scripts that I ran repeatedly over time. Sometimes I would have trouble, sometimes no. It really doesn't make any sense to me. I only noticed the problem in master by accident.
Here's part of the script for 3 of the DBs - this time some objects from DB2 got into Master:
USE [master]
RESTORE DATABASE [DB1] FROM DISK = N'G:\Backups\OneTimeBackup\Prod1\User\DB1_backup.bak'
WITH FILE = 1, MOVE N'DB1_Data' TO N'E:\Data\DB1_Data.mdf', MOVE N'DB1_Data2' TO N'E:\Data\DB1_Data2.mdf', MOVE N'DB1_Log' TO N'F:\Log\DB1_Log.ldf', NOUNLOAD, REPLACE, STATS = 5
GO
GO
USE [master]
RESTORE DATABASE [DB2] FROM DISK = N'G:\Backups\OneTimeBackup\Prod1\User\DB2_backup.bak' WITH FILE = 1, MOVE N'DB2' TO N'D:\Data\User\DB2.mdf', MOVE N'DB2_index' TO N'D:\Data\User\DB2_1.ndf', MOVE N'DB2_log' TO N'F:\Log\DB2_2.LDF', NOUNLOAD, REPLACE, STATS = 5
GO
USE [master]
RESTORE DATABASE [DB3] FROM DISK = N'G:\Backups\OneTimeBackup\Prod1\User\DB3_backup.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 5
GO

Coming back to this, there is nothing there that would add objects to the master database.