schedule a backup - SQL Server Forums

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

SQL Server 2005 Forums

SQL Server Administration (2005)

schedule a backup

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-11-21 : 06:15:24

what is the best way to schedule a bakcup in sql
i'd like it to be a complete backup not an append.

Is there anyway to schedule it to each day backup to a directory for that day?

nr
SQLTeam MVY

12543 Posts

Posted - 2007-11-21 : 06:25:22

Schedule a backup cpmmand using the agent.
backup to a file with name

<dbname>_full_yyyymmdd_hhmmss.bak

==========================================
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.

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-11-21 : 06:36:05

sorry how to i do this
do i put this as a step in a schedule?
what's the correct syntax - i want it to be a full backup not to append

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-21 : 07:25:09

declare @sql varchar(1000)
select @sql = 'BACKUP DATABASE '+(select db_name())+' TO DISK = ''D:\'+(select db_name())+
convert(varchar,GETDATE(),112)+'.bak'''
Exec(@sql)

Madhivanan

Failing to plan is Planning to fail

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-11-21 : 07:38:40

thanks
problem is when i restore to a new db i get an error

Msg 3234, Level 16, State 2, Line 1
Logical file 'testa' is not part of database 'testb'. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

To get around this i know when i use the ssms I choose -Overwrite and not append - how do I do this in the code?

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-11-21 : 11:32:21

Overwrite is the REPLACE keyword in your restore.
From BOL:

REPLACE
Note:
Supported only by RESTORE.

Specifies that SQL Server should create the specified database and its related files even if another database already exists with the same name. In such a case, the existing database is deleted. When the REPLACE option is not specified, a safety check occurs, which prevents overwriting a different database by accident. The safety check ensures that the RESTORE DATABASE statement does not restore the database to the current server if the following conditions both exist:

The database named in the RESTORE statement already exists on the current server, and

The database name is different from the database name recorded in the backup set.

REPLACE also allows RESTORE to overwrite an existing file that cannot be verified as belonging to the database being restored. Normally, RESTORE refuses to overwrite pre-existing files. WITH REPLACE can also be used in the same way for the RESTORE LOG option.

REPLACE also overrides the requirement that you back up the tail of the log before restoring the database.

Future guru in the making.

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-11-21 : 12:07:33

soryr i'm not follwoing - are you say i'm restoring the db wrong? (I want to be able to restore to a new db or the db it was backed up from)

or what do i have to add in the backup database code?

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-11-21 : 13:49:25

quote:
Originally posted by esthera

soryr i'm not follwoing - are you say i'm restoring the db wrong? (I want to be able to restore to a new db or the db it was backed up from)

or what do i have to add in the backup database code?

No, you said you wanted to add overwrite to your restore script, I posted what you need to add, you can look in books online for more although I already posted the excerpt from it.

Basically you need to add "with replace" in your restore command in order for your restore to overwrite like it does in SSMS when you choose overwrite.

Future guru in the making.

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-11-21 : 14:04:28

no i want hte opposite
i want to add overwrite to the backup script -- so that it saves as one file that I can restore

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-11-21 : 14:19:42

If you want the backup to overwrite you have to add WITH INIT to your backup statement.

Future guru in the making.

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-11-21 : 14:48:31

even when backing up with the with init
when restoring to a new db I get

Msg 3234, Level 16, State 2, Line 1
Logical file 'testa' is not part of database 'testb'. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

what am I doing wrong?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-21 : 15:20:16

Why are you confusing backups and restores? The WITH INIT has nothing to do with restores and the WITH REPLACE has nothing to do with backups.

Your error means you aren't performing the restore correctly, but it absolutely has nothing to do with the WITH INIT option for the BACKUP command.

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

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-11-22 : 01:04:24

ok thanks for your help - i'm still confused

if i do a backup in the original way -- then what's the proper way to restore it to a new db (not the same one)

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-11-22 : 05:12:42

ok so i'm doing

declare @sql varchar(1000)
select @sql = 'BACKUP DATABASE '+'dba '+' TO DISK = ''D:\sqlbackups\'+ ' dba '+
convert(varchar,GETDATE(),112)+'.bak'' WITH INIT '
Exec(@sql)

to backup

what code would I use to restore this to a new db called testrestore

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-22 : 12:04:11

The backup command has very little to do with how you would restore it.

But here is an example

RESTORE DATABASE testrestore
FROM DISK = 'D:\sqlbackups\dba\BackupFileName.bak'
WITH MOVE 'LogicalDataFile' TO 'D:\Data\testrestore.MDF',
MOVE 'LogicalLogFile' TO 'D:\Log\testrestore.LDF',
REPLACE

So now you've just got to figure out where to put the MDF and LDF files plus what the logical data and log files are so that you can replace those values in my code.

You can use RESTORE FILELISTONLY to figure out the logical names.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources