backup on sql 7.0 database 1

[maswien]

For some reason we need to work on SQL Server 7.0 database. All I know about the backup command is SQL server 2000, I wonder if there are any difference between this two version in term of backup command. I noticed that SQL Server 7.0 doesn't have a full recovery or bulk-logged recovery mode in database option, how can I configure the SQL Server 7.0 so I can full backup the database per day and backup the log per hour? Thanks.

 

[Catadmin]

You can use the Database Maintence Planner to do your regular backups and your log backups. It is pretty much the same design as the one in SQL 2000. Given your situation, I would just use this (and it will give you a full backup and a full transaction log backup) instead of trying to script one.

But if you want to script...

As far as the T-SQL commands go, it's mostly the same between the two versions. There are a few exeptions, but those are on the optional commands. Books Online can give you a better idea of what commands are supported. Even if you only have a copy of BOL for SQL2k, check out the Backup Database and Backup Log commands. It will tell you what commands are back-supported. Though, I think the major differences are between 7 & 6.x rather than 2k & 7...

Hope that helps.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[mrdenny]

You shouldn't see any major differences in the backup and restore commands between SQL 7 and SQL 2000. In SQL 7 the options are the same for full and simple recovery, they are just named differently.

In a SQL 7 server you don't get the drop down menu. You get check boxes. If you check the truncate log on checkpoint check box, you are basically placing the database in simple recovery mode. If you check the select into/bulk recovery you are placing it into the bulked logged. With them both checked I beleive that simple recovery overrides bulk logged.

To do transaction log backups uncheck both boxes.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[maswien]

Thanks denny,

One question: if I want use transaction backup, that means I can't use "select into " statement anymore, is that true?

 

[maswien]

Or when I want to do select into, I need change the database option by checking that option?

 

[mrdenny]

Incorrect. You can always use the select into statement. If you want to do transaction log backups simply uncheck both boxes.

No SQL Code will stop working if you change the database recovery options.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[Catadmin]

The only difference recovery mode makes to your query is that (depending on which one of the boxes is checked), the Transaction Log will either record only transactions affected by the non-bulk type of SQL statements (bulk recovery) or record hardly anything (simple), keeping its size pretty low.

Full recovery records every last transaction that takes place on the database.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???