MSSQL Backup all Non System Databases Scripted

Microsoft SQL Server backups, MSSQL Backup, process

  • Note: You may need to go to Tools > Options > Query Results > SQL Server > Results to Text in the Studio Manager and change the “Maximum number of characters displayed in each column to 1024” (default is 256). Also make sure you are outputting the results to text. If you do not do this prior, you run into the chance of either set of commands not outputting the full commands needed for the next step.
  • Also, you may need to open a New Query as the change ‘seems’ to only affect newly opened queries. The second pertinent setting, Open a new query window and ensure it has the focus, then go to Query > 249521. Here you can see the setting is located in 249521 > 249521. Change the Maximum number of characters displayed in each column the fields maximum of 8192.

The following command will generate a SQL script to back up all non system SQL databases. Be sure to replace the restore path. Make sure you set the results to text output.

use master
select '
BACKUP DATABASE [' + [name] + ']
TO  DISK = N''C:\RESTORE\PATH\' + [name] + '.bak'' 
WITH NOFORMAT, NOINIT,  NAME = N''' + [name] + '-Full Database Backup''
GO' from master..sysdatabases where dbid > 4

Once the above query has been run, copy the output and run it as a new query on the source server (I migrate a lot, hence the terminology used).