Copy a database in SQL Server using Delphi

Hello All,

I am not sure if this is the best place for this question or if it should go in the ADO section.  Anyway, I would like to make a copy of a SQL Server database to another database within the same Server using Delphi.  For example, take a copy of the Production database and put it into a What_If database. I know that I could get the IT to do a backup of Production and a restore to What_If, but I would like to avoid this method and have it user controllable.  I also do not want to copy the database table by 
table as I think this would be very slow and cumbersome, plus there are about 100 tables.

Any ideas on how I can accomplish what I want to do in Delphi?  Is there a way to launch a backup and restore from Delphi?

Thanks in advance,

Dave
0
David
10/25/2012 8:01:50 PM
embarcadero.delphi.database 1294 articles. 0 followers. Follow

8 Replies
2091 Views

Similar Articles

[PageSpeed] 39

I think the only way is to sort out how to do this by SQL. Find the way searching on SQL server sites and execute such a query with TQuery.execSQL. You can create the query statement asking the user the name of the source and target database and put this info as params in the query.
0
Robert
10/26/2012 8:37:19 AM
> {quote:title=David Cox wrote:}{quote}
> Hello All,
> 
> I am not sure if this is the best place for this question or if it should go in the ADO section.  Anyway, I would like to make a copy of a SQL Server database to another database within the same Server using Delphi.  For example, take a copy of the Production database and put it into a What_If database. I know that I could get the IT to do a backup of Production and a restore to What_If, but I would like to avoid this method and have it user controllable.  I also do not want to copy the database table b
y table as I think this would be very slow and cumbersome, plus there are about 100 tables.
> 
> Any ideas on how I can accomplish what I want to do in Delphi?  Is there a way to launch a backup and restore from Delphi?
> 
> Thanks in advance,
> 
> Dave

 You should probably have a look at the following MSDN article, "Using TRANSACT SQL" part: http://msdn.microsoft.com/en-us/library/ms187510.aspx

 Database backup is something usually handled on the server side, for a number of reasons. Speed, availability (server is always on, clients aren't), avoiding duplicated backups, etc. Using the statements in the article above you might be able to tell the 
server to back up a database, not to a different database directly, but a file. You could then attach it to a new database.

 If you don't want to go this way, you could try some advanced parameters of the "INSERT" statement, described here: http://msdn.microsoft.com/en-us/library/ms174335.aspx. You can give INSERT a range of rows returned by a SELECT. If the data remains on the server side then performance could be OK.

 Please note that I haven't tried any of these myself :)

 Regards,
 ~Victor
0
Victor
10/26/2012 8:43:57 AM
> {quote:title=Robert Triest wrote:}{quote}
> I think the only way is to sort out how to do this by SQL. Find the way searching on SQL server sites and execute such a query with TQuery.execSQL. You can create the query statement asking the user the name of the source and target database and put this info as params in the query.

Robert,

I am thinking the same thing.  

Thanks,

Dave
0
David
10/26/2012 1:04:18 PM
>  You should probably have a look at the following MSDN article, "Using TRANSACT SQL" part: http://msdn.microsoft.com/en-us/library/ms187510.aspx
> 
>  Database backup is something usually handled on the server side, for a number of reasons. Speed, availability (server is always on, clients aren't), avoiding duplicated backups, etc. Using the statements in the article above you might be able to tell the 
> server to back up a database, not to a different database directly, but a file. You could then attach it to a new database.
> 
>  If you don't want to go this way, you could try some advanced parameters of the "INSERT" statement, described here: http://msdn.microsoft.com/en-us/library/ms174335.aspx. You can give INSERT a range of rows returned by a SELECT. If the data remains on the server side then performance could be OK.
> 
>  Please note that I haven't tried any of these myself :)
> 
>  Regards,
>  ~Victor

Thanks for the links Victor.

The Insert looks intriguing, but most of our customers are using SQL Server 2008 and the command looks to be for 2012 only.

The backup and restore would not be an “official backup” of the database, but rather just to enable the user to make a copy to another database.  I am thinking that the steps would be something like:

1.	Get Database names to copy from and to.
2.	Create a backup using a system generated unique filename.
3.	Restore the data to the “to database”
4.	Delete the backup file

It may be a slow process depending on the database size, but faster then inserting individual rows.

Dave
0
David
10/26/2012 1:16:58 PM
I wrote a simple backup routine and restore routine that works.   The downfalls of this method is that the user will need permission to backup and restore and the user cannot interrupt the process of the backup or restore if it is taking a long time.  The destination database to copy to must already exist.

I haven’t done it as yet, but I think it would be better to put the backup and restore routines in a separate thread so that the user could carry on with other activities.

In the examples below, the DBToCopy is the database name that you want to copy, BackupFile is the folder and temporary backup file, and DBToRestore is the database your want to copy over (restore to).  

One error I got was trying to write the backup file to the Program Files directory. You need to make sure that the user has permission to the folder and that the folder already exists.

Note that the backup will append to an existing backup file.  After the restore, I delete the backup file as a clean up.

To backup:
{code}
  with TADOQuery.create(nil) do
    try
      try
      Connection := frmDMS.ADOConnection1;
      SQL.Clear;
      SQL.Add('BACKUP DATABASE '+ DBToCopy+' TO DISK='''+BackupFile+''';');
      ExecSQL;
      except
        raise;
      end;
    finally
      free;
    end;
{code}

After the database has been backed up, I call the following to restore (copy): 
{code}
  with TADOQuery.create(nil) do
    try
      try
      Connection := frmDMS.ADOConnection1;
      SQL.Clear;
      SQL.Add('RESTORE DATABASE '+ DBToRestore+' from DISK= '''+BackupFile+''';');
      ExecSQL;
      except
        raise;
      end;
    finally
      free;
    end;
{code}
0
David
10/26/2012 8:16:13 PM
"David Cox" wrote in message news:517352@forums.embarcadero.com...
>
> One error I got was trying to write the backup file to the Program Files 
> directory. You need to make sure that the user has permission to the 
> folder and that the folder already exists.


Why do you insist on writing files anywhere under the Program Files folder? 
You should not do that. Write files to a folder that is writable by the 
user - typically the best place is the user's appdata area.

-- 
Wayne Niddery (TeamB)
"If there is any principle of the Constitution that more imperatively calls 
for attachment than any other it is the principle of free thought, not free 
thought for those who agree with us but freedom for the thought that we 
hate." - Oliver Wendell Holmes
0
Wayne
10/26/2012 11:44:47 PM
> Why do you insist on writing files anywhere under the Program Files folder? 

I don’t.  We have customers running XP and some of them still write data to Program Files.  I try to test all customer scenarios and environments.
0
David
10/28/2012 8:36:18 PM
Hi David,


 I can also suggest you other ideas, but it's up to you whether you will find them appropriate in your environment.

1. by using T-SQL :
   - detach original db.
   - copy db files to new location (names).
   - reattach original db
   - attach new db ( 'create database ... for attach' t-sql statement)

2.  You can perform the desired tasks by using Transfer object of SQLSMO.
    This approach has some limitations (.Net framework should be installed) 
   
  - Create a SQLSMO project with C#, Prism or VB.Net
  - Make the class COM visible
  - Import the COM class in your Delphi project.

3. Make a similar SQLSMO script in Powershell. (.Net framework should be present as well)
    Call / Execute the powershell script via your Delphi application

 Robert






> {quote:title=David Cox wrote:}{quote}
> Hello All,
> 
> I am not sure if this is the best place for this question or if it should go in the ADO section.  Anyway, I would like to make a copy of a SQL Server database to another database within the same Server using Delphi.  For example, take a copy of the Production database and put it into a What_If database. I know that I could get the IT to do a backup of Production and a restore to What_If, but I would like to avoid this method and have it user controllable.  I also do not want to copy the database table b
y table as I think this would be very slow and cumbersome, plus there are about 100 tables.
> 
> Any ideas on how I can accomplish what I want to do in Delphi?  Is there a way to launch a backup and restore from Delphi?
> 
> Thanks in advance,
> 
> Dave
0
Robert
11/4/2012 3:39:11 PM
Reply:

Similar Artilces:

moving an sql server 2005 database to a sql server 2000 database
I am trying to move a database which I wrote in SQL Server 2005 to a SQL Server 2000 database. I'm not sure the best way to do this....... Can anyone enlighten me?.....   this article explains it. http://searchsqlserver.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid87_gci1149585_tax301536,00.html hth,mcm pizzamaker74: I am trying to move a database which I wrote in SQL Server 2005 to a SQL Server 2000 database. I'm not sure the best way to do this....... Can anyone enlighten me?..... If both are in the same network or box just register the 2005 with the 2000 cr...

Migrating sql server 2005 express database to sql server 2005 database
Hi, I have an application developed using VWD and sqlserver express database. The express database is turning out to be small in size and we need to migrate to larger sqlserver 2005 database. What are the steps for this migration, please list in detail. Regards, Sandyhttp://www.thequinn.infohttp://www.sksdataservices.comhttp://www.infobasket.info Hi Sandy, To move a database from SQL Express to SQL 2005, you can use the following steps. 1. If your database file is attached to the server instance, you will need to detach it first using SQL management studio. If it is under App_Data folde...

SQL Server Database File vs SQL Server Database -VWD Express
VWD Express 2008, SQL Server Express 2005. I have been using SQL Server Express with ASP and VB for some years - I just create a DB in Management Studio Express, have SQL Server set to 'SQL Server and Windows authentication', and use a connection string with 'sa' and a password (I never have managed to get it working with 'integrated security', but that is probably irrelevant to this post). So I have a list of DB's attached to SQL Server that I can see, attach, detach etc in MSE. I am getting started with .NET. Working through a VWD Express book tutori...

Copy database from one SQL Server to SQL Server Compact edition
I have a SQL script that creates tables and have insert scripts to put data into it. I want to create the table in Compact SE for my mobile device project. I know I can run a script from VS using SMO, but is there some other way? I have SQL Server Express 2008 installed. From VS itself is there a way to create my tables with data in SQL Server Compact?Beware of bugs in the above code; I have only proved it correct, not tried it. (Donald Knuth)  In Studio 2008 you can use the Server Explorer to create a connection to an SQL Server 3.5 Compact Edition File, and run queries against it j...

Can microsoft SQL Server 2005 database upgrade to microsoft SQL Server 2008 database?
I have a microsoft SQL Server 2005 database, I don't know how to upgrade the 2005 database to 2008 database, I try to use SQL Server Management Studio Express 2008 to open the 2005 database, it's OK, does it mean that the 2005 database have been upgraded to 2008 database? BTW, is SQL Server 2005 database compatible with SQL Server 2008 database completely?SuperCool Multiple ZIP - A utility to unzip multiple files and work with multiple zip filesSuperCool Random Number Generator Yes. It is compatible. Anyhow go through this url for more informationhttp://msdn.microsoft.com/en-us/li...

how can I copy the entire database from one sql server to another sql server?
How can I copy the entire database (all the objects from one database to another database)? thanks in advance. Use DTS (right click on a database in Enterprise Manager, select All Tasks, and then Import or Export, as appropriate). You can also back up the database and then restore on the other server.Starting with ASP.NET 2.0? Look at:Programming Microsoft Web Forms My Blog hi Douglas, Thanks for your post. I was looking at the Database Copy Wizard. But it says my local system does not have the permission to copy files over the network. I looked over some tuturials and changed ...

How to create a copy of SQL Server 2000 database in SQL Server 2005 Express?
I have a database called 'DB1' in SQL Server 2000. I want to create the same database in SQL Server 2005 Express including the original data in tables. How would I do that? I cannot find any option to do this upgrade in SQL Server Management Studio.sun21170 Check out this:How to: Upgrade to SQL Server 2005 with the Copy Database Wizard Darrell Norton, MVPDarrell Norton's BlogPlease mark this post as answered if it helped you! The article you mentioned does not apply to the 2005 Express Edition. The Management Studio for the Express Edition contains no Copy database opti...

SQL Server instance Error
Hi Guys, I created a default website in VS2008 & dragged in a webpartmanager into the div section (i was going to have a play with creating web parts). I tried to preview the default page to seeif it runs but i get the following error: Server Error in '/WebSite1' Application. A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - E...

How to connect to Remote SQL Server database using SQL Server 2005 Express
Hi,I've an account with a hosting service provider online for SQL Server database. I've downloaded SQL Server 2005 Express from ASP.Net. How can I use it to connect to my SQL Server Database which is sitting on remote server? The hosting provider gave me following things to connect to the remote database.Server NameDatabase NameUser NamePasswordRegards,A.K.R    Anyone? I don't want to ask the obvious, but is there not a Read Me file included with the download of SQL Express which would explain how to connect? Sorry I can't be more helpful, but I've only ever briefly ...

Can I use Bugzilla with Sql server 2000 or sql server 2005 as backend Database
I have Just Successfully installed Bugzilla My installation are Bugzilla 2.2 Apachec Webserver ActivePerl 7.8 Mysql 5.0 Now I like That My Database should be Sql server 2000 or sql server 2005 Can I do that [As I also see that I required some Perl module of Mysql as well] ...

How to enabel sql server notification on database as well as table using sql server 2005
HiI am ASP.NET developer. I have  implemented caching in my site.I am using "System.Web.Caching.SqlCacheDependencyAdmin.EnableNotifications("Data Source=<Data Source name>;Initial Catalog=<database name>User ID=<user id>;pwd=<password>;Pooling=true;Min Pool Size=0;Max Pool Size=1000;") & "SqlCacheDependencyAdmin.EnableTableForNotifications("Data Source=<Data Source name>;Initial Catalog=<database name>User ID=<user id>;pwd=<password>;Pooling=true;Min" Pool Size=0;Max Pool Size=1000;","<tabl...

Problem with creating new SQL server database in VWDExpress using SQL Server 2005 Express
 when I try to create a SQL Database from Visual Web Developer 2005 Express Edition, I get the following error message: "Connections to SQL Server files (*.mdf) require SQL Server Express 2005 to function properly.  Please verify the installation of the component or download from the URL http://go.microsoft.com/fwlink/?LinkID=49251" When I go to that URL, it brings me to the page to download SQL Server Express 2005. any ideathanks for advance  Did you install SQL Server Express on your server/system? JeffPlease: Don't forget to click "Mark as Answe...

Using Microsoft SQL Server Management Studio Express to export/copy database?
How does one export/copy a database using Microsoft SQL Server Management Studio Express?  At this time, I have an existing database that I can access and run queries against.  However, I cannot connect to it via Visual Wed Developer 2005 Express.  At this time, I'm getting the following error message with the existing database: Login failed for user 'bigide2_gims2'.  The user is not associated with a trusted SQL Server Connection. Next, I'm running everything locally.  If anyone can assist, it would be greatly appreciated and thanks in advance. -Conrad...

Access Database and SQL Server database
Hi, I have an access database server and a sql server DB. The  Structure of both DB is same . The data is updated or inserted in access DB. I want to update SQL Server DB on a any event say button click. All updated data or new data in access DB should be reflected on SQL Server DB . Can anyone guide on the approach to be attempted. Thanks   Mark this as an answer if you find my it helpful.Thanks,Anubhuti ONe approach would be to use the System.Data.OleDb library and store connection strings for each database in your web.config file.  Each time you need ...

Web resources about - Copy a database in SQL Server using Delphi - embarcadero.delphi.database

Database - Wikipedia, the free encyclopedia
... requiring information. For example, modelling the availability of rooms in hotels in a way that supports finding a hotel with vacancies. Database ...

Database - Wikipedia, the free encyclopedia
A database is an organized collection of data . The data are typically organized to model aspects of reality in a way that supports processes ...

Dodgy database exposes details of 191 million US voters
... years, there's a high chance that your personal details are now out in the wild. Security researcher Chris Vickery found a misconfigured database ...

3.3 million Hello Kitty fans' details have been exposed in a huge database leak
... week after week, the hacks, breaches and data leaks continue to roll in. The latest victim? Hello Kitty. CSO Online is reporting that a database ...

Under pressure, DNC restores Bernie Sanders campaign's access to their database - Videos - CBS News
A strange twist in the race for the democratic presidential nomination: the campaign of Vermont Senator Bernie Sanders has filed a complaint ...

Massive Voter Database Left Open For Public Consumption
Gosh, we could have avoided all the angst about the Clinton/Sanders voter database breach if only we'd known about this major database , accessible ...

Database configuration issues expose 191 million voter records
A misconfigured database has led to the disclosure of 191 million voter records. The database, discovered by researcher Chris Vickery, doesn't ...

Database error publishes data of 3 million Hello Kitty fans
Online researcher Chris Vickery uncovered a database this weekend containing the personal information of 3.3 million accounts associated with ...

191 Million US Voter Registration Records Leaked In Mystery Database
A huge database of 191 million US citizens is uncovered. But no one knows who carelessly left it open, whilst the FBI have been contacted.

Sanders campaign regains access to DNC voter database
Sanders campaign regains access to DNC voter database

Resources last updated: 12/30/2015 8:34:49 PM