Taking backup of all user database

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Taking backup of all user database

Author

Topic

SadafKhan85
Starting Member

8 Posts

Posted - 2013-07-03 : 07:46:41

In order to take automated backup of all user databases below is the query.
This query will eliminate use of manual backups for user databases, in order to fully automate this just create a SQL Agent job and write this query in the job and forget about taking any manual DB backups.

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 = 'C:\DB_BKPUP\'

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')

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-07-29 : 04:29:27

Here is alternate way http://beyondrelational.com/modules/2/blogs/70/posts/10895/backup-all-databases.aspx

Madhivanan

Failing to plan is Planning to fail

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2013-07-29 : 06:24:48

Thanks

mohammad.javeed.ahmed@gmail.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources