mssql backup
see
mssql backup views
Backup
BACKUP
DATABASE [TestBackup]
TO DISK = N'G:\Backup\TestBackup.bak'
WITH
NOFORMAT, -- save header, else NOFORMAT
INIT, -- overwrite backupset, else append (NOINIT)
NAME = N'TestBackup-Full Database Backup', -- name of backupset
SKIP, -- no checking expiration of exists backupset
NOREWIND, -- no rewind tape
NOUNLOAD, -- after backup tape is loaded
COMPRESSION, -- explicated compress, else NO_COMPRESSION
STATS = 5 -- progress % echo
GO
Restore
RESTORE
DATABASE [TestBackup]
FROM DISK = N'G:\Backup\TestBackup.bak'
WITH
FILE = 1, -- Необязательный параметр - номер файла в backupset
MOVE N'TestBackup_log' TO N'D:\SQLData\TestBackup.ldf', -- перемещение файла в новую локацию
NOUNLOAD, -- ....
REPLACE, -- переписывать имеющийся лог файл
WITH RECOVERY, -- Необязательный параметр
STATS = 5 -- progress % echo
GO
Пользователь для backup/restore
CREATE LOGIN [testbackup] WITH PASSWORD=N'пароль',
DEFAULT_DATABASE=[master],
DEFAULT_LANGUAGE=[us_english],
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF
GO
EXEC sys.sp_addsrvrolemember @loginame = N'testbackup', @rolename = N'processadmin'
GO
EXEC sys.sp_addsrvrolemember @loginame = N'testbackup', @rolename = N'diskadmin'
GO
EXEC sys.sp_addsrvrolemember @loginame = N'testbackup', @rolename = N'dbcreator'
GO
db_owner для базы
Remote Backup
-- configure veraibles
declare @backup_db_name varchar(100) = 'master'
declare @remote_share varchar(100) = '\\address.com\backup'
declare @mnt_drive varchar(10) = 'O:'
declare @remote_dir varchar(200) = '1C-81\backup'
declare @remote_login varchar(100) = 'pswd'
declare @remote_password varchar(100) = 'host\passdesk-db.nikom.ru\'
declare @diff bit = 1
declare @compressed bit = 1
-- main code
declare @backup_name varchar(100)
set @backup_name = 'backup ' + @backup_db_name + ' to '+@remote_share
declare @mnt_cmd varchar(250)
set @mnt_cmd = 'net use '+@mnt_drive+' '+@remote_share+' "'+@remote_password+'" /USER:'+@remote_login
declare @umnt_cmd varchar(250)
set @umnt_cmd = 'net use '+@mnt_drive+' /delete'
declare @curdate datetime
set @curdate = getdate()
declare @curdate_s varchar(100)
set @curdate_s = right(replicate('0',10) + cast(datepart( year, @curdate ) as varchar(10)),4) +
'-' + right(replicate('0',10) + cast(datepart( day, @curdate ) as varchar(10)),2) +
'-' + right(replicate('0',10) + cast(datepart( day, @curdate ) as varchar(10)),2) +
'_' + right(replicate('0',10) + cast(datepart( hour, @curdate ) as varchar(10)),2) +
'-' + right(replicate('0',10) + cast(datepart( minute, @curdate ) as varchar(10)),2) +
'-' + right(replicate('0',10) + cast(datepart( second, @curdate ) as varchar(10)),2)
;
declare @filename nvarchar(250)
if( @diff = 1 ) begin
set @filename = @mnt_drive+'\'+@remote_dir+@backup_db_name+'_'+@curdate_s+'(diff)'+'.bak'
end else begin
set @filename = @mnt_drive+'\'+@remote_dir+@backup_db_name+'_'+@curdate_s+'(full)'+'.bak'
end
print 'mount '+@mnt_drive+' '+@remote_share
DECLARE @result int
exec @result = xp_cmdshell @mnt_cmd
if (@result = 0) begin
print 'mount success'
if ( @diff = 1 ) begin
if ( @compressed = 1 ) begin
backup database @backup_db_name to disk = @filename with
stats = 1,
name = @backup_name,
DIFFERENTIAL,
COMPRESSION
;
end else begin
backup database @backup_db_name to disk = @filename with
stats = 1,
name = @backup_name,
DIFFERENTIAL
;
end
end else begin
if ( @compressed = 1 ) begin
backup database @backup_db_name to disk = @filename with
stats = 1,
name = @backup_name,
COMPRESSION
;
end else begin
backup database @backup_db_name to disk = @filename with
stats = 1,
name = @backup_name
;
end
end
print 'unmount '+@mnt_drive
exec @result = xp_cmdshell @umnt_cmd
if (@result = 0) begin
print 'unmount success'
end
else begin
print 'unmount failure: '+@result
end
end
else
print 'mount failure: '+@result;