Backup/Restore all databases

Hi Folks,
Is there a syntax for backing up ALL databases for the
backup and restore database commands. The only examples I
see are for one database at a time.
Thanks
LarryNope. You have to loop through the master..sysdatabases table with a cursor
or pseudo-cursor to backup/restore all the databases.
Jacco Schalkwijk
SQL Server MVP
"lmorando" <anonymous@.discussions.microsoft.com> wrote in message
news:1d9ee01c45499$7161f0b0$a601280a@.phx
.gbl...
> Hi Folks,
> Is there a syntax for backing up ALL databases for the
> backup and restore database commands. The only examples I
> see are for one database at a time.
> Thanks
> Larry|||Hi,
I suggest you to perform a FULL database backup of all databases (User
databases, System databases such as Master (Includes logins),msdb).
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_DUMP.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','northwind')
OPEN BACKUP_CUR
FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
WHILE @.@.FETCH_STATUS=0
BEGIN
SELECT
@.NAME=ltrim(rtrim(@.folder))+@.@.SERVERNAME
+'_'+@.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 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
Thanks
Hari
MCDBA
"lmorando" <anonymous@.discussions.microsoft.com> wrote in message
news:1d9ee01c45499$7161f0b0$a601280a@.phx
.gbl...
> Hi Folks,
> Is there a syntax for backing up ALL databases for the
> backup and restore database commands. The only examples I
> see are for one database at a time.
> Thanks
> Larry

Backup/Restore all databases

Hi Folks,
Is there a syntax for backing up ALL databases for the
backup and restore database commands. The only examples I
see are for one database at a time.
Thanks
LarryNope. You have to loop through the master..sysdatabases table with a cursor
or pseudo-cursor to backup/restore all the databases.
--
Jacco Schalkwijk
SQL Server MVP
"lmorando" <anonymous@.discussions.microsoft.com> wrote in message
news:1d9ee01c45499$7161f0b0$a601280a@.phx.gbl...
> Hi Folks,
> Is there a syntax for backing up ALL databases for the
> backup and restore database commands. The only examples I
> see are for one database at a time.
> Thanks
> Larry|||Hi,
I suggest you to perform a FULL database backup of all databases (User
databases, System databases such as Master (Includes logins),msdb).
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_DUMP.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','northwind')
OPEN BACKUP_CUR
FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
WHILE @.@.FETCH_STATUS=0
BEGIN
SELECT
@.NAME=ltrim(rtrim(@.folder))+@.@.SERVERNAME+'_'+@.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 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
Thanks
Hari
MCDBA
"lmorando" <anonymous@.discussions.microsoft.com> wrote in message
news:1d9ee01c45499$7161f0b0$a601280a@.phx.gbl...
> Hi Folks,
> Is there a syntax for backing up ALL databases for the
> backup and restore database commands. The only examples I
> see are for one database at a time.
> Thanks
> Larry

Backup/Restore all databases

Hi Folks,
Is there a syntax for backing up ALL databases for the
backup and restore database commands. The only examples I
see are for one database at a time.
Thanks
Larry
Nope. You have to loop through the master..sysdatabases table with a cursor
or pseudo-cursor to backup/restore all the databases.
Jacco Schalkwijk
SQL Server MVP
"lmorando" <anonymous@.discussions.microsoft.com> wrote in message
news:1d9ee01c45499$7161f0b0$a601280a@.phx.gbl...
> Hi Folks,
> Is there a syntax for backing up ALL databases for the
> backup and restore database commands. The only examples I
> see are for one database at a time.
> Thanks
> Larry
|||Hi,
I suggest you to perform a FULL database backup of all databases (User
databases, System databases such as Master (Includes logins),msdb).
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_DUMP.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','northwind')
OPEN BACKUP_CUR
FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
WHILE @.@.FETCH_STATUS=0
BEGIN
SELECT
@.NAME=ltrim(rtrim(@.folder))+@.@.SERVERNAME+'_'+@.DBNA ME+'_'+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 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
Thanks
Hari
MCDBA
"lmorando" <anonymous@.discussions.microsoft.com> wrote in message
news:1d9ee01c45499$7161f0b0$a601280a@.phx.gbl...
> Hi Folks,
> Is there a syntax for backing up ALL databases for the
> backup and restore database commands. The only examples I
> see are for one database at a time.
> Thanks
> Larry

backup, restore commands for db with multiple data files and fileg

I use the below code to backup the database with multiple datafiles, and the
n
try to restore with the command below, but it throws error.
How do I perform backup/restore of database with multiple data files and log
files?
BACKUP DATABASE abc
FILE = 'abc_Data', FILEGROUP = 'PRIMARY',
FILE = 'abc_DataLOB', FILEGROUP = 'FG_abc_DataLOB',
FILE = 'abc_dat', FILEGROUP = 'FG_abc_dat',
FILE = 'abc_dat2', FILEGROUP = 'FG_abcdat2',
FILE = 'abc_dat3', FILEGROUP = 'FG_abcdat3',
TO DISK = 'd:\SQLBackups\abc_200507121535.BAK'
WITH INIT, STATS = 10
go
RESTORE DATABASE abc
FROM DISK ='d:\SQLBackups\abc_200507121535.BAK'
WITH
MOVE 'abc_Data' TO 'd:\MSSQL\MSSQL\data\abc_Data.MDF',
MOVE 'abc_DataLOB' TO
'd:\MSSQL\MSSQL\data\abc_DataLOB.NDF',
MOVE 'abc_dat' TO 'd:\MSSQL\MSSQL\data\abc_dat.NDF' ,
MOVE 'abc_dat2' TO
'd:\MSSQL\MSSQL\data\abc_dat2.NDF' ,
MOVE 'abc_dat3' TO 'd:\MSSQL\MSSQL\data\abc_dat3.NDF',
MOVE 'abc_Log1' TO
'D:\MSSQL\MSSQL\Data\abc_Log1.LDF',
MOVE 'abc_Log2' TO 'D:\MSSQL\MSSQL\Data\abc_Log2.LDF',
RECOVERY, STATS = 10
GOBackup the entire db instead of backing up specific files or filegroups.
BACKUP DATABASE abc
TO DISK = 'd:\SQLBackups\abc_200507121535.BAK'
WITH INIT, STATS = 10
go
AMB
"Pari" wrote:

> I use the below code to backup the database with multiple datafiles, and t
hen
> try to restore with the command below, but it throws error.
> How do I perform backup/restore of database with multiple data files and l
og
> files?
> BACKUP DATABASE abc
> FILE = 'abc_Data', FILEGROUP = 'PRIMARY',
> FILE = 'abc_DataLOB', FILEGROUP = 'FG_abc_DataLOB',
> FILE = 'abc_dat', FILEGROUP = 'FG_abc_dat',
> FILE = 'abc_dat2', FILEGROUP = 'FG_abcdat2',
> FILE = 'abc_dat3', FILEGROUP = 'FG_abcdat3',
> TO DISK = 'd:\SQLBackups\abc_200507121535.BAK'
> WITH INIT, STATS = 10
> go
> RESTORE DATABASE abc
> FROM DISK ='d:\SQLBackups\abc_200507121535.BAK'
> WITH
> MOVE 'abc_Data' TO 'd:\MSSQL\MSSQL\data\abc_Data.MDF',
> MOVE 'abc_DataLOB' TO
> 'd:\MSSQL\MSSQL\data\abc_DataLOB.NDF',
> MOVE 'abc_dat' TO 'd:\MSSQL\MSSQL\data\abc_dat.NDF' ,
> MOVE 'abc_dat2' TO
> 'd:\MSSQL\MSSQL\data\abc_dat2.NDF' ,
> MOVE 'abc_dat3' TO 'd:\MSSQL\MSSQL\data\abc_dat3.NDF',
> MOVE 'abc_Log1' TO
> 'D:\MSSQL\MSSQL\Data\abc_Log1.LDF',
> MOVE 'abc_Log2' TO 'D:\MSSQL\MSSQL\Data\abc_Log2.LDF',
> RECOVERY, STATS = 10
> GO
>

backup, restore commands for db with multiple data files and fileg

I use the below code to backup the database with multiple datafiles, and then
try to restore with the command below, but it throws error.
How do I perform backup/restore of database with multiple data files and log
files?
BACKUP DATABASE abc
FILE = 'abc_Data', FILEGROUP = 'PRIMARY',
FILE = 'abc_DataLOB', FILEGROUP = 'FG_abc_DataLOB',
FILE = 'abc_dat', FILEGROUP = 'FG_abc_dat',
FILE = 'abc_dat2', FILEGROUP = 'FG_abcdat2',
FILE = 'abc_dat3', FILEGROUP = 'FG_abcdat3',
TO DISK = 'd:\SQLBackups\abc_200507121535.BAK'
WITH INIT, STATS = 10
go
RESTORE DATABASE abc
FROM DISK ='d:\SQLBackups\abc_200507121535.BAK'
WITH
MOVE 'abc_Data' TO 'd:\MSSQL\MSSQL\data\abc_Data.MDF',
MOVE 'abc_DataLOB' TO
'd:\MSSQL\MSSQL\data\abc_DataLOB.NDF',
MOVE 'abc_dat' TO 'd:\MSSQL\MSSQL\data\abc_dat.NDF' ,
MOVE 'abc_dat2' TO
'd:\MSSQL\MSSQL\data\abc_dat2.NDF' ,
MOVE 'abc_dat3' TO 'd:\MSSQL\MSSQL\data\abc_dat3.NDF',
MOVE 'abc_Log1' TO
'D:\MSSQL\MSSQL\Data\abc_Log1.LDF',
MOVE 'abc_Log2' TO 'D:\MSSQL\MSSQL\Data\abc_Log2.LDF',
RECOVERY, STATS = 10
GOBackup the entire db instead of backing up specific files or filegroups.
BACKUP DATABASE abc
TO DISK = 'd:\SQLBackups\abc_200507121535.BAK'
WITH INIT, STATS = 10
go
AMB
"Pari" wrote:
> I use the below code to backup the database with multiple datafiles, and then
> try to restore with the command below, but it throws error.
> How do I perform backup/restore of database with multiple data files and log
> files?
> BACKUP DATABASE abc
> FILE = 'abc_Data', FILEGROUP = 'PRIMARY',
> FILE = 'abc_DataLOB', FILEGROUP = 'FG_abc_DataLOB',
> FILE = 'abc_dat', FILEGROUP = 'FG_abc_dat',
> FILE = 'abc_dat2', FILEGROUP = 'FG_abcdat2',
> FILE = 'abc_dat3', FILEGROUP = 'FG_abcdat3',
> TO DISK = 'd:\SQLBackups\abc_200507121535.BAK'
> WITH INIT, STATS = 10
> go
> RESTORE DATABASE abc
> FROM DISK ='d:\SQLBackups\abc_200507121535.BAK'
> WITH
> MOVE 'abc_Data' TO 'd:\MSSQL\MSSQL\data\abc_Data.MDF',
> MOVE 'abc_DataLOB' TO
> 'd:\MSSQL\MSSQL\data\abc_DataLOB.NDF',
> MOVE 'abc_dat' TO 'd:\MSSQL\MSSQL\data\abc_dat.NDF' ,
> MOVE 'abc_dat2' TO
> 'd:\MSSQL\MSSQL\data\abc_dat2.NDF' ,
> MOVE 'abc_dat3' TO 'd:\MSSQL\MSSQL\data\abc_dat3.NDF',
> MOVE 'abc_Log1' TO
> 'D:\MSSQL\MSSQL\Data\abc_Log1.LDF',
> MOVE 'abc_Log2' TO 'D:\MSSQL\MSSQL\Data\abc_Log2.LDF',
> RECOVERY, STATS = 10
> GO
>

