One Script to Backup All SQL Databases @ 肖恩D++

One Script to Backup All SQL Databases

Posted on August 15th, 2011 Sean Add comments

It’s a nightmare for backup your DB if you have 100+ DB’s in SQL server. Just simply run a script while you taking a coffee:

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 = "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') 
--skip system database (optional)

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

       FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

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 = "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')

--skip system database (optional)

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

FETCH NEXT FROM db_cursor INTO @name

END

CLOSE db_cursor

DEALLOCATE db_cursor

ASP.NET Database, SQL

2 Stars

3 Stars

4 Stars

5 Stars

(No Ratings Yet)

Loading...

263 views

Leave a Reply

Be the First to Comment!

Please insert the code above to comment

Notify of new replies to this comment
Notify of all new follow-up comments

Calendar

Networks

Categories

Archives

Recent Comments

stefan on Enable App Twin for HUAWEI Mate 8 “Unsupported” Apps
coin info on Fix HP LaserJet 1018/1020 not printing issue in Ubuntu 14.04
Joseph on Fix HP LaserJet 1018/1020 not printing issue in Ubuntu 14.04
berry johnson on How to manually update extensions on Google Chrome
Zeljko on Enable App Twin for HUAWEI Mate 8 “Unsupported” Apps

Links

Who's Online

1 visitors online now

Visitor Counter

Total Visitors 222,849
Visitors today 72
Page views today 288
Curently online 2
Page views of current page 434,346
Statistics
Counterstart 2014-12-11

Meta