Copy SQL Server Database to Code

Question

I have two SQL Server connection strings, CX and CY.

What I need to do is

• Make sure there are no tables in CY.
• Backing up the CX database.
• Restore it to CY.

Didn't find what I'm looking for. I do not want the tool to do this, I need to do this in C # code at runtime, because the action of adding a new client needs to copy the main database to an empty database. I cannot use the pre-created script because I also need to copy the data, and the main database could be updated a few seconds before adding a new client.

=== UPDATE ===

I use Smo.Backup and Smo.Restore. When I try to recover, I get:

ERROR 3154 The backup set contains a backup of a database other than the existing database.

Can someone tell me how to get around this? In addition, I have a working solution!

thanks

Pete

Answer 1

Database backup solution:

1) Make sure there are no tables

select COUNT(*) from sys.sysobjects where xtype = 'U'

2) Backup

BACKUP DATABASE MyFirstDatabase TO DISK= @path WITH FORMAT

3) Restore

RESTORE DATABASE MySecondDatabase   FROM DISK = @path WITH REPLACE

See SQL Books Online for more information: http://msdn.microsoft.com/en-us/library/ms186865.aspx

Answer 2

You can use SQL Server Management Objects (SMOs). Check out http://msdn.microsoft.com/en-us/library/ms162169.aspx . Information about backing up the database can be found at http://technet.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.backup.aspx .

Answer 3

#, , .

, script, , , . , SPROC .

Answer 4

,

SqlServer.Management.Smo.Backup  class

. , , .