Before deployment, the best practice is to back up the database before any updates are made. This way, the script can be tested properly by rolling back the old database and rerun the script after fixes have been made.
Right click on the database you need to backup > Tasks > Back Up
Under Destination, choose to back up to “Disk”
Press the “Add” button
Press the “. . .” button
Choose where to save the backup file. Make sure to include “.BAK” in the file name b/c it won’t assign that file type automatically.
Press “OK”
Press “OK”
Press “OK.” The backup will execute for a while…
Finally, you should see a “Success” popup
ROLL BACK/RESTORE DATABASE
When you run a script and it returns errors, you need to rollback the database to properly test a script.
Right click on the database you need to restore > Tasks > Restore > Database
Select “Device” and press the “. . .” button
Press the Add button
Find the .BAK file that you need to restore.
Press OK
Press OK
The backup file should appear in the “Backup sets to restore” grid. Make sure “Restore” is checked, though it should be checked automatically.
Select the “Options” tab in the left panel.
Check “Overwrite the existing database”
Press OK.
If the restore fails, open a new SQL query window pointed to “master” and execute the following:
sp_who2
A list of processes that are referencing/using the databases will load. Find any records with the name of the database you are trying to restore in the DBName column. You will need to kill those processes. Enter kill statements like this with the IDs of the processes you need to kill:
kill 60
kill 63
After killing the processes, execute sp_who2 again and you should no longer see any processes referencing your database. Try running the restore again now.
