Database Unit Testing: backup

Showing posts with label backup. Show all posts

Thursday, March 29, 2012

Database Status = Loading

A coworker is repeatedly experiencing problems restoring database backups on
a new sql virtual server (clustered). He has restored a database backup
using enterprise manager. Enterprise manager told him that the restore has
completed without giving any errors. We can not access the database and
enterprise manager shows the status of the database as "Loading". I ran the
command RESTORE DATABASE <databasename> WITH RECOVERY and received an error
basically saying that the database was not completely loaded or the restore
did not complete. Can anyone tell me what the problem is or tell me how to
avoid this scenario?
--
-SJHi
Have you checked the SQL Server log and the System Event log to see if there
is any additional information in them?
It is usually a good idea to post the exact error numbers and messages.
John
"SJ" wrote:
> A coworker is repeatedly experiencing problems restoring database backups on
> a new sql virtual server (clustered). He has restored a database backup
> using enterprise manager. Enterprise manager told him that the restore has
> completed without giving any errors. We can not access the database and
> enterprise manager shows the status of the database as "Loading". I ran the
> command RESTORE DATABASE <databasename> WITH RECOVERY and received an error
> basically saying that the database was not completely loaded or the restore
> did not complete. Can anyone tell me what the problem is or tell me how to
> avoid this scenario?
> --
> -SJ|||Here is the error:
File '<logical file name>' was only partially restored by a database or file
restore. The entire file must be successfully restored before applying the
log.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Enterprise manager told me "Restore of database '<databasename>' completed
successfully."
-SJ
"John Bell" wrote:
> Hi
> Have you checked the SQL Server log and the System Event log to see if there
> is any additional information in them?
> It is usually a good idea to post the exact error numbers and messages.
> John
> "SJ" wrote:
> > A coworker is repeatedly experiencing problems restoring database backups on
> > a new sql virtual server (clustered). He has restored a database backup
> > using enterprise manager. Enterprise manager told him that the restore has
> > completed without giving any errors. We can not access the database and
> > enterprise manager shows the status of the database as "Loading". I ran the
> > command RESTORE DATABASE <databasename> WITH RECOVERY and received an error
> > basically saying that the database was not completely loaded or the restore
> > did not complete. Can anyone tell me what the problem is or tell me how to
> > avoid this scenario?
> >
> > --
> > -SJ|||Hi SJ
Check out:
http://groups.google.com/group/microsoft.public.sqlserver.clients/browse_frm/thread/b5447588fe32acf0/527357de5278e8bd#527357de5278e8bd
John
"SJ" wrote:
> Here is the error:
> File '<logical file name>' was only partially restored by a database or file
> restore. The entire file must be successfully restored before applying the
> log.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> Enterprise manager told me "Restore of database '<databasename>' completed
> successfully."
> -SJ
> "John Bell" wrote:
> > Hi
> >
> > Have you checked the SQL Server log and the System Event log to see if there
> > is any additional information in them?
> >
> > It is usually a good idea to post the exact error numbers and messages.
> >
> > John
> >
> > "SJ" wrote:
> >
> > > A coworker is repeatedly experiencing problems restoring database backups on
> > > a new sql virtual server (clustered). He has restored a database backup
> > > using enterprise manager. Enterprise manager told him that the restore has
> > > completed without giving any errors. We can not access the database and
> > > enterprise manager shows the status of the database as "Loading". I ran the
> > > command RESTORE DATABASE <databasename> WITH RECOVERY and received an error
> > > basically saying that the database was not completely loaded or the restore
> > > did not complete. Can anyone tell me what the problem is or tell me how to
> > > avoid this scenario?
> > >
> > > --
> > > -SJ

Database Space Available Is Zero Even AFter Running Backup Log With No Truncate

I refreshed the SQL server name after rnning a backup log with no
truncate ....and still found out that space available size is still 0!
I also shrunk the Trans Log and still the same rusults.
Would very much appreciate your help
Thank you
*** Sent via Developersdex http://www.codecomments.com ***Hi,
What is the Recovery model of your database?
If it is FULL OR BULK_LOGGED then
If you need the transaction log backup execute below to clear the
Transaction log:-
backup log <dbname> to disk='d:\backup\dbname.trn
Incase if you do not require the transaction log backup go for;
backup log <dbname> with truncate_only
The above commands will clear the log , bit to reduce the physical LDF file
size you will have to execute the below command:-
DBCC SHRINKFILE (db1_log1_logical_name,truncat_eonly)
After doing these execute the below command to get the log size
dbcc sqlperf(logspace)
Thanks
Hari
SQL Server MVP
"mike" <-nospam@.yahoo.com> wrote in message
news:%23F7ieaVkFHA.2852@.TK2MSFTNGP14.phx.gbl...
> I refreshed the SQL server name after rnning a backup log with no
> truncate ....and still found out that space available size is still 0!
> I also shrunk the Trans Log and still the same rusults.
> Would very much appreciate your help
> Thank you
>
>
> *** Sent via Developersdex http://www.codecomments.com ***|||did you hear mike <-nospam@.yahoo.com> say in news:#F7ieaVkFHA.2852
@.TK2MSFTNGP14.phx.gbl:

> AFter Running Backup Log With No Truncate
sorry, but wouldn't this be the problem? if you wish to remove the
committed transactions from the log you should remove the no_truncate
option.
http://msdn.microsoft.com/library/d...rl=/library/en-
us/tsqlref/ts_ba-bz_35ww.asp
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||thank you. It worked, perfectly-

Mike
*** Sent via Developersdex http://www.codecomments.com ***

Database Space Available Is Zero Even AFter Running Backup Log With No Truncate

I refreshed the SQL server name after rnning a backup log with no
truncate ....and still found out that space available size is still 0!
I also shrunk the Trans Log and still the same rusults.
Would very much appreciate your help
Thank you
*** Sent via Developersdex http://www.codecomments.com ***
Hi,
What is the Recovery model of your database?
If it is FULL OR BULK_LOGGED then
If you need the transaction log backup execute below to clear the
Transaction log:-
backup log <dbname> to disk='d:\backup\dbname.trn
Incase if you do not require the transaction log backup go for;
backup log <dbname> with truncate_only
The above commands will clear the log , bit to reduce the physical LDF file
size you will have to execute the below command:-
DBCC SHRINKFILE (db1_log1_logical_name,truncatXeonly)
After doing these execute the below command to get the log size
dbcc sqlperf(logspace)
Thanks
Hari
SQL Server MVP
"mike" <-nospam@.yahoo.com> wrote in message
news:%23F7ieaVkFHA.2852@.TK2MSFTNGP14.phx.gbl...
> I refreshed the SQL server name after rnning a backup log with no
> truncate ....and still found out that space available size is still 0!
> I also shrunk the Trans Log and still the same rusults.
> Would very much appreciate your help
> Thank you
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
|||did you hear mike <-nospam@.yahoo.com> say in news:#F7ieaVkFHA.2852
@.TK2MSFTNGP14.phx.gbl:

> AFter Running Backup Log With No Truncate
sorry, but wouldn't this be the problem? if you wish to remove the
committed transactions from the log you should remove the no_truncate
option.
http://msdn.microsoft.com/library/de...l=/library/en-
us/tsqlref/ts_ba-bz_35ww.asp
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs
|||thank you. It worked, perfectly-

Mike
*** Sent via Developersdex http://www.codecomments.com ***

Database Space Available Is Zero Even AFter Running Backup Log With No Truncate

I refreshed the SQL server name after rnning a backup log with no
truncate ....and still found out that space available size is still 0!
I also shrunk the Trans Log and still the same rusults.
Would very much appreciate your help
Thank you
*** Sent via Developersdex http://www.developersdex.com ***Hi,
What is the Recovery model of your database?
If it is FULL OR BULK_LOGGED then
If you need the transaction log backup execute below to clear the
Transaction log:-
backup log <dbname> to disk='d:\backup\dbname.trn
Incase if you do not require the transaction log backup go for;
backup log <dbname> with truncate_only
The above commands will clear the log , bit to reduce the physical LDF file
size you will have to execute the below command:-
DBCC SHRINKFILE (db1_log1_logical_name,truncateonly)
After doing these execute the below command to get the log size
dbcc sqlperf(logspace)
Thanks
Hari
SQL Server MVP
"mike" <-nospam@.yahoo.com> wrote in message
news:%23F7ieaVkFHA.2852@.TK2MSFTNGP14.phx.gbl...
> I refreshed the SQL server name after rnning a backup log with no
> truncate ....and still found out that space available size is still 0!
> I also shrunk the Trans Log and still the same rusults.
> Would very much appreciate your help
> Thank you
>
>
> *** Sent via Developersdex http://www.developersdex.com ***|||did you hear mike <-nospam@.yahoo.com> say in news:#F7ieaVkFHA.2852
@.TK2MSFTNGP14.phx.gbl:
> AFter Running Backup Log With No Truncate
sorry, but wouldn't this be the problem? if you wish to remove the
committed transactions from the log you should remove the no_truncate
option.
http://msdn.microsoft.com/library/default.asp?url=/library/en-
us/tsqlref/ts_ba-bz_35ww.asp
--
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogssql

Wednesday, March 21, 2012

Database setup for backup and recovery

I am new to SQLServer but a DB2 DBA.

I want to be able to backup a specific table and restore it. Actually, i may want to backup/restore several tables - a sub-set of tables on the database.

I understand that backup-recovery is at a database level, not a table-file-filrgroup level. Therefore i have to backup-restore a database, but i only want a table.

How do sites handle this. Are many databases created based on backup recovery requirements.

If so, then how do developers know what database tables reside in - given that there are now many databases created to handle recovery requirements. A synonymns/alias/views added ?

tia

glenn

If you put the table on its own filegroup, you can restore just that filegroup/table. Else, you will need to restore the database to a staging db and copy/transfer the table from the staging db to the real db.|||

Can i restore a single filegroup to a previous point in time (say 8am), but keep the rest of the database unchanged (say 10am).

Or, does the whole database have to be at the same point in time after a restore?

tia

Database setup for backup and recovery

I am new to SQLServer but a DB2 DBA.

I want to be able to backup a specific table and restore it. Actually, i may want to backup/restore several tables - a sub-set of tables on the database.

I understand that backup-recovery is at a database level, not a table-file-filrgroup level. Therefore i have to backup-restore a database, but i only want a table.

How do sites handle this. Are many databases created based on backup recovery requirements.

If so, then how do developers know what database tables reside in - given that there are now many databases created to handle recovery requirements. A synonymns/alias/views added ?

tia

glenn

Can i restore a single filegroup to a previous point in time (say 8am), but keep the rest of the database unchanged (say 10am).

Or, does the whole database have to be at the same point in time after a restore?

tia

Database set to simple recovery

Hello I want to perform a transaction log backup for disaster recovery every
30min. Because the recovery mode is set to simple it is not allowing me to
to do this.
My questions are;
1. What are the beneifits of a simple recovery? My transaction log still gro
ws very large.
2. What are the benefits of performing a transaction log backup?
3. Do I have to detach the database to change from simple to full recovery?
ThanksPlease read the page titled "Selecting a Recovery Model" in SQL Server 2000
Books Online. This answers most of your questions.
Transaction log backups help you recover to a point-in-time, and no, you
don't have to detach the database, to change the recovery mode. See ALTER
DATABASE command in SQL Server Books Online for more information on changing
recovery models of databases.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"robert salazar" <rsalazar@.cbbank.com> wrote in message
news:E055BCF9-34BE-45DE-BE89-62C56AF61E11@.microsoft.com...
Hello I want to perform a transaction log backup for disaster recovery every
30min. Because the recovery mode is set to simple it is not allowing me to
to do this.
My questions are;
1. What are the beneifits of a simple recovery? My transaction log still
grows very large.
2. What are the benefits of performing a transaction log backup?
3. Do I have to detach the database to change from simple to full recovery?
Thanks

Database set to simple recovery

Hello I want to perform a transaction log backup for disaster recovery every 30min. Because the recovery mode is set to simple it is not allowing me to to do this.
My questions are;
1. What are the beneifits of a simple recovery? My transaction log still grows very large.
2. What are the benefits of performing a transaction log backup?
3. Do I have to detach the database to change from simple to full recovery?
Thanks
Please read the page titled "Selecting a Recovery Model" in SQL Server 2000
Books Online. This answers most of your questions.
Transaction log backups help you recover to a point-in-time, and no, you
don't have to detach the database, to change the recovery mode. See ALTER
DATABASE command in SQL Server Books Online for more information on changing
recovery models of databases.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"robert salazar" <rsalazar@.cbbank.com> wrote in message
news:E055BCF9-34BE-45DE-BE89-62C56AF61E11@.microsoft.com...
Hello I want to perform a transaction log backup for disaster recovery every
30min. Because the recovery mode is set to simple it is not allowing me to
to do this.
My questions are;
1. What are the beneifits of a simple recovery? My transaction log still
grows very large.
2. What are the benefits of performing a transaction log backup?
3. Do I have to detach the database to change from simple to full recovery?
Thanks

Monday, March 19, 2012

Database Seal: Transaction Log Backup, error in translog backup

I have changed the time for the maintenanceplan but i still get the same
message, could it be something wrong with the database?
a part from the last backuplog:
[31] Database SPS01_Config_db: Transaction Log Backup...
Destination: [D:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\SPS01_Config_db\SPS01_Config_db_tlog_200505100115.TRN]
** Execution Time: 0 hrs, 0 mins, 1 secs **
[32] Database SPS01_Config_db: Verifying Backup...
** Execution Time: 0 hrs, 0 mins, 1 secs **
Backup can not be performed on database 'VirtualCenter'. This sub task is
ignored.
End of maintenance plan 'UserDBs' on 2005-05-10 01:15:20
SQLMAINT.EXE Process Exit Code: 1 (Failed)You can not perform a transaction log backup on a database that uses the
simple recovery model. Set your database to either the full or bulk_logged
recovery model and you can make transaction log backups.
--
Jacco Schalkwijk
SQL Server MVP
"Biowaste" <Biowaste@.discussions.microsoft.com> wrote in message
news:35E8E641-6689-461A-A12C-CF1E69C08B8A@.microsoft.com...
>I have changed the time for the maintenanceplan but i still get the same
> message, could it be something wrong with the database?
> a part from the last backuplog:
> [31] Database SPS01_Config_db: Transaction Log Backup...
> Destination: [D:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\SPS01_Config_db\SPS01_Config_db_tlog_200505100115.TRN]
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> [32] Database SPS01_Config_db: Verifying Backup...
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> Backup can not be performed on database 'VirtualCenter'. This sub task is
> ignored.
> End of maintenance plan 'UserDBs' on 2005-05-10 01:15:20
> SQLMAINT.EXE Process Exit Code: 1 (Failed)
>|||How and where do I change that in the maint. plan?
this has worked in the past... just a few weaks ago.
how come it is only one db that fails?
"Jacco Schalkwijk" wrote:
> You can not perform a transaction log backup on a database that uses the
> simple recovery model. Set your database to either the full or bulk_logged
> recovery model and you can make transaction log backups.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Biowaste" <Biowaste@.discussions.microsoft.com> wrote in message
> news:35E8E641-6689-461A-A12C-CF1E69C08B8A@.microsoft.com...
> >I have changed the time for the maintenanceplan but i still get the same
> > message, could it be something wrong with the database?
> >
> > a part from the last backuplog:
> > [31] Database SPS01_Config_db: Transaction Log Backup...
> > Destination: [D:\Program Files\Microsoft SQL
> > Server\MSSQL\BACKUP\SPS01_Config_db\SPS01_Config_db_tlog_200505100115.TRN]
> >
> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
> >
> > [32] Database SPS01_Config_db: Verifying Backup...
> >
> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
> >
> > Backup can not be performed on database 'VirtualCenter'. This sub task is
> > ignored.
> >
> > End of maintenance plan 'UserDBs' on 2005-05-10 01:15:20
> > SQLMAINT.EXE Process Exit Code: 1 (Failed)
> >
>
>|||This is a database option, not something you set in the maint plan. EM, right-click the database,
properties, options.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Biowaste" <kalmeborg@.hotmail.com> wrote in message
news:85A3F0C2-62B8-47E2-B424-09A2B5B245A4@.microsoft.com...
> How and where do I change that in the maint. plan?
> this has worked in the past... just a few weaks ago.
> how come it is only one db that fails?
> "Jacco Schalkwijk" wrote:
>> You can not perform a transaction log backup on a database that uses the
>> simple recovery model. Set your database to either the full or bulk_logged
>> recovery model and you can make transaction log backups.
>> --
>> Jacco Schalkwijk
>> SQL Server MVP
>>
>> "Biowaste" <Biowaste@.discussions.microsoft.com> wrote in message
>> news:35E8E641-6689-461A-A12C-CF1E69C08B8A@.microsoft.com...
>> >I have changed the time for the maintenanceplan but i still get the same
>> > message, could it be something wrong with the database?
>> >
>> > a part from the last backuplog:
>> > [31] Database SPS01_Config_db: Transaction Log Backup...
>> > Destination: [D:\Program Files\Microsoft SQL
>> > Server\MSSQL\BACKUP\SPS01_Config_db\SPS01_Config_db_tlog_200505100115.TRN]
>> >
>> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
>> >
>> > [32] Database SPS01_Config_db: Verifying Backup...
>> >
>> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
>> >
>> > Backup can not be performed on database 'VirtualCenter'. This sub task is
>> > ignored.
>> >
>> > End of maintenance plan 'UserDBs' on 2005-05-10 01:15:20
>> > SQLMAINT.EXE Process Exit Code: 1 (Failed)
>> >
>>

