Backup all databases in Microsoft SQL Server


Find here, a cool SQL script to backup all SQL Server databases. Of course, you can put it in a Scheduled Job to execute each night.


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

--specific location
SET @path = 'E:\Backup\Database\'

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

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

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
 --SET @fileName = @path + @fileDate + '-' + @name + '.bak'
 SET @fileName = @path + @name + '.bak'
 BACKUP DATABASE @name TO DISK = @fileName WITH INIT, COMPRESSION

FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor

Advertisements
Tagged with: ,
Posted in SQL Server

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow me
   RSS Feed    Twitter    Linked In

Write you email address to subscribe at this blog and to receive new posts by mail.

Join 611 other followers

Who I am?






%d bloggers like this: