تقييم الموضوع :
  • 0 أصوات - بمعدل 0
  • 1
  • 2
  • 3
  • 4
  • 5
لعمل نسخ احتياطي يومي إلى مكان معين
#1
كاتب الموضوع : AhmedEssawy


كود :
-- Code
declare @strDrive char(3)
declare @strDir nvarchar(30)
declare @strDB nvarchar(20)
select @strDrive = 'e:\'
select @strDir = 'sqlBak\'
select @strDB = 'A7Dev'
Declare @strYear char(1)
declare @strMonth char(2)
declare @strDay char(2)
declare @strHour char(2)
declare @strMin char(2)
declare @strUnique char(100)
declare @strDesc nvarchar(30)
declare @myfile char(100)
declare @mydir char(100)
declare @mdcmd char(100)
select @strDesc = @strDB + ' Backup'
-- Build the Year portion of the dir.
select @strYear = Right(Str(DatePart(yy, getdate())), 1)
-- Build the Month portion of the dir.
select @strMonth = Right('0' + LTrim(Str(DatePart(mm, getdate()))), 2)
-- Build the Day portion of the dir.
select @strDay = Right('0' + LTrim(Str(DatePart(dd, getdate())) ), 2)
-- Build the Hour portion of the dir.
select @strHour = Right('0' + LTrim(Str(DatePart(hh, getdate())) ), 2)
-- Build the Minute portion of the dir.
select @strMin = Right('0' + LTrim(Str(DatePart(mi, getdate())) ), 2)
-- Add the prefix to the directory.
select @strUnique = @strYear + @strMonth + @strDay + @strHour + @strMin
-- Backup the LocMon database.
select @mydir = @strDrive + @strDir + @strDB + '\b' + RTrim(@strUnique)
select @mdcmd = 'md ' + @mydir
-- print @mdcmd
exec master..xp_cmdshell @mdcmd
select @myfile = RTrim(@mydir) + '\' + @strDB + '.sqb'
BACKUP DATABASE @strDB TO DISK = @myfile WITH INIT, NOUNLOAD , NAME = @strDesc, SKIP , STATS = 10, DESCRIPTION = @strDesc, NOFORMAT
-- Usage
-- Create a SQL Job to run often. Paste this script into
-- the Command area of the wizard where you define the
-- steps.
-- Notes
-- You will need to change the DB variable, and maybe the
-- driver letter and folder. Otherwise, it should work
-- for you.
-----------------------------------------------------------
-- Title: Backup/Restore DB with script -- Language: SQL
-- Description: Might be useful for TDD with small
-- databases. Probably useful for rollout scripts
-- Author: Bram Borak
-- Code
USE master
GO
PRINT '--------------------------------------------------------'
GO
EXEC sp_addumpdevice 'disk', 'whateverDB_backup', 'c:\whateverDB_backup_'+REPLACE(REPLACE(REPLACE(CONVERT(char(25),GETDATE(),20),':','_'),' ','_'),'-','_')+'.dat'
GO
BACKUP DATABASE whateverDB TO whateverDB_backup
GO
EXEC sp_dropdevice 'whateverDB_backup'
GO
PRINT 'Backed up whateverDB_backup'
GO
PRINT '--------------------------------------------------------'
GO
----------------------------------------------------------

PRINT '--------------------------------------------------------'
GO
EXEC sp_addumpdevice 'disk', 'whateverDB_backup', 'c:\whateverDB_backup.dat'
GO
RESTORE DATABASE whateverDB FROM whateverDB_backup
GO
EXEC sp_dropdevice 'whateverDB_backup'
GO
PRINT 'Restored whateverDB'
GO
PRINT '--------------------------------------------------------'
GO
}}}
تم الشكر بواسطة:


المواضيع المحتمل أن تكون متشابهة .
الموضوع : الكاتب الردود : المشاهدات : آخر رد
  لعمل password عشوائية RaggiTech 0 2,214 17-10-12, 02:10 PM
آخر رد: RaggiTech

التنقل السريع :


يقوم بقرائة الموضوع: بالاضافة الى ( 1 ) ضيف كريم