Different companies have various requirements and processes for database or database server decommissioning. In this tip we have provided steps that will help you decommission a database and make sure you have a good back-out plan (for example, in case there is a need to bring the database back online). We have also provided scripts that will help you identify dependencies in case there is a need to migrate the database instead of decommissioning.
You may already have a process documented, but this list will help you review most of the dependencies and make sure you are prepared for either a migration or decommissioning.
One of the most common DBAs tasks is databases backup and restore. We do this to test the backups, to refresh test databases, to migrate the databases etc. For some of the tasks we have scripts, for others we need to create scripts on the fly.
Here I would like to share with you a simple script that you will need to run in SQL Server Management Studio’s (SSMS) SQLCMD mode. This script will allow you to create the backup, copy the backup file and restore it on the different server – all from a single SSMS window.
Start new query in SSMS and under “Query” menu select “SQLCMD Mode”:
Run this script (replace host name and SQL Server Instance name for the source and destination variables):
Note, that I commented out the “MOVE” option. Remember to add it to the script if your destination database files’ locations are different from the source.