Microsoft Sql Server 2014 Backup All DataBases Except tempdb

I would like to backup all databases on our Sql Server 2014 except the tempdb using the SQL Agent. Would this script work okay? In addition, should I remove the (DATABASEPROPERTYEX(name, N'Status') IN ( N'EMERGENCY', N'ONLINE' ))? If you have a better script please post it.
Thanks
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 = 'D:\BackupLocation\'  --Case Sensitive

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 
   + '_' 
   + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')

DECLARE db_cursor CURSOR FOR  
SELECT name
FROM master.dbo.sysdatabases 
WHERE
    name NOT IN (N'tempdb') AND
    DATABASEPROPERTYEX(name, N'Status') IN ( N'EMERGENCY', N'ONLINE' )

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name 

WHILE @@FETCH_STATUS <> -1   
BEGIN   
       SET @fileName = @path + @name + '_Full_' + @fileDate + '.bak'  
       PRINT 'Backup Database command being issued on database "' + @name + '".' 
       BACKUP database @name TO DISK = @fileName WITH CONTINUE_AFTER_ERROR 
       --IF @@ERROR > 0
       --BEGIN 
           --add code here to record and/or notify yourself that an error occurred during BACKUP
       --END --IF
       FETCH NEXT FROM db_cursor INTO @name   
END  

CLOSE db_cursor   
DEALLOCATE db_cursor 

Open in new window

LVL 2
CityInfoSysAsked:
Who is Participating?
 
Mandeep SinghDatabase AdministratorCommented:
HI,

Please go through link, http://hightechnology.in/how-to-backup-all-database-in-sql-server-which-are-online/ it have script which backup all databases except system databases and which are online.
0
 
Scott PletcherSenior DBACommented:
Looks good as long as:
1) db name never exceeds 50 chars
2) db name does not contain special chars that would not be allowed in a windows file name.
0
 
PadawanDBAOperational DBACommented:
I'm all for fun scripts, but I am also a fan of not reinventing the wheel.  And since you asked for script suggestions, I can't help but recommend Ola Hallengren's stored procs (particularly the backup stored proc in your case).  Make sure you also get the CommandLog table creation script and there's a commandExecute sp as well (if you are downloading separately, otherwise the maintenance solution has all you need).  https://ola.hallengren.com/sql-server-backup.html.  Never leave home without them (he also has lovely store procs that handle automating index/stats maintenance and consistency checks)!
0
 
CityInfoSysAuthor Commented:
Now if the database recovery model is set to Simple or Full I would assume the script would still work? In addition, can i change the recovery model when the database is online without affecting it?
0
 
Mandeep SinghDatabase AdministratorCommented:
Yes script will work and also you can change database recovery model.

More info.. https://technet.microsoft.com/en-us/library/ms178052(v=sql.105).aspx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.