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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-08-25 : 08:14:51
|
| Dwayne Basden writes "Firstly, this site rocks !I am trying to backup and archive about 100 sql2000 databases so that i can free up some disk space. These are old databases that i no longer need attached. I was wondering if you had a script that could back these databases. I have a list of these database in an Excel spreadsheet." |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-25 : 08:32:25
|
| Hi Dwayne, Welcome to SQL Team!BACKUP DATABASE MyDatabaseName TO DISK = 'x:\MSSQL\BACKUP\MyDatabaseName.BAK' WITH NOINIT, STATS=10I would put that as a formula in a spare column, replacing MyDatabaseName with a refrence to the column/cell containing the database name, then copy the whoel column (or SQL statements) in Query Analyser and run it. You may need to add a GO between each BACKUP statementKristen |
 |
|
|
shanec20
Starting Member
1 Post |
Posted - 2005-08-26 : 06:36:47
|
Here is a quick way to backup all your databases to a single folder if you have the space. You can then just delete the ones you don't need (just replace "C:\BACKUP" with your backup folder path:sp_msforeachdb 'if ''?'' <> ''tempdb'' BACKUP DATABASE  TO DISK = N''C:\BACKUP\?.bak'' WITH INIT , NAME = N''? backup'', NOSKIP , STATS = 10, NOFORMAT' |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-08-26 : 07:38:58
|
| but be aware that sp_msforeachdb is undocumentedMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
backing up a list of databases