467,118 Members | 929 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,118 developers. It's quick & easy.

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 Morrison
Jul 20 '05 #1
  • viewed: 8766
Share:
3 Replies
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" <sa***@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

Jul 20 '05 #2
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" <da*******@nospam-earthlink.net> wrote in message
news:7O****************@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" <sa***@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


Jul 20 '05 #3
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" <da*******@nospam-earthlink.net> wrote in message
news:7O****************@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" <sa***@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



Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by emmexx | last post: by
3 posts views Thread by JIMMIE WHITAKER | last post: by
1 post views Thread by john_20_28_2000@yahoo.com | last post: by
1 post views Thread by gcetti@nauticom.net | last post: by
6 posts views Thread by Eric Herber | last post: by
4 posts views Thread by Hardy | last post: by
1 post views Thread by Paul Aspinall | last post: by
5 posts views Thread by Zenek | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.