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 |
|
rasher83
Starting Member
24 Posts |
 Posted - 2008-04-18 : 12:15:53
|
| I have installed the sql server management studio express for my express edition of studio 2005 for backup purposes.I need to automate backing up my database.I created a backup script and have successfully got sched tasks to backup each night. the problem is it is not overwriting the previous data. My first backup was 1 gig, the next night the file was 2 gig, the next it was 3 and so on. This is not due to that much data being added to the db.When I go to restore, I have the option to restore any of the 3 backups. I would like my backup to be a single image of the database at the last backup time. How can I do this? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-04-18 : 12:34:56
|
| Add WITH INIT to the command.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
rasher83
Starting Member
24 Posts |
Posted - 2008-04-18 : 12:43:49
|
| I added that to the end of my command and it didnt work, i got the follwoing error when i tried to execute it:C:\Documents and Settings\denisf>"c:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE" -S \RCARCHIVE -i "\\Rcfile\Reliable\Home\Users\denisf\newjimmy.sql"Msg 156, Level 15, State 1, Server DENIS_XPSP2\RCARCHIVE, Line 1Incorrect syntax near the keyword 'WITH'.Msg 319, Level 15, State 1, Server DENIS_XPSP2\RCARCHIVE, Line 1Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.my backup script is as follows: BACKUP DATABASE [jimmy] TO DISK = N'c:\RC-Archvie2.0 Database\MSSQL.1\MSSQL\Backup\jimmy.bak' WITH RETAINDAYS = 1, NOFORMAT, NOINIT, NAME = N'jimmy-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10, WITH INIT GOam i using the WITH INIT correctly? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-04-18 : 12:51:00
|
| You already have other WITH options so you can't add another WITH.But all you need to do is change NOINIT to INIT in your command. NOINIT means to append the files, INIT means to overwrite if one exists with the same name already.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
rasher83
Starting Member
24 Posts |
Posted - 2008-04-18 : 12:55:43
|
| thanks for that, just realised the error, should have examined my script before replying. thnaks again, that worked a treat. |
|
|
|
|
|
|
|
|
Backup file size problem