SQL Server: Scripting to backup and restore at one go

Take All Backups to a single location

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

 

SET @path = '\\servername\SQLBackups\foldername\' 

 

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','ReportServer','ReportServerTempDB','anyOtherDB') 

 

OPEN db_cursor  

FETCH NEXT FROM db_cursor INTO @name  

 

WHILE @@FETCH_STATUS = 0  

BEGIN  

       --SET @fileName = @path + @name + '_' + @fileDate + '.BAK' 

       SET @fileName = @path + @name +'.BAK' 

       Print 'BACKUP DATABASE ' + @name + ' TO DISK = ''' + @fileName + ''''

         Print 'Started: ' + CONVERT(VARCHAR(20),current_timestamp)

         BACKUP DATABASE @name TO DISK = @fileName

         Print 'Finished: ' + CONVERT(VARCHAR(20),current_timestamp)

       FETCH NEXT FROM db_cursor INTO @name  

END  

 

CLOSE db_cursor  

DEALLOCATE db_cursor

 

Create Blank databases before restore in different data and log file location

DECLARE @name VARCHAR(50) -- database name 

DECLARE @data_file_path VARCHAR(256) -- path for data files 

DECLARE @log_file_path VARCHAR(256) -- path for log files 

DECLARE @data_file_name VARCHAR(256) -- filename for mdf 

DECLARE @log_file_name VARCHAR(256) -- filename for ldf 

DECLARE @fileDate VARCHAR(20) -- used for file name

 

SET @data_file_path = 'E:\MSSQL10_50.MSSQLSERVER\MSSQL\Data\'

SET @log_file_path = 'F:\MSSQL10_50.MSSQLSERVER\MSSQL\Logs\'

 

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','ReportServer','ReportServerTempDB','db_not_need') 

 

OPEN db_cursor  

FETCH NEXT FROM db_cursor INTO @name  

 

WHILE @@FETCH_STATUS = 0  

BEGIN  

       --SET @fileName = @path + @name + '_' + @fileDate + '.BAK' 

       SET @data_file_name = @data_file_path + @name +'.mdf' 

         SET @log_file_name = @log_file_path + @name +'_log.ldf' 

       Print 'USE master;'

         Print 'GO'

         Print 'CREATE DATABASE ' + @name + ' ON '

         Print '( NAME = ' + @name + '_dat' + ', FILENAME=''' + @data_file_name +''')' 

         Print 'LOG ON '       
         Print '( NAME = ' + @name + '_log' + ', FILENAME=''' + @log_file_name +''');' 

       Print 'GO'

       Print '-----'

         --Print 'BACKUP DATABASE ' + @name + ' TO DISK = ''' + @fileName + ''''

         --Print 'Started: ' + CONVERT(VARCHAR(20),current_timestamp)

         --BACKUP DATABASE @name TO DISK = @fileName

         --Print 'Finished: ' + CONVERT(VARCHAR(20),current_timestamp)

       FETCH NEXT FROM db_cursor INTO @name  

END  

 

CLOSE db_cursor  

DEALLOCATE db_cursor 

 

 

Restore all databases  from that particular dump source

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

 

SET @path = '\\servername\SQLBackups\foldername\' 

 

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','Northwind','ReportServer','ReportServerTempDB') 

 

 

OPEN db_cursor  

FETCH NEXT FROM db_cursor INTO @name  

WHILE @@FETCH_STATUS = 0  

BEGIN  

      --SET @fileName = @path + @name + '_' + @fileDate + '.BAK' 

       SET @fileName = @path + @name +'.BAK' 

       Print 'RESTORE DATABASE ' + @name + ' FROM DISK = ''' + @fileName + ''' NORECOVERY'

       FETCH NEXT FROM db_cursor INTO @name  

END  

 

CLOSE db_cursor  

DEALLOCATE db_cursor

Take the DBs in restricted mode and change collation (reportServer dbs should be ommited)

DECLARE @name VARCHAR(50) -- database name 

DECLARE @fileDate VARCHAR(20) -- used for file name

 

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

 

DECLARE db_cursor CURSOR FOR 

SELECT name

FROM master.dbo.sysdatabases

WHERE name IN (SELECT name

FROM master.sys.databases sd

WHERE name not like 'ReportServer%'

and sd.collation_name <> CONVERT(varchar(50),SERVERPROPERTY('collation'))) 

 

OPEN db_cursor  

FETCH NEXT FROM db_cursor INTO @name  

 

WHILE @@FETCH_STATUS = 0  

BEGIN  

       Print 'ALTER DATABASE ' + @name + ' SET RESTRICTED_USER'

       FETCH NEXT FROM db_cursor INTO @name  

END  

CLOSE db_cursor

 

Print ''

 

OPEN db_cursor  

FETCH NEXT FROM db_cursor INTO @name  

WHILE @@FETCH_STATUS = 0  

BEGIN   

       Print '-- Priviously ' + @name + ' is in Collation ' + CONVERT(varchar(50),DATABASEPROPERTYEX(@name, 'Collation'))

       Print 'ALTER DATABASE ' + @name + ' ' + CONVERT(varchar(50),SERVERPROPERTY('collation'))

       FETCH NEXT FROM db_cursor INTO @name  

END     

CLOSE db_cursor

DEALLOCATE db_cursor

Source:
http://www.mssqltips.com/sqlservertip/1070/simple-script-to-backup-all-sql-server-databases/