Copy tables from one database to another in SQL Server

SELECT * 
INTO DestinationDB..MyDestinationTable 
FROM SourceDB..MySourceTable 

How to Backup All Databases in SQL Server

Below  is a SQL script to get all Databases Backup in specified folder.
Backup file name will be like "DataBaseName_{Date}-{Month}-{Year}.bak"

DECLARE @DBName VARCHAR(MAX) 

DECLARE @DBBackupFileName VARCHAR(MAX) 

DECLARE @FileDate VARCHAR(20) 

DECLARE @Path VARCHAR(MAX) 

SET @Path='D:\DBBackup\' ---Check with this path

SET @FileDate=CONVERT(VARCHAR(20),GETDATE(),105) 

DECLARE dbCursor CURSOR FOR 

SELECT name 

FROM master.dbo.sysdatabases 

WHERE name NOT IN ('master','tempdb','model','msdb') 

OPEN dbCursor 

FETCH NEXT FROM dbCursor into @DBName 

WHILE @@FETCH_STATUS = 0 

BEGIN 

SET @DBBackupFileName= @Path + @DBName + '_' + @FileDate + '.bak' 

BACKUP DATABASE @DBName TO DISK =@DBBackupFileName 

FETCH NEXT FROM dbCursor into @DBName 

END 

CLOSE dbCursor 

DEALLOCATE dbCursor