Automate a restore through a backup

Hi all,
Regards SQL Server 2000.
I have a situation whereby the client takes a nightly backup of his
databases. The backups are placed on a network share. He has a laptop where
he has sql server installed and wants to update the laptop's databases from
the lastest backup of the day before.
Is there a way to automate (within enterprice manager or t-sql) the
restoration of a database through a backup file?
Thanks,
Ivan> Is there a way to automate (within enterprice manager or t-sql) the
> restoration of a database through a backup file?
Sure, you can execute a T-SQL restore script. You may need to specify the
MOVE option if you need to restore to different drives/folders than the
source database:
RESTORE DATABASE MyDatabase
FROM DISK='\\BackupServer\BackupShare\MyDatab
ase.bak'
WITH
MOVE 'MyDatabase' TO 'C:\DataFiles\MyDatabase.mdf',
MOVE 'MyDatabase_Log' TO 'C:\LogFiles\MyDatabase_Log.ldf'
The restore runs in the OS security context of the SQL Server service
account when performed by a sysadmin role member so the service account will
need read permissions to the network backup share. Also, you can include
the restore scripts in a SQL Agent job to facilitate execution, either
scheduled or manual.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
news:eNh3EBedHHA.4188@.TK2MSFTNGP02.phx.gbl...
> Hi all,
> Regards SQL Server 2000.
> I have a situation whereby the client takes a nightly backup of his
> databases. The backups are placed on a network share. He has a laptop
> where he has sql server installed and wants to update the laptop's
> databases from the lastest backup of the day before.
> Is there a way to automate (within enterprice manager or t-sql) the
> restoration of a database through a backup file?
> Thanks,
> Ivan
>|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:CBF1540E-BD97-436B-87DF-9FE24BD4EA6F@.microsoft.com...
> Sure, you can execute a T-SQL restore script. You may need to specify the
> MOVE option if you need to restore to different drives/folders than the
> source database:
> RESTORE DATABASE MyDatabase
> FROM DISK='\\BackupServer\BackupShare\MyDatab
ase.bak'
> WITH
> MOVE 'MyDatabase' TO 'C:\DataFiles\MyDatabase.mdf',
> MOVE 'MyDatabase_Log' TO 'C:\LogFiles\MyDatabase_Log.ldf'
> The restore runs in the OS security context of the SQL Server service
> account when performed by a sysadmin role member so the service account
> will need read permissions to the network backup share. Also, you can
> include the restore scripts in a SQL Agent job to facilitate execution,
> either scheduled or manual.
He may also want to look into straight log-shipping.
There's scripts for 'rolling your own'.
Rather than restore the full backup every night, he can restore just the
logs and put the DB into read-only mode if that'll work for him.
Even if he doesn't go that far, he can get "smarter" possibly and look in
the MSDB for the last backup set and automatically load that one.

> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
> news:eNh3EBedHHA.4188@.TK2MSFTNGP02.phx.gbl...
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> schrieb im Newsbeitrag
news:CBF1540E-BD97-436B-87DF-9FE24BD4EA6F@.microsoft.com...
> Sure, you can execute a T-SQL restore script. You may need to specify the
> MOVE option if you need to restore to different drives/folders than the
> source database:
> RESTORE DATABASE MyDatabase
> FROM DISK='\\BackupServer\BackupShare\MyDatab
ase.bak'
> WITH
> MOVE 'MyDatabase' TO 'C:\DataFiles\MyDatabase.mdf',
> MOVE 'MyDatabase_Log' TO 'C:\LogFiles\MyDatabase_Log.ldf'
> The restore runs in the OS security context of the SQL Server service
> account when performed by a sysadmin role member so the service account
> will need read permissions to the network backup share. Also, you can
> include the restore scripts in a SQL Agent job to facilitate execution,
> either scheduled or manual.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
> news:eNh3EBedHHA.4188@.TK2MSFTNGP02.phx.gbl...
>
That's a great idea. One problem is that the backup job creates the backup
files with a timestamp in the name. Is there a way to exclude this or at
least get the latest backup copy automatically?
Thanks,
Ivan|||On Apr 4, 12:14 pm, "Ivan Debono" <ivanm...@.hotmail.com> wrote:
> "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net> schrieb im Newsbeitrag
news:CBF1540E-BD97-436B-87DF-9FE24BD4EA6F@.microsoft.com...
>
>
>
>
>
>
>
>
>
>
>
>
>
> That's a great idea. One problem is that the backup job creates the backup
> files with a timestamp in the name. Is there a way to exclude this or at
> least get the latest backup copy automatically?
> Thanks,
> Ivan- Hide quoted text -
> - Show quoted text -
You can rename your latest backup file to a constant file name and
restore it from this.
If you don't want to change the name of the backup file use
xp_cmdshell 'dir ' and put into a table and parse to get the name of
the latest backup file|||"M A Srinivas" <masri999@.gmail.com> schrieb im Newsbeitrag
news:1175684363.282267.133330@.e65g2000hsc.googlegroups.com...
> On Apr 4, 12:14 pm, "Ivan Debono" <ivanm...@.hotmail.com> wrote:
> You can rename your latest backup file to a constant file name and
> restore it from this.
> If you don't want to change the name of the backup file use
> xp_cmdshell 'dir ' and put into a table and parse to get the name of
> the latest backup file
>
And how does one places the output into the table?
Ivan|||> And how does one places the output into the table?
You can use INSERT...EXEC. For example:
CREATE TABLE #FileList(output_line varchar(8000))
INSERT INTO #FileList
EXEC master..xp_cmdshell 'DIR C:\Backups'
However, I think parsing the output is a bit of a kludge. You might instead
consider using a VBScript that you can include as an ActiveX script job step
or a stand-alone vbs file. Below is a VBScript example you can tweak for
your needs.
'Restore from latest backup
Option Explicit
Const BackupFolder = "C:\Backups"
Const ConnectionString = "Provider=SQLOLEDB;Data Source=MyServer;Initial
Catalog=master;Integrated Security=SSPI"
Const RestoreScriptTemplate = "RESTORE DATABASE MyDatabase FROM
DISK='$(BackupFilePath)'"
Dim oFSO, BackupFilePath
Set oFSO = CreateObject("Scripting.FileSystemObject")
BackupFilePath = GetLatestBackupFile()
RestoreDatabase ConnectionString, BackupFilePath
Function GetLatestBackupFile()
Dim oFolder, oFile, LatestFilePath, LatestFileDateCreated
Set oFolder = oFSO.GetFolder(BackupFolder)
For Each oFile In oFolder.Files
If oFile.DateCreated > LatestFileDateCreated Then
LatestFileDateCreated = oFile.DateCreated
LatestFilePath = oFile.Path
End If
Next
GetLatestBackupFile = LatestFilePath
End Function
Sub RestoreDatabase(ConnectionString, BackupFilePath)
Dim RestoreScript, connection
RestoreScript = Replace(RestoreScriptTemplate, "$(BackupFilePath)",
BackupFilePath)
Set connection = CreateObject("ADODB.Connection")
connection.Open ConnectionString
connection.Execute RestoreScript
connection.Close
End Sub
Hope this helps.
Dan Guzman
SQL Server MVP
"Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
news:%239dlo%23qdHHA.4872@.TK2MSFTNGP03.phx.gbl...
> "M A Srinivas" <masri999@.gmail.com> schrieb im Newsbeitrag
> news:1175684363.282267.133330@.e65g2000hsc.googlegroups.com...
> And how does one places the output into the table?
> Ivan
>