Backup Log using names - 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 2000 Forums

Transact-SQL (2000)

Backup Log using names

Author

Topic

SQL_freak
Starting Member

2 Posts

Posted - 2005-09-10 : 07:07:58

Hi all

I'm pretty new using SQL so I need some help with the backup log syntax. I need to backup a log using different names, for example TLbackup1, TLbackup2, TLbackup3 and on.. Here's an example of what i'm trying to achieve:

--creates a new database
CREATE DATABASE MyPracticeDB

--creates one table in MyPracticeDB database
USE MyPracticeDB
CREATE TABLE pet (
PetID CHAR(2),
Pet_Name VARCHAR(10))

--add two rows to the pet table
USE MyPracticeDB
INSERT INTO pet VALUES('01', 'Dog')
INSERT INTO pet VALUES('02', 'Cat')

--backup MyPracticeDB to device DBBackupStartOfDay1 in full mode
BACKUP DATABASE MyPracticeDB TO DBBackupStartOfDay1 WITH INIT

--deletes one row from pet table
DELETE FROM pet WHERE PetID = '02'

--returns the deleted records to its original status
RESTORE DATABASE MyPracticeDB FROM DBBackupStartOfDay1

--add two more records to table pet
INSERT INTO pet VALUES ('03', 'Bird')
INSERT INTO pet VALUES ('04', 'Rabbit')

--saves the log for the previous rows added
BACKUP LOG MyPracticeDB TO DBBackupStartOfDay1

so i want to append the log using a specified name and not the default which i think in this case is 'MyPracticeDB_Log', but im not sure where to put it on the last piece of code.

thanks in advance

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-09-12 : 01:13:16

you're using a backup device, in this case if you append the backup, you'll see multiple files with the same name but are handled by sql server, and you can only open this device through sql

but if you need individual files use the to disk='name' clause instead
this way, you can change the 'name' to whatever suits you, if you want it autogenerated, append the date/time to differentiate which backup was taken on which date/time

HTH

--------------------
keeping it simple...

SQL_freak
Starting Member

2 Posts

Posted - 2005-09-13 : 05:52:29

yep, it works

thanks HTH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources