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.
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 nameSET @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 @nameWHILE @@FETCH_STATUS = 0 BEGIN SET @fileName = @path + @name + '_' + @fileDate + '.BAK' BACKUP DATABASE @name TO DISK = @fileNameFETCH NEXT FROM db_cursor INTO @name ENDCLOSE db_cursor DEALLOCATE db_cursor |
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
ahmeds08
Aged Yak Warrior
737 Posts |
Posted - 2013-07-29 : 06:24:48
|
Thanksmohammad.javeed.ahmed@gmail.com |
|
|
|
|
|