Re: Multiple Database or Full Backup

From: Hari (hari_prasad_k_at_hotmail.com)
Date: 03/26/04 

Date: Fri, 26 Mar 2004 09:20:16 +0530

Hi,

Using a automated script you can

1. Backup all the user databases remotely to Laptop
2. After the backup restore it in Laptop ( You might need to start all the
databases in sinle_user using Alter database statement)

I have a script whckh will Backup remotely, please add the restore portion
in the script

Prerequisites

  1.. SQL server and SQL Server Agent should be configured to start in
Domain Account
  2.. This Domain account should have change privileges to add files to the
Remote machine
Script

CREATE PROCEDURE BACKUP_SP @UNCPATH VARCHAR(200) AS
BEGIN
SET NOCOUNT ON
DECLARE @NAME VARCHAR(100),
DECLARE @DBNAME VARCHAR(100)
DECLARE BACKUP_CUR CURSOR FOR
SELECT name FROM master..Sysdatabases where name not in
('model','pubs','tempdb','northwind')

OPEN BACKUP_CUR
FETCH NEXT FROM BACKUP_CUR INTO @DBNAME

WHILE @@FETCH_STATUS=0

 BEGIN

     SELECT

     NAME=@UNCPATH+@DBNAME+'_'+ltrim (rtrim (convert (char,
getdate(),105)))+'Dump.bak'
      BACKUP DATABASE @DBNAME TO DISK = @NAME WITH INIT , NOUNLOAD ,
NAME

      = @DBNAME, NOSKIP, STATS = 10, NOFORMAT
      FETCH NEXT FROM BACKUP_CUR INTO @DBNAME

 END

CLOSE BACKUP_CUR
DEALLOCATE BACKUP_CUR
END

How to Execute
This procedure will take @UNCPATH as the input parameter, Say you have to
backup the database to machine BACKUPSERVER in to share SQLBACKUP then the
execution will be

EXEC BACKUP_SP '\\BACKUPSERVER\SQLBACKUP\'

This will backup all the databases to the SQLBACKUP folder in BACKUPSERVER.

Thanks
Hari
MCDBA

"Top Cat" <anonymous@discussions.microsoft.com> wrote in message
news:efbd01c412b1$8bc40fd0$a001280a@phx.gbl...
> I can! In fact I am doing just that as I write this. I
> had hoped there would be a way of doing a full backup and
> a full restore bring over security intact, etc., but I
> hearing there isn't. Seems rather manual this way!
>
> Thanks!!
>
>
> >-----Original Message-----
> >can you take SQL off line, copy database files to new
> server and attach
> >databases?
> >
> >"Top Cat" <anonymous@discussions.microsoft.com> wrote in
> message
> >news:10b9501c412ad$961d95e0$a601280a@phx.gbl...
> >> I can't see a way of backing up all databases at once,
> or
> >> the ability to choose multiple databases to back up. I
> am
> >> in the process of moving a bunch of databases from one
> >> laptop to another and I would rather not do it one at a
> >> time.
> >>
> >> Ideas?
> >>
> >> Thanks in advance!!
> >
> >
> >.
> >

Relevant Pages

  • Re: POINT IN TIME RESTORE
    ... This is what you should have done in order to do the restore as you wish: ... > 3- erase data ... > 5- restore database with norecovery ... The BACKUP DATABAE can of course be at an earlier point in time, ...
    (microsoft.public.sqlserver.server)
  • RE: How to restore edb & stm files from bkf backup file on another exc
    ... copied to a different storage group on the same server or to a different ... On production server successfully backup information ... Restore Status ... Failed to find a database to restore to from the Microsoft ...
    (microsoft.public.exchange.admin)
  • Re: Transaction Log ??
    ... we used to simplify our database restore process: ... every database got their respective backup folder. ... -- get logFileName, FirstLSN, LastLSN list ...
    (microsoft.public.sqlserver.security)
  • RE: How do I restore from mdf and ldf files?
    ... Tasks -> Backup Database ... Tasks -> Restore Database ... Windows 2003 Server with Latest Service Pack ... Pre-requisites for Sharepoint Backup and Restore: ...
    (microsoft.public.sharepoint.portalserver)
  • Backup and Restore Files
    ... What is the best way to backup and restore a runtime database file? ... pszDisplayName As String ...
    (microsoft.public.access.gettingstarted)