Backup All MS SQL Server Databases

Refer : Refactormycode.com

### โค้ดอันแรกนี้จะเป็นการแบ็คอัพทุกก้อนเลยถ้าชื่อฐานข้อมูลปกติก็ไม่มีปัญหา

Use Master

Declare @DatabaseName sysname
Declare @SQLCommand varchar(1024)
Declare curDBName Cursor For
Select [Name] From Master..Sysdatabases
Where [Name] Not In ('tempdb')

Open curDBName
Fetch curDBName Into @DatabaseName

While (@@fetch_status = 0)
Begin
if databasepropertyex (@DatabaseName,'Status') = 'online'
Begin
Select @SQLCommand = 'Backup Database ' + @DatabaseName +
' To Disk = ''D:\Backups\Databases\' + @DatabaseName + '.bak'' With Format'
execute (@SQLCommand)
End 
Fetch curDBName Into @DatabaseName
End

Close curDBName
Deallocate curDBName

### Refractor above code to ใช้ระบุเฉพาะก้อนๆ ไป

Use Master

Declare @ToExecute VarChar(8000)

Select @ToExecute = Coalesce (@ToExecute + 'Backup Database ' + [Name]
+ ' To Disk = ''D:\Backups\Databases\' + [Name]  + '.bak'' With Format;'
+ char(13),'')

From Master..Sysdatabases

Where [Name] Not In ('tempdb')
And databasepropertyex ([Name],'Status') = 'online'

--Print @ToExecute
Exec (@ToExecute)

### Refractor above code to by juuier ^^'

Use Master

Declare @ToExecute VarChar(8000)

Declare @DatabaseName varchar(50)
Declare @PathTo varchar(300)

set @DatabaseName = 'ชื่อฐานข้อมูลที่ต้องการ backup'
set @PathTo = 'D:\Backups\samples\'

Select @ToExecute = Coalesce (@ToExecute + 'Backup Database ' + @DatabaseName
+ ' To Disk = ''' + @PathTo  + @DatabaseName + '_backup_'
+ convert(varchar,year(getDate())) + '-'
+ convert(varchar,month(getDate())) + '-'
+ convert(varchar,day(getDate())) + '.bak'' With Format;'
+ char(13),'')

From Master..Sysdatabases

Where @DatabaseName Not In ('tempdb')
And databasepropertyex (@DatabaseName,'Status') = 'online'

--Print @ToExecute
Exec (@ToExecute)

Note: ดูเหมือนว่ามันจะมีปัญหากับ ชื่อ database ที่มีเครื่องหมายพิเศษนะเพราะมันขึ้นงี้อ่ะ

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '-'.

• backup ฐานข้อมูลชื่อ db-1 ปัญหาคงคล้ายเรื่อง spacebar ในชื่อ
• หรือ มันอาจ Command(s) completed successfully. แต่จะไม่มีการสร้างไฟล์ .bak อันนี้มันมั่ว - -'
• แล้วมันจะแก้ไงล่ะเนี๋ย ^^' เอางี้ดีกว่าถ้าจะสร้าง Database ควรตั้งชื่อโดยใช้หลักเดียวกันกับชื่อ e-mail
• ก็ใช้หลักการเดียวกับ การแก้เรื่องช่องว่า เช่นเมื่อเราสร้าง table ชื่อ table 1 ซึ่งมีช่องว่าคั่นกลางเวลาเราเขียนโปรแกรมจะมีปัญหาตอนเรียก table นั้น เราต้องเรียกในโค้ดเป็น [table 1]

set @DatabaseName = '[' + 'ชื่อฐานข้อมูลที่ต้องการ backup' + ']' -- แก้แบบนี้มันยังไม่หายเดื้องอ่ะ