backup, restore commands for db with multiple data files and fileg

I use the below code to backup the database with multiple datafiles, and then
try to restore with the command below, but it throws error.
How do I perform backup/restore of database with multiple data files and log
files?
BACKUP DATABASE abc
FILE = 'abc_Data', FILEGROUP = 'PRIMARY',
FILE = 'abc_DataLOB', FILEGROUP = 'FG_abc_DataLOB',
FILE = 'abc_dat', FILEGROUP = 'FG_abc_dat',
FILE = 'abc_dat2', FILEGROUP = 'FG_abcdat2',
FILE = 'abc_dat3', FILEGROUP = 'FG_abcdat3',
TO DISK = 'd:\SQLBackups\abc_200507121535.BAK'
WITH INIT, STATS = 10
go
RESTORE DATABASE abc
FROM DISK ='d:\SQLBackups\abc_200507121535.BAK'
WITH
MOVE 'abc_Data' TO 'd:\MSSQL\MSSQL\data\abc_Data.MDF',
MOVE 'abc_DataLOB' TO
'd:\MSSQL\MSSQL\data\abc_DataLOB.NDF',
MOVE 'abc_dat' TO 'd:\MSSQL\MSSQL\data\abc_dat.NDF' ,
MOVE 'abc_dat2' TO
'd:\MSSQL\MSSQL\data\abc_dat2.NDF' ,
MOVE 'abc_dat3' TO 'd:\MSSQL\MSSQL\data\abc_dat3.NDF',
MOVE 'abc_Log1' TO
'D:\MSSQL\MSSQL\Data\abc_Log1.LDF',
MOVE 'abc_Log2' TO 'D:\MSSQL\MSSQL\Data\abc_Log2.LDF',
RECOVERY, STATS = 10
GO
Backup the entire db instead of backing up specific files or filegroups.
BACKUP DATABASE abc
TO DISK = 'd:\SQLBackups\abc_200507121535.BAK'
WITH INIT, STATS = 10
go
AMB
"Pari" wrote:

> I use the below code to backup the database with multiple datafiles, and then
> try to restore with the command below, but it throws error.
> How do I perform backup/restore of database with multiple data files and log
> files?
> BACKUP DATABASE abc
> FILE = 'abc_Data', FILEGROUP = 'PRIMARY',
> FILE = 'abc_DataLOB', FILEGROUP = 'FG_abc_DataLOB',
> FILE = 'abc_dat', FILEGROUP = 'FG_abc_dat',
> FILE = 'abc_dat2', FILEGROUP = 'FG_abcdat2',
> FILE = 'abc_dat3', FILEGROUP = 'FG_abcdat3',
> TO DISK = 'd:\SQLBackups\abc_200507121535.BAK'
> WITH INIT, STATS = 10
> go
> RESTORE DATABASE abc
> FROM DISK ='d:\SQLBackups\abc_200507121535.BAK'
> WITH
> MOVE 'abc_Data' TO 'd:\MSSQL\MSSQL\data\abc_Data.MDF',
> MOVE 'abc_DataLOB' TO
> 'd:\MSSQL\MSSQL\data\abc_DataLOB.NDF',
> MOVE 'abc_dat' TO 'd:\MSSQL\MSSQL\data\abc_dat.NDF' ,
> MOVE 'abc_dat2' TO
> 'd:\MSSQL\MSSQL\data\abc_dat2.NDF' ,
> MOVE 'abc_dat3' TO 'd:\MSSQL\MSSQL\data\abc_dat3.NDF',
> MOVE 'abc_Log1' TO
> 'D:\MSSQL\MSSQL\Data\abc_Log1.LDF',
> MOVE 'abc_Log2' TO 'D:\MSSQL\MSSQL\Data\abc_Log2.LDF',
> RECOVERY, STATS = 10
> GO
>