Quick Backup & Restore with ‘sp_MSForEachDB’
Another undocumented SP in Microsoft SQL Server is the ‘sp_MSForEachDB‘ procedure. This procedure enumerates every database on a Microsoft SQL Server Instance and let you perform different actions on every database. This can be quite useful in several situations. For example the following two scripts are
- creating database backups of all databases on a SQL Server Instance
- creating a restore script for those databases
To use those scripts you may want to modify the paths (‘c:\#Backup’, ‘C:\MS SQL Data\MSSQL.1\MSSQL\Data\?.mdf’,….) to match your environment:
Backup
EXEC sp_MSForEachDB 'IF DB_NAME((SELECT dbid FROM sysdatabases WHERE [Name]= ''?'')) NOT IN (''tempdb'',''master'',''model'',''msdb'') BACKUP DATABASE [?] TO DISK = N''C:\#BACKUP\?.bak'' WITH NOFORMAT, NOINIT, NAME = N''?-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD' |
Restore
EXEC sp_MSForEachDB 'IF DB_NAME((SELECT dbid FROM sysdatabases WHERE [Name]= ''?'')) NOT IN (''tempdb'',''master'',''model'',''msdb'') PRINT "RESTORE DATABASE [?] FROM DISK =N''C:\#BACKUP\?.bak'' WITH FILE =1, MOVE N''?'' TO N''C:\MS SQL Data\MSSQL.1\MSSQL\Data\?.mdf'', MOVE N''?_log'' TO N''C:\MS SQL Data\MSSQL.1\MSSQL\Data\?.ldf'', NOUNLOAD, REPLACE, STATS = 10 GO" ' |