| hoctinhoc |
27-12-2016 03:29 PM |
Script backup toàn bộ các Database trên SQL Server
Script backup toàn bộ các Database trên SQL Server
Trong trường hợp nếu SQL server của bạn có vài chục Database thì việc backup từng Database sẽ mất nhiều thời gian,
Bạn có thể dùng script sau để tiến hành backup 1 loạt Database 1 lần duy nhất
Chỉ việc thay đường dẫn: SET @path = 'your backup location' và tiến hành chạy scrip là xong
Trích dẫn:
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 = 'C:\Backup\'
-- specify filename format
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') -- exclude these databases
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
|
Script 2: Backup Full ghi đè và Tên file không đặt theo ngày
Trích dẫn:
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
-- please change the set @path = 'change to your backup location'. for example,
-- SET @path = 'C:\backup\'
-- or SET @path = 'O:\sqlbackup\' if you using remote drives
-- note that remotedrive setup is extra step you have to perform in sql server in order to backup your dbs to remote drive
-- you have to chnage you sql server accont to a network account and add that user to have full access to the network drive you are backing up to
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 + '_' + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName
WITH NOFORMAT, INIT, SKIP, NOREWIND, NOUNLOAD, STATS = 10
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
|
Tham khảo: https://community.spiceworks.com/scr...ith-one-script
Restore All
Trích dẫn:
restore database [RefreshATGStaging] From disk = 'c:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup\RefreshATGStag ing\RefreshATGStaging_backup_2017_08_16_070641_183 3060.bak' With replace, recovery restore database [ShrinkTest] From disk = 'c:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup\ShrinkTest\Shr inkTest_backup_2017_08_16_070641_1853060.bak' With replace, recovery restore database [Test2] From disk = 'c:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup\Test2\Test2_ba ckup_2017_08_16_070641_1763060.bak' With replace, recovery
|
:battay: |
