automatic restoration

any automated restoration script is available in sql server2000.
I want to do the restoration for 10 databases(full backup,latest differential backup) in other server.

Pls do the needful.

Quote:

Originally Posted by bharadwaj

any automated restoration script is available in sql server2000.
I want to do the restoration for 10 databases(full backup,latest differential backup) in other server.

Pls do the needful.

Try the following

use master
go
declare @.file varchar(200)
select @.file = 'c:\dbackup.backup'

declare @.mdf varchar(500), @.ldf varchar(500)
select @.mdf = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\databasename.mdf'
select @.ldf = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\databasename_log.ldf'

restore database <databasename>
from disk = @.file
with
move 'databasename' to @.mdf,
move 'databasename_Log' to @.ldf
go|||Thanks for ur reply.I will try it and let you know.
Bharat

Quote:

Originally Posted by Vidhura

Try the following

use master
go
declare @.file varchar(200)
select @.file = 'c:\dbackup.backup'

declare @.mdf varchar(500), @.ldf varchar(500)
select @.mdf = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\databasename.mdf'
select @.ldf = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\databasename_log.ldf'

restore database <databasename>
from disk = @.file
with
move 'databasename' to @.mdf,
move 'databasename_Log' to @.ldf
go

sql

AutoMatic Configuration Differential Backup

hello every one
i am using Microsoft SQL Server 2000
Enterprise Edition and running four data bases on
server.All on Local Areal Network the size of every data
bases is 1 GB and changing occour every Second and minute
so i want to implement Differential Backup after every One
Hours using SQL Server agent and schedule task can any
body guide me properly and any tutorial like web site that
help me step by step ......Documentation etc...
Second i also want replicated to another machine on LAN
thank
IrfanIn SQL Enterprise Manager, select SQL Agent->jobs, New job
Give the job a name and go to steps -> new Step... Choose t-sql step...
The command should be
backup database yourdbname to disk =
'\\servername\sharename\backupfilename.bkp' with init,differential
OK this and add similar steps for each of the other databases.
Then go to schedule and choose a schedule for this to run...
Save it and see if it works...
More details can be found in SQL Server Books On Line...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Irfan" <mirfanaslam@.hotmail.com> wrote in message
news:0ca401c48b8c$9c5f9a90$a301280a@.phx.gbl...
> hello every one
> i am using Microsoft SQL Server 2000
> Enterprise Edition and running four data bases on
> server.All on Local Areal Network the size of every data
> bases is 1 GB and changing occour every Second and minute
> so i want to implement Differential Backup after every One
> Hours using SQL Server agent and schedule task can any
> body guide me properly and any tutorial like web site that
> help me step by step ......Documentation etc...
> Second i also want replicated to another machine on LAN
> thank
> Irfan
>

AutoMatic Configuration Differential Backup

hello every one
i am using Microsoft SQL Server 2000
Enterprise Edition and running four data bases on
server.All on Local Areal Network the size of every data
bases is 1 GB and changing occour every Second and minute
so i want to implement Differential Backup after every One
Hours using SQL Server agent and schedule task can any
body guide me properly and any tutorial like web site that
help me step by step ......Documentation etc...
Second i also want replicated to another machine on LAN
thank
Irfan
In SQL Enterprise Manager, select SQL Agent->jobs, New job
Give the job a name and go to steps -> new Step... Choose t-sql step...
The command should be
backup database yourdbname to disk =
'\\servername\sharename\backupfilename.bkp' with init,differential
OK this and add similar steps for each of the other databases.
Then go to schedule and choose a schedule for this to run...
Save it and see if it works...
More details can be found in SQL Server Books On Line...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Irfan" <mirfanaslam@.hotmail.com> wrote in message
news:0ca401c48b8c$9c5f9a90$a301280a@.phx.gbl...
> hello every one
> i am using Microsoft SQL Server 2000
> Enterprise Edition and running four data bases on
> server.All on Local Areal Network the size of every data
> bases is 1 GB and changing occour every Second and minute
> so i want to implement Differential Backup after every One
> Hours using SQL Server agent and schedule task can any
> body guide me properly and any tutorial like web site that
> help me step by step ......Documentation etc...
> Second i also want replicated to another machine on LAN
> thank
> Irfan
>

AutoMatic Configuration Differential Backup

hello every one
i am using Microsoft SQL Server 2000
Enterprise Edition and running four data bases on
server.All on Local Areal Network the size of every data
bases is 1 GB and changing occour every Second and minute
so i want to implement Differential Backup after every One
Hours using SQL Server agent and schedule task can any
body guide me properly and any tutorial like web site that
help me step by step ......Documentation etc...
Second i also want replicated to another machine on LAN
thank
IrfanIn SQL Enterprise Manager, select SQL Agent->jobs, New job
Give the job a name and go to steps -> new Step... Choose t-sql step...
The command should be
backup database yourdbname to disk ='\\servername\sharename\backupfilename.bkp' with init,differential
OK this and add similar steps for each of the other databases.
Then go to schedule and choose a schedule for this to run...
Save it and see if it works...
More details can be found in SQL Server Books On Line...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Irfan" <mirfanaslam@.hotmail.com> wrote in message
news:0ca401c48b8c$9c5f9a90$a301280a@.phx.gbl...
> hello every one
> i am using Microsoft SQL Server 2000
> Enterprise Edition and running four data bases on
> server.All on Local Areal Network the size of every data
> bases is 1 GB and changing occour every Second and minute
> so i want to implement Differential Backup after every One
> Hours using SQL Server agent and schedule task can any
> body guide me properly and any tutorial like web site that
> help me step by step ......Documentation etc...
> Second i also want replicated to another machine on LAN
> thank
> Irfan
>

Automate backup method

Dear All,
I plan my backup strategy during the week as follow:
Mon | Differential database backup
Tue | Differential database backup
Wed | Differential database backup
Thu | Differential database backup
Fri | Differential database backup
Sat | Differential database backup
Sun | Full database backup
All days Transaction log backups four times.
I want the backup files will follow the format below:
dbname_backuptype_YYYYMMDDHHMM.bak
Does anyone knows how to automate this task?
Thanks
Robert LieHi,
Compile the below stored procedure in Master database and define the folder
in which backup needs to be taken. schedule this procedure using SQL
Agent -- Jobs. This procedure will backup all the databases with a unique
name place it in the folder your are passing.
Unique name will be: SERVERNAME_DBNAME_DD_MM_YYYY_D_UMP.BAK
Script to Backup all databases
--
CREATE PROCEDURE BACKUP_SP @.Folder VARCHAR(100)
AS
begin
DECLARE @.NAME VARCHAR(100),
@.DBNAME VARCHAR(100)
DECLARE BACKUP_CUR CURSOR FOR
SELECT name FROM sysdatabases where name not
in('model','pubs','tempdb','no_rthwind')
OPEN BACKUP_CUR
FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
WHILE @.@.FETCH_STATUS=0
BEGIN
SELECT
@.NAME=ltrim(rtrim(@.folder))+@.@._SERVERNAM
E+'_'+@.DBNAME+'_'+ltr_im(rtrim(conve
rt
(char,getdate(),105)))+'Dump.b_ak'
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 schedule
--
Enterprise Manager - Management -- SQL Agent -- Jobs -- Right click and
create new job.
Give a name to the Job and in Job step menthon this procedure with
foldername as parameter and scdule the job to be executed based on
requirement
Note:
You could change the script to do a differential or log backup.
--
Thanks
Hari
SQL Server MVP
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:%230FZVEJeFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Dear All,
> I plan my backup strategy during the week as follow:
> Mon | Differential database backup
> Tue | Differential database backup
> Wed | Differential database backup
> Thu | Differential database backup
> Fri | Differential database backup
> Sat | Differential database backup
> Sun | Full database backup
> All days Transaction log backups four times.
> I want the backup files will follow the format below:
> dbname_backuptype_YYYYMMDDHHMM.bak
>
> Does anyone knows how to automate this task?
> Thanks
> Robert Lie|||Hi there, search for RE: Backup schedule (Differential backups) in this
group.
Andrew J. Kelly was kind to provide the solution.
"Hari Prasad" wrote:

> Hi,
> Compile the below stored procedure in Master database and define the folde
r
> in which backup needs to be taken. schedule this procedure using SQL
> Agent -- Jobs. This procedure will backup all the databases with a unique
> name place it in the folder your are passing.
>
> Unique name will be: SERVERNAME_DBNAME_DD_MM_YYYY_D_UMP.BAK
>
> Script to Backup all databases
> --
> CREATE PROCEDURE BACKUP_SP @.Folder VARCHAR(100)
> AS
> begin
> DECLARE @.NAME VARCHAR(100),
> @.DBNAME VARCHAR(100)
> DECLARE BACKUP_CUR CURSOR FOR
> SELECT name FROM sysdatabases where name not
> in('model','pubs','tempdb','no_rthwind'
)
> OPEN BACKUP_CUR
> FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
> WHILE @.@.FETCH_STATUS=0
> BEGIN
> SELECT
> @.NAME=ltrim(rtrim(@.folder))+@.@._SERVERNA
ME+'_'+@.DBNAME+'_'+ltr_im(rtrim(c
onvert
> (char,getdate(),105)))+'Dump.b_ak'
> 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 schedule
> --
> Enterprise Manager - Management -- SQL Agent -- Jobs -- Right click and
> create new job.
> Give a name to the Job and in Job step menthon this procedure with
> foldername as parameter and scdule the job to be executed based on
> requirement
> Note:
> You could change the script to do a differential or log backup.
> --
> Thanks
> Hari
> SQL Server MVP
>
> "Robert Lie" <robert.lie24@.gmail.com> wrote in message
> news:%230FZVEJeFHA.3032@.TK2MSFTNGP10.phx.gbl...
>
>

Automate backup method

Dear All,
I plan my backup strategy during the week as follow:
Mon | Differential database backup
Tue | Differential database backup
Wed | Differential database backup
Thu | Differential database backup
Fri | Differential database backup
Sat | Differential database backup
Sun | Full database backup
All days Transaction log backups four times.
I want the backup files will follow the format below:
dbname_backuptype_YYYYMMDDHHMM.bak
Does anyone knows how to automate this task?
Thanks
Robert Lie
Hi,
Compile the below stored procedure in Master database and define the folder
in which backup needs to be taken. schedule this procedure using SQL
Agent -- Jobs. This procedure will backup all the databases with a unique
name place it in the folder your are passing.
Unique name will be: SERVERNAME_DBNAME_DD_MM_YYYY_DXUMP.BAK
Script to Backup all databases
CREATE PROCEDURE BACKUP_SP @.Folder VARCHAR(100)
AS
begin
DECLARE @.NAME VARCHAR(100),
@.DBNAME VARCHAR(100)
DECLARE BACKUP_CUR CURSOR FOR
SELECT name FROM sysdatabases where name not
in('model','pubs','tempdb','noXrthwind')
OPEN BACKUP_CUR
FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
WHILE @.@.FETCH_STATUS=0
BEGIN
SELECT
@.NAME=ltrim(rtrim(@.folder))+@.@.XSERVERNAME+'_'+@.DBN AME+'_'+ltrXim(rtrim(convert
(char,getdate(),105)))+'Dump.bXak'
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 schedule
Enterprise Manager - Management -- SQL Agent -- Jobs -- Right click and
create new job.
Give a name to the Job and in Job step menthon this procedure with
foldername as parameter and scdule the job to be executed based on
requirement
Note:
You could change the script to do a differential or log backup.
Thanks
Hari
SQL Server MVP
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:%230FZVEJeFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Dear All,
> I plan my backup strategy during the week as follow:
> Mon | Differential database backup
> Tue | Differential database backup
> Wed | Differential database backup
> Thu | Differential database backup
> Fri | Differential database backup
> Sat | Differential database backup
> Sun | Full database backup
> All days Transaction log backups four times.
> I want the backup files will follow the format below:
> dbname_backuptype_YYYYMMDDHHMM.bak
>
> Does anyone knows how to automate this task?
> Thanks
> Robert Lie
|||Hi there, search for RE: Backup schedule (Differential backups) in this
group.
Andrew J. Kelly was kind to provide the solution.
"Hari Prasad" wrote:

> Hi,
> Compile the below stored procedure in Master database and define the folder
> in which backup needs to be taken. schedule this procedure using SQL
> Agent -- Jobs. This procedure will backup all the databases with a unique
> name place it in the folder your are passing.
>
> Unique name will be: SERVERNAME_DBNAME_DD_MM_YYYY_DXUMP.BAK
>
> Script to Backup all databases
> --
> CREATE PROCEDURE BACKUP_SP @.Folder VARCHAR(100)
> AS
> begin
> DECLARE @.NAME VARCHAR(100),
> @.DBNAME VARCHAR(100)
> DECLARE BACKUP_CUR CURSOR FOR
> SELECT name FROM sysdatabases where name not
> in('model','pubs','tempdb','noXrthwind')
> OPEN BACKUP_CUR
> FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
> WHILE @.@.FETCH_STATUS=0
> BEGIN
> SELECT
> @.NAME=ltrim(rtrim(@.folder))+@.@.XSERVERNAME+'_'+@.DB NAME+'_'+ltrXim(rtrim(convert
> (char,getdate(),105)))+'Dump.bXak'
> 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 schedule
> --
> Enterprise Manager - Management -- SQL Agent -- Jobs -- Right click and
> create new job.
> Give a name to the Job and in Job step menthon this procedure with
> foldername as parameter and scdule the job to be executed based on
> requirement
> Note:
> You could change the script to do a differential or log backup.
> --
> Thanks
> Hari
> SQL Server MVP
>
> "Robert Lie" <robert.lie24@.gmail.com> wrote in message
> news:%230FZVEJeFHA.3032@.TK2MSFTNGP10.phx.gbl...
>
>

Automate backup method

Dear All,
I plan my backup strategy during the week as follow:
Mon | Differential database backup
Tue | Differential database backup
Wed | Differential database backup
Thu | Differential database backup
Fri | Differential database backup
Sat | Differential database backup
Sun | Full database backup
All days Transaction log backups four times.
I want the backup files will follow the format below:
dbname_backuptype_YYYYMMDDHHMM.bak
Does anyone knows how to automate this task?
Thanks
Robert LieHi,
Compile the below stored procedure in Master database and define the folder
in which backup needs to be taken. schedule this procedure using SQL
Agent -- Jobs. This procedure will backup all the databases with a unique
name place it in the folder your are passing.
Unique name will be: SERVERNAME_DBNAME_DD_MM_YYYY_D­UMP.BAK
Script to Backup all databases
--
CREATE PROCEDURE BACKUP_SP @.Folder VARCHAR(100)
AS
begin
DECLARE @.NAME VARCHAR(100),
@.DBNAME VARCHAR(100)
DECLARE BACKUP_CUR CURSOR FOR
SELECT name FROM sysdatabases where name not
in('model','pubs','tempdb','no­rthwind')
OPEN BACKUP_CUR
FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
WHILE @.@.FETCH_STATUS=0
BEGIN
SELECT
@.NAME=ltrim(rtrim(@.folder))+@.@.­SERVERNAME+'_'+@.DBNAME+'_'+ltr­im(rtrim(convert
(char,getdate(),105)))+'Dump.b­ak'
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 schedule
--
Enterprise Manager - Management -- SQL Agent -- Jobs -- Right click and
create new job.
Give a name to the Job and in Job step menthon this procedure with
foldername as parameter and scdule the job to be executed based on
requirement
Note:
You could change the script to do a differential or log backup.
--
Thanks
Hari
SQL Server MVP
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:%230FZVEJeFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Dear All,
> I plan my backup strategy during the week as follow:
> Mon | Differential database backup
> Tue | Differential database backup
> Wed | Differential database backup
> Thu | Differential database backup
> Fri | Differential database backup
> Sat | Differential database backup
> Sun | Full database backup
> All days Transaction log backups four times.
> I want the backup files will follow the format below:
> dbname_backuptype_YYYYMMDDHHMM.bak
>
> Does anyone knows how to automate this task?
> Thanks
> Robert Lie|||Hi there, search for RE: Backup schedule (Differential backups) in this
group.
Andrew J. Kelly was kind to provide the solution.
"Hari Prasad" wrote:
> Hi,
> Compile the below stored procedure in Master database and define the folder
> in which backup needs to be taken. schedule this procedure using SQL
> Agent -- Jobs. This procedure will backup all the databases with a unique
> name place it in the folder your are passing.
>
> Unique name will be: SERVERNAME_DBNAME_DD_MM_YYYY_D­UMP.BAK
>
> Script to Backup all databases
> --
> CREATE PROCEDURE BACKUP_SP @.Folder VARCHAR(100)
> AS
> begin
> DECLARE @.NAME VARCHAR(100),
> @.DBNAME VARCHAR(100)
> DECLARE BACKUP_CUR CURSOR FOR
> SELECT name FROM sysdatabases where name not
> in('model','pubs','tempdb','no­rthwind')
> OPEN BACKUP_CUR
> FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
> WHILE @.@.FETCH_STATUS=0
> BEGIN
> SELECT
> @.NAME=ltrim(rtrim(@.folder))+@.@.­SERVERNAME+'_'+@.DBNAME+'_'+ltr­im(rtrim(convert
> (char,getdate(),105)))+'Dump.b­ak'
> 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 schedule
> --
> Enterprise Manager - Management -- SQL Agent -- Jobs -- Right click and
> create new job.
> Give a name to the Job and in Job step menthon this procedure with
> foldername as parameter and scdule the job to be executed based on
> requirement
> Note:
> You could change the script to do a differential or log backup.
> --
> Thanks
> Hari
> SQL Server MVP
>
> "Robert Lie" <robert.lie24@.gmail.com> wrote in message
> news:%230FZVEJeFHA.3032@.TK2MSFTNGP10.phx.gbl...
> > Dear All,
> >
> > I plan my backup strategy during the week as follow:
> >
> > Mon | Differential database backup
> > Tue | Differential database backup
> > Wed | Differential database backup
> > Thu | Differential database backup
> > Fri | Differential database backup
> > Sat | Differential database backup
> > Sun | Full database backup
> > All days Transaction log backups four times.
> >
> > I want the backup files will follow the format below:
> > dbname_backuptype_YYYYMMDDHHMM.bak
> >
> >
> > Does anyone knows how to automate this task?
> >
> > Thanks
> >
> > Robert Lie
>
>