June 14, 2004 at 8:35 am
What is the best practice to Backup Database from one Server to another once a day? I currently backup multiple production Servers Database using Database Maintenance Wizard to a network storgae device. My supervisor wants me to back up the multiple production Databases to a test Server on a routine basis; then just backup the one test Server.
June 14, 2004 at 8:49 am
Try a continous tranzactional replication between the production server and the backup server.For me it works fine.
June 15, 2004 at 7:12 am
I do something similar, to populate a reporting database. I run a dts package from the reporting server that first backs up the production database to a dump device (disk) on a network share, then restores the database on the reporting server. I also copy the logins (in case any accounts have been added - this is a DTS step), then run a SQL Server script against the database to auto_fix any orphaned users. It works great, and we just back up the file on the network share to tape for DR purposes.
June 15, 2004 at 9:18 am
Hi Chris, I think your approach will work for me...being a newbie, could you provide more detail steps for the dts packages and script to copy logins/fix orphans
June 15, 2004 at 9:31 am
Sure,
Steps:
1) Backup Database (Execute SQL Task):
USE dbname
GO
BACKUP DATABASE dname TO budevice WITH INIT,
NAME = 'backup set name'
GO
2) Kill users in destination db (Execute SQL Task):
Use master
go
exec sp_kill @dbname='dbname'
Use master
go
alter database SLS
set SINGLE_USER
3) Restore database (Execute SQL task):
Use master
go
RESTORE DATABASE dbname FROM budevice With Replace, MOVE (if necessary - Look at BOL)
4) Transfer Logins (Transfer Logins Task)
5) Fix Orphaned Users Cursor (Execute SQL Task) I found this script on this website:
Use dbname go
declare @usrname varchar(100), @command varchar(100)
declare Crs insensitive cursor for
select name as UserName from sysusers
where issqluser = 1 and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null
order by name
for read only
open Crs
fetch next from Crs into @usrname
while @@fetch_status=0
begin
select @command=' sp_change_users_login ''auto_fix'', '''+@usrname+''' '
exec(@command)
fetch next from Crs into @usrname
end
close Crs
deallocate Crs
/*
Fixing some Orphaned Users left after step 2.
*/
-- sp_change_users_login 'auto_fix','<user_name>'
-- sp_change_users_login 'update_one','<user_name>','<user_name>'
I run a DTS package that includes all of these steps, as well as some logging, on a nightly basis.
June 16, 2004 at 10:09 am
Thanks Chris for your time and help in providing detail steps!!!
June 16, 2004 at 2:53 pm
Hi Chris,
Please help me one more time, can you please provide the script for "sp_kill" as I think it is a User Procedure?
Thanks, Richard
June 17, 2004 at 7:13 am
Sorry I didn't include it the firt time around:
Use master
go
exec sp_kill @dbname='dbname'
Use master
go
alter database dbname
set SINGLE_USER
June 17, 2004 at 11:22 am
Hi, Chris...
You had already provided the sql statement:
Use master
go
exec sp_kill @dbname='dbname'
Use master
go
alter database dbname
set SINGLE_USER
But when I run it, I get:
Could not find stored procedure 'sp_kill'.
What is the script for the stored procedure 'sp_kill'?????
June 17, 2004 at 11:26 am
Doh! Copied the wrong thing. Here you go:
create proc sp_kill
@dbname varchar(100) = null, -- When specified, kills all spids inside of the database
@loginame varchar(50) = null, -- When specified, kills all spids under the login name
@hostname varchar(50) = null -- When specified, kills all spids originating from the host machine
as
begin
set nocount on
select spid, db_name(dbid) as 'db_name', loginame, hostname into #tb1_sysprocesses from master.dbo.sysprocesses (nolock)
declare @total_logins int, @csr_spid varchar(100)
set @total_logins = ( select count(distinct spid) from #tb1_sysprocesses )
if @dbname is null
begin
if @loginame is null
begin
if @hostname is null
begin
if @total_logins > 0
begin
declare csr_spid cursor fast_forward for select distinct spid from #tb1_sysprocesses where loginame <> 'sa' and spid <> @@spid
open csr_spid
fetch next from csr_spid into @csr_spid
while @@fetch_status = 0
begin
set nocount on
exec ('kill ' + @csr_spid)
fetch next from csr_spid into @csr_spid
end
close csr_spid
deallocate csr_spid
end
end
else
begin
if @total_logins > 0
begin
declare csr_spid cursor fast_forward for select distinct spid from #tb1_sysprocesses where hostname = @hostname and loginame <> 'sa' and spid <> @@spid
open csr_spid
fetch next from csr_spid into @csr_spid
while @@fetch_status = 0
begin
set nocount on
exec ('kill ' + @csr_spid)
fetch next from csr_spid into @csr_spid
end
close csr_spid
deallocate csr_spid
end
end
--------------------------------------------------
end
else
begin
if @total_logins > 0
begin
declare csr_spid cursor fast_forward for select distinct spid from #tb1_sysprocesses where loginame = @loginame and loginame <> 'sa' and spid <> @@spid
open csr_spid
fetch next from csr_spid into @csr_spid
while @@fetch_status = 0
begin
set nocount on
exec ('kill ' + @csr_spid)
fetch next from csr_spid into @csr_spid
end
close csr_spid
deallocate csr_spid
end
end
-----------------------
end
else
begin
if @total_logins > 0
begin
declare csr_spid cursor fast_forward for select distinct spid from #tb1_sysprocesses where db_name = @dbname and loginame <> 'sa' and spid <> @@spid
open csr_spid
fetch next from csr_spid into @csr_spid
while @@fetch_status = 0
begin
set nocount on
exec ('kill ' + @csr_spid)
fetch next from csr_spid into @csr_spid
end
close csr_spid
deallocate csr_spid
end
end
drop table #tb1_sysprocesses
end
GO
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply