May 16, 2007 at 6:00 pm
Hi guys, I have a question, how can I restore a database using a Job in a specific schedule, I'm working with SQL Server 2005...
Thanks...
May 16, 2007 at 7:59 pm
Create a sql agent job with a restore script like the one below; if you need to restore over an existing db then firstly would be to set the db (UserDBCopy below) in single user mode, then add "WITH REPLACE" to the restore line in script, then bring your restored db in multi-user mode.
Declare
@vDataFileName
Varchar(255),
@vFileName
Varchar(100),
@vDataPath
Varchar(100),
@vLogPath
Varchar(100),
@vRestoreString
Varchar(1000)
Set
@vDBName='UserDB'
Set
@vFileName='YourBackupPath\BackupFileName.bak'
Set
@vDataFileName='UserDB_Data'
Set
@vDataPath='YourDataPath\UserDBCopy_Data'
Set
@vLogFileName='UserDB_Log'
Set
@vLogPath='YourLogPath\UserDBCopy_Log'
Set
@vRestoreString = 'RESTORE DATABASE [UserDBCopy] FROM
DISK '''
+ @vFileName
+ ''' WITH FILE = 1, MOVE N''' +
@vDataFileName
+ '''TO N'''+ @vDataPath + ', MOVE N'''+ @vLogFileName + '''TO N'''
+ @vLogPath +', NOUNLOAD,
STATS = 10'
Exec
(@vRestoreString)
GO
Good luck.
May 17, 2007 at 3:08 pm
You don't need to set user databases in single user mode. To have a succesfull restore you do need to make sure none is connected to the database though.
May 18, 2007 at 10:26 am
..so you can use some kind of kill users stored procedures in the previous step...
May 19, 2007 at 4:06 pm
you can do the same using alter database command...
ALTER DATABASE <DBNAME> set single_user rollback immediate...
check BOL for correct syntax...
MohammedU
Microsoft SQL Server MVP
May 20, 2007 at 8:20 pm
I like to do it this way, because it prevents anyone from connecting to the database. Even a single user connection will cause the restore to fail.
use master alter database My_Database_Name set offline with rollback immediate
May 23, 2007 at 12:46 pm
Hello,
http://blog.sqlauthority.com/2007/02/25/sql-server-restore-database-backup-using-sql-script-t-sql/
Pinal Dave
sqlauthority.com
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply