sql server 2008 automatic database restore

beberapa waktu lalu saya diminta untuk setup sql server 2008. skenarionya adalah sebagai berikut : server ini akan digunakan untuk menyimpan backup database dari server utama secara harian.  hak akses ke server backup ini hanya untuk dibaca saja alias read only. jadi setiap sore administrator database utama akan melakukan backup di server utama dan mengcopy file backup tersebut ke server backup via ftp. selanjutnya di server backup akan dilakukan restore secara otomatis sehingga database siap untuk dibaca. berikut ini adalah dokumentasi bagaimana skenario tersebut dapat berjalan dengan lancar.

setelah melakukan instalasi sistem operasi windows server 2008 standard sp2, dilanjutkan dengan instalasi sql server 2008 standard. kemudian instalasi filezila server untuk ftp  server. lalu buat user sekaligus juga dengan direktori dan hak asesnya di filezila.

masuk ke sql server management studio dan buat database baru sesuai dengan nama database di server utama. untuk menambah user klik security lalu klik kanan logins pilih new login. di bagian general ketik nama user di login name misalnya userbaru lalu pilih sql server authentication dan berikan password. di bagian user mapping centang nama database yang baru dibuat lalu untuk database role membership hilangkan centang di db_owner dan berikan centang di db_datareader. klik ok untuk mengeksekusi pembuatan userbaru.

selanjutnya adalah melakukan restore database dari server utama. setelah backup database di server utama ditransfer via ftp ke server backup, masuk ke sql server management studio lalu klik database yang baru kemudian klik new query dan gunakan script berikut ini untuk melakukan restore.  script ini saya dapatkan dari internet dapat didownload disini dan ada beberapa penyesuaian yang harus dilakukan antara lain :

1. dua perubahan di bagian — BEGIN – MODIFY THIS CODE yaitu NAMA-DATABASE

2. lima perubahan di bagian — MODIFY THIS LINE

/*
Automate restore w/o needing to know the logical file names.
Specify destination database name, database backup source filename
and .MDF, .LDF and .NDF directories.
I do nightly automated database restores,
and I’ve been using this code for about a month.
Works for sql server 2008, might work for 2005.
Created by wtm 5/27/2010
*/

— BEGIN – MODIFY THIS CODE – create a blank db
if not exists(select * from master.sys.databases where [name]=’NAMA-DATABASE’)
begin
create database NAMA-DATABASE
end
go
— END – MODIFY THIS CODE – create a blank db

declare @strDatabase varchar(130)=’NAMA-DATABASE’ — MODIFY THIS LINE – db name
declare @strBackupFile varchar(500)=’c:docsdb-backupssc.bak’ — MODIFY THIS LINE – source db backup file
declare @strRestoreMDFFilesTo varchar(500)=’c:docssqldata’ — MODIFY THIS LINE – destination restore directory for main files
declare @strRestoreLDFFilesTo varchar(500)=’c:docssqldata’ — MODIFY THIS LINE – destination restore directory for tlog files
declare @strRestoreNDFFilesTo varchar(500)=’c:docssqldata’ — MODIFY THIS LINE – destination restore directory for non-main files

— other variables used
declare @strSQL nvarchar(max)
declare @strOriginalPhysicalName varchar(150)
declare @strPhysicalName varchar(150)
declare @strLogicalName varchar(150)
declare @intReturn int

— begin restoring
begin try
drop table #tmpFilelist
end try
begin catch
end catch
create table #tmpFilelist (
LogicalName varchar(64), PhysicalName varchar(130), [Type] varchar(1), FileGroupName varchar(64), Size decimal(20, 0)
,MaxSize decimal(25, 0), FileID bigint, CreateLSN decimal(25,0), DropLSN decimal(25,0), UniqueID uniqueidentifier
,ReadOnlyLSN decimal(25,0), ReadWriteLSN decimal(25,0), BackSizeInBytes decimal(25,0), SourceBlockSize int
,filegroupid int, loggroupguid uniqueidentifier, differentialbaseLSN decimal(25,0), differentialbaseGUID uniqueidentifier
,isreadonly bit, ispresent bit, TDEThumbpr decimal
)
if not exists(select * from sc.sys.tables) or exists(select * from sc.sys.tables where [name]=’not-an-original-table’) — MODIFY THIS LINE – business logic to see if we need to restore the database at all
begin
print ‘Restoring ‘+@strDatabase+’ db …’
use master
exec msdb.dbo.sp_delete_database_backuphistory @database_name = @strDatabase
use [master]
exec(‘alter database ‘+@strDatabase+’ set single_user with rollback immediate’)
use [master]
exec(‘drop database ‘+@strDatabase)
insert into #tmpFilelist
exec(‘restore filelistonly from disk = ”’+@strBackupFile+””)
set @strSQL=’restore database [‘+@strDatabase+’] from disk=”’+@strBackupFile+”’ with ‘
set @strSQL=@strSQL+ ‘file=1 ‘
set @strSQL=@strSQL+ ‘,nounload ‘
set @strSQL=@strSQL+ ‘,replace ‘
set @strSQL=@strSQL+ ‘,stats=10 ‘ — show restore status every 10%
while exists(select * from #tmpFilelist)
begin
select top 1 @strOriginalPhysicalName=PhysicalName, @strLogicalName=LogicalName from #tmpFilelist
set @strPhysicalName=@strOriginalPhysicalName
set @strPhysicalName=reverse(@strPhysicalName)
set @strPhysicalName=left(@strPhysicalName, charindex(”, @strPhysicalName)-1)
set @strPhysicalName=reverse(@strPhysicalName)
set @strPhysicalName=replace(@strPhysicalName, ‘.’, ‘_’+@strDatabase+’.’)
if @strPhysicalName like ‘%.mdf’
set @strPhysicalName=@strRestoreMDFFilesTo+@strPhysicalName
else if @strPhysicalName like ‘%.ldf’
set @strPhysicalName=@strRestoreLDFFilesTo+@strPhysicalName
else
set @strPhysicalName=@strRestoreNDFFilesTo+@strPhysicalName
set @strSQL=@strSQL+ ‘,move ”’+@strLogicalName+”’ to ”’+@strPhysicalName+”’ ‘
delete from #tmpFilelist where PhysicalName=@strOriginalPhysicalName
end
execute @intReturn=sp_executesql @strSQL
end

lalu eksekusi dengan klik pada !Execute

setelah restore selesai keluar dari sql server management studio. lalu masuk lagi dengan user yang baru. maka akan muncul pesan error Cannot open user default database. Login failed. Login failed for user dst……

ini disebabkan karena userbaru belum terdaftar di database. untuk itu harus dilakukan mapping ulang namun sebelumnya dilihat dulu  nama user yang saat ini terdaftar. login ke sql server management studio dengan user administrator. di bagian database klik kanan pada nama database backup lalu pilih propertis. klik permissions dan lihat di bagian users or roles. misalnya namanya userlama. klik cancel untuk keluar dari properties.

klik new query lalu berikan script berikut ini dengan beberapa penyesuaian:

use NAMA-DATABASE
go
alter user userlama with login = userbaru
exec sp_droprolemember ‘db_owner’,’userlama’
exec sp_addrolemember ‘db_datareader’,’userlama’

klik !Execute dan setelah selesai eksekusi, keluar dari sql management studio lalu coba masuk lagi dengan userbaru. jika tidak ada lagi pesan error yang muncul maka userbaru sudah dapat mengakses database backup dengan hak akses read only. selanjutnya adalah melakukan seting agar proses restore ini berjalan secara otomatis. untukproses tranfer via ftp tetap dilakukan secara manual.

fasilitas yang digunakan untuk penjadwalan adalah sql server agent yang terdapat pada sql server standard atau enterprise. untuk sql server express tidak ada. sql server agent dapat diakses melalui sql server management studio dengan login sebagai administrator. klik kanan jobs lalu klik new job. pada bagian general di kotak name beri nama misalnya restore-db.

pada bagian steps klik new, beri nama step name misalnya run-restore-script, type T-SQL, pilih database, copy paste script restore diatas pada command lalu klik ok. klik new untuk membuat step kedua, beri nama alter-permission, type T-SQL, pilih database, copy paste script permission diatas pada command lalu klik ok.

pada bagian schedules klik new beri nama restore-db, schedule-type recuring, occurs daily, occurs once at pilih jam yg dikehendaki, start date pilih tanggal hari ini lalu klik ok beberapa kali hingga keluar dari job properties.