cuongk6t's Blog

How they do that – step by step

SQLServer – Backup and Restore database

leave a comment »


(A gift to Quân)

Backup:

Ten file sau khi backup se co dang: “DBnameYYYDDMM.BAK”
Tao 1 store de thuc hien viec backup nay. Noi dung store:

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

Luu y: may cai tham so sau:

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

 

Hay chinh lai de no la tham so dau vao cua store procedure. (bo declare, them dau “,” o sau, …)

RESTORE:

Khong can dung Cursor nhu phan backup nua, quan trong nhat la cau lenh nay:

RESTORE DATABASE TENDATABASE FROM DISK = N’Đường dẫn đã sao lưu\\tên file.bak’ WITH FILE = 1, NOUNLOAD, STATS = 10

cuongk6t@yahoo.com – Nguyen Quoc Cuong

Written by cuongk6t

20/07/2011 at 11:15 am

Posted in Kinh nghiệm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: