Backup question - 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

Express Edition and Compact Edition (2005)

Backup question

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-08-15 : 09:57:21

I run this every night in a stored proc

BACKUP DATABASE Db1
TO DISK = @path1
WITH INIT

BACKUP DATABASE Db2
TO DISK = @path2
WITH INIT

BACKUP DATABASE Db3
TO DISK = @path3
WITH INIT

BACKUP DATABASE Db4
TO DISK = @path4
WITH INIT

If my server fails, will these backups be all I need to completely restore the databases to a new SQL 2005 Express server?

Someone mentioned I also need to back up the transaction logs? Could someone confirm?

Thanks

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-15 : 10:02:05

if you are taking complete backup you do not need transactional backup.
any changes made after this backup will be lost if the DB fails and you try to restore.

go to www.DBArecovery.com you will see a very good description of everything you need to know about backup and restore

Ashley Rhodes

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-08-15 : 11:04:11

Our data doesn't change often, as our db's are mostly read only. So a backup of the previous day would be fine.

Thanks

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-15 : 11:09:41

just take a complete backup everyday at end of the day if you want.
of you can do it few times a day depending on your need.

if there are not much changes you do not need to have transactional backup. just restoer from complete backup and you will be fine.

Ashley Rhodes

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-08-15 : 12:13:05

Thanks,

Does this command perform a complete backup

BACKUP DATABASE Db1
TO DISK = @path1
WITH INIT

Thanks

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-15 : 13:35:07

Yes it does. Also INIT will overwrite the existing backup if that is what you want.

Ashley Rhodes

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources