In this article, we will discuss how to deal with a situation wherein you face an issue with a mandatory stored procedure in your critical database.
Suppose you have a massive database — more than 1TB in size — and you have an important stored procedure running to transact data with your database. You encountered a problem and now have come up with a conclusion to work on this stored procedure.
By default, as a DBA, you will opt for restoring full backup of the database. But, this will take considerable time as you have a big database running and some part of data transaction is totally dependent upon the stored procedure that you need work on.
What you will do to overcome this situation?
These are the three most-practiced solutions to deal with such stored procedure issues in SQL Server.
Taking backups from the database as structure only
To be on the safe side, you can take structure only scripts from your databases, periodically as per the following steps:
- Right-click the database then tasks and generate scripts:
- After skipping the introduction page you will be directed to the below page:
Here, if you want to script the database creation and all objects in the database, you can select the first option: (Script entire database and all database objects)
Or, you can select specific database objects to script them, when you choose the second option (select specific database objects) and specify the object name you need on the below lists:
- You can select to save it to a specific location with below options:
- Save to file: it will be saved as SQL file:
- Single file: all objects you have selected will be combined in the same file.
- Single file per object: each object you selected in the database will be generated in a separate file.
- Overwrite existing file: if the name you specified for the file exists in the same location, it will be overwritten.
- Save as Unicode or ANSI code text: it is the formatting you need to save the file as (Unicode is helpful in languages other than English like Arabic.)
- Save to clipboard: if you want to paste the result to a specific file.
- Save to new query window: once finished, a new query window will be opened with your script.
- Click on the advanced button:
You need to be sure that everything related to your specified objects will be scripted using the above options.
The most important option is (types of data to script) you need to choose schema only to script only the schema of the objects without any data. Then click ‘OK' and Next, 'Finish'.
By that, you will have the script of your objects saved. So, if you faced any issue with that object or stored procedure, you can get back to its previous script and check the differences to implement them.
But, this method lacks the accurate times, if you need to return back the stored procedure to a specific time.
Restoring the database on other instance
We can simply restore the available backup to another SQL Server instance, and then script the stored procedure we need and implement it on our database.
- To restore the database from a .bak file, please check this article.
- To generate a script of the needed stored procedure, please refer to the first part of this article above.
By that, you can get the script of the stored procedure you need in the desired time which will be the time of the backup.
But this also will take time if the backup is huge.
The third and most optimal option:
I will mention here a very good and attractive option supported by a trusted SQL Recovery Software. The software can recover not only a specific stored procedure but also tables, triggers, indexes, keys, and rules.
It can recover your work from any one of the below issues:
- The file *.mdf is missing and needs to be restored
- Possible index corruption detected. Run DBCC CHECKDB
- Database 'dbname' cannot be opened due to inaccessible files or insufficient memory or disk space
- The process could not execute 'sp_replcmds' on server
- Server cannot find the requested database table
- Internal error. Buffer provided to read column value is too small. Run DBCC CHECKDB to check for any corruption
- Table Corrupt: Object ID 0, index ID 0, page ID (1:623)
Every experienced DBA has faced stored procedure related issues due to SQL database corruptions, which may need some help from third-party tools. What I mentioned here about restoring a specific stored procedure is a very hard manual option and might not always be the right choice to get the desired result.
Using a third-party SQL database repair tool will be more convenient and effective for restoring not just the stored procedures but the several individual elements of a SQL database.
I hope this article has been informative for you.