Getting SQL scripts from the wizards in the SQL Server Management Studio

Ok, this is not exactly AX but it IS in the category of related to. The thing is that you sometimes needs to take a backup of a database or other stuff on the SQL server and sometimes you need to either do this repeatedly every once in a while, have somebody else trigger it or simply document what you did.

Microsoft SQL Server Management Studio offers a lot of nice UI to handle processes like backup, restore and index manipulation. And it is so much easier to do it here than trying to write it in a query window. But it can be a pain remembering – I mean documenting – every option set and redo these settings every time. So this often forgotten feature in most of these UI elements in the Management Studio could come in handy.

After doing all the setup of e.g. the backup process you can use the Script button in the upper left-ish corner:

The script button

We are now offered 4 different ways of getting our script including short cuts for us to remember. The top three gives us the script in respectively a query window, a file or in the clipboard:

Script to query

Now you can combine multiple scripts into one if you would like to for example backup one database and restore it over another by

  1. Create the script for the backup in a query window
  2. Create the script for the restore in the clipboard and paste it in query window with the backup
  3. Do whatever you need to do with the full script to backup/restore

The 4th possibility is to get the script to be a step in a new job if you need to repeat the script on for example a daily basis. Select the Script Action to Job:

Script to job

From here you just need to set up the schedule and you are ready to go.

The script is added to Step 1 so you can manipulate it or just see what is being executed.

The script is added to step 1

Remember that firing scripts directly on the SQL-server or through other clients can be extremely powerful and dangerous and can have fatal effects on the server and databases. So whatever you do … be carefull out there …

Advertisements