Select @ToExecute = Coalesce (@ToExecute + 'Backup Database ' + '[' +
@DatabaseName + ']' -- อันนี้อ่ะผ่าน ^^' ได้แหละ

####################################################################
### Final refractor above code to by juuier ^^' ok any database name

Use Master

Declare @ToExecute VarChar(8000)

Declare @DatabaseName varchar(50)
Declare @PathTo varchar(300)

set @DatabaseName = 'ชื่อฐานข้อมูลที่ต้องการ backup'
set @PathTo = 'D:\Backups\samples\'

Select @ToExecute = Coalesce (@ToExecute + 'Backup Database ' + '['
+ @DatabaseName + ']'
+ ' To Disk = ''' + @PathTo  + @DatabaseName + '_backup_'
+ convert(varchar, year(getDate())) + '-'
+ convert(varchar, month(getDate())) + '-'
+ convert(varchar, day(getDate())) + '.bak'' With Format;'
+ char(13),'')

From Master..Sysdatabases

Where @DatabaseName Not In ('tempdb') 
And databasepropertyex (@DatabaseName,'Status') = 'online'

--Print @ToExecute
Exec (@ToExecute)

####################################################################

• ลืมบอกไปอีกอย่างอันที่ backup (คำสั่งที่ refractor แล้ว) จะเป็นอบบ overwrite คือ มันจะเป็นการเขียนทับอ่ะนะ และ ถ้ามี ไฟล์ .bak อยู่แล้วมันก็จะสร้างไปทับไฟล์เดิม
• ส่วนการ restore ไม่รู้อ่ะ แง๋ว ^^'
• ถ้าเปรียบเทียบกับ Job ก็ไม่ต่างเท่าไหร่หรอกอ่ะ
• Job มันก็คล้ายโปรแกรมเราที่เขียนขึ้นมาเพราะมันจะทำงานตามสคริปเราเหมือนเดิม
• การใช้ Job backup ฐานข้อมูล Go

1. คลิกขวาที่ Jobs => New Job ...

2. มันจะได้ Dialog สำหรับกำหนดค่างานให้เรามองทางด้านซ้าย

General => ใช้ตั้งชื่อ และ กำหนดเจ้าของงาน

Steps => สร้างสคริปคำสั่ง

Scheduled => ตั้งเวลาการทำงาน

* Type เราสามารถเลือกรัน ไฟล์ประเภท bat, cmd หรือ exe ได้โดยเราเลือกเป็น CmdExec

3. เราไปดู Steps เราเลยดีกว่า Step name: ใส่ชื่อซะ และที่ Database: เลือก ชื่อ db ที่จะ backup ซะ

4. command: ให้เราเขียนประมาณนี้ สมมุติเราเลือก Database: เป็น ReportServer

BACKUP DATABASE ReportServer TO

DISK='D:\Backups\Databases\fullbackup.bak' WITH INIT

# หรือ จะใช้อันนี้คิดว่าน่าจะดีกว่านะ

Declare @DatabaseName varchar(50)

Declare @PathTo varchar(300)

Declare @Fullpath varchar(300)

set @DatabaseName = 'dbname'

set @PathTo = 'D:\database backups\'

set @Fullpath = @PathTo + @DatabaseName + '_backup_'

+ convert(varchar,year(getDate())) + '-'

+ convert(varchar,month(getDate())) + '-'

+ convert(varchar,day(getDate())) + '.bak'

BACKUP DATABASE @DatabaseName TO

DISK=@Fullpath WITH INIT

5. ตั้ง scheduled สำหรับเวลาทำงานของ Job

6. แค่นี้อ่ะ ง่ายมากๆ คิดว่าน่าจะดีกว่าที่จะมานั่งเขียนโปรแกรมเรียก สคริปอยู่นะ อืม แต่มันก็ไม่ต่างกันเท่าไหร่อ่ะ

เพิ่มเติม: เราสามารถ backup เป็นไฟล์เดียวก็ได้แต่ต้องทำแบบ append คือ จะไม่ลบข้อมูลก่อนหน้านี้ จะแยกกันตามวันเวลา backup แต่จะอยู่ในไฟล์เีดียวกัน แต่ไม่รู้ต้องใส่คำสั่งอาไรเพิ่มอีกอ่ะนะ ^^'

Example backup procedure

CREATE PROCEDURE [dbo].[BACKUP_PROCEDURE]
@backup_path varchar(50) = 'D:\Backups' -- path file bak
, @backup_name varchar(50) = 'sample' -- for bak name
, @database_for_backup varchar(50) = 'sample' -- database name for backup
AS
BEGIN

Declare @DatabaseNameForBackup varchar(50)
Declare @DatabaseName varchar(50)
Declare @PathTo varchar(300)
Declare @Fullpath varchar(300)

-- @DatabaseNameForBackup = @backup_name
set @DatabaseNameForBackup = @database_for_backup
set @DatabaseName = @backup_name
set @PathTo = @backup_path
set @Fullpath = @PathTo + @DatabaseName + '_backup_'
+ convert(varchar,year(getDate())) + '-'
+ convert(varchar,month(getDate())) + '-'
+ convert(varchar,day(getDate())) + '.bak'

BEGIN TRY

--BACKUP DATABASE @DatabaseName TO
BACKUP DATABASE @DatabaseNameForBackup TO
DISK=@Fullpath WITH INIT

print 'backup successed'

return 1 -- แจ้งการทำงานว่าสำเร็จด้วยดี

END TRY
BEGIN CATCH

print 'backup error'

return -1 -- สำหรับแจ้งการทำงานว่าล้มเหลว

END CATCH

END

Error ที่อาจพบ

Msg 3201, Level 16, State 1, Procedure BACKUP_PROCEDURE, Line 18
Cannot open backup device 'D:\backup.bak'. Operating system error 5(error not found).
Msg 3013, Level 16, State 1, Procedure BACKUP_PROCEDURE, Line 18
BACKUP DATABASE is terminating abnormally.

• เมื่อเราทำการสร้าง procedure สำหรับ backup แล้วทำการเรียก procedure บางครั้งมันอาจเกิด error แบบนี้เมื่อเราทำการ execute procedure
• น่าจะเกียวกับ permission อันนี้เดาเอาเอง ^^' ถ้าเกิด error ดังตัวสีแดงให้เราลองเปลี่ยน path สำหรับเก็บไฟล์ bak ใหม่ดู เช่น จาก error จะเห็นว่าเราบันทึกไฟล์ไว้ที่ 'D:\backup.bak'
• ให้เราลองเปลี่ยน path เป็นที่อื่นเช่น sub folder ลงไปอีกอาไรประมาณนั้น 'D:\Backups\backup.bak' ต้องสร้าง Backups folder ไว้ด้วยล่ะ
  

Related Posts

• http://juuier.blogspot.com/2008/07/db-back-up-and-restore-database-sql.html

References

• MSSQL 2005

สรุปสั้นๆ ได้ใจความการ backup

-------------------- Easier -------------------

จริงแล้ว backup สั้นๆ แค่นี้ก็ได้

Backup Database [DatabaseName] to DISK='c:\BackupFile'

ถ้ามี BackupFile อยู่แล้ว จะเป็นการ append อ่ะนะเข้าใจตรงกันไม่ต้องกลัวว่าจะไปทับของเดิม
อีกอย่างควรเติมนามสกุล bak เพื่อบอกประเภทไฟล์ซะหน่อยเดียวงง ว่ามันไฟล์อาไรหว่า

-------------------- End easier ---------------

บทความที่คล้ายกัน

• http://www.geekzilla.co.uk/View487F82A5-C96B-4660-A070-F7C8B7FC4431.htm
• http://msdn.microsoft.com/en-us/library/ms186865.aspx