Backup All User Databases
-
Comments posted to this topic are about the item Backup All User Databases
Greg Roberts
-
Thanks for this - just a note: why are we backing up only user databases? System databases are essential for the server recovery and must be backed up daily as well.
-
You are correct, system databases are every bit as important. But in my environment they do not require as aggressive a backup schedule. So i basically run 2 versions. ! for User databases and a second for System database.
If you feel very adventurous you could create a table that stores the databases on your system and add meta data to determine when and what type of backup to run. But then the table has to be maintained. So we turn the wheel and start over again.
Please feel free to use this as a jumping off point for your own backup strategies.
Greg Roberts
-
Thanks for the script.
-
Find the attached backup script will help to take all user Database backup and specified DB backup.
You can modify the script based on your requirement.
-
Find attached backup script will help to take the backup of multiple databases, specific databases , all user databases.
We can update the parameters based on our requirement.
****************************
DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileName1 VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
-- specify database backup directory
SET @path = '\\TestServer\FULL\' -- Backup location
-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR READ_ONLY FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name IN ('DB1','DB2') -- these databases
--WHERE name NOT IN ('master','model','msdb','tempdb') -- Excluded these databases
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_CO_' + @fileDate + '.BAK' + ' with compression,Copy_only,stats=10,buffercount=400'
BACKUP DATABASE @name TO DISK = @fileName
-- print (@filename)
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
**************************************
DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileName1 VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
DECLARE @Servername VARCHAR(50)='ServerName' -- Server Name
-- specify database backup directory
SET @path = '\\TestServer\FULL\' -- Backup location
-- specify filename format
--SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
SELECT @fileDate =convert(char(8),getdate(),112)+ltrim(replace(convert(char(5),getdate(),108),':',''))
DECLARE db_cursor CURSOR READ_ONLY FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name IN ('DB1','DB2') -- these databases
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @Servername+ '_' + @name + '_CO_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName with compression,Copy_only,stats=10,buffercount=900
-- print (@filename)
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply