[dba-SQLServer] Backing up

Sort of makes you wish you could attach pseudo drives/directories like in
Unix/Linix.

Jim

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of David
Mcafee
Sent: Thursday, July 21, 2005 1:06 PM
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [dba-SQLServer] Backing up

Not that I know of, only local drives.

--- Jim Lawrence <accessd at shaw.ca> wrote:

> 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
> 
> _______________________________________________
> 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