Created
April 25, 2020 01:01
-
-
Save rosshiga/30dfaebedfe6ca750b39675825d58444 to your computer and use it in GitHub Desktop.
MS SQL Backup Script
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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 | |
| -- specify database backup directory | |
| SET @path = 'F:\ACSSQL\' | |
| -- specify filename format | |
| SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) + '_' + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','') | |
| DECLARE db_cursor CURSOR READ_ONLY FOR | |
| SELECT name | |
| FROM master.sys.databases | |
| WHERE name NOT IN ('master','model','msdb','tempdb') -- exclude these databases | |
| AND state = 0 -- database is online | |
| AND is_in_standby = 0 -- database is not read only for log shipping | |
| OPEN db_cursor | |
| FETCH NEXT FROM db_cursor INTO @name | |
| WHILE @@FETCH_STATUS = 0 | |
| BEGIN | |
| SET @fileName = @path + @name + '_' + @fileDate + '.BAK' | |
| BACKUP DATABASE @name TO DISK = @fileName WITH COPY_ONLY; | |
| FETCH NEXT FROM db_cursor INTO @name | |
| END | |
| CLOSE db_cursor | |
| DEALLOCATE db_cursor |
Author
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
https://www.mssqltips.com/sqlservertip/1070/simple-script-to-backup-all-sql-server-databases/