Dump all databases into single file

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Dump all databases into single file

Author

Topic

zayl543
Starting Member

7 Posts

Posted - 2012-02-14 : 18:26:30

Hi

I am newbie to this univers of MSSQL, I want to dump all databases into a single file just for backup, what is the right way to do that especially by command line would be better since I am very familiar with, also I want the command to instantly starts the dump process instead of do it for a couple of times.

Thank you

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-02-14 : 18:28:21

Do you mean BACKUP DATABASE command?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-02-14 : 18:47:52

Let me expand on that. You can do this with BACKUP DATABASE by not initializing the file. So put all database backups into one file by specifying WITH NOINIT.

I don't know what you mean by instantly start the dump process. I just use a scheduled backup job.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

zayl543
Starting Member

7 Posts

Posted - 2012-02-14 : 19:04:12

quote:
Originally posted by tkizer
Do you mean BACKUP DATABASE command?

Yes, I was familiar with mysql process witch is mysqldump :lol

I want to backup it without scheduled process, to backup a single database, it is :

sqlcmd -E -S databaseNameHere -i C:\SQLDatabases\BackupDatabaseScript.sql

but to backup all, I have no idea.
Thank you

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-02-14 : 19:20:59

Just put all of the commands in the BackupDatabaseScript.sql file. There's no issue with adding multiple commands in there.

-S should be the server name, not the database name.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

zayl543
Starting Member

7 Posts

Posted - 2012-02-14 : 20:08:40

Okay, I did download an example of BackupDatabaseScript.sql from [url]http://www.kodyaz.com/articles/how-to-backup-sql-database-command-line-sqlcmd-sql-batch-file.aspx[/url]. And as you have the right -S is the server name so because I want to backup everything I will type osql -L command to list all MSSQL servers, and then make backup for each one.

Just an other tiny question remain, if I backup those MSSQL databases into *.sql files. Can I import them for later use or diagnose in phpMyAdmin ? I asked that because I know that phpMyAdmin is using mySQL but for MSSQL no idea right now.

Thank you

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-02-14 : 20:11:53

You don't back them up into sql files. You back them up into SQL Server backup files, which typically use bak as the extension of the full backups.

If you are asking how to backup databases on multiple servers using one script, then you will need to add multiple calls to sqlcmd and then wrap that entire thing into a cmd/bat file.

No you can't import them into phpMyAdmin. SQL Server backup files are only good in SQL Server.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

zayl543
Starting Member

7 Posts

Posted - 2012-02-14 : 20:26:01

Okay I understand, so where those bak files are made after a successful backup ? in the current directory I am or a default MSSQL directory ?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-02-14 : 20:31:26

Wherever you tell it to go.

Here's an example:

backup database myDb
to disk = 'F:\Backup\myDb\myDb.bak'
with init

Or if you are going to put them into a single file, then you'd use "with noinit". I hate having them in a single file though as it makes recovery more challenging.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

zayl543
Starting Member

7 Posts

Posted - 2012-02-14 : 20:40:15

Thank you so mush for your quick responses. As there is no luck with phpMyAdmin. Do you know a Windows or linux application that give me the ability to browse the *.bak database file after importing it into that application ?

There is a wizard I think in Windows Server but I am plaining to use a windows XP or 7 for that.

Thank you again

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-02-14 : 20:57:52

what do you mean by "browse the *.bak database file" ?
access to the tables, records of a database ?

"importing it into that application"
You database backup file is basically a backup file, you can't import it into application. To access the backup file, you will have to restore it into a SQL Server

I took a quick look at phpMyAdmin site, seems like it is for MySQL only. Doesn't seems to be supporting MS SQL.

KH
[spoiler]Time is always against us[/spoiler]

zayl543
Starting Member

7 Posts

Posted - 2012-02-14 : 21:08:06

Okay, after browsing the BackupDatabaseScript.sql I have seen the following :

Backup Database MySQLSamples To Disk = 'C:\SQLDatabases\MySQLSamples.bak'

OKay here two main questions are poped out :
As I am admin of the server,

1 - which command give me the list of all databases on the server ?
2 - Did I need to provide a database password for the backup process to start ? because I haven't seen entering a password.

Thank you

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-02-14 : 21:14:43

1. SELECT * FROM sys.databases

2. It is optional. If you have a password for backup, you will need supply it during restore

KH
[spoiler]Time is always against us[/spoiler]

zayl543
Starting Member

7 Posts

Posted - 2012-02-14 : 21:33:02

quote:
Originally posted by khtan

1. SELECT * FROM sys.databases

2. It is optional. If you have a password for backup, you will need supply it during restore

KH
[spoiler]Time is always against us[/spoiler]

Thanks, so the file became :


Backup Database SELECT * FROM sys.databases To Disk = 'C:\db1.bak'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-02-14 : 21:37:30

No. you have to issue the Backup database command for each database

Use the cursor or while loop to do that for each database. You might also want to exclude some of the database that you don't need to backup like tempdb and any others

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-02-14 : 21:46:25

You may also check out Tara's blog at weblogs.sqlteam.com/tarad

She has a backup stored procedure that do that

KH
[spoiler]Time is always against us[/spoiler]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-02-14 : 22:47:29

Here you go: http://weblogs.sqlteam.com/tarad/archive/2009/12/29/Backup-SQL-Server-2005-and-2008-DatabasesAgain.aspx

Once you look at the code, you'll understand why it's not as easy as just putting sys.databases into the backup command.