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 |
|
mdubey
Posting Yak Master
133 Posts |
 Posted - 2010-11-16 : 17:37:56
|
| Can you guys give me scripts that will generate t-sql to backup all Databases in SQL Server (Any version) and also would like the same to restore from the backup files.I have 90 Databases on a SQL server 2005 Sp3 std. and would like to move those 90 Db's to new SQL 2008R2 std server.I can backup and restore from SSMS but it's time consuing and can't make it on golive date. So I think if we have restore script for all DB's at a time then it's gonna bit comfortable.There are couple of scripts to generate the backup script in the Planet and would appreciate if I can get it the same for restore. I am sorry but I am not expert in scripts.ManojMCP, MCTS |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2010-11-16 : 19:44:45
|
| Are you backing up to a device or a file?Can you write the script to backup one of your databases? If so, can you supply it?Can you get a list of databases out of the sys.databases table?=======================================Man is least himself when he talks in his own person. Give him a mask, and he will tell you the truth. -Oscar Wilde, writer (1854-1900) |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-11-17 : 04:17:05
|
| "I can backup and restore from SSMS but it's time consuing and can't make it on golive date"For time-critical migrations we take a FULL backup and restore in NORECOVERY mode a little in advance on the go-live.Then we take DIFF backups and do the same shortly before go-liveThen we put up holding pages / disconnect server / stop jobs & disallow all external connections etc.Then we take TLog backup and apply to the new database with RECOVERY option to put the databases live.We generally have downtime of just a minute or two with this approach, although if moving to a new version of SQL there is also the time that SQL will take to apply the "updates" to get from SQL2005 to SQL2008R2 version when the final backup is restored.There are also maintenance tasks to be run post-migration to a new version - if time is of the essence you may elect to run some of these after putting the site live - but query performance is likely to be dire! until indexes / stats are rebuilt, and it would be safer to run DBCC Checks before putting the database live rather than then having to back-out later.Details of migration tasks here:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=138230Database Migration steps discussion here:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=44537#145142 |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-11-17 : 04:43:04
|
quote:
Then we take DIFF backups and do the same shortly before go-liveThen we put up holding pages / disconnect server / stop jobs & disallow all external connections etc.Then we take TLog backup and apply to the new database with RECOVERY option to put the databases live.
But I think Diff or Full backup can run alongside a TLog backup.PBUH |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2010-11-17 : 07:26:11
|
| Tara has some scripts on her blog that may do what you wanthttp://weblogs.sqlteam.com/tarad/archive/2004/06/16/1607.aspxhttp://weblogs.sqlteam.com/tarad/archive/2005/11/08/8262.aspxthough judging from the blog dates they may have been updated since then-----------I used to think I wasn't a morning person but it never got better in the afternoon |
|
|
|
|
|
|
Backup/Restore script for all Databases in 2008