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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Windows Server 2012

From novice to tech pro — start learning today.