[dba-SQLServer] Backing up

Hi David:

Quite brilliant! I will definitely try out your code as I am trying to get
up to speed in .NET....

But the question is can you backup to a remote drive using just the built in
MS SQL backup procedures?

Jim

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of
dmcafee at pacbell.net
Sent: Thursday, July 21, 2005 12:35 PM
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [dba-SQLServer] Backing up

I wrote a VB.NET app that copies it over between servers:

    Public Shared Sub MoveBackUp()
        Dim path As String = "\\server1\c$\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\MachinesLive.Bak"
        Dim path2 As String = "\\server2\c$\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\MachinesLive.Bak"
        If File.Exists(path) Then
            If File.Exists(path2) Then
                ' Ensure that the target file does not exist, since this is
disallowed.
                File.Delete(path2)
            End If
            ' Copy the file.
            File.Copy(path, path2, True)
        Else
            MsgBox(path + " does not exist.", , "Cannot copy file!")
        End If
    End Sub


I created a job on the live server that backs up a db:

BACKUP DATABASE [MACHINES] TO  DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\MachinesLive.Bak' WITH  INIT ,  NOUNLOAD ,  NAME =
N'MACHINES backup',  NOSKIP ,  STATS = 10,  NOFORMAT

then a few minutes later a service on my computer runs the VB.Net app which
copies the file from the live server to the dev server

A few minutes after that I runa job on the dev server which restores the db:

(I first clear the users:)
DECLARE @dbname as VARCHAR(50)
SET @dbname = 'MACHINES'

DECLARE @strSQL varchar(255)
PRINT 'Killing Users'
PRINT '-----------------'

CREATE table #tmpUsers(
						spid int,
						eid int,
						status varchar(30),
						loginname varchar(50),
						hostname varchar(50),
						blk int,
						dbname varchar(50),
						cmd varchar(30))

INSERT INTO #tmpUsers EXEC SP_WHO

DECLARE LoginCursor CURSOR
READ_ONLY
FOR SELECT spid, dbname FROM #tmpUsers WHERE dbname = @dbname

DECLARE @spid varchar(10)
DECLARE @dbname2 varchar(40)
OPEN LoginCursor

FETCH NEXT FROM LoginCursor INTO @spid, @dbname2
WHILE (@@fetch_status <> -1)
BEGIN
	IF (@@fetch_status <> -2)
	BEGIN
		PRINT 'Killing ' + @spid
		SET @strSQL = 'KILL ' + @spid
		EXEC (@strSQL)
	END
FETCH NEXT FROM LoginCursor INTO  @spid, @dbname2
END

CLOSE LoginCursor
DEALLOCATE LoginCursor
DROP table #tmpUsers
GO


then I restore:
RESTORE DATABASE Machines
   FROM DISK =  'c:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\MachinesLive.Bak'


If you simply wanted to store or back up the db, you could always zip up the
file and copy that somewhere.

HTH
David McAfee


-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of Jim
Lawrence
Sent: Thursday, July 21, 2005 12:01 PM
To: dba-sqlserver at databaseadvisors.com
Subject: [dba-SQLServer] Backing up


Hi All:

This is a simple question I am asking.

For years when backing up MS SQL databases I have either relied on an
external automated server based backup system or backed up to a local tape
drive.

For the first time I was hoping to use the MS SQL backup system to backup to
a special large capacity drive on the network. When attempting to select the
appropriate backup device from the EM backup options there is only shown
'local' shares? How do I make a backup from the MS SQL that will backup to a
remote drive? (..and subsequently restoring.)

Am I missing something obvious?

TIA
Jim

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com