Automated Databes Restore from Backup 1

[Ztrek7]

I have basically 2 databases, live (for the office) and a puble (for the public). Every night our maintenance plans creates a complete backup of the live database, which is then backuped up. Then in the morning, I come in and do a database restore from the live backup to the public database. My questions is, there has to be some kind of way to automate this restore, what is it?

Suggestions???

 

[SQLBill]

Why not set up replication? Refer to the BOL for more information on that subject.

-SQLBill

BOL=Books OnLine=Microsoft SQL Server's HELP
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine

Posting advice: FAQ481-4875

 

[Corran007]

I Would proably look into replication. but if you do still want to do it with an auntomation of the current way...

First you will need to kill all the users in the database so taht you can do the restore. Then you will need to initiate the restore command. Put all of this in a sql job to run at the appropriate time.

step1 is kill, step 2 is restore.

 

[Ztrek7]

Thats what I need help with, the aql kill command and restore. I can do it by pointing and clicking, but the automation. Is it a maintenance plan?

I tried the replication subscription subscriber, and it appears the my tables are compatible and some manual intervention would be required. I would prefer the second route.

Please Help, Thanks.

 

[Corran007]

Ok, hopefully these will come out ok. This first one is a kill all process. you need to put it in a dabase other than the one you are going to kill it in. i just put it in master.


CREATE PROCEDURE spKillAllProcesses
@pDbName varchar (100)=NULL, /*database where we will kill processes.
If NULL-we will attempt to kill processes in all DBs*/
@pUserName varchar (100)=NULL /*user in a GIVEN database or in all databases where such a user name exists,
whose processes we are going to kill. If NULL-kill all processes. */

/*Purpose: Kills all processes in a given database and/or belonging to a specified user.
If no parameters supplied it will attempt to kill all user processes on the server.
Server: all
Database: DBAservice
Created: Igor Raytsin,Yul Wasserman 2000-10-13
Last modified: Yul Wasserman 2002-02-08
*/
AS
SET NOCOUNT ON

DECLARE @p_id smallint
DECLARE @dbid smallint
DECLARE @dbname varchar(100)
DECLARE @exec_str varchar (255)
DECLARE @error_str varchar (255)

IF NOT EXISTS (SELECT * FROM master.dbo.sysdatabases where name=ltrim(rtrim(@pDbName)) or @pDbName is NULL)
BEGIN
Set @error_str='No database '+ltrim(rtrim(@pDbName)) +' found.'
Raiserror(@error_str, 16,1)
RETURN-1
END
Create Table ##DbUsers(dbid smallint,uid smallint)

If @pUserName is not null
BEGIN
--Search for a user in all databases or a given one
DECLARE curDbUsers CURSOR FOR
SELECT dbid,name FROM master.dbo.sysdatabases where name=ltrim(rtrim(@pDbName)) or @pDbName is NULL
OPEN curDbUsers
FETCH NEXT FROM curDbUsers INTO @dbid,@dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @exec_str='Set quoted_identifier off
INSERT ##DbUsers SELECT '+cast(@dbid as char)+', uid FROM '+@dbname+'.dbo.sysusers
WHERE name="'+ltrim(rtrim(@pUserName))+'"'
EXEC (@exec_str)
FETCH NEXT FROM curDbUsers INTO @dbid,@dbname
END
CLOSE curDbUsers
DEALLOCATE curDbUsers
If not exists(Select * from ##DbUsers)
BEGIN
Set @error_str='No user '+ltrim(rtrim(@pUserName)) +' found.'
DROP TABLE ##DbUsers
Raiserror(@error_str, 16,1)
RETURN-1
END
END

ELSE --IF @pUserName is null
BEGIN
INSERT ##DbUsers SELECT ISNULL(db_id(ltrim(rtrim(@pDbName))),-911),-911
END

--select * from ##dbUsers

DECLARE curAllProc CURSOR FOR
SELECT spid,sp.dbid FROM master.dbo.sysprocesses sp
INNER JOIN ##DbUsers t ON (sp.dbid = t.dbid or t.dbid=-911) and (sp.uid=t.uid or t.uid=-911)
OPEN curAllProc
FETCH NEXT FROM curAllProc INTO @p_id, @dbid

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @exec_str = 'KILL '+ Convert(varchar,@p_id)+ ' checkpoint'
SELECT @error_str = 'Attempting to kill process '+Convert(varchar,@p_id)+' in database '+db_name(@dbid)
RAISERROR (@error_str,10,1)with log
EXEC (@exec_str)
FETCH NEXT FROM curAllProc INTO @p_id, @dbid
END
CLOSE curAllProc
DEALLOCATE curAllProc
DROP TABLE ##DbUsers
SET NOCOUNT OFF

GO


RESTORE DATABASE DatabaseName
From Disk = '\\Servername\D\Microsoft SQL Server\MSSQL\BACKUP\DatabaseBackup.bak'
with move 'DatabaseName_Data' to 'D:\Microsoft SQL Server\MSSQL\Data\DatabaseName_Data.mdf',
move 'DatabaseName_Log' to 'D:\Microsoft SQL Server\MSSQL\Data\DatabaseName_Log.ldf',
Replace

You will need to modify the restore to work for your database with the correct names and locations, but that should give you a start.

stick the proc call in the first step. then run the restore in the second step.

Let us know if you need anymore help.

 

[Ztrek7]

that seems a little complicated to just kill users and do a restore. I appreciate your help, i think i will just do it manually until i read up on sql.

Thanks, i will give ya a star for the time, but forum readers, I have not tried the code.

 

[Corran007]

Its not really that hard.

the first part is in a stored proc. so once you make it, all you do is either run it blank, or run it while providing the database to kill.

the first step of the job would be

exec spKillAllProcesses 'Databasetokillusersin'

The second step would be the restore. Which is this part
To make it less confusing. my database is named fred.

RESTORE DATABASE fred
From Disk = '\\Servername\D\Microsoft SQL Server\MSSQL\BACKUP\fredBackup.bak'
with move 'DatabaseName_Data' to 'D:\Microsoft SQL Server\MSSQL\Data\fred_Data.mdf',
move 'DatabaseName_Log' to 'D:\Microsoft SQL Server\MSSQL\Data\fred_Log.ldf',
Replace

That shoudl work. i havent tried it, but should be fine. the kill users i know worsk, i use it before i run my maintenance.

 

[Ztrek7]

I have decided to try it. I have created the stored procedure under the public database. Now, on the restore, does the command support the *.bak instead of the file name. The names are generated with the date as the file name. Also, can't I just use the first restore command without all the moves? Also, How do I "call" these procedures at a certain time? As a side note, is there a command I can put in the restore to email an address if fail?

Thanks,

 

[Ztrek7]

the stored procedure I created was the kill users. Sorry, left that out, have not created the restore yet.

 

[Corran007]

Yes. you can for it. it can be just
RESTORE DATABASE fred
From Disk = '\\Servername\D\Microsoft SQL Server\MSSQL\BACKUP\fredBackup.bak' for the restore.

What you will need to do is set up a job in sql server to call it at a certain time. The kill is the first step. the restore is the second step. One of the parts for a job is notification. you can set it up so that it will email on failure.

 

[Ztrek7]

How long does the kill command stay into effect? Also, you say I CAN use a wildcard?

Such as:

From Disk = '\\Servername\D\Microsoft SQL Server\MSSQL\BACKUP\*.bak

 

[Corran007]

The kill command is just at that instant. any connections are terminated into the database. After that you can start up new ones normally. But to restore a database, you have to clear out all the connections for it.

as for the database, i dont believe so. i believe you have to say a specific filename. most likely it would be looking for a backup file named *.bak.