Backup / Restore all USERS databases

backup.sql

DECLARE @name VARCHAR(50) -- database name  
DECLARE @path VARCHAR(256) -- path for backup files  
DECLARE @fileName VARCHAR(256) -- filename for backup  
DECLARE @fileDate VARCHAR(20) -- used for file name

 
-- specify database backup directory
SET @path = 'C:\Backups\'  

 
-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 

 
DECLARE db_cursor CURSOR FOR  
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases

 
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   

 
WHILE @@FETCH_STATUS = 0   
BEGIN   
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
       BACKUP DATABASE @name TO DISK = @fileName  

 
       FETCH NEXT FROM db_cursor INTO @name   
END   

 
CLOSE db_cursor   
DEALLOCATE db_cursor

restore.sql

USE Master; 
GO  
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO
SET NOCOUNT ON 

-- 1 - Variable declaration 
DECLARE @dbName sysname 
DECLARE @backupPath NVARCHAR(500) 
DECLARE @cmd NVARCHAR(500) 
DECLARE @fileList TABLE (backupFile NVARCHAR(255)) 
DECLARE @lastFullBackup NVARCHAR(500) 
DECLARE @lastDiffBackup NVARCHAR(500) 
DECLARE @backupFile NVARCHAR(500) 

-- 2 - Initialize variables 
SET @backupPath = 'C:\Backups\' 

-- 3 - get list of files 
SET @cmd = 'DIR /b ' + @backupPath 

INSERT INTO @fileList(backupFile) 
EXEC master.sys.xp_cmdshell @cmd 


DECLARE db_cursor CURSOR FOR  
SELECT backupFile FROM @fileList  WHERE backupFile LIKE '%.BAK'  

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @backupFile
 
WHILE @@FETCH_STATUS = 0   
BEGIN   

SET @dbName = SUBSTRING( @backupFile, 0,  CHARINDEX('_', @backupFile))


SET @cmd = 'RESTORE DATABASE ' + @dbName + ' FROM DISK = '''  
  	+ @backupPath + @backupFile + ''' WITH NORECOVERY, REPLACE' 
PRINT @cmd 

SET @cmd = 'RESTORE DATABASE ' + @dbName + ' WITH RECOVERY' 
PRINT @cmd

       FETCH NEXT FROM db_cursor INTO @backupFile
END   
 
CLOSE db_cursor   
DEALLOCATE db_cursor