Sample SQL Server Disaster Recover Plans

A third-party tool is used to replicate the databases from the production server to the standby server, on a regular basis.

After SQL1 failure, SQL2 will be brought on line.

We will change SQL2 ip address to the SQL ip address.

Remember to update all records in “originating_server  column” in msdb..sysjobs

update sysjobs set  originating_server =’SQL2′

a) Hardware Configuration

SQL1

1.1	Server Type	Advanced Server Windows 2000
1.2	System Model	86821RY
1.3	RAM Memory	2.0 G
1.4	No. of CPU’s	1
1.5	Processor Type & Speed	INTEL700 MHZ
Drives	Hard Disk Space	C(3.99G);D(8.94G); E(101 G); F(33.8G);Q(33.8G)

SQL2

1.1	Server Type	Advanced Server Windows 2000
1.2	System Model	86821RY
1.3	RAM Memory	2.0 G
1.4	No. of CPU’s	1
1.5	Processor Type & Speed	INTEL 700 MHZ
Drives	Hard Disk Space	C(3.99G);D(8.94G); E(101G); F(33.8G);Q(33.8G)

b) Server Software Configuration

2.1	Operating System	Enterprise Edition on Windows NT 5.0 (2195)
2.2	RDBMS	Microsoft SQL Server 2000 – 8.00.194 (Intel X86)
2.3	General (SQL Server)	(a) OS Memory – 2560 MB (b) Root Directory – “C:Program FilesMicrosoft SQL Server”
2.4	Memory (SQL Server)	Dynamically configure SQL Server Memory (0 to 2560 MB)
2.5	Processor (SQL Server)	4 Nos.
2.6	Security (SQL Server)	(a) Authentication (SQL Server & windows NT) (b) Audit level (none)
2.7	Connections (SQL Server)	(a) Allow other SQL Servers to connect remotely to this SQL Server using RPC. (b) Maximum concurrent user connections = 0 (unlimited) (c) Query timeout =600 sec
2.8	Server Settings (SQL Server)	(a)   Default language – English (b)   Allow triggers to be fired which fire other triggers (nested triggers)
2.9	Database Settings (SQL Server)	(a) Default Index fill factor to use when rebuilding indexes – Default (optimal) (b) Backup/restore time-out period – Wait indefinitely

Database Details

1. List of databases.

You can find an up-to-date report which contain list of SQL1 databases in:

“path”  on the “ComputerName” server

(I created the report which reach the server nightly for getting status of dbs ,names, and lastbackupname. The server has lot’s of dbs and every week new dbs are added, so report cannot be static)

2. Database Maintenance Plan.

Because databases on SQL1 often are being set OFFLINE (for loading data) and also as the quantity of databases grows, we can use the Data Maintenance Plan wizard just for system databases. Manually written scripts are scheduled to maintain user databases.

Manually Written Scripts

Maintenance Job Name	Maintenance Job Description	Schedule Name	Time to Run
Transaction Log Backup Job for DB Maintenance Plan ‘DB Maintenance UserDB’	Back up transaction log of Full recovery mode user dbs	Daily (except Saturday, Sunday)	22:05 PM
Integrity Checks Job for DB Maintenance Plan ‘DB Maintenance – User DB’	Integrity check for user dbs	Weekly (Sunday)	11:00 PM
DB Backup Job for DB Maintenance Plan ‘DB Maintenance – User DB’	Backup User Databases	Daily (except Saturday, Sunday)	11:30 PM
Optimizations Job for DB Maintenance Plan – User Databases’	Optimize User Databases	Weekly (Sunday)	12:00 am
Shrink Transaction Logs	Dumps and shrinks transaction logs down to 25 megs.	Weekly Schedule (Sunday)	10:00 pm
Data Maintenance Plan (DB Maintenance – System DB)
DB Backup Job for DB Maintenance Plan ‘DB Maintenance – System DB’	Backup System Databases	Daily (except Monday, Sunday)	2:10 am
Optimizations Job for DB Maintenance Plan ‘DB Maintenance System DB’	Optimize System dbs	Weekly (Sunday)	2:07 am
Integrity Checks Job for DB Maintenance Plan ‘DB Maintenance – System DB’	Integrity check for System DBS	Weekly (Sunday)	12:35 am

Scenario 1

We’ve lost the master database data file.

Recovery steps:

    1. Verify that you have master db backup

    2. Copy the data files of the master db from original CD (“… x86 data”)

to the local hard disk before you attempt to run the rebuild. Remember to uncheck Read-only attributes in property form of those files.

     3. Rebuild the master database using rebuildm tool located in

     C:. . .toolsbinn> rebuildm.exe

Figure 1 shows the splash screen for the Rebuild Master tool.

• Provide the location

The sort order for master database is: SQLSortOrder=52

If your sort order is different from the default, change the sort orders for the databases.

• Confirm that we want to rebuild the master and overwrite the system information stored in the system database

Figure 2 Rebuild Master confirmation screen.

Figure 3 Rebuild Master copying files.

Figure 4 Rebuild Master configuration.

Figure 5 Rebuild Master complete.

If after, that for some reason we cannot restore master (backup is not available), please execute the following script in Query Analyzer:

“path”->>Here I scripted all server login 

 This script will create logins for SQL1

4.Restoring the master Database

·  Add the backup device:

sp_addumpdevice ‘disk’, ‘master_backup’, c:tmpsql_backupmaster_backup.dmp’

go

·  Stop SQL Server.       

·  Bring SQL Server up into single-user mode via the command line.

To do this:

–    go to a DOS prompt

–    change directory into the . . .MSSQLBINN directory

–    then execute the following command:

C:Program FilesMicrosoft SQL ServerMSSQLBinn> sqlservr.exe -c –m

This will start SQL Server and the output will be directed to the console. Here’s a sample of what will be displayed on your console screen (it is also in the errorlog):

_______________

2001-04-29 15:44:08.43 spid3

SQL Server started in single user mode.

Updates allowed to system catalogs.

2001-04-29 15:44:08.46 spid3

Starting up database ‘master’.   

______________

·    Log into SQL Server as SQL Server user sa, or as a trusted administrator.

·    Restore the master database just as you would any other database. Note that master’s recovery mode is simple, so you will not restore any transaction logs.

RESTORE DATABASE master FROM master_backup
GO

Here’s the output from that command:

____________

The master database has been successfully restored.

Shutting down SQL Server.

SQL Server is terminating this process.

__________

·    Restart SQL Server. Once master has been restored, SQL Server will automatically shut down. Bring it up just as you normally would.

·    Restore other user databases as needed. If your other database files are okay, then you will not need to restore them. If other databases were damaged, or if you are rebuilding the server, you will need to restore them.

·    Resynchronize SQL Server logins and users as needed. If everything is a clean restore, then there are no orphaned users and logins. If there are orphans refer to Scenario 3 latter in this document.

The model and msdb database need to be restored from a backup after rebuilding the master database.

Use restoredbs.sql(path)

The code for this is below:

/*Procedure to optimize databases which are ‘ONLINE’*/
declare @myDB varchar(50)
declare @myDBmdf varchar(50)
declare @myDBldf varchar(50)
Declare @bakupFile varchar (100)

declare dbnamecur cursor for

select a.name,mdfFile=case a.name
when ‘CE8′ then a.name+’_data.mdf’
when ‘Rasinfo’ then a.name+’_data.mdf’
else a.name+’.mdf’
end
,ldfFile=case a.name
when ‘CE8′ then a.name+’_log.mdf’
when ‘Rasinfo’ then a.name+’_log.mdf’
else a.name+’.ldf’
end,
–bakupFile=dbname+’_db_20030902%’
bakupFile=m.physical_device_name
from master.dbo.sysdatabases a ,

msdb.dbo.backupset o,
msdb.dbo.backupmediafamily m,
(SELECT database_name,
MAX(backup_finish_date) as ‘maxfinishdate’
FROM msdb.dbo.backupset
WHERE Type = ‘D’
GROUP BY database_name) C
where a.name=o.database_name
and o.media_set_id = m.media_set_id AND
o.backup_finish_date = C.maxfinishdate AND
o.type = ‘D’

and a.name not in (‘master’,’tempdb’,’msdb’,’model’)
and a.name =’test’

open dbnamecur
Fetch next from dbnamecur into
@myDB, @myDBmdf, @myDBldf,@bakupFile

While @@fetch_status = 0
Begin

RESTORE DATABASE @myDB
FROM disk=@bakupFile
WITH noRECOVERY

Fetch next from dbnamecur into
@myDB, @myDBmdf, @myDBldf,@bakupFile
END
close dbnamecur
deallocate dbnamecur
GO

Scenario 2

MSDB database has been corrupted.

A data file could not be opened, as seen in the errorlog.

Recovery steps:

1. Verify database backup

2. Stop SQL Server Agent
3. Restore database (no transaction log need to be applied as it is in simple recovery model)

  

Scenario 3

The user database has become corrupted in some way, but the SQL Server installation continues to function.

In other words, unwanted data inserted/deleted from database, but database is still operational.

Recovery steps for full recovery model database:

1. Verify the backups (do not backup the database!!!).

Backup file should be available at

•  “path”
•  or tapes 

   2. Limit use of the database

   3. Backup Current Transaction Log

   4. Restore Database

5. Apply the transactions logs in priority sequence using either:

• Enterprise Manager

   

  • Right click on database
  • Choose “Restore database” option
  • If you know that the database was corrupted at a particular time, tick the check box “Point in time restore,” and you will have opportunity to specify.

• The Options tab
• As you are going to restore all the log backups in this one go, there is no need to alter anything on the Options tab.

• Query Analyzer     

      –restore database:

FROM DISK = ‘E:ColdBackupsDbBackupName’ 

FROM TAPE =’\.Tape0’ (do not include file extension in path)

USE MASTER

GO

RESTORE DATABASE  Test1  FROM  DISK =  ‘C:testtestbackup’

WITH FILE = 1,  NOUNLOAD ,  STATS = 10, 

NORECOVERY,  REPLACE

GO

–restore sequence of transaction log backups done after last backup backup except the last one:

RESTORE LOG test1 FROM  disk=’C:testtestTransactionLog’ WITH NORECOVERY

GO

–restore last transaction log backup

RESTORE LOG test1 FROM  disk=’C:testtest3t’

WITH RECOVERY, STOPAT = ‘Jul 21, 2003 3:10 PM’

GO

Scenario 4

When you restore a database, you are quite often just restoring users to the database and not restoring server logins. Thus, you end up with having a database with users, but a server with different login SIDs for those users.

When you check Enterprise manager ->>Databases->>databasename->>users you see have an empty spot instead of login name.

You have orphaned users.

Recovery steps:

            1. What’s important to remember is that the user and login have to pre-exist

            2. Use the provided stored procedure

Scenario 5

For some reason we cannot restore from master database. So we lost all logins, although we still have users in database.

Recovery steps:

1. Run a script that creates the logins in the server based on the users in the database, and then resynchronizes the SID numbers via system stored procedures and/or scripts.
2. Use this script to create a stored procedure in the database where are users without logins and run this stored procedure:

sp_fixuser

Test it first:

exec fix_logins @random=’RANDOM’, @run=’TEST‘

Run it (it will create lost login):

exec fix_logins @random=’RANDOM’, @run=’RUN’

Code for sp_fixuser:

CREATE PROCEDURE dbo.sp_fixusers
AS
BEGIN
DECLARE @username varchar(25)
DECLARE fixusers CURSOR
FOR
SELECT UserName = name FROM sysusers
WHERE issqluser = 1 and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null
ORDER BY name
OPEN fixusers
FETCH NEXT FROM fixusers
INTO @username
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_change_users_login ‘update_one’, @username, @username
FETCH NEXT FROM fixusers
INTO @username
END
CLOSE fixusers
DEALLOCATE fixusers
END

Code for sp_fix_logins:

create proc fix_logins
@random varchar(20)=”,
@run varchar(20)=’TEST’
as
begin
set nocount on
declare @no_users int
declare @user_name sysname
declare @dbname sysname
declare @def_lang sysname
declare @counter int
declare @name sysname
declare @sid varbinary(85)
declare @pw varchar(20)
if (upper(@run) <> ‘RUN’)
begin
print ”
print ‘**************************’
print ‘* Test Run *’
print ‘**************************’
print ”
end
select @no_users = count(*)
from sysusers
where
sid <> 0x01 and
sid <> 0x00 and
sid is not NULL
select @dbname = db_name(dbid) from master..sysprocesses where spid=@@spid
select @def_lang =a.name
from master..syslanguages a, master..sysconfigures b
where
b.comment = ‘default language’ and
b.value = a.langid
select @counter=1
declare sysusers_cursor cursor for
select name, sid
from sysusers
where
sid <> 0x01 and
sid <> 0x00 and
sid is not NULL
order by name
open sysusers_cursor
fetch next from sysusers_cursor into @name, @sid
while (@@FETCH_STATUS = 0)
begin
select @counter=@counter + 1
if (upper(@random) = ‘RANDOM’)
begin
select @pw=convert(varchar(50),RAND(@counter))
select @pw=substring(@pw,3,5)
end
else
begin
select @pw=’password’
end
if (upper(@run) = ‘RUN’)
begin
exec sp_addlogin @name, @pw, @dbname, @def_lang, @sid
end
select ‘Adding Login: ‘, @name, @pw, @dbname, @def_lang, @sid
fetch next from sysusers_cursor into @name, @sid
end
close sysusers_cursor
deallocate sysusers_cursor
print ‘master..syslogins:’
select name, sid from master..syslogins order by name
select @dbname=@dbname+’..sysusers’
print ”
print @dbname
select name, sid
from sysusers
where
sid <> 0x01 and
sid <> 0x00 and
sid is not NULL
order by name
set nocount off
end

]]>