CODEWITHSUNDEEP
c#sql-server
Peter Morris
Peter Morris

Reputation: 23264

Copy SQL Server database in code

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

Upvotes: 3

Views: 8089

Answers (4)

Arthur
Arthur

Reputation: 8129

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

Upvotes: 8

TLiebe
TLiebe

Reputation: 7996

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.

Upvotes: 1

mjv
mjv

Reputation: 75288

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.

Upvotes: 0

Eric Petroelje
Eric Petroelje

Reputation: 60559

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.

Upvotes: 0

Related Questions