Setting up a Standby SQL Server (please let me know if I left something out)

Step 1. Install the Standby SQL Server.

Step 2. Create devices and databases exactly as you created them on the primary server.

Step 3. Change your server configurations to match the primary server.

Step 4. Restore the backups of all your databases, except master, to the standby server.

Step 5. Copy all the loginids and passwords to the standby SQL Server as follows:

From Primary Server -- bcp master..syslogins out logins.txt /c /Usa /Sserver1 /P
On Standby Server -- use pubs - sp_dboption 'pubs','select into', true
On Standby Server -- use pubs - select * into templogins from master..syslogins
On Standby Server -- use pubs - truncate table templogins
On Standby Server -- bcp pubs..templogins in logins.txt /c /Usa /P
On Standby Server -- use pubs - sp_dboption 'pubs','select into', false
On Standby Server -- use pubs - delete pubs..templogins where status = 9

--*set sql server to allow updates to system tables in MASTER
On Standby Server -- sp_configure 'allow updates',1
On Standby Server -- reconfigure with override
--*make table modifications within a transaction so they can be verified before commiting
On Standby Server -- begin transaction
--*delete all existing login ids (except for system ids)
On Standby Server -- delete syslogins where status = 8
--*copy all of the login ids into the backup server syslogins table
On Standby Server -- insert syslogins select * from pubs..newlogins
--*if all is well
On Standby Server -- commit transaction

Step 6. Mark all user databases 'no chkpt on trunc.'

Step 7. Set up Hourly dump of TLOG on Primary. One way to do this is to run this proc hourly:

Create Proc sp_DumpTransLogHourly
as
declare @DumpFile varchar(50)
declare @DumpDB varchar(50)
select @DumpDB = db_name()
select @DumpFile = 'E:\OUTGOING\DumpLog_' + @DumpDB + convert(varchar(2),datepart(hour,getdate())) + '.DAT'
Dump Transaction @DumpDB to disk = @DumpFile

Step 8. Set up Hourly restore of TLOG on Standby. One way to do this is to run this proc hourly:

Create Proc sp_LoadTransLogHourly
as
declare @DumpFile varchar(50)
declare @DumpDB varchar(50)
select @DumpDB = db_name()
select @DumpFile = 'I:\OUTGOING\DumpLog_' + @DumpDB + convert(varchar(2),datepart(hour,getdate())) + '.DAT'
Load Transaction @DumpDB from disk = @DumpFile

Step 9. Set up a process to keep your logins in sync. You might use this stored proc to create logins on the Primary server rather than sp_addlogin:

/****** Object: Stored Procedure dbo.sp_addlogintoboth Script Date: 2/6/97 4:50:01 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.sp_addlogintoboth') and sysstat & 0xf = 4)
drop procedure dbo.sp_addlogintoboth
GO

create procedure sp_addlogintoboth
@loginame varchar(30), /* login name of the new user */
@passwd varchar(30) = NULL, /* password of the new user */
@defdb varchar(30) = 'master', /* default db for the new user */
@deflanguage varchar(30) = NULL /* default language for the new user */
as

declare @returncode int
/*
** If we're in a transaction, disallow this since it might make recovery
** impossible.
*/
if @@trancount > 0
begin
raiserror(15002,-1,-1,'sp_addlogin')
return (1)
end

/*
** Only the system administrator (sa) can run this command.
** Check to make sure the executor is the sa.
*/
if suser_id() <> 1
begin
raiserror(15003,-1,-1)
return (1)
end
/*
** Check to see that the @loginame is valid.
*/
exec @returncode = sp_validname @loginame
if @returncode <> 0
return (1)
/*
** Check to see that the @deflanguage is valid.
*/
if @deflanguage is not null
begin
exec @returncode = sp_validlang @deflanguage
if @returncode <> 0
return(1)
end

/*
** Make sure the login doesn't already exist.
*/
if exists (select * from syslogins where name = @loginame)
begin
raiserror(15025,-1,-1,@loginame)
return (1)
end
/*
** Check that the database name is valid.
*/
if not exists (select * from sysdatabases where name = @defdb)
begin
raiserror(15010,-1,-1,@defdb)
return (1)
end
/*
** Create the login, finding the first available suid in the process.
*/
insert into syslogins(suid, status, accdate, totcpu, totio,
spacelimit, timelimit, resultlimit, dbname, name, password, language)
select min(s1.suid)+1, 8, getdate(), 0, 0,
0, 0, 0, @defdb, @loginame, pwdencrypt(@passwd), @deflanguage
from syslogins s1
where suid < 32767 /* Don't cause arithmetic overflow. */
and not exists
(select * from syslogins s2
where s2.suid = s1.suid+1)
/*
** Begin New Code for Adding Login ID to Seconday Server
** Added by Rob Kraft - 2/6/97
*/
Declare @suid int
select @suid = suid from syslogins where name = @loginame
exec XXXX.master..sp_addloginfromPrimary @loginame, @defdb, @passwd, @suid
/*
** End New Code for Adding Login ID to Seconday Server
** Added by Rob Kraft - 2/6/97
*/
print 'New login created.'
return (0)
GO

Step 10. To run the above, you need this proc installed on the Standby server:

/****** Object: Stored Procedure dbo.sp_addloginfromPrimary Script Date: 2/6/97 4:50:01 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.sp_addloginfromPrimary') and sysstat & 0xf = 4)
drop procedure dbo.sp_addloginfromPrimary
GO

--sp_configure 'allow updates',1
--reconfigure with override

create procedure sp_addloginfromPrimary
@loginame varchar(30), /* login name of the new user */
@defdb varchar(30) = 'master', /* default db for the new user */
@passwd varchar(30) = NULL, /* password of the new user */
@suid int /* default language for the new user */
as

declare @returncode int

/*
** If we're in a transaction, disallow this since it might make recovery
** impossible.
*/
if @@trancount > 0
begin
raiserror(15002,-1,-1,'sp_addlogin')
return (1)
end
/*
** Only the system administrator (sa) can run this command.
** Check to make sure the executor is the sa.
*/
if suser_id() <> 1
begin
raiserror(15003,-1,-1)
return (1)
end

/*
** Check to see that the @loginame is valid.
*/
exec @returncode = sp_validname @loginame
if @returncode <> 0
return (1)
/*
** Make sure the login doesn't already exist.
*/
if exists (select * from syslogins where name = @loginame)
begin
raiserror(15025,-1,-1,@loginame)
return (1)
end

/*
** Check that the database name is valid.
*/
if not exists (select * from sysdatabases where name = @defdb)
begin
raiserror(15010,-1,-1,@defdb)
return (1)
end

/*
** Create the login, finding the first available suid in the process.
*/
insert into syslogins(suid, status, accdate, totcpu, totio,
spacelimit, timelimit, resultlimit, dbname, name, password, language)
values(@suid, 8, getdate(), 0, 0,
0, 0, 0, @defdb, @loginame, pwdencrypt(@passwd), null)
print 'New login created.'
return (0)
GO