Backup command and getting progress like Enterprise/Management Studio

Sorry if this is the wrong place for this, but I couldn't find a better place...

I have a .net application that calls a TSQL command to backup the database when the user clicks a button. I would like to give the user incremental feedback on what's going on.

So I hooked into the connection's InfoMessage event.

It gets called at the end of the command, not during. I read somewhere that you're supposed to be able to add "WITH NOWAIT" to a TSQL command to have it return immediately and then use the InfoMessage for updates, but the Backup command doesn't support it.

I also set a trace on the SQL Server and told SQL Server to backup using Enterprise manager hoping to catch what it was doing special to get these commands, and didn't see anything unique.

Does anyone have suggestions? I really need to show the progress of what's going on.

Thanks!

SqlClient unfortunately does not support retrieving of messages asynchronously during command execution. You can do this however if you use System.Data.Odbc instead. The steps are pretty much the same. This can also be done if you use C/C++ and use ODBC/OLEDB/SNAC.

Backup command and getting progress like Enterprise/Management Studio

Sorry if this is the wrong place for this, but I couldn't find a better place...

I have a .net application that calls a TSQL command to backup the database when the user clicks a button. I would like to give the user incremental feedback on what's going on.

So I hooked into the connection's InfoMessage event.

It gets called at the end of the command, not during. I read somewhere that you're supposed to be able to add "WITH NOWAIT" to a TSQL command to have it return immediately and then use the InfoMessage for updates, but the Backup command doesn't support it.

I also set a trace on the SQL Server and told SQL Server to backup using Enterprise manager hoping to catch what it was doing special to get these commands, and didn't see anything unique.

Does anyone have suggestions? I really need to show the progress of what's going on.

Thanks!

SqlClient unfortunately does not support retrieving of messages asynchronously during command execution. You can do this however if you use System.Data.Odbc instead. The steps are pretty much the same. This can also be done if you use C/C++ and use ODBC/OLEDB/SNAC.

Backup and restore to a different DB

Hello,
I am trying to get all the data from one DB and put it in another DB. I
want to do this via TSQL so that I can script it to run daily.
I have tried the following:
****************************************************************************
USE master
EXEC sp_addumpdevice 'disk', 'SMSBackup',
'F:\MSSQL\backup\SMSBackup.dat'
Backup Database SMS_S01 to SMSBackup
Restore filelistonly from SMSBackup
Restore Database Inventory from SMSBackup with Move 'SMSBackup' TO
'F:\MSSQL\Data\Inventory.mdf'
****************************************************************************
I get the following results:
****************************************************************************
(1 row(s) affected)
'Disk' device added.
Processed 24344 pages for database 'SMS_S01', file 'SMS_S01_Data' on
file 4.
Processed 1 pages for database 'SMS_S01', file 'SMS_S01_Log' on file 4.
BACKUP DATABASE successfully processed 24345 pages in 13.192 seconds
(15.117 MB/sec).
(2 row(s) affected)
Server: Msg 3234, Level 16, State 2, Line 7
Logical file 'SMSBackup' is not part of database 'Inventory'. Use
RESTORE FILELISTONLY to list the logical file names.
Server: Msg 3013, Level 16, State 1, Line 7
RESTORE DATABASE is terminating abnormally.
****************************************************************************
What am I doing wrong here? Should I be trying to use a file rather
than disk device?
Cheers-- Backup Database
BACKUP DATABASE DatabaseName TO DISK = 'D:\DatabaseName.bak'
GO
-- Show File info
RESTORE FILELISTONLY FROM DISK = 'D:\DatabaseName.bak'
GO
-- Restore the files for DatabaseName2 new db
RESTORE DATABASE DatabaseName2
FROM DISK = 'D:\DatabaseName.bak' -- backuped file
WITH RECOVERY,
MOVE 'DatabaseName_Data' TO 'D:\SQL Server
Data\MSSQL\Data\DatabaseName2_data.mdf', -- new file
MOVE 'DatabaseName_Log' TO 'D:\SQL Server
Data\MSSQL\Data\DatabaseName2_log.ldf' -- new file
GO|||You need to provide the logical filename that you got from the output of
"RESTORE FILELISTONLY" +>
RESTORE FILELISTONLY
FROM DISK = 'F:\backupfilename.bak'
as input of
RESTORE DATABASE DB_NAME_TO_BE_RESTORED
FROM DISK = 'F:\backupfilename.bak'
WITH MOVE 'Logical_Name_Data' TO 'F:\MSSQL\DATA\Physical_Name_Data.mdf',
MOVE 'Logical_Data_Log' TO 'E:\MSSQL\LOG\Physical_Name_Log.ldf',
STATS = 1, REPLACE
GO
Thanks,
Sree
"dishan@.gmail.com" wrote:
> -- Backup Database
> BACKUP DATABASE DatabaseName TO DISK = 'D:\DatabaseName.bak'
> GO
> -- Show File info
> RESTORE FILELISTONLY FROM DISK = 'D:\DatabaseName.bak'
> GO
> -- Restore the files for DatabaseName2 new db
> RESTORE DATABASE DatabaseName2
> FROM DISK = 'D:\DatabaseName.bak' -- backuped file
> WITH RECOVERY,
> MOVE 'DatabaseName_Data' TO 'D:\SQL Server
> Data\MSSQL\Data\DatabaseName2_data.mdf', -- new file
> MOVE 'DatabaseName_Log' TO 'D:\SQL Server
> Data\MSSQL\Data\DatabaseName2_log.ldf' -- new file
> GO
>|||Sreejith G wrote:
> You need to provide the logical filename that you got from the output of
> "RESTORE FILELISTONLY" +>
> RESTORE FILELISTONLY
> FROM DISK = 'F:\backupfilename.bak'
>
> as input of
>
> RESTORE DATABASE DB_NAME_TO_BE_RESTORED
> FROM DISK = 'F:\backupfilename.bak'
> WITH MOVE 'Logical_Name_Data' TO 'F:\MSSQL\DATA\Physical_Name_Data.mdf',
> MOVE 'Logical_Data_Log' TO 'E:\MSSQL\LOG\Physical_Name_Log.ldf',
> STATS = 1, REPLACE
> GO
>
> Thanks,
> Sree
Thanks. Does that mean that it cannot be done as an automated script,
or is there a way to pass the output of the RESTORE FILELISTONLY to the
RESTORE DATABASE command (of does your command do that, but I just
can't tell)?|||There should be no proble with diskdevice.
Do,
restore database inventory
from smsbackup
with move
<logical name for data file> to <physical path>,
move
<logical name for log file> to <physical path>
Regards
Amish Shah|||> Thanks. Does that mean that it cannot be done as an automated script,
> or is there a way to pass the output of the RESTORE FILELISTONLY to the
> RESTORE DATABASE command
Not sure exactly what you are looking for, but I have some code at
http://www.karaszi.com/SQLServer/util_restore_all_in_file.asp that might be useful, with some
changes.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"cqmman" <cqmman@.yahoo.co.uk> wrote in message
news:1139917015.033030.137950@.g44g2000cwa.googlegroups.com...
> Sreejith G wrote:
>> You need to provide the logical filename that you got from the output of
>> "RESTORE FILELISTONLY" +>
>> RESTORE FILELISTONLY
>> FROM DISK = 'F:\backupfilename.bak'
>>
>> as input of
>>
>> RESTORE DATABASE DB_NAME_TO_BE_RESTORED
>> FROM DISK = 'F:\backupfilename.bak'
>> WITH MOVE 'Logical_Name_Data' TO 'F:\MSSQL\DATA\Physical_Name_Data.mdf',
>> MOVE 'Logical_Data_Log' TO 'E:\MSSQL\LOG\Physical_Name_Log.ldf',
>> STATS = 1, REPLACE
>> GO
>>
>> Thanks,
>> Sree
> Thanks. Does that mean that it cannot be done as an automated script,
> or is there a way to pass the output of the RESTORE FILELISTONLY to the
> RESTORE DATABASE command (of does your command do that, but I just
> can't tell)?
>|||Tibor Karaszi wrote:
> Not sure exactly what you are looking for, but I have some code at
> http://www.karaszi.com/SQLServer/util_restore_all_in_file.asp that might be useful, with some
> changes.
>
Thanks I will take a look.
Bascially, I have a database, and I want to perform queries on it from
remote workstations. I don't want to be querying the original DB, so
want a DB which is effectively a copy. I want to make this copy (or
update it) automatically (daily), and thought that the best way to do
this would be a scheduled TSQL script.. So I am trying to get a TSQL
script that will take a copy of the DB, and put that data into a
different DB.
Cheers

Backup and restore to a different DB

Hello,
I am trying to get all the data from one DB and put it in another DB. I
want to do this via TSQL so that I can script it to run daily.
I have tried the following:
****************************************
************************************
USE master
EXEC sp_addumpdevice 'disk', 'SMSBackup',
'F:\MSSQL\backup\SMSBackup.dat'
Backup Database SMS_S01 to SMSBackup
Restore filelistonly from SMSBackup
Restore Database Inventory from SMSBackup with Move 'SMSBackup' TO
'F:\MSSQL\Data\Inventory.mdf'
****************************************
************************************
I get the following results:
****************************************
************************************
(1 row(s) affected)
'Disk' device added.
Processed 24344 pages for database 'SMS_S01', file 'SMS_S01_Data' on
file 4.
Processed 1 pages for database 'SMS_S01', file 'SMS_S01_Log' on file 4.
BACKUP DATABASE successfully processed 24345 pages in 13.192 seconds
(15.117 MB/sec).
(2 row(s) affected)
Server: Msg 3234, Level 16, State 2, Line 7
Logical file 'SMSBackup' is not part of database 'Inventory'. Use
RESTORE FILELISTONLY to list the logical file names.
Server: Msg 3013, Level 16, State 1, Line 7
RESTORE DATABASE is terminating abnormally.
****************************************
************************************
What am I doing wrong here? Should I be trying to use a file rather
than disk device?
Cheers-- Backup Database
BACKUP DATABASE DatabaseName TO DISK = 'D:\DatabaseName.bak'
GO
-- Show File info
RESTORE FILELISTONLY FROM DISK = 'D:\DatabaseName.bak'
GO
-- Restore the files for DatabaseName2 new db
RESTORE DATABASE DatabaseName2
FROM DISK = 'D:\DatabaseName.bak' -- backuped file
WITH RECOVERY,
MOVE 'DatabaseName_Data' TO 'D:\SQL Server
Data\MSSQL\Data\DatabaseName2_data.mdf', -- new file
MOVE 'DatabaseName_Log' TO 'D:\SQL Server
Data\MSSQL\Data\DatabaseName2_log.ldf' -- new file
GO|||You need to provide the logical filename that you got from the output of
"RESTORE FILELISTONLY" +>
RESTORE FILELISTONLY
FROM DISK = 'F:\backupfilename.bak'
as input of
RESTORE DATABASE DB_NAME_TO_BE_RESTORED
FROM DISK = 'F:\backupfilename.bak'
WITH MOVE 'Logical_Name_Data' TO 'F:\MSSQL\DATA\Physical_Name_Data.mdf',
MOVE 'Logical_Data_Log' TO 'E:\MSSQL\LOG\Physical_Name_Log.ldf',
STATS = 1, REPLACE
GO
Thanks,
Sree
"dishan@.gmail.com" wrote:

> -- Backup Database
> BACKUP DATABASE DatabaseName TO DISK = 'D:\DatabaseName.bak'
> GO
> -- Show File info
> RESTORE FILELISTONLY FROM DISK = 'D:\DatabaseName.bak'
> GO
> -- Restore the files for DatabaseName2 new db
> RESTORE DATABASE DatabaseName2
> FROM DISK = 'D:\DatabaseName.bak' -- backuped file
> WITH RECOVERY,
> MOVE 'DatabaseName_Data' TO 'D:\SQL Server
> Data\MSSQL\Data\DatabaseName2_data.mdf', -- new file
> MOVE 'DatabaseName_Log' TO 'D:\SQL Server
> Data\MSSQL\Data\DatabaseName2_log.ldf' -- new file
> GO
>|||Sreejith G wrote:
> You need to provide the logical filename that you got from the output of
> "RESTORE FILELISTONLY" +>
> RESTORE FILELISTONLY
> FROM DISK = 'F:\backupfilename.bak'
>
> as input of
>
> RESTORE DATABASE DB_NAME_TO_BE_RESTORED
> FROM DISK = 'F:\backupfilename.bak'
> WITH MOVE 'Logical_Name_Data' TO 'F:\MSSQL\DATA\Physical_Name_Data.mdf
',
> MOVE 'Logical_Data_Log' TO 'E:\MSSQL\LOG\Physical_Name_Log.ldf',
> STATS = 1, REPLACE
> GO
>
> Thanks,
> Sree
Thanks. Does that mean that it cannot be done as an automated script,
or is there a way to pass the output of the RESTORE FILELISTONLY to the
RESTORE DATABASE command (of does your command do that, but I just
can't tell)?|||There should be no proble with diskdevice.
Do,
restore database inventory
from smsbackup
with move
<logical name for data file> to <physical path>,
move
<logical name for log file> to <physical path>
Regards
Amish Shah|||> Thanks. Does that mean that it cannot be done as an automated script,
> or is there a way to pass the output of the RESTORE FILELISTONLY to the
> RESTORE DATABASE command
Not sure exactly what you are looking for, but I have some code at
http://www.karaszi.com/SQLServer/ut...all_in_file.asp that might be
useful, with some
changes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"cqmman" <cqmman@.yahoo.co.uk> wrote in message
news:1139917015.033030.137950@.g44g2000cwa.googlegroups.com...
> Sreejith G wrote:
> Thanks. Does that mean that it cannot be done as an automated script,
> or is there a way to pass the output of the RESTORE FILELISTONLY to the
> RESTORE DATABASE command (of does your command do that, but I just
> can't tell)?
>|||Tibor Karaszi wrote:

> Not sure exactly what you are looking for, but I have some code at
> http://www.karaszi.com/SQLServer/ut...all_in_file.asp that might b
e useful, with some
> changes.
>
Thanks I will take a look.
Bascially, I have a database, and I want to perform queries on it from
remote workstations. I don't want to be querying the original DB, so
want a DB which is effectively a copy. I want to make this copy (or
update it) automatically (daily), and thought that the best way to do
this would be a scheduled TSQL script.. So I am trying to get a TSQL
script that will take a copy of the DB, and put that data into a
different DB.
Cheers

Backup and restore to a different DB

Hello,
I am trying to get all the data from one DB and put it in another DB. I
want to do this via TSQL so that I can script it to run daily.
I have tried the following:
************************************************** **************************
USE master
EXEC sp_addumpdevice 'disk', 'SMSBackup',
'F:\MSSQL\backup\SMSBackup.dat'
Backup Database SMS_S01 to SMSBackup
Restore filelistonly from SMSBackup
Restore Database Inventory from SMSBackup with Move 'SMSBackup' TO
'F:\MSSQL\Data\Inventory.mdf'
************************************************** **************************
I get the following results:
************************************************** **************************
(1 row(s) affected)
'Disk' device added.
Processed 24344 pages for database 'SMS_S01', file 'SMS_S01_Data' on
file 4.
Processed 1 pages for database 'SMS_S01', file 'SMS_S01_Log' on file 4.
BACKUP DATABASE successfully processed 24345 pages in 13.192 seconds
(15.117 MB/sec).
(2 row(s) affected)
Server: Msg 3234, Level 16, State 2, Line 7
Logical file 'SMSBackup' is not part of database 'Inventory'. Use
RESTORE FILELISTONLY to list the logical file names.
Server: Msg 3013, Level 16, State 1, Line 7
RESTORE DATABASE is terminating abnormally.
************************************************** **************************
What am I doing wrong here? Should I be trying to use a file rather
than disk device?
Cheers
-- Backup Database
BACKUP DATABASE DatabaseName TO DISK = 'D:\DatabaseName.bak'
GO
-- Show File info
RESTORE FILELISTONLY FROM DISK = 'D:\DatabaseName.bak'
GO
-- Restore the files for DatabaseName2 new db
RESTORE DATABASE DatabaseName2
FROM DISK = 'D:\DatabaseName.bak' -- backuped file
WITH RECOVERY,
MOVE 'DatabaseName_Data' TO 'D:\SQL Server
Data\MSSQL\Data\DatabaseName2_data.mdf', -- new file
MOVE 'DatabaseName_Log' TO 'D:\SQL Server
Data\MSSQL\Data\DatabaseName2_log.ldf' -- new file
GO
|||You need to provide the logical filename that you got from the output of
"RESTORE FILELISTONLY" +>
RESTORE FILELISTONLY
FROM DISK = 'F:\backupfilename.bak'
as input of
RESTORE DATABASE DB_NAME_TO_BE_RESTORED
FROM DISK = 'F:\backupfilename.bak'
WITH MOVE 'Logical_Name_Data' TO 'F:\MSSQL\DATA\Physical_Name_Data.mdf',
MOVE 'Logical_Data_Log' TO 'E:\MSSQL\LOG\Physical_Name_Log.ldf',
STATS = 1, REPLACE
GO
Thanks,
Sree
"dishan@.gmail.com" wrote:

> -- Backup Database
> BACKUP DATABASE DatabaseName TO DISK = 'D:\DatabaseName.bak'
> GO
> -- Show File info
> RESTORE FILELISTONLY FROM DISK = 'D:\DatabaseName.bak'
> GO
> -- Restore the files for DatabaseName2 new db
> RESTORE DATABASE DatabaseName2
> FROM DISK = 'D:\DatabaseName.bak' -- backuped file
> WITH RECOVERY,
> MOVE 'DatabaseName_Data' TO 'D:\SQL Server
> Data\MSSQL\Data\DatabaseName2_data.mdf', -- new file
> MOVE 'DatabaseName_Log' TO 'D:\SQL Server
> Data\MSSQL\Data\DatabaseName2_log.ldf' -- new file
> GO
>
|||Sreejith G wrote:
> You need to provide the logical filename that you got from the output of
> "RESTORE FILELISTONLY" +>
> RESTORE FILELISTONLY
> FROM DISK = 'F:\backupfilename.bak'
>
> as input of
>
> RESTORE DATABASE DB_NAME_TO_BE_RESTORED
> FROM DISK = 'F:\backupfilename.bak'
> WITH MOVE 'Logical_Name_Data' TO 'F:\MSSQL\DATA\Physical_Name_Data.mdf',
> MOVE 'Logical_Data_Log' TO 'E:\MSSQL\LOG\Physical_Name_Log.ldf',
> STATS = 1, REPLACE
> GO
>
> Thanks,
> Sree
Thanks. Does that mean that it cannot be done as an automated script,
or is there a way to pass the output of the RESTORE FILELISTONLY to the
RESTORE DATABASE command (of does your command do that, but I just
can't tell)?
|||There should be no proble with diskdevice.
Do,
restore database inventory
from smsbackup
with move
<logical name for data file> to <physical path>,
move
<logical name for log file> to <physical path>
Regards
Amish Shah
|||> Thanks. Does that mean that it cannot be done as an automated script,
> or is there a way to pass the output of the RESTORE FILELISTONLY to the
> RESTORE DATABASE command
Not sure exactly what you are looking for, but I have some code at
http://www.karaszi.com/SQLServer/uti...ll_in_file.asp that might be useful, with some
changes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"cqmman" <cqmman@.yahoo.co.uk> wrote in message
news:1139917015.033030.137950@.g44g2000cwa.googlegr oups.com...
> Sreejith G wrote:
> Thanks. Does that mean that it cannot be done as an automated script,
> or is there a way to pass the output of the RESTORE FILELISTONLY to the
> RESTORE DATABASE command (of does your command do that, but I just
> can't tell)?
>
|||Tibor Karaszi wrote:

> Not sure exactly what you are looking for, but I have some code at
> http://www.karaszi.com/SQLServer/uti...ll_in_file.asp that might be useful, with some
> changes.
>
Thanks I will take a look.
Bascially, I have a database, and I want to perform queries on it from
remote workstations. I don't want to be querying the original DB, so
want a DB which is effectively a copy. I want to make this copy (or
update it) automatically (daily), and thought that the best way to do
this would be a scheduled TSQL script.. So I am trying to get a TSQL
script that will take a copy of the DB, and put that data into a
different DB.
Cheers

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