re: restore backup's - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

re: restore backup's

Author

Topic

sql9.babu
Starting Member

21 Posts

Posted - 2008-02-03 : 21:27:33

hi,
I have A & B servers, I took the Full backup by using a (by exec storedprocedure in job1)job1 on A,
now I am trying to restore the recent backup on server B which is taken on server A by another job2,

* I have a problem in calling the recent backup taken on server A, how to get the recent backup.

* How to restore the recent Full backup (taken on A) to B by using jobs (Iam trying to do by using storedprocedures), what is the procedure to call the job on remote server

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-02-03 : 21:54:39

Run following on server b and sql service account of server b needs permission on backup file:

restore database db_name from disk = '\\servera\path\file_name' with ...

sql9.babu
Starting Member

21 Posts

Posted - 2008-02-03 : 23:18:18

but I mentioned the last recent backup or the most recent backup,

[ ex: lets say my job will scheduled backup for every 2hr I want to restore the most recent backup at the time of restore on server B(any time in a day) ]

that to by calling a job how to get the recent backup be called and to restore it on server B

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-02-03 : 23:38:04

How do you name backup file?

sql9.babu
Starting Member

21 Posts

Posted - 2008-02-04 : 00:56:22

only Iam mentioning the "drive:\" all other things are created based on the backup type,DBname,currentdate,currenttime at the time of backup

it looks something like

drive:\currentDBname_bkptype_currentdate_currenttime.bak or trn

unable to restore backup i.e, the most recent backup?

HOW TO RESTORE MOST RECENT BACKUP

what ever is the backup name I have to restore the most recent backup

sql9.babu
Starting Member

21 Posts

Posted - 2008-02-04 : 21:50:05

I got the partial answer,how to pic the recent backup, now i will try the rest

thank u rmiao,

dewacorp.alliances

452 Posts

Posted - 2008-02-05 : 05:13:57

quote:

I got the partial answer,how to pic the recent backup, now i will try the rest

You may need to join msdb.dbo.backupset and msdb.dbo.backupmediafamily from you there you will get StartDate (recently backup).

We have something like in on our view in Administration database:

SELECT BKS.[name] BackupSetName,
CASE BKS.type
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Diff'
WHEN 'L' THEN 'Log'
ELSE 'File/Group' END TypeOf,
BMF.physical_device_name Location,
BKS.server_name Server,
BKS.database_name DBName,
BKS.backup_start_date StartDate,
BKS.backup_finish_date EndDate,
CASE
WHEN BKS.backup_size > 0 THEN CAST(BKS.backup_size / 1024 AS NUMERIC(20, 0))
ELSE 0
END SizeMb,
DATEDIFF(ss, BKS.backup_start_date, BKS.backup_finish_date) TimeTakenSecs,
CAST(BKS.software_major_version AS VARCHAR(3)) + '.' + CAST(BKS.software_minor_version AS VARCHAR(3)) + '.' + CAST(BKS.software_build_version AS VARCHAR(5)) DBVersion,
CASE compatibility_level
WHEN 60 THEN 'SQL Server version 6.0'
WHEN 65 THEN 'SQL Server 6.5'
WHEN 70 THEN 'SQL Server 7.0'
WHEN 80 THEN 'SQL Server 8.0'
WHEN 90 THEN 'SQL Server 9.0'
ELSE 'Unknown'
END CompatibilityLevel,
BKS.collation_name Collation,
BKS.first_lsn StartLSN,
BKS.last_lsn EndLSN,
BKS.checkpoint_lsn CheckpointLSN
FROM msdb.dbo.backupset BKS
INNER JOIN msdb.dbo.backupmediafamily BMF
ON BKS.media_set_id = BMF.media_set_id

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources