Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««45678

Automatically Restores All database ".BAK" Files Expand / Collapse
Author
Message
Posted Wednesday, January 4, 2012 8:17 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 10:22 AM
Points: 47, Visits: 284
Hi, the script is working fine, it doesn't display any errors, but I'd like to know if there are some parts of the current code that could be improved. I'd also like to add something to evaluate which are the most recent backup files, and take those for restoring, since I have backup files from several weeks in a single folder. I currently added a code to delete the backups from cmdshell table that weren't made the current day:

SET @DATE =                     (CONVERT(VARCHAR,DATEPART(DAY,GETDATE()))) + '/' +(CONVERT(VARCHAR,DATEPART(MONTH,GETDATE()))) --Gets the current day and month
-- I added this part to eliminate the backups that were not made the current day
DELETE FROM cmdshell
WHERE FEntry NOT LIKE @DATE + '%'

but there are sometimes on which the most recent backups are from 1, 2, or more days ago, and I'd like to dynamically get them from cmdshell table, do you have any suggestions??

Thank you very much for your time
Post #1229979
Posted Wednesday, January 4, 2012 6:49 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, March 6, 2014 2:38 AM
Points: 398, Visits: 2,423
So what you want is to restore the latest backup? regardless if the backup is 1 or 2 days ago as long as it is the current one? are you restoring...

only 1 backup?
1 diff and 1 full?
1 full?

1. you can retain (in the cmdshell table) the max date from your script
2. you can look per filename (which is your format) or per system date (the first 20 chars of the result in cmdshell table)


"-=Still Learning=-"

Lester Policarpio

Post #1230454
Posted Monday, February 3, 2014 4:26 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 8:19 PM
Points: 1, Visits: 91
hi :
i have one my db bkp files in format t1_db_201402031813.bak
but when i run..i am getting the following error..

any help would be greatly appreciated...
i need to restore close 200 dbs..




Msg 213, Level 16, State 7, Line 1
Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.
Post #1537556
Posted Monday, February 3, 2014 4:34 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: 2 days ago @ 1:51 PM
Points: 21,644, Visits: 15,317
Here is another version that should work pretty well.

http://jasonbrimhall.info/2014/01/14/t-sql-tuesday-050-automating-database-restores/




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1537560
« Prev Topic | Next Topic »

Add to briefcase «««45678

Permissions Expand / Collapse