Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
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


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, August 9, 2016 11:34 AM
Points: 60, Visits: 424
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, April 14, 2016 4:55 PM
Points: 399, Visits: 2,459
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: Thursday, June 25, 2015 9:36 AM
Points: 1, Visits: 126
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


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 9:29 PM
Points: 19,958, Visits: 18,191
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, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1537560
Posted Wednesday, December 17, 2014 12:49 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, April 14, 2016 4:55 PM
Points: 399, Visits: 2,459
What is the version of your database server?

"-=Still Learning=-"

Lester Policarpio

Post #1644609
Posted Thursday, May 12, 2016 7:34 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:58 AM
Points: 7,968, Visits: 784
Thanks for the script.
Post #1785714
Posted Thursday, May 12, 2016 9:18 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 7:48 AM
Points: 246, Visits: 933
Iwas Bornready (5/12/2016)
Thanks for the script.


Is it necessary to post this on so many old threads?


LinkedIn
Post #1785792
Posted Thursday, May 12, 2016 9:21 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Yesterday @ 2:44 PM
Points: 23,250, Visits: 37,143
tindog (5/12/2016)
Iwas Bornready (5/12/2016)
Thanks for the script.


Is it necessary to post this on so many old threads?


Just padding his post count, like that really means anything.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1785795
Posted Thursday, May 12, 2016 9:24 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, August 9, 2016 11:34 AM
Points: 60, Visits: 424
Well, you're also posting on so many old threads
Post #1785796
Posted Thursday, May 12, 2016 9:29 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 7:48 AM
Points: 246, Visits: 933
hiram.osiris (5/12/2016)
Well, you're also posting on so many old threads




LinkedIn
Post #1785800
« Prev Topic | Next Topic »

Add to briefcase «««45678

Permissions Expand / Collapse