January 9, 2008 at 7:57 am
Hi,
I need to copy a existing, running SQL-Server Database very fast, without disconnecting the Users.
I must do this with VB6 (or optionally VB.Net).
The Database only contains Tables with Data (no stored Procedures or anything else, the only one View is not necassary needed in the copy)
Does anyone knows how I can do this?
Please can anyone Help me or give me a Hint?
Thanks
John
January 9, 2008 at 8:29 am
I'm assuming a regular backup and restore to a different location are off the table?
You could do a snapshot backup which are nearly instantaneous but to restore from one of these, you have to take the database offline.
What are you hoping to achieve?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 9, 2008 at 9:39 am
A database snapshot would be my first response as well, but you have not given enough information for us to help.
What do you intend to use the copy of the database for?
How big is the database?
What do you expect for a total time to do this?
Do you need full database consistency?
What edition of SQL are you using?
How often do you need to do this?
These will make a big difference in the response.
Depending on these answers, snapshots, log shipping, replication, or a backup / restore operation may make sense.
January 9, 2008 at 10:15 am
Thank you for your answer !
Backup and Restore would be fine, if it can be done programmaticly - without an interaction from the User. How can i call this from a program?
We have a Informationmanagement system (Consolidate.at) where the users use a Database in a network. A replication is done programmaticly by our program and our own logic.
Creating scuh a "offline" Database is done now by reading every single line and transfering it to the copy. This is very slow.
The size of the Database depends on the customer - between 2 GB and open end (30+ GB...)
The differenz after creating the Database is in one Table, this table will be deleted and created new on the copy, after that some few entries in the Maindatabase are made and the creation of the notebook-user-Database is done.
this should be as fast as possible.
regards, John
January 9, 2008 at 10:22 am
Thank you for Your answer, michael!
What do you intend to use the copy of the database for?
We have a Informationmanagement system (Consolidate.at) where the users use a Database in a network. A replication is done programmaticly by our program and our own logic.
Creating scuh a "offline" Database is done now by reading every single line and transfering it to the copy. This is very slow.
The differenz after creating the Database is in one Table, this table will be deleted and created new on the copy, after that some few entries in the Maindatabase are made and the creation of the notebook-user-Database is done.
this should be as fast as possible.
How big is the database?
The size of the Database depends on the customer - between 2 GB and open end (30+ GB...)
What do you expect for a total time to do this?
as fast as possible 😉
Do you need full database consistency?
yes
What edition of SQL are you using?
depends on the Customer (MSDE, SQL Enterprise, SQLServer 2005, ...)
How often do you need to do this?
For Every Customer (350) ... about each 3-10 times a year
Depending on these answers, snapshots, log shipping, replication, or a backup / restore operation may make sense.
Can this all be done Programmaticly from Visual Basic?
Regards,
John
January 9, 2008 at 10:34 am
Normally I'd say "Sure, just create a stored procedure" but since you don't use them... yes, you can do this from a VB app. You simply have to have a couple of things, the security that the app runs under has to be able to run backups and restores. You also need to make sure that the backup and restore is done to a location that the SQL Server Service account has access to. The simple syntax looks like this:
BACKUP DATABASE x TO DISK = '\\myserver\myshare\my.bak'
The restore would look something like this:
RESTORE DATABASE x FROM DISK = \\myserver\myshare\my.bak' WITH MOVE 'x_Data' TO 'drive\file', MOVE 'x_Log' TO 'drive\file';What this is doing is "moving" the files in order to create a new database from the backup of the old one.
To do a snapshot, use this code to create a read-only copy of the database:
CREATE DATABASE Adventureworks_ss1430 ON (NAME = AdventureWorks_Data, FILENAME = 'C:\Backups\AdventureWorks_data_1430.ss') AS SNAPSHOT OF AdventureWorks;
I wrote an article on backups in 2005 a while back. It was published on Simple-Talk[/url]. It might help you get started. More details on snapshots are here[/url].
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 9, 2008 at 10:43 am
Hey Grant,
Thanks a lot !!!!
I will dig into that right away !
thankfull john 🙂
January 9, 2008 at 11:28 am
I would also agree with Grant's suggestion - a simple backup / restore solution would probably work best for you.
Depending on your bandwidth, you may also want to include zipping the file at the source (requiring software at both ends of the process) because there is no compression in a SQL backup so they are pretty large files.
January 9, 2008 at 1:05 pm
okay, thanks a lot
seems to be the thing I was looking for !
:w00t:
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply