• Got a Tipila, google, windows live or facebook account ?

Here's how to backup all the db s on the server to a single .bak file using tsql in sql server.

DECLARE @name VARCHAR(50) 
DECLARE @path VARCHAR(256)
DECLARE @fileName VARCHAR(256) 
DECLARE @fileDate VARCHAR(20)

SET @path = 'h:\Backup\'  

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')  
SET @fileName = @path + 'backup' + '_' + @fileDate + '.BAK'  
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   

WHILE @@FETCH_STATUS = 0   
BEGIN   

       BACKUP DATABASE @name TO DISK = @fileName  
       FETCH NEXT FROM db_cursor INTO @name   
END   

CLOSE db_cursor   
DEALLOCATE db_cursor

This is taken from here

1
votes
Login to Tipila