Backup all databases
-
Hi, am using this script to backup all databases.
But now i want it to overwrite the previus databas and not incremental backup.
DECLARE @name VARCHAR(50)
DECLARE @path VARCHAR(256)
DECLARE @fileName VARCHAR(256)
DECLARE @fileDate VARCHAR(20)
SET @path = 'C:\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')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
Anyone got a tip howto?
/J
-
Hi,
You just need a WITH INIT on the end of your backup line.
Regards,
Ian.
-
Many thx,
It was an to easy task for you i see,
is there a simular script for sql 2000?
Because Sqlcmd doesnt work there.
/J
-
Hi,
Personally I use this, which is quite similar to yours, except I have this "strange" habit of calling my cursors gerbil
This does work on both 2000 & 2005 platforms.
declare @db sysname
declare gerbil cursor for select name from sysdatabases where name <> 'tempdb' order by name
open gerbil
fetch next from gerbil into @db
while (@@fetch_status = 0)
begin
exec ('backup database ['+@DB+ '] to disk = ''c:\sqlbackups\' + @DB + '.bak''' + ' with init')
fetch next from gerbil into @db
end
close gerbil
deallocate gerbil
Regards,
Ian.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply