Backup and restore in C# using t-sql

Question

i am running a stored proc from c# that backs up my database:

USE [cxdsdf]
GO
/****** Object:  StoredProcedure [dbo].[usp_BACKUP]    Script Date: 08/17/2012 13:25:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROC [dbo].[usp_BACKUP]
 @SAVEPATH nvarchar(500)
AS
BACKUP DATABASE [cxdsdf]
TO DISK = @SAVEPATH

I then try to restore it using the following stored procs:

ALTER PROC [dbo].[usp_GETLOGICALNames]
 
 @RESTOREPATH nvarchar(500)
AS
RESTORE FILELISTONLY FROM DISK = @RESTOREPATH

USE [cxdsdf]
GO
/****** Object:  StoredProcedure [dbo].[usp_GETDATABASEPath]    Script Date: 08/17/2012 13:28:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROC [dbo].[usp_GETDATABASEPath]
AS
SELECT SUBSTRING(physical_name, 1,
CHARINDEX(N'master.mdf',
LOWER(physical_name)) - 1) DataFileLocation
FROM master.sys.master_files
WHERE database_id = 1 AND FILE_ID = 1

Then i restore using this stored proc.

  ALTER PROC [dbo].[usp_RESTOREDatabase]
 @RESTORELOGICALMDFNAME nvarchar(500),
 @RESTORELOGICALLDFNAME nvarchar(500),
 @RESTOREPATH nvarchar(500),
 @RESTOREDATABASENAME nvarchar(500),
 @RESTORETODATABASEPATH nvarchar(500),
 @RESTORETOLOGPATH nvarchar(500)
AS
RESTORE FILELISTONLY FROM DISK = @RESTOREPATH
RESTORE DATABASE @RESTOREDATABASENAME FROM  DISK = @RESTOREPATH WITH  FILE = 1,  MOVE @RESTORELOGICALMDFNAME TO  @RESTORETODATABASEPATH,
MOVE @RESTORELOGICALLDFNAME TO @RESTORETOLOGPATH, NOUNLOAD,  STATS = 10

this works fine as long i restore onto the sql server instance the backup came from, however i need to be able to restore the database onto different instances.   Any ideas

Answer 1

Your not limited by the instances or servers you connect to. The only thing you need to ensure is that you specify the path to your files correctly as they are local to the instance.

Answer 2

Never ever do any maintenance activities from outside SQL. First of all, you will not have access. Even you have, do not miss use. Any break in communication would end up you in a bad situation. I cant think of out of my head the other consequences. But my suggestion is not to do so.

---

Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

Answer 3

You should only run a SQL Server Agent job from a program related to maintenance:

http://www.sqlusa.com/bestpractices2005/startjobfromstoredproc/

One of the many reasons: you get nice job history reports.

---

Kalman Toth SQL SERVER 2012 & BI TRAINING