September 17, 2003 at 7:32 am
Does anyone have a script to grap the latest full db backup file from a network share folder and restore it?
September 17, 2003 at 8:54 am
That script would scare me...are you doing this to restore a database to a second server? If you are, you might think about writing a static backup job that ouputs and overwrites one backup file. Then on your destination machine, write a restore job that always restores that file (scheduled after the backup of course)
HTH
Ray Higdon MCSE, MCDBA, CCNA
------------
Ray Higdon MCSE, MCDBA, CCNA
September 17, 2003 at 9:57 am
Scare me too.
The restore from a network share is the same as another restore, use the UNC path, be sure the SQLAgent has rights to read from the share.
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://qa.sqlservercentral.com/bestof/
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 17, 2003 at 11:44 am
To grab the latest backup file? Assuming you know what the backup file filename is. You'd either need to know the filename or have some scheme to get the filename. Hrm. If you're looking for an off the shelf script for this, it'll need to be one that records the filenames as it does backups.
I guess there are a bunch of reasons why you need to do something like this. Here, it sounds like you have a co-lo'd web server & database that you'd like to update via restoring backups. In which case, you probably aren't looking for an all emcompassing backup solution deal. If you don't find what you're looking for and venture out to write this on your own here's how I would do it.
Write a script that calls xp_cmdshell and does an "if exists" for a backup file. If it doesn't find a file, return fail. If it finds a file return succeed. Backup Database. Kill all connections to Db being restored. Make Single User. Restore database.
-Ken
September 17, 2003 at 1:10 pm
Yikes, I'm hoping what you really want is to know what "FILE" you want to restore inside your backup file. For that you would want to use something like
RESTORE HEADERONLY FROM DISK = '\\your path here'
then once you know the position you can set your FILE parameter.
RESTORE DATABASE YourDb
FROM DISK = '\\ your path here'
WITH FILE = 1 -- position found above.
Can I ask why you need this? 🙂
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
September 17, 2003 at 1:45 pm
You will probably find all you need to make this script in de backup tables in msdb - check backupset and backupfile tables.
September 18, 2003 at 12:48 am
I used the following code within a VB app that I have developed to set up an automated LogShipping utility which even the simplest NT admin bloke can set up without having any SQL Server SQL exp.
The utility is composed of three parts - the setup, the shipper and the applicator. In my applicator executable which is called via SQL Agent job ( defined via the Setup executable ), I have the executable use the following code to find out the latest backup and develop a restore script on the fly.
The part of the code I think you are interested in is as follows :-
select physical_device_name
from msdb.dbo.backupmediafamily
where media_set_id =
( select media_set_id
from msdb.dbo.backupset
where backup_set_id =
( select max(backup_set_id) from msdb.dbo.backupset where type = 'backtype'
and database_name = 'dbname'
)
)
where
backtype - replace with D or L for Database backup or Log backup
dbname - replace name of database
obviously modify to suit own needs but I think it will provide what you're generally after.
September 18, 2003 at 11:27 am
Maintenance plan creates backup with different name every time. Backup name has dbname then "_db_" then datetime portion. Knowing that we can use xp_cmdshell dir command to get all available backups then select max name which is the name of the latest database backup.
/* ------------------------------------------------------------------------- */
/* Restore latest database from the network backup. */
/* --.AlexM. 1/13/2002 MSSQL7 ---------------------------------------------- */
IF (object_id('tempdb..#t') IS NOT NULL) DROP TABLE #t
USE master
GO
DECLARE @fname varchar(255), @sql varchar(512)
, @cmd varchar(512), @path varchar(512),@db varchar(30)
--EXEC master..xp_cmdshell'dir \\server\share' -- Test folder access
SET @path = '\\server\share' --<<--<< Change
SET @db = 'pubs' --<<--<< Change
SET @cmd='@echo off & for /f "tokens=2-4 delims= " %a in (''dir '+@path
+' ^|findstr /i "'+@db+'_db_"'') do (echo %c)'
PRINT @cmd
CREATE TABLE #t(fname varchar(255))
INSERT INTO #t EXEC master..xp_cmdshell @cmd
SELECT @fname = max(fname) FROM #t
-- Make sure database has no active users
-- SET @sql='EXEC master..sp_killdbusers '+@db
-- EXEC (@sql)
SET @sql= 'restore database ['+@db+'] from disk='''
+@path+'\'+@fname+''' with replace, STATS = 1'
PRINT @sql
-- EXEC (@sql)
EXEC master..xp_cmdshell 'net send alexm database restored'
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply