3

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

What I need to do is

  1. Ensure CY has no tables in it.
  2. Backup the database CX.
  3. Restore it to CY.

Haven't found what I am looking for yet. I don't want a tool to do this, I need to do it in C# code at runtime because the action of adding a new Client needs to copy a master DB to an empty DB. I can't use a pre-made script because I need to also copy data, and the master DB could have been updated only seconds before the new client was added.

===UPDATE===

I am using Smo.Backup and Smo.Restore. When I try to restore I get:

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

Can anyone tell me how to get around this? Other than that I have a working solution!

Thanks

Pete

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Peter Morris
  • 20,174
  • 9
  • 81
  • 146
  • Hi Pete, to your update (understanding that you probably addressed this over a month ago:) ) - are you setting the "Restore.ReplaceDatabase" property to True? – Tao Nov 18 '09 at 17:48

4 Answers4

8

A Solution with Database Backup:

1) Ensure 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 Details: http://msdn.microsoft.com/en-us/library/ms186865.aspx

Arthur
  • 7,939
  • 3
  • 29
  • 46
1

You can use SQL Server Management Objects (SMO). Check out http://msdn.microsoft.com/en-us/library/ms162169.aspx. Information specifically on backing up a database is at http://technet.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.backup.aspx.

TLiebe
  • 7,913
  • 1
  • 23
  • 28
0

I understand your desire to do this in C# code, but I really think just calling a command line tool to do it for you would be easiest.

That said, this guy has a script that looks like it would do what you want. You could probably wrap that up in a SPROC and call it easily enough.

Eric Petroelje
  • 59,820
  • 9
  • 127
  • 177
0

I think you need to look into the

SqlServer.Management.Smo.Backup  class

see this technet article also, this article could be a reference, depending on your specific needs.

mjv
  • 73,152
  • 14
  • 113
  • 156
  • I'm trying this approach but unable to restore the backup to a new DB. Any ideas? (Check out the updated question text). – Peter Morris Oct 01 '09 at 13:59
  • The error message shown seems to indicate that the ReplaceDatabase property of the [SqlServer.Management.Smo.]Restore object is not set to True; CY doesn't match the database defined in the backup, and therefore SQL sends this message (better that way... one may have typo-ed the dbname) By explicitly setting ReplaceDatabase on tells SQL that it is ok to overwrite the existing db. – mjv Oct 01 '09 at 15:02
  • BTW, I assumed this was in the context of a 'fancier' utility that would automate this backup process (with progress bars, more specific error handling etc.) For a simpler approach, T-SQL exposes much of the backup/restore feature, as shown in Arthur's response. You can call such TSQL statements, by way of ADO or other and avoid dealing with SMO's object model. – mjv Oct 01 '09 at 15:29