The best way to backup changing Database

[Michael Lugassy]

I'm hosting a mission critical Database on a remote SQL 7 NT system.
What is the best method to backup the database (or import the information)
daily
to our local computer (sql 2000)?

I tried to use DTS package scheduly, but this works only for 1 table.
I need a DIFFRENT table for each day, with a unique name:

something like: backupdb041201 -> (db backup as for 04/12/2001)

Any solutions?

Thanks alot!!

-=Michael.

[Andrew Prosser]

"Michael Lugassy" <michaell@imvamp.com> wrote:
>
>I'm hosting a mission critical Database on a remote SQL 7 NT system.
>What is the best method to backup the database (or import the information)
>daily
>to our local computer (sql 2000)?
>
>I tried to use DTS package scheduly, but this works only for 1 table.
>I need a DIFFRENT table for each day, with a unique name:
>
>something like: backupdb041201 -> (db backup as for 04/12/2001)
>
>Any solutions?
>
>Thanks alot!!
>

If you create a networked share on your SQL Server (must be started with
a valid NT account), you can run this procedure daily which will backup all
your databases to the networked drive;

USE master
GO

IF EXISTS (SELECT * FROM sysobjects
WHERE id = OBJECT_ID(N'[dbo].[sp__backup_to_network]')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[sp__backup_to_network]
GO

CREATE PROCEDURE sp__backup_to_network (@directory VARCHAR(100))
AS
/****************************************************************************/
/* Stored Procedure: sp__backup_to_network
*/
/* Creation Date: 2001/02/20
*/
/* Copyright: AJP IT Solutions
*/
/* Written by: Andrew Prosser
*/
/*
*/
/* Purpose: This procedure performs the databases dumps for all databases
*/
/* on your system.
*/
/*
*/
/* Input Parameters:
*/
/* @directory Name of the directory to use
*/
/* @media Type of dump media (Used for dump option settings) */
/*
*/
/* Output Parameters: None
*/
/*
*/
/* Return Status: None
*/
/*
*/
/* Usage:
*/
/* exec sp__backup_to_network <directory name>
*/
/* ex: exec sp__backup_to_network '\\DBLONHA02\DBFS\backup_UKLONSQL11'
*/
/*
*/
/* Local Variables:
*/
/* @dbname Database being dumped */
/* @command Dump command to be processed */
/*
*/
/* Called By:
*/
/* SQL Server task */
/*
*/
/* Calls: None
*/
/*
*/
/* Data Modifications: None
*/
/*
*/
/* Updates:
*/
/* Date Author Purpose
*/
/*
*/
/****************************************************************************/

DECLARE @startdate DATETIME,
@path VARCHAR(100),
@dbname VARCHAR(30),
@command VARCHAR(255),
@devname VARCHAR(150),
@status TINYINT,
@fileexist INT,
@errors TINYINT

IF SUBSTRING(@directory, (DATALENGTH(@directory)), 1) != '\'
SELECT @path = @directory + '\'
ELSE
SELECT @path = @directory

SELECT @command = 'if not exist ' + @path + ' dir ' + @path

EXEC @status = master..xp_cmdshell @command, no_output
IF @status != 0
BEGIN
PRINT 'Program Terminating: Invalid Backup Directory Specified ' + @path

RETURN 1
END

SELECT @startdate = GETDATE(), @errors=0
PRINT 'Backups started at ' + RTRIM(CONVERT(varchar(30), GETDATE()))

--Declare cursor for all databases except tempdb and master
--Note: if you want to exclude any other databases from your backups, add
them to the where clause
DECLARE curdb CURSOR FOR SELECT name FROM master..sysdatabases
WHERE name NOT IN ('Northwind', 'tempdb', 'pubs') FOR READ ONLY

--Open and perform initial fetch
OPEN curdb
FETCH curdb INTO @dbname

--While there are databases to process, dump the database
WHILE @@fetch_status = 0
BEGIN
--Create dump database command
SELECT @devname = @path + @@SERVERNAME + '_' + @dbname + '_' + DATENAME(weekday,
GETDATE()) + '.dmp'
SELECT @command = 'BACKUP DATABASE ' + @dbname + ' TO DISK = "' + @devname
+ '" WITH INIT'
--Execute dump
EXEC (@command)
-- print @command
-- See if backup file exists after the backup
EXEC master..xp_fileexist @devname, @fileexist OUTPUT
IF @fileexist = 0
BEGIN
PRINT 'Program Error: Backup Not Created ' + @devname
SELECT @errors=1
END
--Get the next database to process
FETCH curdb INTO @dbname
END

--Clean up
CLOSE curdb
DEALLOCATE curdb

PRINT 'Backups finished at ' + RTRIM(CONVERT(varchar(30), GETDATE()))
PRINT 'Backup duration (minutes) = ' + RTRIM(CONVERT(varchar(30), (DATEDIFF(mi,
@startdate, GETDATE()))))

IF @errors=1
RETURN 1

RETURN 0
GO
>-=Michael.

[Michael Lugassy]

Dear Andrew,
The problem is that I don't have admin right on the mission critical server/db.
I need to fetch the information using microsoft sql client tools.
Maybe you can help in this solution:

1. I'll schedule the backup for everyday on 12:00 (backing up the entire
db and naming it backup)
2. I'll run a schuedled SQL script (LOCALLY) that will only change the table
name.

how can i do that?

Thanks alot!!!

My GENIuS!


"Andrew Prosser" <andrew_prosser@bigfoot.com> wrote:
>
>"Michael Lugassy" <michaell@imvamp.com> wrote:
>>
>>I'm hosting a mission critical Database on a remote SQL 7 NT system.
>>What is the best method to backup the database (or import the information)
>>daily
>>to our local computer (sql 2000)?
>>
>>I tried to use DTS package scheduly, but this works only for 1 table.
>>I need a DIFFRENT table for each day, with a unique name:
>>
>>something like: backupdb041201 -> (db backup as for 04/12/2001)
>>
>>Any solutions?
>>
>>Thanks alot!!
>>
>
>If you create a networked share on your SQL Server (must be started with
>a valid NT account), you can run this procedure daily which will backup
all
>your databases to the networked drive;
>
>USE master
>GO
>
>IF EXISTS (SELECT * FROM sysobjects
> WHERE id = OBJECT_ID(N'[dbo].[sp__backup_to_network]')
> AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
> DROP PROCEDURE [dbo].[sp__backup_to_network]
>GO
>
>CREATE PROCEDURE sp__backup_to_network (@directory VARCHAR(100))
>AS
>/****************************************************************************/
>/* Stored Procedure: sp__backup_to_network

> */
>/* Creation Date: 2001/02/20

> */
>/* Copyright: AJP IT Solutions

> */
>/* Written by: Andrew Prosser

> */
>/*

> */
>/* Purpose: This procedure performs the databases dumps for all databases
> */
>/* on your system.

> */
>/*

> */
>/* Input Parameters:

> */
>/* @directory Name of the directory to use

> */
>/* @media Type of dump media (Used for dump option settings) */
>/*

> */
>/* Output Parameters: None

> */
>/*

> */
>/* Return Status: None

> */
>/*

> */
>/* Usage:

> */
>/* exec sp__backup_to_network <directory name>

> */
>/* ex: exec sp__backup_to_network '\\DBLONHA02\DBFS\backup_UKLONSQL11'
>*/
>/*

> */
>/* Local Variables:

> */
>/* @dbname Database being dumped */
>/* @command Dump command to be processed */
>/*

> */
>/* Called By:

> */
>/* SQL Server task */
>/*

> */
>/* Calls: None

> */
>/*

> */
>/* Data Modifications: None

> */
>/*

> */
>/* Updates:

> */
>/* Date Author Purpose

> */
>/*

> */
>/****************************************************************************/
>
>DECLARE @startdate DATETIME,
> @path VARCHAR(100),
> @dbname VARCHAR(30),
> @command VARCHAR(255),
> @devname VARCHAR(150),
> @status TINYINT,
> @fileexist INT,
> @errors TINYINT
>
>IF SUBSTRING(@directory, (DATALENGTH(@directory)), 1) != '\'
> SELECT @path = @directory + '\'
>ELSE
> SELECT @path = @directory
>
>SELECT @command = 'if not exist ' + @path + ' dir ' + @path
>
>EXEC @status = master..xp_cmdshell @command, no_output
>IF @status != 0
>BEGIN
> PRINT 'Program Terminating: Invalid Backup Directory Specified ' + @path
>
> RETURN 1
>END
>
>SELECT @startdate = GETDATE(), @errors=0
>PRINT 'Backups started at ' + RTRIM(CONVERT(varchar(30), GETDATE()))
>
>--Declare cursor for all databases except tempdb and master
>--Note: if you want to exclude any other databases from your backups, add
>them to the where clause
>DECLARE curdb CURSOR FOR SELECT name FROM master..sysdatabases
> WHERE name NOT IN ('Northwind', 'tempdb', 'pubs') FOR READ ONLY
>
>--Open and perform initial fetch
>OPEN curdb
>FETCH curdb INTO @dbname
>
>--While there are databases to process, dump the database
>WHILE @@fetch_status = 0
>BEGIN
> --Create dump database command
> SELECT @devname = @path + @@SERVERNAME + '_' + @dbname + '_' + DATENAME(weekday,
>GETDATE()) + '.dmp'
> SELECT @command = 'BACKUP DATABASE ' + @dbname + ' TO DISK = "' + @devname
>+ '" WITH INIT'
> --Execute dump
> EXEC (@command)
> -- print @command
> -- See if backup file exists after the backup
> EXEC master..xp_fileexist @devname, @fileexist OUTPUT
> IF @fileexist = 0
> BEGIN
> PRINT 'Program Error: Backup Not Created ' + @devname
> SELECT @errors=1
> END
> --Get the next database to process
> FETCH curdb INTO @dbname
>END
>
>--Clean up
>CLOSE curdb
>DEALLOCATE curdb
>
>PRINT 'Backups finished at ' + RTRIM(CONVERT(varchar(30), GETDATE()))
>PRINT 'Backup duration (minutes) = ' + RTRIM(CONVERT(varchar(30), (DATEDIFF(mi,
>@startdate, GETDATE()))))
>
>IF @errors=1
> RETURN 1
>
>RETURN 0
>GO
>>-=Michael.
>

[Andrew Prosser]

I am not sure what you are asking really, if you can do a backup, you could
probably also create a share on the server;

xp_cmdshell "net share=c:\backup"

Run that stored procedure to the share and then use that with the procedure
I supplied.

"Michael Lugassy" <michaell@imvamp.com> wrote:
>
>Dear Andrew,
>The problem is that I don't have admin right on the mission critical server/db.
>I need to fetch the information using microsoft sql client tools.
>Maybe you can help in this solution:
>
>1. I'll schedule the backup for everyday on 12:00 (backing up the entire
>db and naming it backup)
>2. I'll run a schuedled SQL script (LOCALLY) that will only change the table
>name.
>
>how can i do that?
>
>Thanks alot!!!
>
>My GENIuS!
>
>
>"Andrew Prosser" <andrew_prosser@bigfoot.com> wrote:
>>
>>"Michael Lugassy" <michaell@imvamp.com> wrote:
>>>
>>>I'm hosting a mission critical Database on a remote SQL 7 NT system.
>>>What is the best method to backup the database (or import the information)
>>>daily
>>>to our local computer (sql 2000)?
>>>
>>>I tried to use DTS package scheduly, but this works only for 1 table.
>>>I need a DIFFRENT table for each day, with a unique name:
>>>
>>>something like: backupdb041201 -> (db backup as for 04/12/2001)
>>>
>>>Any solutions?
>>>
>>>Thanks alot!!
>>>
>>
>>If you create a networked share on your SQL Server (must be started with
>>a valid NT account), you can run this procedure daily which will backup
>all
>>your databases to the networked drive;
>>
>>USE master
>>GO
>>
>>IF EXISTS (SELECT * FROM sysobjects
>> WHERE id = OBJECT_ID(N'[dbo].[sp__backup_to_network]')
>> AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
>> DROP PROCEDURE [dbo].[sp__backup_to_network]
>>GO
>>
>>CREATE PROCEDURE sp__backup_to_network (@directory VARCHAR(100))
>>AS
>>/****************************************************************************/
>>/* Stored Procedure: sp__backup_to_network

>
>> */
>>/* Creation Date: 2001/02/20

>
>> */
>>/* Copyright: AJP IT Solutions

>
>> */
>>/* Written by: Andrew Prosser

>
>> */
>>/*

>
>> */
>>/* Purpose: This procedure performs the databases dumps for all databases
>> */
>>/* on your system.

>
>> */
>>/*

>
>> */
>>/* Input Parameters:

>
>> */
>>/* @directory Name of the directory to use

>
>> */
>>/* @media Type of dump media (Used for dump option settings) */
>>/*

>
>> */
>>/* Output Parameters: None

>
>> */
>>/*

>
>> */
>>/* Return Status: None

>
>> */
>>/*

>
>> */
>>/* Usage:

>
>> */
>>/* exec sp__backup_to_network <directory name>

>
>> */
>>/* ex: exec sp__backup_to_network '\\DBLONHA02\DBFS\backup_UKLONSQL11'
>>*/
>>/*

>
>> */
>>/* Local Variables:

>
>> */
>>/* @dbname Database being dumped */
>>/* @command Dump command to be processed */
>>/*

>
>> */
>>/* Called By:

>
>> */
>>/* SQL Server task */
>>/*

>
>> */
>>/* Calls: None

>
>> */
>>/*

>
>> */
>>/* Data Modifications: None

>
>> */
>>/*

>
>> */
>>/* Updates:

>
>> */
>>/* Date Author Purpose

>
>> */
>>/*

>
>> */
>>/****************************************************************************/
>>
>>DECLARE @startdate DATETIME,
>> @path VARCHAR(100),
>> @dbname VARCHAR(30),
>> @command VARCHAR(255),
>> @devname VARCHAR(150),
>> @status TINYINT,
>> @fileexist INT,
>> @errors TINYINT
>>
>>IF SUBSTRING(@directory, (DATALENGTH(@directory)), 1) != '\'
>> SELECT @path = @directory + '\'
>>ELSE
>> SELECT @path = @directory
>>
>>SELECT @command = 'if not exist ' + @path + ' dir ' + @path
>>
>>EXEC @status = master..xp_cmdshell @command, no_output
>>IF @status != 0
>>BEGIN
>> PRINT 'Program Terminating: Invalid Backup Directory Specified ' + @path
>>
>> RETURN 1
>>END
>>
>>SELECT @startdate = GETDATE(), @errors=0
>>PRINT 'Backups started at ' + RTRIM(CONVERT(varchar(30), GETDATE()))
>>
>>--Declare cursor for all databases except tempdb and master
>>--Note: if you want to exclude any other databases from your backups, add
>>them to the where clause
>>DECLARE curdb CURSOR FOR SELECT name FROM master..sysdatabases
>> WHERE name NOT IN ('Northwind', 'tempdb', 'pubs') FOR READ ONLY
>>
>>--Open and perform initial fetch
>>OPEN curdb
>>FETCH curdb INTO @dbname
>>
>>--While there are databases to process, dump the database
>>WHILE @@fetch_status = 0
>>BEGIN
>> --Create dump database command
>> SELECT @devname = @path + @@SERVERNAME + '_' + @dbname + '_' + DATENAME(weekday,
>>GETDATE()) + '.dmp'
>> SELECT @command = 'BACKUP DATABASE ' + @dbname + ' TO DISK = "' + @devname
>>+ '" WITH INIT'
>> --Execute dump
>> EXEC (@command)
>> -- print @command
>> -- See if backup file exists after the backup
>> EXEC master..xp_fileexist @devname, @fileexist OUTPUT
>> IF @fileexist = 0
>> BEGIN
>> PRINT 'Program Error: Backup Not Created ' + @devname
>> SELECT @errors=1
>> END
>> --Get the next database to process
>> FETCH curdb INTO @dbname
>>END
>>
>>--Clean up
>>CLOSE curdb
>>DEALLOCATE curdb
>>
>>PRINT 'Backups finished at ' + RTRIM(CONVERT(varchar(30), GETDATE()))
>>PRINT 'Backup duration (minutes) = ' + RTRIM(CONVERT(varchar(30), (DATEDIFF(mi,
>>@startdate, GETDATE()))))
>>
>>IF @errors=1
>> RETURN 1
>>
>>RETURN 0
>>GO
>>>-=Michael.
>>
>