| Author |
Topic |
|
bienew
Starting Member
8 Posts |
 Posted - 2007-07-31 : 09:26:04
|
Hi  I'm migrating from access to sql server 2005.With access, I did full backups every 6 hours, by simply copying the .mdb file to a folder on another hard disk on the server, and then ftp it to a distant server.I guess i just can't simply do the same with the SQL server .mdf file.What is the best practice to achieve the same goal ? Thank you |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-07-31 : 09:27:39
|
| Have a look at backup database in bol==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
bienew
Starting Member
8 Posts |
Posted - 2007-07-31 : 09:34:49
|
| What is 'bol' ? |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-07-31 : 09:35:53
|
| You can use the Database Maintenance Plan Wizard to create scheduled automatic database backups in just a few minutes.You can read about the Database Maintenance Plan Wizard and Backup/Restore in SQL Server Books Online (BOL).CODO ERGO SUM |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-07-31 : 09:40:43
|
| bol is books on-linejust runbackup database mydatabase to disk = 'c:\mydatabase.bak'and it will create the backup.You can schedule this by crating a job.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
bienew
Starting Member
8 Posts |
Posted - 2007-07-31 : 11:24:08
|
| I got it. Everything is working as expected.Thanks a lot ! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-07-31 : 12:13:04
|
but sadly there may be a bit more to it!If your Recovery model is FULL (rather than SIMPLE) then you also need to backup your transaction log - otherwise your .LDF file will grow-and-grow ... until your disk is full  You should prove that you can restore your database before you get too far into the project - otherwise the first time you need your backup files might be the first time you discover that, for whatever reason, they don't work.Some pointers here:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=How%20Backup%20works,What%20type%20of%20backup%20should%20I%20use,Automating%20Backups,BackupEdit: Oh, and backup database mydatabase to disk = 'c:\mydatabase.bak'will append each backup, so that file will get bigger-and-bigger too ... (various ways around that, but my suggestion would be to use a c:\mydatabase_YYMMDD_HHMMSS.bak style naming convention)Kristen |
|
|
|
bienew
Starting Member
8 Posts |
Posted - 2007-07-31 : 13:53:31
|
Thanks for the info.My db is in simple recovery mode.Backups are full.The command line was generated by the wizard:BACKUP DATABASE [mainSQL] TO DISK = N'D:\Backup\mainSQL.bak' WITH NOFORMAT, INIT, NAME = N'mainSQL_backup_20070731194133', SKIP, REWIND, NOUNLOAD, STATS = 10 |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-07-31 : 13:58:42
|
| Note that "mainSQL.bak" will be overwritten each time, so if you have not already backed it up to, say, Tape then you won't have any earlier backups to revert to.Other than that looks OK (there are some stupid parameters that the Wizard has added (e.g. SKIP, REWIND, NOUNLOAD), but they are benignKristen |
|
|
|
|
How to backup live a full database into a file