کلمات کلیدی جهت جستجو : پشتیبان ؛ پشتیبان گیری ؛ Backup ؛ Back up
باکمک این SPمیتونید بانک خودتون رو به یکی از روشهای SQLSERVER در آدرس خاص
پشتیبان بگیرید.
البته اگر رکرودهای خاصی مدنظرتون هست میتونید قبل از پشتیبان گیری اونا رو به یک جدول کمکی منتقل کنید بعد اون جدول رو پشتیبان بگیرید
میتونی یک بانک خاص (پارامتر اول )رو به سه روش (پارامتر دوم)
0=full
1=diff
2=tlog
در آدرس خاصی پشتیبان بگیری (پارامتر سوم وچهارم)
this stored procedure is designed to take x parameters:
@dbname : required, this is the database you wish to backup null or comma seperated list
@bktype : required, this is the type of backup 0=full,1=diff,2=tlog
@bkpath : required, this is the path can be a drive letter i.e. c:\bak or a unc \\myserver\bak
@bkpathhold : required, this is the directory that backups are spooled to first
usage
usp_backup <null or comma seperated list>,2,'<path to put finished files>','<path to holding files>'
this has only been tested under ms-sql2k and win2k
اینم کد SP
--installes the error messages for the backup scripts.
if (select count(*) from dbo.sysmessages where error between 51000 and 51005) > 0
begin
print 'error messages may already exist, please check and correct the error.'
end
else
begin
EXEC sp_addmessage 51000, 16,
N'backup space does not exist',
@with_log = 'true',
@replace = 'replace'
EXEC sp_addmessage 51001, 16,
N'error in syntax ether set @bkall to 0=full,1=diff, or 2=tlog',
@with_log = 'true',
@replace = 'replace'
EXEC sp_addmessage 51002, 16,
N'backup file corrupt',
@with_log = 'true',
@replace = 'replace'
EXEC sp_addmessage 51003, 16,
N'backup file did not copy',
@with_log = 'true',
@replace = 'replace'
EXEC sp_addmessage 51004, 16,
N'backup file does not exist to move',
@with_log = 'true',
@replace = 'replace'
EXEC sp_addmessage 51005, 16,
N'backup drive does not have enough space to complete',
@with_log = 'true',
@replace = 'replace'
end
go
if exists (select * from dbo.sysobjects where id = object_id('[dbo].[usp_backup]') and objectproperty(id, 'isprocedure') = 1)
begin
drop procedure dbo.usp_backup
end
go
--drop it if it is already in the syscatalog
create procedure usp_backup
@dbname as varchar(255),
@bktype as tinyint,
@bkpath varchar(300),
@bkpathhold varchar(300)
with recompile
-- we do with recompile because we don't need an execution plan hanging around
as
set nocount on
declare @fds as numeric(38,8)
/*holds free drive space*/
declare @fdshld as numeric(38,8)
/*holds free drive space*/
declare @cmd varchar(4000)
/*holds command to be executed*/
declare @dbname_cursor as varchar(255)
/*holds database name for cursor*/
declare @exiterr as bit
/*holds the error indicator*/
declare @db_used as numeric(38,8)
/*holds db space used*/
declare @log_used as numeric(38,8)
/*holds log space used*/
declare @db_bk_mode as varchar(255)
/*holds backup mode indicator*/
declare @dbholder varchar(200)
/*holds db name for parsing*/
declare @pos numeric(38,8)
/*used in parsing*/
declare @bkvar as varchar(255)
/*holds path for backup directory*/
declare @bkvarhld as varchar(255)
/*holds path for backup directory*/
declare @bkpathholdsrv as varchar(500)
/*holds path for backup directory*/
declare @bkpathsrv as varchar(500)
/*holds path for backup directory*/
declare @full as varchar(255)
/*holds path for backup directory*/
declare @fullhld as varchar(255)
/*holds path for backup directory*/
declare @fname as varchar(255)
/*holds path for backup directory*/
declare @svrname as varchar(255)
/*holds path for backup directory*/
declare @time as varchar(255)
/*holds path for backup directory*/
declare @date as varchar(255)
/*holds path for backup directory*/
declare @vrfy as tinyint
/*hold the verification of backup*/
/*get our date and time for later use*/
select @date = convert(varchar,getdate(),112)
select @time = replace(convert(varchar,current_timestamp,114),':' ,'')
select @time = left(@time,4)
/*get local servername to use in backup job*/
select @svrname = cast(serverproperty('servername') as varchar(255))
/*check to see if it is an instance name and correct for the slash*/
if charindex('\',@svrname,1) > 0
begin
set @svrname = replace(@svrname,'\','_')
end
/*clear the error flag*/
set @exiterr = 0
/*table to hold our parsed list of databases*/
create table #dblist
(
dbname varchar(255)
)
/*table #free_drive_space to hold free drive space on backup drive from command dir output*/
create table #free_drive_space
(
dir varchar(8000)
)
/*table #idxtmp to hold used space in db from sysindexes*/
create table #idxtmp
(
dbname varchar(255),
used numeric(38,8)
)
/*table #logspace to hold log space useage in db from dbcc sqlperf(logspace)*/
create table #logspace
(
name varchar(255),
log_size numeric(38,8),
used numeric(38,8),
status bit
)
/*table #db_stats to hold database useage statistics*/
create table #db_stats
(
dbname varchar(255),
db_size numeric(38,8),
log_size numeric(38,8),
db_used numeric(38,8),
log_used numeric(38,8),
db_free numeric(38,8),
log_free numeric(38,8)
)
/*for error checking and making sure our backup structures exisit on the file system*/
create table #direxist
(
fe numeric(38,8),
fd numeric(38,8),
pd numeric(38,8)
)
/*hold active thread info for delay if needed*/
create table #tmpthread
(
name varchar(100),
program_name varchar(500),
cmd varchar(500)
)
/*grabs the status of every database*/
create table #dbstatus
(
dbname varchar(100),
recovery varchar(100),
status varchar(100),
updateability varchar(100),
useraccess varchar(100)
)
create table #first_backup
(
dbname varchar(100),
create_date datetime,
backup_start_date datetime
)
insert into #first_backup
select
s.name as 'database',
s.crdate as 'create_date',
b.backup_start_date
from
master.dbo.sysdatabases s
left outer join
msdb..backupset b
on
s.name = b.database_name
and
b.backup_start_date = (select
max(backup_start_date)
from
msdb..backupset
where
database_name = b.database_name
and type = 'D'
)
where
s.name <> 'tempdb'
and
b.backup_start_date IS NOT NULL
/*set command we want to run*/
set @cmd='exec master..xp_cmdshell ''dir "'+@bkpathhold+'"'''
/*insert output of the dir command into temp table*/
insert into #free_drive_space exec (@cmd)
/*pull free drive space in bytes into a variable*/
select @fdshld = (
select top 1
cast(replace(rtrim(ltrim(right(substring(substring (dir,1,charindex(' bytes',dir)),charindex('(s)',dir),len(dir)),len(su bstring(substring(dir,1,charindex(' bytes',dir)),charindex('(s)',dir),len(dir)))-2))),',','')as float) as drive_space
from
#free_drive_space
where
dir like '%bytes free%'
order by
dir asc
)
truncate table #free_drive_space
/*set command we want to run*/
set @cmd='exec master..xp_cmdshell ''dir "'+@bkpath+'"'''
/*insert output of the dir command into temp table*/
insert into #free_drive_space exec (@cmd)
/*pull free drive space in bytes into a variable*/
select @fds = (
select top 1
cast(replace(rtrim(ltrim(right(substring(substring (dir,1,charindex(' bytes',dir)),charindex('(s)',dir),len(dir)),len(su bstring(substring(dir,1,charindex(' bytes',dir)),charindex('(s)',dir),len(dir)))-2))),',','')as float) as drive_space
from
#free_drive_space
where
dir like '%bytes free%'
order by
dir asc
)
/*insert output of dbcc sqlperf(logspace) into temp table*/
insert into #logspace exec('dbcc sqlperf(logspace) with no_infomsgs')
/*build cursor to populate #idxtmp*/
declare db_cursor cursor for
select name from master.dbo.sysdatabases
open db_cursor
fetch next from db_cursor into @dbname_cursor
while @@fetch_status = 0
begin
insert into #dbstatus
select
@dbname_cursor as dbname,
convert(sysname,databasepropertyex(@dbname_cursor, 'recovery')),
convert(sysname,databasepropertyex(@dbname_cursor, 'status')),
convert(sysname,databasepropertyex(@dbname_cursor, 'updateability')),
convert(sysname,databasepropertyex(@dbname_cursor, 'useraccess '))
if (select status from #dbstatus where dbname = @dbname_cursor) <> 'RESTORING'
begin
exec('insert into #idxtmp select '''+@dbname_cursor+''' as dbname,sum(convert(numeric(38,8),(used))*8192)
from ['+@dbname_cursor+'].dbo.sysindexes
where indid in (0, 1, 255)')
end
fetch next from db_cursor into @dbname_cursor
end
close db_cursor
deallocate db_cursor
/*populate #db_stats for later use*/
insert into #db_stats
select
a.name,
a.db_size,
b.log_size,
isnull(i.used,0) as db_used,
isnull(l.used,0) as log_used,
(a.db_size-i.used) as db_free,
isnull((b.log_size-l.used),0) as log_free
from
(
select
d.name,
sum(cast(f.size as numeric(38,8)))*8192 as db_size
from
master..sysdatabases d
inner join
master..sysaltfiles f
on
d.dbid = f.dbid
where
(f.status & 64 = 0)
group by
d.name
)a
inner join
(
select
d.name,
sum(cast(f.size as numeric(38,8)))*8192 as log_size
from
master..sysdatabases d
inner join
master..sysaltfiles f
on
d.dbid = f.dbid
where
(f.status & 64 <> 0)
group by
d.name
)b
on
a.name = b.name
inner join
#idxtmp i
on
a.name = i.dbname
left outer join
(
select
name,
cast(round((log_size*1048576)*(used/100),0,1)as numeric(38,8)) as used from #logspace
) l
on
a.[name] = l.[name]
where
a.name not like '%tempdb%'
/*if backup directory isn't found issue and error*/
if @fds is null or @fdshld is null
begin
raiserror (51000,16,1) with log
set @exiterr = 1
end
/*if wrong backup type indicated issue error*/
if @bktype is null or @bktype > 2
begin
raiserror (51001,16,1) with log
set @exiterr = 1
end
/*if the database name is not null parse the list*/
if @dbname is not null
begin
set @dbname = ltrim(rtrim(@dbname))+ ','
set @pos = charindex(',', @dbname, 1)
if replace(@dbname, ',', '') <> ''
begin
while @pos > 0
begin
set @dbholder = ltrim(rtrim(left(@dbname, @pos - 1)))
if @dbholder <> ''
begin
insert into #dblist (dbname) values (@dbholder) --use appropriate conversion
end
set @dbname = right(@dbname, len(@dbname) - @pos)
set @pos = charindex(',', @dbname, 1)
end
end
end
else
begin
insert into #dblist
select dbname from #db_stats
end
/* if there are no errors from above continue on*/
/*build directory structures to hold backups*/
if @exiterr = 0
begin
select @bkpath = rtrim(@bkpath)
select @bkpathsrv = rtrim(@bkpath)+'\'+rtrim(@svrname)
select @bkpathhold = rtrim(@bkpathhold)
select @bkpathholdsrv = rtrim(@bkpathhold)+'\'+rtrim(@svrname)
truncate table #direxist
insert into #direxist exec master..xp_fileexist @bkpathsrv
if (select fd from #direxist) = 1
begin
truncate table #direxist
end
else
if (select fd from #direxist) = 0
begin
set @cmd='exec master..xp_cmdshell ''md "'+@bkpathsrv+'"'',no_output'
exec(@cmd)
end
truncate table #direxist
insert into #direxist exec master..xp_fileexist @bkpathholdsrv
if (select fd from #direxist) = 1
begin
truncate table #direxist
end
else
if (select fd from #direxist) = 0
begin
set @cmd='exec master..xp_cmdshell ''md "'+@bkpathholdsrv+'"'',no_output'
exec(@cmd)
end
declare full_cursor cursor scroll for
select
dbname
from
#db_stats
where
dbname <> 'tempdb'
and
dbname <> 'pubs'
and
dbname <> 'northwind'
--get all the db names and load up a cursor
open full_cursor
--open up the cursor
fetch next from full_cursor into @dbname
--load the first db name
while @@fetch_status = 0
--while we have db names run the loop!
begin
select @bkvar = rtrim(@bkpath)+'\'+rtrim(@svrname)+'\'+rtrim(@dbna me)
select @bkvarhld = rtrim(@bkpathhold)+'\'+rtrim(@svrname)+'\'+rtrim(@ dbname)
truncate table #direxist
insert into #direxist exec master..xp_fileexist @bkvar
if (select fd from #direxist) = 0
begin
set @cmd='exec master..xp_cmdshell ''md "'+@bkvar+'"'',no_output'
exec(@cmd)
end
truncate table #direxist
insert into #direxist exec master..xp_fileexist @bkvarhld
if (select fd from #direxist) = 0
begin
set @cmd='exec master..xp_cmdshell ''md "'+@bkvarhld+'"'',no_output'
exec(@cmd)
end
truncate table #direxist
fetch next from full_cursor into @dbname
end
close full_cursor
deallocate full_cursor
/*if there is nothing in the db list process all databases*/
if (select count(*) from #dblist) > 0
begin
if @bktype = 0
begin
declare full_cursor cursor scroll for
select
s.dbname,
s.db_used,
s.log_used
from
#db_stats s
inner join
#dblist d
on
s.dbname=d.dbname
inner join
#dbstatus c
on
d.dbname=c.dbname
where
c.status = 'ONLINE'
and
c.useraccess = 'MULTI_USER'
and
s.dbname <> 'tempdb'
and
s.dbname <> 'pubs'
and
s.dbname <> 'northwind'
--get all the db names and load up a cursor
open full_cursor
--open up the cursor
fetch next from full_cursor into @dbname,@db_used,@log_used
--load the first db name
while @@fetch_status = 0
--while we have db names run the loop!
begin
select @bkpath = rtrim(@bkpath)
select @bkpathsrv = rtrim(@bkpath)+'\'+rtrim(@svrname)
select @bkvar = rtrim(@bkpath)+'\'+rtrim(@svrname)+'\'+rtrim(@dbna me)
select @fname = @dbname+'_full_'+@date+@time+'.bak'
select @full = @bkvar+'\'+@fname
select @bkpathhold = rtrim(@bkpathhold)
select @bkpathholdsrv = rtrim(@bkpathhold)+'\'+rtrim(@svrname)
select @bkvarhld = rtrim(@bkpathhold)+'\'+rtrim(@svrname)+'\'+rtrim(@ dbname)
select @fname = @dbname+'_full_'+@date+@time+'.bak'
select @fullhld = @bkvarhld+'\'+@fname
select @fds =
(
select top 1
cast(replace(rtrim(ltrim(right(substring(substring (dir,1,charindex(' bytes',dir)),charindex('(s)',dir),len(dir)),len(su bstring(substring(dir,1,charindex(' bytes',dir)),charindex('(s)',dir),len(dir)))-2))),',','')as bigint) as drive_space
from
#free_drive_space
where
dir like '%bytes free%'
order by
dir asc
)
if (@db_used+@log_used) < @fds
begin
--if it fits backup the db
truncate table #tmpthread
insert into #tmpthread
select
b.name,
a.program_name,
a.cmd
from master.dbo.sysprocesses a with (nolock)
inner join
master.dbo.sysdatabases b with (nolock)
on
a.dbid = b.dbid
where
ltrim(rtrim(b.name)) = @dbname
and
(
program_name like '%sqllitespeed%'
or
cmd like '%DBCC%'
or
cmd like '%BACKUP DATABASE%'
or
cmd like '%BACKUP LOG%'
)
startwatch1:
if (select count(*) from #tmpthread) > 0
begin
truncate table #tmpthread
insert into #tmpthread
select
b.name,
a.program_name,
a.cmd
from
master.dbo.sysprocesses a with (nolock)
inner join
master.dbo.sysdatabases b with (nolock)
on
a.dbid = b.dbid
where
ltrim(rtrim(b.name)) = @dbname
and
(
program_name like '%sqllitespeed%'
or
cmd like '%DBCC%'
or
cmd like '%BACKUP DATABASE%'
or
cmd like '%BACKUP LOG%'
)
waitfor delay '000:01:00'
goto startwatch1
end
else
begin
--backup database
backup database @dbname
to disk = @fullhld
--verify backup
restore verifyonly
from
disk=@fullhld
if @@error <> 0
begin
raiserror (51002,16,1) with log
end
end
truncate table #direxist
insert into #direxist exec master..xp_fileexist @fullhld
if (select fe from #direxist) = 1
begin
/*set command we want to run*/
set @cmd='move "'+@fullhld+'" "'+@full+'"'
/*move files to backup locale*/
exec master..xp_cmdshell @cmd,no_output
truncate table #direxist
insert into #direxist exec master..xp_fileexist @full
if (select fe from #direxist) = 0
begin
raiserror (51003,16,1) with log
fetch next from full_cursor into @dbname,@db_used,@log_used
end
end
else
begin
raiserror (51004,16,1) with log
end
truncate table #direxist
fetch next from full_cursor into @dbname,@db_used,@log_used
end
else
begin
raiserror (51005,16,1) with log
truncate table #direxist
fetch next from full_cursor into @dbname,@db_used,@log_used
end
end
close full_cursor
deallocate full_cursor
end
else
if @bktype = 1
begin
declare diff_cursor cursor scroll for
select
s.dbname,
s.db_used,
s.log_used
from
#db_stats s
inner join
#dblist d
on
s.dbname=d.dbname
inner join
#dbstatus c
on
d.dbname=c.dbname
inner join
#first_backup e
on
c.dbname = e.dbname
where
c.status = 'ONLINE'
and
c.useraccess = 'MULTI_USER'
and
s.dbname <> 'tempdb'
and
s.dbname <> 'pubs'
and
s.dbname <> 'northwind'
and
s.dbname <> 'master'
--get all the db names and load up a cursor
open diff_cursor
--open up the cursor
fetch next from diff_cursor into @dbname,@db_used,@log_used
--load the first db name
while @@fetch_status = 0
--while we have db names run the loop!
begin
select @bkpath = rtrim(@bkpath)
select @bkpathsrv = rtrim(@bkpath)+'\'+rtrim(@svrname)
select @bkvar = rtrim(@bkpath)+'\'+rtrim(@svrname)+'\'+rtrim(@dbna me)
select @fname = @dbname+'_diff_'+@date+@time+'.bak'
select @full = @bkvar+'\'+@fname
select @bkpathhold = rtrim(@bkpathhold)
select @bkpathholdsrv = rtrim(@bkpathhold)+'\'+rtrim(@svrname)
select @bkvarhld = rtrim(@bkpathhold)+'\'+rtrim(@svrname)+'\'+rtrim(@ dbname)
select @fname = @dbname+'_diff_'+@date+@time+'.bak'
select @fullhld = @bkvarhld+'\'+@fname
select @fds =
(
select top 1
cast(replace(rtrim(ltrim(right(substring(substring (dir,1,charindex(' bytes',dir)),charindex('(s)',dir),len(dir)),len(su bstring(substring(dir,1,charindex(' bytes',dir)),charindex('(s)',dir),len(dir)))-2))),',','')as bigint) as drive_space
from
#free_drive_space
where
dir like '%bytes free%'
order by
dir asc
)
if (@db_used+@log_used) < @fds
begin
--if it fits backup the db
truncate table #tmpthread
insert into #tmpthread
select
b.name,
a.program_name,
a.cmd
from master.dbo.sysprocesses a with (nolock)
inner join
master.dbo.sysdatabases b with (nolock)
on
a.dbid = b.dbid
where
ltrim(rtrim(b.name)) = @dbname
and
(
program_name like '%sqllitespeed%'
or
cmd like '%DBCC%'
or
cmd like '%BACKUP DATABASE%'
or
cmd like '%BACKUP LOG%'
)
startwatch2:
if (select count(*) from #tmpthread) > 0
begin
truncate table #tmpthread
insert into #tmpthread
select
b.name,
a.program_name,
a.cmd
from
master.dbo.sysprocesses a with (nolock)
inner join
master.dbo.sysdatabases b with (nolock)
on
a.dbid = b.dbid
where
ltrim(rtrim(b.name)) = @dbname
and
(
program_name like '%sqllitespeed%'
or
cmd like '%DBCC%'
or
cmd like '%BACKUP DATABASE%'
or
cmd like '%BACKUP LOG%'
)
waitfor delay '000:01:00'
goto startwatch2
end
else
begin
--backup database
backup database @dbname
to disk = @fullhld
with differential
--verify backup
restore verifyonly
from
disk=@fullhld
if @@error <> 0
begin
raiserror (51002,16,1) with log
end
end
truncate table #direxist
insert into #direxist exec master..xp_fileexist @fullhld
if (select fe from #direxist) = 1
begin
/*set command we want to run*/
set @cmd='move "'+@fullhld+'" "'+@full+'"'
/*move files to backup locale*/
exec master..xp_cmdshell @cmd,no_output
truncate table #direxist
insert into #direxist exec master..xp_fileexist @full
if (select fe from #direxist) = 0
begin
raiserror (51003,16,1) with log
fetch next from diff_cursor into @dbname,@db_used,@log_used
end
end
else
begin
raiserror (51004,16,1) with log
end
truncate table #direxist
fetch next from diff_cursor into @dbname,@db_used,@log_used
end
else
begin
raiserror (51005,16,1) with log
truncate table #direxist
fetch next from diff_cursor into @dbname,@db_used,@log_used
end
end
close diff_cursor
deallocate diff_cursor
end
else
if @bktype = 2
begin
declare tlog_cursor cursor scroll for
select
s.dbname,
s.db_used,
s.log_used
from
#db_stats s
inner join
#dblist d
on
s.dbname=d.dbname
inner join
#dbstatus c
on
d.dbname=c.dbname
inner join
#first_backup e
on
c.dbname = e.dbname
where
c.status = 'ONLINE'
and
c.useraccess = 'MULTI_USER'
and
s.dbname <> 'tempdb'
and
s.dbname <> 'pubs'
and
s.dbname <> 'northwind'
and
s.dbname <> 'master'
and
c.recovery <> 'SIMPLE'
--get all the db names and load up a cursor
open tlog_cursor
--open up the cursor
fetch next from tlog_cursor into @dbname,@db_used,@log_used
--load the first db name
while @@fetch_status = 0
--while we have db names run the loop!
begin
select @bkpath = rtrim(@bkpath)
select @bkpathsrv = rtrim(@bkpath)+'\'+rtrim(@svrname)
select @bkvar = rtrim(@bkpath)+'\'+rtrim(@svrname)+'\'+rtrim(@dbna me)
select @fname = @dbname+'_tran_'+@date+@time+'.trn'
select @full = @bkvar+'\'+@fname
select @bkpathhold = rtrim(@bkpathhold)
select @bkpathholdsrv = rtrim(@bkpathhold)+'\'+rtrim(@svrname)
select @bkvarhld = rtrim(@bkpathhold)+'\'+rtrim(@svrname)+'\'+rtrim(@ dbname)
select @fname = @dbname+'_tran_'+@date+@time+'.trn'
select @fullhld = @bkvarhld+'\'+@fname
if (@log_used) < @fds
begin
--if it fits backup the db
truncate table #tmpthread
insert into #tmpthread
select
b.name,
a.program_name,
a.cmd
from master.dbo.sysprocesses a with (nolock)
inner join
master.dbo.sysdatabases b with (nolock)
on
a.dbid = b.dbid
where
ltrim(rtrim(b.name)) = @dbname
and
(
program_name like '%sqllitespeed%'
or
cmd like '%DBCC%'
or
cmd like '%BACKUP DATABASE%'
or
cmd like '%BACKUP LOG%'
)
startwatch3:
if (select count(*) from #tmpthread) > 0
begin
truncate table #tmpthread
insert into #tmpthread
select
b.name,
a.program_name,
a.cmd
from
master.dbo.sysprocesses a with (nolock)
inner join
master.dbo.sysdatabases b with (nolock)
on
a.dbid = b.dbid
where
ltrim(rtrim(b.name)) = @dbname
and
(
program_name like '%sqllitespeed%'
or
cmd like '%DBCC%'
or
cmd like '%BACKUP DATABASE%'
or
cmd like '%BACKUP LOG%'
)
waitfor delay '000:01:00'
goto startwatch3
end
else
begin
--backup database
backup log @dbname
to disk = @fullhld
--verify backup
restore verifyonly
from
disk=@fullhld
if @@error <> 0
begin
raiserror (51002,16,1) with log
end
truncate table #direxist
insert into #direxist exec master..xp_fileexist @fullhld
if (select fe from #direxist) = 1
begin
/*set command we want to run*/
set @cmd='move "'+@fullhld+'" "'+@full+'"'
/*move files to backup locale*/
exec master..xp_cmdshell @cmd,no_output
truncate table #direxist
insert into #direxist exec master..xp_fileexist @full
if (select fe from #direxist) = 0
begin
raiserror (51003,16,1) with log
end
end
else
begin
raiserror (51004,16,1) with log
end
end
fetch next from tlog_cursor into @dbname,@db_used,@log_used
end
else
begin
raiserror (51005,16,1) with log
fetch next from tlog_cursor into @dbname,@db_used,@log_used
end
end
close tlog_cursor
deallocate tlog_cursor
end
end
end
/*drop all temp tables*/
drop table #dbstatus
drop table #first_backup
drop table #tmpthread
drop table #logspace
drop table #idxtmp
drop table #db_stats
drop table #free_drive_space
drop table #dblist
drop table #direxist
set nocount off
go
بابک زواری پشتیبان گیری SQL
Backup گرفتن تعدادی از table ها در Sql server توسط Store Procedure
من در یکی از سیستمهای خود تمام اطلاعات سال های مالی مختلف را در یک Database قرار داده‌ام . حالا فرش کنید شما می خواهید از اطلاعات سال مالی 84 یک پشتیبان تهیه کنید ولی اطلاعات تمام سال ها backup گرفته می شوند که اصلاٌ جالب نیست . حال می خواهم بدانم که آیا کسی روشی یا برنامه ای سراغ داره که بتوان تعدادی از table ها که مربوط به یک سال مالی در آن دیتابیس است به صورت جداگانه backup بگیره و همانها را نیز restore کنه و نه کل دیتابیس ؟
ولی من شنیدم که توسط دستورات Import/Export این کار رو می شه انجام داد.اما خودم نتونستم این کار رو بکنم. آیا مطمئنید که نمیشه؟ حتما باید یه راهی باشه !!!
نوشته شده توسط hpx
می تونم بپرسم که چطوری می شه با File Group این کار رو انجام داد و اصولا File Group چی کار می کنه؟
