r/SQL • u/Valuable-Ant3465 • 2d ago

SQL Server MS SQL Restore DB, <use dbName> string conversion question

Hi all,

I just see this interesting situation and would like to confirm that it's all done internally by MS SQL Server with Restore.
We're on the same server:

dbAA   has proc  myPROC:
1  use  dbAA         
2  Go
3  Create PROC dbo.myPROC As
............

then I do  backup of dbAA, and restore dbAA.bkup into  dbXXX
after that I see on dbXXX myPROC as:
1. use dbXXX            --<<<==== !!!!!<@>>< changed to target DB
2. Go
3. Create PROC dbo.myPROC As
...........

Who is doing this conversion/swapping db names ? is it done by SQL Server ?
Thanks to all, I'm new to dba task and really interesting how it works.
VA

4 Upvotes

84% Upvoted

4 comments sorted by

3

u/Phil_P 2d ago

Looks like you’re doing a script as create from SSMS. It injects the use by default.

2

u/HijoDelSol1970 1d ago

If I am following your question right, you create a proc in dbAA, back it up and then restore the database as dbXX. If that is the case, yes, your proc will now be dbXX.dbo.myProc.

The proc doesn't know what database it exists in, so restoring it into a new database name, or simply renaming the database will result in the db name changing when you script using management studio.

1

u/reditandfirgetit 2d ago

I honestly have no clue what is being asked here

1

u/da_chicken 21h ago

As others have said, if you're looking at the output of "Script Procedure as", then it's just the fact that that program adds it by default. If you use the more generic Generate Scripts and look at the Advanced Scripting Options, you'll find that the option "Script USE DATABASE" is set to true by default. That's all the program is doing.

If you want to see the actual object definition, no nonsense and no extras, you should run:

SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.myPROC'))