| Author |
Topic |
|
eltincho2202
Starting Member
5 Posts |
 Posted - 2011-07-23 : 00:04:42
|
| Hi guys, I need to create an script, such as a batch, that allows me to backup my database in a sql file. The reason is simple... I've a program and I want to add a scheduled task that every hour runs that script file and backs me up the database like "MyDatabase-yyyy_MM_dd-HH_mm_ss.sql". I've been doing it with MySQL, using mysqldump. I've read about some sqlcmd, but I don't know how to use it properly. Thanks! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-07-23 : 05:58:34
|
| That's not a backup, that's an export.What's wrong with BACKUP DATABASE ... TO DISK ...?--Gail ShawSQL Server MVP |
 |
|
|
eltincho2202
Starting Member
5 Posts |
Posted - 2011-07-23 : 08:36:45
|
| I really don't know the difference between backup and export, I just want to create a .sql file that has all the structure of the database, so if something happens with the computer, I can put the same database in another computer without losing any data at all |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-07-23 : 08:43:36
|
| create .sql means you're asking for scripting out of database rather than database backup itself.Also you want structure only means only script objects,constraints,indexes etc rather than data. then why should you do it hourly?anyways you can use generate scripts wizard for that.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
eltincho2202
Starting Member
5 Posts |
Posted - 2011-07-23 : 08:51:16
|
| Basically, the idea is to create a sql file that contains tables structure, including contraints, indexes, keys, etc., and all data and views. The reason of doing it hourly is because it's really sensitive data and one hour of data loss could be very harmful for the client. I've done this using "mysqldump -pPASSWORD -u root myDatabase > mySqlFile.sql" in MySQL. That creates me a sql file that contains the create table statements, the create index statements, the create view statements and the insert statements (if I had functions or procedures they also appeared there in the sql file), so if I wanted to restore that database it was just a simple command that ran that sql file and create a database with all the structure and data in the file. What I want to do is the same, but with SQL Server |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-07-23 : 08:54:46
|
| Rather take database backups. MySQL may script the entire DB as a backup but that's not the way it should be done in SQL Server.Backup database is a single command, it creates a file that is a backup of the entire database. Restore database recreates the DB exactly as it was at time of backup, again one single command. Then read up on transaction log backups, so that you can get point-in-time recovery no matter what the database size is. (believe me, scripting a few hundred GB is not a sensible option)--Gail ShawSQL Server MVP |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-07-23 : 08:58:28
|
| If you want to backup the database (which I suspect is what you are asking for), take a look a this page: http://msdn.microsoft.com/en-us/library/ms187510.aspx which has all the information you may need and more.At least for me, pictures and screen-shots help when I am trying to do something I have never done before, and if you are in the same category, take a look at this page: http://www.linglom.com/2008/01/12/how-to-backup-and-restore-database-on-microsoft-sql-server-2005/One other thing that I want to add is that, after you create a backup, restore it to a development server to make sure that you did everything right, and that you can restore it successfully.And, btw, I agree with Visakh, that unless you have a very strange situation what you need is not .sql scripts (which are for creating just the structure of the database)Edit: Forgot to mention that you can use the graphical interface when you do it the first time and while you are doing it, you can use the script button at the top left to generate the scripts. Then on, you can run the scripts rather than use the graphical interface. |
|
|
|
eltincho2202
Starting Member
5 Posts |
Posted - 2011-07-23 : 14:28:28
|
| I think GilaMonster understood me, what I don't want to do is to go to the SQL Management Studio and create a backup file, I want to do it via console commands, so a .bat file (for example) can be run automatically every time the program closes and make a backup of the database.GilaMonster, I'm not going to deal with big databases in this case, this database may grow till 500 MB maximum, I don't think it could grow more, unless they load a lot of pictures into some tables.So... what's that famous command I should use when I am at Windows Console (cmd) to backup my database to a single file? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-07-24 : 05:33:47
|
| Look up SQLCMD. It's a command SQL query tool, can take script files, can be fully automated. You can write a batch file that calls SQLCMD and runs a database backup.Btw, that's also not generally how backups in SQL are done (maybe it's how MySQL is done, but it's not the SQL Server way). For SQL Server, the usually way is to use SQL Agent (the SQL job scheduler) and schedule a backup to run on a certain schedule. Full backups, differential backups and transaction log backups as necessary.That the DB won't grow big is no reason to not do things properly. p.s. There is no single command in SQL Server that will script the DB and data to a file. That's partially why I'm pushing you towards proper database backups--Gail ShawSQL Server MVP |
|
|
|
eltincho2202
Starting Member
5 Posts |
Posted - 2011-07-24 : 08:24:42
|
| So... if I understand what you are saying, SQL Agent is a scheduler that allows me to schedule database backups hourly, daily, weekly, etc., right? Thanks very much guys!And just to let you know GilaMonster, if you ever work with MySQL, yes, thats the way to backup and restore databases, you just run the mysqldump command using -pPASSWORD (where PASSWORD is the password of the user given), -u USERNAME and after that you chose your database or tables and redirect it to a file MyDataBase > mySqlFile.sql.Thanks! |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-07-24 : 09:36:32
|
| Yup. It comes with all versions of SQL other than Express. Open SQL Management Studio, open the object explorer and connect to your instance, look at the bottom of the tree and you'll see SQL Agent.If you need help with the backup, first have a read through books Online (the SQL documentation) on the topic, then post here if you're still unsure. You might also want to search the net for some articles on basic SQL Server backups (full, diff and log backups)I've had the 'pleasure' of working with MySQL backups before (my blog is wordpress), not fun.--Gail ShawSQL Server MVP |
|
|
|
Bailifei
Starting Member
3 Posts |
Posted - 2011-11-18 : 01:17:16
|
| we've been using todo backup server for a long time. it offers full, incremental, and differential backup.we usually set up a daily backup schedule, and save three versions of the images, which means that the software automatically delete the old images.a specific file can also be restored in Windows explorer from a folder or partition backup image.we are small business users, and cannot afford some backup software costing 1k dollar.so we found todo backup suits us very well.u can also try it. http://www.todo-backup.com/backup-resource/sql-backup-software/schedule-backup-sql-server.htm |
|
|
|
|
Backup MS SQL 2008 database