Backup / Restore Question - MSDE

MSDE2000

I have an application in which I am running a TSQL command of BACKUP DATABASE and RESTORE DATABASE for the backup and restore commands for my application. For testing purposes, i did the following:

1) Ran a BACKUP DATABASE command to a file named C:\TEST.BAK.
2) Deleted the database completely.
3) Ran a RESTORE DATABASE on the same file (note, I did NOT recreate the database)

Now I have the database back with all my data. What are the gotchas when doing a backup and restore using this method? I am not relying on transaction logs to restore to a certain point, the user can only restore back to their last backup (may be daily, weekly or monthly)

TIA

--
Tim MorrisonThis method will work find for the SIMPLE recovery model. It doesn't matter
whether or not the target database exists since it will be recreated during
the restore if needed.

Be sure to backup WITH INIT or the backup will append to the existing backup
file and the file will grow indefinitely. Also, consider copying the backup
file elsewhere for disaster recovery.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Tim Morrison" <sales@.kjmsoftware.com> wrote in message
news:YE3Db.545312$Tr4.1480932@.attbi_s03...
MSDE2000

I have an application in which I am running a TSQL command of BACKUP
DATABASE and RESTORE DATABASE for the backup and restore commands for my
application. For testing purposes, i did the following:

1) Ran a BACKUP DATABASE command to a file named C:\TEST.BAK.
2) Deleted the database completely.
3) Ran a RESTORE DATABASE on the same file (note, I did NOT recreate the
database)

Now I have the database back with all my data. What are the gotchas when
doing a backup and restore using this method? I am not relying on
transaction logs to restore to a certain point, the user can only restore
back to their last backup (may be daily, weekly or monthly)

TIA

--
Tim Morrison|||Yes, I have the database set to simple recovery in my initial SQL script the
user uses to create the database.

I also have WITH INIT in my backup command.

I also discovered that the restore database name does not have to be the
same as the initial database. This is both a benefit and a risk.

I even 100% uninstalled MSDE, and reinstalled (Including SP3a), then
performed my restore command, and everything seems to work perfectly.

Im guessing that if I wanted to send a sample database with my application
that includes sample data, it would be very easy to do using this method.

Im learning more and more every day. I have a SAMS SQL Server 2000 book
which is always helpfull

Tim Morrison

"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:7O5Db.1465$Bg5.648@.newsread2.news.atl.earthli nk.net...
> This method will work find for the SIMPLE recovery model. It doesn't
matter
> whether or not the target database exists since it will be recreated
during
> the restore if needed.
> Be sure to backup WITH INIT or the backup will append to the existing
backup
> file and the file will grow indefinitely. Also, consider copying the
backup
> file elsewhere for disaster recovery.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
>
> "Tim Morrison" <sales@.kjmsoftware.com> wrote in message
> news:YE3Db.545312$Tr4.1480932@.attbi_s03...
> MSDE2000
> I have an application in which I am running a TSQL command of BACKUP
> DATABASE and RESTORE DATABASE for the backup and restore commands for my
> application. For testing purposes, i did the following:
> 1) Ran a BACKUP DATABASE command to a file named C:\TEST.BAK.
> 2) Deleted the database completely.
> 3) Ran a RESTORE DATABASE on the same file (note, I did NOT recreate the
> database)
> Now I have the database back with all my data. What are the gotchas when
> doing a backup and restore using this method? I am not relying on
> transaction logs to restore to a certain point, the user can only restore
> back to their last backup (may be daily, weekly or monthly)
> TIA
>
> --
> Tim Morrison|||Yes, I have the database set to simple recovery in my initial SQL script the
user uses to create the database.

I also have WITH INIT in my backup command.

I also discovered that the restore database name does not have to be the
same as the initial database. This is both a benefit and a risk.

I even 100% uninstalled MSDE, and reinstalled (Including SP3a), then
performed my restore command, and everything seems to work perfectly.

Im guessing that if I wanted to send a sample database with my application
that includes sample data, it would be very easy to do using this method.

Im learning more and more every day. I have a SAMS SQL Server 2000 book
which is always helpfull

Tim Morrison

"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:7O5Db.1465$Bg5.648@.newsread2.news.atl.earthli nk.net...
> This method will work find for the SIMPLE recovery model. It doesn't
matter
> whether or not the target database exists since it will be recreated
during
> the restore if needed.
> Be sure to backup WITH INIT or the backup will append to the existing
backup
> file and the file will grow indefinitely. Also, consider copying the
backup
> file elsewhere for disaster recovery.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
>
> "Tim Morrison" <sales@.kjmsoftware.com> wrote in message
> news:YE3Db.545312$Tr4.1480932@.attbi_s03...
> MSDE2000
> I have an application in which I am running a TSQL command of BACKUP
> DATABASE and RESTORE DATABASE for the backup and restore commands for my
> application. For testing purposes, i did the following:
> 1) Ran a BACKUP DATABASE command to a file named C:\TEST.BAK.
> 2) Deleted the database completely.
> 3) Ran a RESTORE DATABASE on the same file (note, I did NOT recreate the
> database)
> Now I have the database back with all my data. What are the gotchas when
> doing a backup and restore using this method? I am not relying on
> transaction logs to restore to a certain point, the user can only restore
> back to their last backup (may be daily, weekly or monthly)
> TIA
>
> --
> Tim Morrison