Database Seal: Transaction Log Backup, error in translog backup

Database says loading, cannot use it

An attempt to restore database left the database in a loading status. Still have backup and would like to restore, but cannot get database out of loading status. Get message that database is inaccessible and cannot be edited until load is complete. I have tried bouncing the server. Also tried restore with recovery.If you can't drop the database because of the state, open Query Analyzer and
try:
USE MASTER
GO
EXEC sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GO
UPDATE sysdatabases SET status = 32768
WHERE name = 'db_name'
GO
Restart SQL Server, reconnect using Query Analyzer
USE MASTER
GO
DROP DATABASE db_name
If that doesn't work, repeat but changing status = 0
Then free up disk space, or whatever you need to do, and try to RESTORE
again (maybe you can allocate this restore to a different drive, if space is
the issue).
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"bscottparis" <anonymous@.discussions.microsoft.com> wrote in message
news:BD86FF00-33A2-4147-8B01-688F577190E9@.microsoft.com...
> An attempt to restore database left the database in a loading status.
Still have backup and would like to restore, but cannot get database out of
loading status. Get message that database is inaccessible and cannot be
edited until load is complete. I have tried bouncing the server. Also
tried restore with recovery.

Database save and restore

Hello,

A database EntryForm has a MenuStrip. How could the Menustrip implemented that it would have a Save and Restore data to the database for backup purposes?

What is the Menustrip for? None of the standard Items seems to work. What can be printed or previewed?

This question is NOT about how to save the data into the database from the EntryForm.

Thanks for any answer,

Hi,

Sorry but didn't get you!!!! what you are trying to achieve!? Is their any error in SQL Server Backup And/Or Restore!?

Hemantgiri S. Goswami

|||

Thanks Gos,

I have this small database entry front-end binded to a database. When this is published and run, there is nothing the user can do except what is implemented on it, which is save, delete, add new record to the database. These seems to be working.

When this is published, I can see the setup.exe. When it is run it does not seem to be installing anywhere, just opens up the program ready for action. Well, there is a shortcut in the StartMenu.

In theVisual Studio\Project\MyFirstProject\ I can see the *.mdf file as well as in the Visual Studio\Project\MyFirstPoject\MyFirstProject\bin\Release\

Greetings.

|||

HI,

You might ask this question to Visual Studio / .Net forum as it seems problem with the front end application.

Hemantgiri S. Goswami

|||

Hi Gos,

Right. There is definitely a problem with my application. (Newbie). How to implement the application that the user can save and restore the database?

Thanks,

|||

Hi Cesar,

If i understand properly, you may use SQL DMO to restore/backup database, their is a sample directory from where your SQL Server folder resides in your installation drive, refer http://www.codeproject.com/vb/net/SQLDBBackup.asp

Hemantgiri S. Goswami

|||

Thanks Gos,

A program which produces something, like a picture, a text, a spreadsheet, etc. has the feature that enable it's user to save and open it.

Lets call a small accounting application made by Visual Basic with Sql Express as "Income2007". This application has a DataEntryForm which has Save, Clear, Delete, NewRecord, Find Buttons and a TableViewForm. It also has a MenuStrip and a StatusStrip. That's it.

Can the "Income2007"'s MenuStrip implemented such a manner that would allow the user to save and open his data? What the user of the application would want a MenuStrip in "Income2007" for?

Have you seen Tabor's SqlServer Videos? He includes with the videos a DB Sample: MyCompany.zip, a 320KB file. This file is not an application file. It is a Database file. Is it not?

Can the user do the same, by the "Income2007"'s MenuStrip or other simple method supplied with this program?

The program's user uses the DataEntryForm to handle the database's delete, update, query, etc functions. As it should be. When the user finished he wants to save and take the data with him. Does he have to have SQL Server Manager Studio installed on his computer to able to accomplish this task?

As always, appreciate your answer,

|||

Hi Cesar,

Cesar Francisco wrote:

Thanks Gos,
A program which produces something, like a picture, a text, a spreadsheet, etc. has the feature that enable it's user to save and open it.
Lets call a small accounting application made by Visual Basic with Sql Express as "Income2007". This application has a DataEntryForm which has Save, Clear, Delete, NewRecord, Find Buttons and a TableViewForm. It also has a MenuStrip and a StatusStrip. That's it.
Can the "Income2007"'s MenuStrip implemented such a manner that would allow the user to save and open his data? What the user of the application would want a MenuStrip in "Income2007" for?
Have you seen Tabor's SqlServer Videos? He includes with the videos a DB Sample: MyCompany.zip, a 320KB file. This file is not an application file. It is a Database file. Is it not?
Can the user do the same, by the "Income2007"'s MenuStrip or other simple method supplied with this program?
The program's user uses the DataEntryForm to handle the database's delete, update, query, etc functions. As it should be. When the user finished he wants to save and take the data with him. Does he have to have SQL Server Manager Studio installed on his computer to able to accomplish this task?
-
As always, appreciate your answer,

Yes your program can save,insert,delete and update data within Database.

No he don't need to have Sql Server Management Studio installed on his machine but if it is Windows Forms application you have to deploy your package (you can built one using package and deploy wizard) Refer an exampler here http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=3805&lngWId=10

You may definately allow user to Save and Open a Data in SQL Server Database, when he clicks on Save you have to run insert query and saved record can be displayed in Grid and while he wants to open it for editing he/she click on the record which will populate all your textbox/combo/list box with record for editing. Kindly refer suggested link for Code.

Hemantgiri S. Goswami

|||

Thanks Gos - for your patience,

I am certainly learning about my lack of communication ability.

What is implemented so far?

"Income 2007" has

One Form, 1TabControl with 4TabPages. The Tabpages are DataEntry, DataEntryTableView, Query, QueryTableView. When the Form Loads, through the TableAdapter.Fill the DataEntryTableView is filled with data. The user can see all the data entered up to that point. In the DataEntry Tabpage the user can Save, Delete, Update the database's data.

Through the QueryEntry Tabpage and QueryView Tabpage the user can look at sorted data.

Through the Build>Publish, "Income 2007" is saved to a CD and is given to the user.

NOT Implemented:

The user calls: I would like to save the data (*.mdf) on the day's end and have it on a disk.

Why?

The user has two computers each one is located in a different city (City A and B), each one has a copy of "Income 2007". These computers are NOT networked. In City A, during the week, the user enters data through the DataEntry Tabpage into "Income 2007(A)" 's database. .

On the weekends he goes to City B. He opens "Income 2007(B)". At the very first, he has no data in it's database. If he has NO data in the database, he copies the data File (*.mdf) from "Income 2007(A)" into "Income 2007(B)" database. When he finished working he saves the data to a File. He travells back to City A, purges the data in "Income 2007(A)" and copies the data from "Income 2007(B)" into "Income 2007(A)" 's database.

I believed, that through an implemented MenuStrip the user can save *.mdf (the data of the database) to a File and take the data (not the database) with him (on a floppy or memory stick). Is there a way for the USER to save the data to a File? Definitely there should be a way. What kind of program is that where you can't save your data? A crippled, demo program maybe.

In resumen: the problem is NOT how to manipulate the database' data in the program, but rather how to enable the user of the windows application "Income 2007" to Save, Purge, Open the (*.mdf) in "Income 2007(A) or (B)".

Greetings,

|||

Hi,

If i understood correctly you are asking about backup of Database(is this what you mean by save data / .mdf & .ldf files) you may take a look at SQL DMO sample code, backup of SQL Database contains .mdf/.ldf files , and if you wish you can use sp_attach_db and sp_detach_db, but this will work after you are disconnected from Database. SQL DMO sample codes can be found in folder where sql server is installed.

Hemantgiri S. Goswami

|||

Hello again,

I suspect from your last answer that the answer to my question is NO. It seems that you need a copy of Managment Studio, which by the way, will overwhelm any causal user.

1. Now how this could be accomplished inside the application? Is there a way to implement backup from inside the application? Can a script written to open Studio in the background and perform this task?

2. As you said "but this will work after you are disconnected from Database." How could the user disconnect from the database and than save and later open the database from inside the application?

Illustration of my question:

Not long ago, I did download a small application called Ant Movie Catalog (http://www.antp.be/software/moviecatalog/sources) ~ 3MB. This was written in Borland Delphi 7. It is, as it says, a Movie Catalog. After installation there was nothing in it, it was just an empty application. (Exactly the same as my "Income 2007"). After collecting data through the interface, I did save the collected data into a file, in this case a *.amc (~11MB) and saved into a memory stick. How? By clicking on File>Save. To look at my or anybody else collection, the only thing I have to do is File>Open>navigate where the *.amd file is and presto.

Cheers,

|||

Hi,

1. refer http://www.google.co.in/search?hl=en&q=sql+dmo%2Bbackup&btnG=Search&meta= for code example to backup from inside application, i have done this in vb and vb.net

2. That is if you wants to Attach & Detach Database.

Hemantgiri S. Goswami

|||

Hello again,

I see that this question already draw "sunshine". I pray Gos, show the code and explain. Hey, I am a Newbie.

I really do not care to attach or detach the database. The user of my application (if ever) has to be able to do the basic functions that any application should have - as per indicated in the preceding message about the Ant Catalogue. I wonder if you had time to have a look at it.

The example on the site you pointed to is for VB 6.

This was a question recently rised on the Forum by Allen White 01-02-2007

Re: Schedule SQL Express Backup using SQL/DMO
is it possible to Schedule SQLExpress Backup using SQL/DMO? SQL Express does not support SQL Agent then how can I view jobs created using SQLDMO? In management studio ,jobs option is unavailable. I have written code to add JObSchedule/job/jobStep and program is running without errors .. but no way to find out whether job is actually created and executed.

Answer by dmouser: Because SQL Express doesn't include the SQL Server Agent the jobs you are creating can't be executed. You'd be best served to use Windows Scheduler to run the programs performing your backups.

|||

Hello all, seekers of truth and real solution,

Seemingly there is an overwhelming interest in this subject. As it should be! Are we asking for something outlandish, bizarre? What would be the "Word" for, if it could not save, and open a file created with it?
If VB 2005 Express was intended for hobbyist - how to demonstrate the "Hello World" application on a friend's computer? Installing the behemoth, enterprise strength, multi-user, bulletproof SQL Express 250MB first? But lets say the application have some limited merit, - should the user hire an expert/ buy other program, just save and restore the data?
The deprecated Access database might be the solution to this problem for hobbyists. I posted in "Solution! Access database....." what I got so far.
It shows some promise - plus it has a small footprint. Nevertheless, none of the experts on this circuit gave me tips (code, please) how to solve the perennial problem mentioned above. What? Is it too difficult? We are talking about Access, not SQL anymore. It is well in the realm of possibility. Beside the free "Ant Movie Catalog" (by the way, it's author - a selfless guy from Belgium - giving away the source code as well. Too bad it is written in Delphi 7!) -- I have a small commercial program based on Access but not in "Access". It can do all that (save, purge, restore).

Is somebody out there somewhere to give the code away for a fee (not free)? It is becoming such a wasted time looking for something so trivial.

Cheers,

|||

The solution for Access/Jet databases.

'Do not forget to add reference to

Microsoft ActiveX Data Object Recordset 2.8 Library,

Microsoft ADO Ext.2.8 for DDL and Security and

Microsoft Jet and Replication Objects 2.6 Library under the COM Tab.

Imports System.IO
Imports System.IO.File
Public Class Form3
Public Shared Sub Restart()
End Sub

'Watch out for the DialogBoxes: BackupInfo,RestoreRestart,NewDBRestart.DataBaseEraseWarning.

You have to make them,

'BACKUP A DATABASE

'This backup will add a timestamp Month/Day/Year-HoursMinutes to the original database (DB.mdb) file's name and moves that file into a BackUps folder. Do not forget to add this BackUps folder before publish your app.

Private Sub BackUp_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BackUp.Click
Dim x As String
Dim f As String
Dim z As String
Dim g As String
Dim BCK As New BackupInfo
x = Format(Now(), "_MMddyy_HHmm")
z = "C:\Program Files\YourApplication\DB.mdb"
g = x + ".mdb"
f = "C:\Program Files\YourApplicationNameHere\BackUps\DB" & g & ""
File.Copy(z, f)
ToolStripStatusLabel1.Text = "Backup completed succesfully."
If BCK.ShowDialog() = DialogResult.OK Then
End If
End Sub

'RESTORE A DATABASE

'Find you Backup file with TimeStamp in the BackUps folder (or anywhere else you backed up your database).

'This will replace (overwrite the database) in the YourApplication folder.
Private Sub RestoreDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RestoreDB.Click
Dim Filename As String
Dim R As New RestoreRestart
Dim overwrite As Boolean
overwrite = True
Dim xi As String
With OpenFileDialog1
'.Filter = "Database files (*.mdb)|*.mdb|" & "All files|*.*"
If .ShowDialog() = DialogResult.OK Then
Filename = .FileName
xi = "C:\Program Files\YourApplicationNameHere\DB.mdb"
File.Copy(Filename, xi, overwrite)
End If
End With

'Notify user in the ToolStrip (or MessageBox.Show("Data restored....")
ToolStripStatusLabel1.Text = "Data restored successfully"

'Restart application

If R.ShowDialog() = DialogResult.OK Then
Application.Restart()
End If
End Sub

'CREATE A NEW EMPTY DATABASE.
Private Sub CrNewDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CrNewDB.Click
Dim L As New DatabaseEraseWarning
Dim Cat As ADOX.Catalog
Cat = New ADOX.Catalog
Dim R2 As New NewDBRestart
If File.Exists("C:\Program Files\YourApplicationNameHere\DB.mdb") Then
If L.ShowDialog() = DialogResult.Cancel Then
Exit Sub
Else
File.Delete("C:\Program Files\YourApplicationNameHere\DB.mdb")
End If
End If
Cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\ YourApplicationNameHere \
DB.mdb; Jet OLEDB:Engine Type=5")

Dim Cn As ADODB.Connection
'Dim Cat As ADOX.Catalog
Dim Tablename As ADOX.Table

'Taylor these according to your need - add so many column as you need.
Dim col As ADOX.Column = New ADOX.Column
Dim col1 As ADOX.Column = New ADOX.Column
Dim col2 As ADOX.Column = New ADOX.Column
Dim col3 As ADOX.Column = New ADOX.Column
Dim col4 As ADOX.Column = New ADOX.Column
Dim col5 As ADOX.Column = New ADOX.Column
Dim col6 As ADOX.Column = New ADOX.Column

Cn = New ADODB.Connection
Cat = New ADOX.Catalog
Tablename = New ADOX.Table

'Open the connection
Cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\YourApplicationNameHere\DB.mdb;Jet OLEDB:Engine Type=5")
'Open the Catalog
Cat.ActiveConnection = Cn

'Create the table (you can name it anyway you want)
Tablename.Name = "Table1"

'Taylor these according to your need - add so many column as you need. Watch for the DataType!
col.Name = "ID"
col.Type = ADOX.DataTypeEnum.adInteger
col1.Name = "Serial"
col1.Type = ADOX.DataTypeEnum.adInteger
col1.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col2.Name = "FName"
col2.Type = ADOX.DataTypeEnum.adVarWChar
col2.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col3.Name = "LName"
col3.Type = ADOX.DataTypeEnum.adVarWChar
col3.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col4.Name = "DOB"
col4.Type = ADOX.DataTypeEnum.adDate
col4.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col5.Name = "Sex"
col5.Type = ADOX.DataTypeEnum.adVarWChar
col5.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col6.Name = "Ph1"
col6.Type = ADOX.DataTypeEnum.adVarWChar
col6.Attributes = ADOX.ColumnAttributesEnum.adColNullable

Tablename.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "ID")

'You have to append all your columns you have created above
Tablename.Columns.Append(col)
Tablename.Columns.Append(col1)
Tablename.Columns.Append(col2)
Tablename.Columns.Append(col3)
Tablename.Columns.Append(col4)
Tablename.Columns.Append(col5)
Tablename.Columns.Append(col6)

'Append the newly created table to the Tables Collection
Cat.Tables.Append(Tablename)

'User notification (again you can use MessageBox.Show() if you want)

ToolStripStatusLabel1.Text = "A new empty database was created successfully"

'clean up objects
Tablename = Nothing
Cat = Nothing
Cn.Close()
Cn = Nothing

'This restart the application
If R2.ShowDialog() = DialogResult.OK Then
Application.Restart()
End If
End Sub

'COMPACT A DATABASE
'Have a Button with "CompactDB" design name
Private Sub CompactDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CompactDB.Click
Dim JRO As JRO.JetEngine
JRO = New JRO.JetEngine

'The first source is the original, the second is the compacted database under an other

name.

JRO.CompactDatabase("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program

Files\VSoft\AppMiss\AmDB.mdb; Jet OLEDB:Engine Type=5",

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program

Files\VSoft\AppMiss\AmDBComp.mdb; JetOLEDB:Engine Type=5")

'Here the original (not compacted database is deleted)
File.Delete("C:\Program Files\YourApplicationNameHere\DB.mdb")

'Here the compacted database is renamed to the original database.
Rename(" C:\Program Files\YourApplicationNameHere\DBComp.mdb", "C:\Program

Files\ YourApplicationNameHere \DB.mdb")

'User notification
ToolStripStatusLabel1.Text = "The database was compacted successfully"
End Sub

End Class