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

Improve this question
1
  • Hi Pete, to your update (understanding that you probably addressed this over a month ago:) ) - are you setting the "Restore.ReplaceDatabase" property to True? Commented Nov 18, 2009 at 17:48

4 Answers 4

Reset to default
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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

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.

Improve this answer

Comments

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.

Improve this answer

Comments

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.

Improve this answer

3 Comments

I'm trying this approach but unable to restore the backup to a new DB. Any ideas? (Check out the updated question text).
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.
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.