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.
| Author |
Topic |
|
Kristen
Test
22859 Posts |
 Posted - 2005-08-09 : 15:24:49
|
| I need to find out when a Stored Procedure changed.Is there a way that I successively restore each day's DIFF backups (or possibly even our hourly TRANS backups), make a quick SELECT, and then restore the next one?I'm tring to avoid having to restore FULL + DIFF for Monday, then FULL + DIFF for Tuesday etc. as that will take AGES!I'm thinking of how a stand-by server (or perhaps I mean a read-only-log-ship server) presumably works, but I've not had to do anything like this before, so I could do with a HeadsUp if its possible.ThanksKristen |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-08-09 : 15:26:51
|
Yes if you use the WITH STANDBY option.quote:
STANDBY = undo_file_nameSpecifies the undo file name so the recovery effects can be undone. The size required for the undo file depends on the volume of undo actions resulting from uncommitted transactions. If neither NORECOVERY, RECOVERY, or STANDBY is specified, RECOVERY is the default.STANDBY allows a database to be brought up for read-only access between transaction log restores and can be used with either warm standby server situations or special recovery situations in which it is useful to inspect the database between log restores.If the specified undo file name does not exist, SQL Server creates it. If the file does exist, SQL Server overwrites it.The same undo file can be used for consecutive restores of the same database. For more information, see Using Standby Servers. Important If free disk space is exhausted on the drive containing the specified undo file name, the restore operation stops.STANDBY is not allowed when a database upgrade is necessary.
Tara |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-08-09 : 16:20:35
|
Your initial restore from the full backup would look like this with the STANDBY undo file:restore database MyDBfrom disk = @BACKUP_FILE_NAMEwith move 'MyDB_Sys' to 'D:\MSSQL\data\MyDB_Sys.mdf' , move 'MyDB_Log' to 'E:\MSSQL\data\MyDB_log.ldf' , replace, dbo_only , standby = 'R:\MSSQL\Backup\MyDB_undo_log.ldf' The restores from logs will use the same undo file.CODO ERGO SUM |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-09 : 21:49:19
|
| Great, thanks Tara and MVJKristen |
|
|
|
|
|
|
|
|
Successive Restores