Back up of selected dbs

Question

Post reply

Back up of selected dbs

VRT

SSCertifiable

Points: 5776
More actions
March 23, 2009 at 5:06 am

#202552

Hi,

I have sql server 2000. I asked admins to load sql server 2005.

Now I need to take backups how can I do the backups for selected dbs and I need to restore them into 2005.

Does Creation of dbs automatically happen while doing restoration or we need to create the dbs again in new server?

Is there any scripts that will take backups for selected dbs and restore them to new server at once?

creation of backup for each db is taking so much time and frustating also?

Please let me know is there any way?

Thank You

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Adiga One Orange Chip Points: 27284 More actions · Answer 1

Adiga

One Orange Chip

Points: 27284

March 23, 2009 at 5:23 am

#964322

Maintenance plans help you take backup of selected databases and automate it. Details are here. Look for "Set Up Backup Schedules (Maintenance Plan Wizard) "

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

Atul DBA Hall of Fame Points: 3610 More actions · Answer 2

There are many ways to take the backup of user databases. Use can create a script and job to take the backup. Just change the db name in the script for taking the backup of different dbs. You can also use the Maintenance Plan for taking backup of multiple dbs in one go.

Read the article for detailed information on db Backup.

http://www.sql-server-performance.com/articles/dba/Backup_User_Databases_Using_a_Maintenance_Plan_p1.aspx

http://qa.sqlservercentral.com/articles/Backup/64454/

VRT SSCertifiable Points: 5776 More actions · Answer 3

Thanks Pradeep and Atul.

I am checking this options. It's very interesting to study these materials. I am lot of points.

I didn't get answer for my second question.

To restore the dbs, do I need to create the dbs again in new server or will it automatically create the dbs when I am running the restoration?

My databases are in server 2000 which is remote and I am connecting from my system where I have server 2005. But to use Maintenance plan we need the access as db_admin fixed server role. But I don't have that role. That's why I am not able to see options for Maintenance plan. For restoration also If any scripts available that would be more helpful.

You may get annoyed for my question but I have around 15 user dbs which needs to upgraded to SQL Server 2005. So it is very difficult to take each one and restoring. That's why I am asking for scripts to execute at one shot

Thank You

Atul DBA Hall of Fame Points: 3610 More actions · Answer 4

While restoration you need to provide the DB name. Without providing the DB name, restoration can't be done.

In case you don't know which backup file belongs to which database, you can extract the information from the Backup file contents or HEADERONLY option of restoration script.

RESTORE HEADERONLY

FROM DISK = N'C:\AdventureWorks-FullBackup.bak'

WITH NOUNLOAD;

GO

For more information, please check the URL :

http://technet.microsoft.com/en-us/library/ms178536.aspx

http://msdn.microsoft.com/en-us/library/aa238455(SQL.80).aspx