1

I have some large DB that I need to restore to another db from time to time, this process take some time to conclude. The annoying thing I found out in last version of SSMS is that I cannot script any sp of the current db while I do this restore.

In my case I have the current db named FleetXS that is online and is in use and I do the restore of backup of this db with the date of 2 days ago in other db named Fleetxs_Restore. For all the time of the restore it's not possible to script any sp of db FleetXS with the weird error

Cannot open database "FLEETXS_restore" requested by the login. The login failed. Login failed for user 'anna'. (Microsoft SQL Server, Error: 4060)

login failed

I have no need to open the database FLEETXS_restore that even is not actual to date, I operate within my current database FleetXS that is online, why on the earth my login "requested db FLEETXS_restore"? Is there any workaround or should I wait for hours when the restore finishes to be able to get the text of sp of another database?

sp scripting

Microsoft SQL Server 2019 (RTM-CU10) (KB5001090) - 15.0.4123.1 (X64) Mar 22 2021 18:10:24 Copyright (C) 2019 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Datacenter 10.0 (Build 14393: ) (Hypervisor)

  • SQL Server Management Studio 15.0.18390.0
  • SQL Server Management Objects (SMO) 16.100.46521.71
  • Microsoft Analysis Services Client Tools 15.0.19714.0
  • Microsoft Data Access Components(MDAC) 10.0.19041.1
  • Microsoft MSXML 3.0 6.0
  • Microsoft .NET Framework 4.0.30319.42000
  • Operating System 10.0.19043

I do not use SSMS to do the restore, I use my own script. There are two backups to restore, full + diff. I do full with norecovery and diff with recovery.

5
  • Seems like a bad design in SSMS. How about do the scripting from Powershell, using SMO, instead? I have done that for a case I had, picked up some PS/SMO code "from internet". :-) Nov 5, 2021 at 11:01
  • Please add your restore script to the post.
    – J.D.
    Nov 5, 2021 at 12:35
  • @J.D. Sincerely I don't see any relation between my restore script and SSMS behaviour, it seems that SSMS just make a loop and try co connect to all databases in order to produce a script of just 1 object of 1 current database. I already answer your question about RECOVERY and NORECOVERY options that, in any way, cannot induce SSMS to go to other databases
    – sepupic
    Nov 5, 2021 at 14:07
  • SSMS is just a client application that executes T-SQL (mostly) just the same as if you executed the code yourself. So I think it's a low likelihood (though not impossible) that SSMS is your problem. Having your restore script would be useful because sometimes restoring to a new database still affects the previous database that the backup came from, depending on the restore options used. One example is the "Leave source database in the restoring state" option, which is different from the "NORECOVERY" option you mentioned, so you haven't answered my question. I'm no expert on database...
    – J.D.
    Nov 5, 2021 at 18:48
  • ...restores, but given what I know and mentioned about the source database potentially being affected during a restore, and the fact we have nothing else to go off of, your best bet for help here would be to update your question with your restore script.
    – J.D.
    Nov 5, 2021 at 18:49

1 Answer 1

0

Maybe you can use sp_helptext stored procedure.

Microsoft documentation

1
  • This works well (it is not sp_help_text but sp_helptext) but it works as any other code that is not generated by SSMS. As I said, my current db is online and completely operational so any code that reguards only this db works. The question is what code does SSMS do to make a script of one object and why it goes to other databases and how to make it avoid going to other databases. I personally can use this sp but we have many other colleagues that get used to use GUI...
    – sepupic
    Nov 5, 2021 at 14:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.