Automating Restoring of *.BAK files

I have set up a Maintenance Plans on a SQL Server 2000 SP3a on one server to
create flat file backup of full databases to *.BAK files nightly.
Is it possible to automate the restoring of such BAK files on another SQL
Server 2000 SP3a on another server (assume I have in place scripts for
copying the BAK files from the source server to the destination server)? If
so, how?http://msdn.microsoft.com/library/en-us/adminsql/ad_automate_42r7.asp
--
David Portas
SQL Server MVP
--|||Yes, I know how to create a job in general, but what exactly do I run to
restore a BAK file?
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1106572451.019746.272450@.f14g2000cwb.googlegroups.com...
> http://msdn.microsoft.com/library/en-us/adminsql/ad_automate_42r7.asp
> --
> David Portas
> SQL Server MVP
> --
>|||Use the RESTORE DATABASE command in a Transact SQL job step. See Books
Online for details of the RESTORE DATABASE command.
--
David Portas
SQL Server MVP
--|||Taking a step back, I am just wondering whether a flat-file backup-restore
would be the best way to synchronise 2 SQL Server 2000 databases? Or should
I go for a DTS package to export database on the source server to an Access
mdb file and import it on the other end? Sometimes, I find that the users
in an exported flat file, following an import on another server is not
"usable" even if the referenced user are already defined on the destination
server.
"Patrick" <patl@.reply.newsgroup.msn.com> wrote in message
news:%23CWx88hAFHA.2552@.TK2MSFTNGP09.phx.gbl...
> Yes, I know how to create a job in general, but what exactly do I run to
> restore a BAK file?
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1106572451.019746.272450@.f14g2000cwb.googlegroups.com...
> > http://msdn.microsoft.com/library/en-us/adminsql/ad_automate_42r7.asp
> > --
> > David Portas
> > SQL Server MVP
> > --
> >
>|||"Patrick" <patl@.reply.newsgroup.msn.com> wrote in message
news:%23S0tVOhAFHA.3416@.TK2MSFTNGP09.phx.gbl...
> I have set up a Maintenance Plans on a SQL Server 2000 SP3a on one server
to
> create flat file backup of full databases to *.BAK files nightly.
> Is it possible to automate the restoring of such BAK files on another SQL
> Server 2000 SP3a on another server (assume I have in place scripts for
> copying the BAK files from the source server to the destination server)?
If
> so, how?
>
Yes.
In my case I wrote a stored proc on the restoring server and called it from
the backing up server.
CREATE procedure restore_FOO as
declare @.backup_file as varchar(255)
select @.backup_file=physical_device_name from
nell.msdb.dbo.backupmediafamily where media_set_id in (select
max(media_set_id) from BAR.msdb.dbo.backupset where database_name='foo')
print @.backup_file
restore database FOO from disk=@.backup_file with
move 'SearchActivity_Data' to 'e:\sql_data\FOO_data.mdf',
move 'SearchActivity_Log' to 'f:\SQL_LOGs\FOO_log.ldf',
move 'SearchActivity_Index' to 'g:\sql_index\FOO_Index_Data.NDF',
replace
GO
>

Automating Restoring of *.BAK files

I have set up a Maintenance Plans on a SQL Server 2000 SP3a on one server to
create flat file backup of full databases to *.BAK files nightly.
Is it possible to automate the restoring of such BAK files on another SQL
Server 2000 SP3a on another server (assume I have in place scripts for
copying the BAK files from the source server to the destination server)? If
so, how?
http://msdn.microsoft.com/library/en...omate_42r7.asp
David Portas
SQL Server MVP
|||Yes, I know how to create a job in general, but what exactly do I run to
restore a BAK file?
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1106572451.019746.272450@.f14g2000cwb.googlegr oups.com...
> http://msdn.microsoft.com/library/en...omate_42r7.asp
> --
> David Portas
> SQL Server MVP
> --
>
|||Use the RESTORE DATABASE command in a Transact SQL job step. See Books
Online for details of the RESTORE DATABASE command.
David Portas
SQL Server MVP
|||"Patrick" <patl@.reply.newsgroup.msn.com> wrote in message
news:%23S0tVOhAFHA.3416@.TK2MSFTNGP09.phx.gbl...
> I have set up a Maintenance Plans on a SQL Server 2000 SP3a on one server
to
> create flat file backup of full databases to *.BAK files nightly.
> Is it possible to automate the restoring of such BAK files on another SQL
> Server 2000 SP3a on another server (assume I have in place scripts for
> copying the BAK files from the source server to the destination server)?
If
> so, how?
>
Yes.
In my case I wrote a stored proc on the restoring server and called it from
the backing up server.
CREATE procedure restore_FOO as
declare @.backup_file as varchar(255)
select @.backup_file=physical_device_name from
nell.msdb.dbo.backupmediafamily where media_set_id in (select
max(media_set_id) from BAR.msdb.dbo.backupset where database_name='foo')
print @.backup_file
restore database FOO from disk=@.backup_file with
move 'SearchActivity_Data' to 'e:\sql_data\FOO_data.mdf',
move 'SearchActivity_Log' to 'f:\SQL_LOGs\FOO_log.ldf',
move 'SearchActivity_Index' to 'g:\sql_index\FOO_Index_Data.NDF',
replace
GO

>
sql

Automating Restoring of *.BAK files

I have set up a Maintenance Plans on a SQL Server 2000 SP3a on one server to
create flat file backup of full databases to *.BAK files nightly.
Is it possible to automate the restoring of such BAK files on another SQL
Server 2000 SP3a on another server (assume I have in place scripts for
copying the BAK files from the source server to the destination server)? If
so, how?http://msdn.microsoft.com/library/e...tomate_42r7.asp
--
David Portas
SQL Server MVP
--|||Yes, I know how to create a job in general, but what exactly do I run to
restore a BAK file?
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1106572451.019746.272450@.f14g2000cwb.googlegroups.com...
> http://msdn.microsoft.com/library/e...tomate_42r7.asp
> --
> David Portas
> SQL Server MVP
> --
>|||Use the RESTORE DATABASE command in a Transact SQL job step. See Books
Online for details of the RESTORE DATABASE command.
David Portas
SQL Server MVP
--|||"Patrick" <patl@.reply.newsgroup.msn.com> wrote in message
news:%23S0tVOhAFHA.3416@.TK2MSFTNGP09.phx.gbl...
> I have set up a Maintenance Plans on a SQL Server 2000 SP3a on one server
to
> create flat file backup of full databases to *.BAK files nightly.
> Is it possible to automate the restoring of such BAK files on another SQL
> Server 2000 SP3a on another server (assume I have in place scripts for
> copying the BAK files from the source server to the destination server)?
If
> so, how?
>
Yes.
In my case I wrote a stored proc on the restoring server and called it from
the backing up server.
CREATE procedure restore_FOO as
declare @.backup_file as varchar(255)
select @.backup_file=physical_device_name from
nell.msdb.dbo.backupmediafamily where media_set_id in (select
max(media_set_id) from BAR.msdb.dbo.backupset where database_name='foo')
print @.backup_file
restore database FOO from disk=@.backup_file with
move 'SearchActivity_Data' to 'e:\sql_data\FOO_data.mdf',
move 'SearchActivity_Log' to 'f:\SQL_LOGs\FOO_log.ldf',
move 'SearchActivity_Index' to 'g:\sql_index\FOO_Index_Data.NDF',
replace
GO

>

Automating deployment of maintenance plans

Hello,

I have created a Maintenance Plan on our development SQL Server 2005 Standard using the designer in SQL Server Management Studio. The plan backs up databases and transaction logs to a hard disk and does some cleanup too. It is scheduled to run nightly. This plan needs to be deployed to 13 production sites by someone else not familiar with SQL Server.

Can I use some combination of a SQL script, an export of the maintenance plan, and/or a batch file to automate the deployment of this plan and it's schedule to servers at several different sites? The deployment team will have admin remote desktop access to the production SQL Servers, which also have SQL Management Studio installed but we cannot expect the team to recreate the plan manually on each site.

I haven't been able to find much documentation on doing this automatically. Any help will be appreciated.

Thank you,

- Jason

Create a SSIS package to perform this maintenance plan task and use DTUTIL to deploy on multiple servers.

http://www.microsoft.com/technet/prodtechnol/sql/2005/mgngssis.mspx#ERGAE fyi.

sql

Automating db Backups

Is there a way to automate (schedule) backups of the databases in SQL Express? Similar to Maintenance Plans in SQL2000....Hi

Mark McFarlane,

If you come accross anything reagrding automating a schedule backup,Pls let me know,
Have been on the look out for this some time now.
Will do likewise.
email : papali4@.hotmail.com
Tnx

|||

hi,

not directly as SQL Server Agent is not provided, but you can workarond that using the OS provided native scheduler (AT or SCHTASKS)..

you can write down a cmd file like

<backup.cmd>

REM scheduled backup

SqlCmd-E -S(Local) -Q"SET NOCOUNT ON; SELECT 'Backup executions started at - ' + CONVERT(varchar, GETDATE());" >d:\YourCheckFolder\ScheduledBCK.txt

SqlCmd-E -S(Local) -Q"SET NOCOUNT ON; SELECT 'backup database [db_name]'; PRINT '';" >>d:\YourCheckFolder\ScheduledBCK.txt

SqlCmd-E -S(Local) -Q"BACKUP DATABASE [db_name] TO DISK = N'D:\BackupFolder\db_name.Bak' WITH FORMAT, INIT, NAME = N'Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10" >>d:\YourCheckFolder\ScheduledBCK.txt

SqlCmd-E -S(Local) -Q"SET NOCOUNT ON; SELECT 'Backup terminated at - ' + CONVERT(varchar, GETDATE());" >>d:\YourCheckFolder\ScheduledBCK.txt
</backup.cmd>

then you can schedule it as desired... the script will backup the db as required and will output the result of the task to a text file, d:\YourCheckFolder\ScheduledBCK.txt, you can later review to verify the performed operation...

in my own scenarios, I do add another "features".. I wrote a CLR assmbly exporting a stored procedure to "mimic" database mail feature (not present in SQLExpress) so that the "d:\YourCheckFolder\ScheduledBCK.txt" will be automatically sent to a defined list of recipients (sysadmins, dba or myself as well).. the component (amDBObj) is free and can be downloaded from http://www.asql.biz/en/Download2005.aspx .. feedback is apprecieted

the resulting script can be modified adding the

<add this>

REM adding SMTP mail to sysadmins, dba, etc of the backup operation result..

SqlCmd-E -S(Local) -Q"SET NOCOUNT ON; SELECT 'Mailing backup result of - ' + CONVERT(varchar, GETDATE());" >d:\YourCheckFolder\ScheduledBCK-mailing.txt

SqlCmd -E -S(Local) -Q"SET NOCOUNT ON; DECLARE @.ret int;EXEC @.ret = [db_hosting_the_CLR_assembly].[dbo].[amSMTPmail] @.Server = N'your_mail_server', @.Sender = N'the_SQLsender@.sender.com', @.AddressesTO = N'me@.me.com', @.AddressesCC = N'further_recipients@.domain.com', @.AddressesCCN = NULL, @.AttachFiles = N'd:\YourCheckFolder\ScheduledBCK.txt', @.Subject = N'Backup performed', @.MessageBody = N'Backup performed'; SELECT @.ret AS [Execution result];" >>d:\YourCheckFolder\ScheduledBCK-mailing.txt

</add this>

or whatever required change to the original cmd file...

regards

|||

hi Andrea Montanari ,

That was very informative. Is there any other way to run and schedule jobs. Also, what about SSIS in express edition. Do have to install them seperately. Thanks

|||

UMAR DAR wrote:

hi Andrea Montanari ,

That was very informative. Is there any other way to run and schedule jobs.

hy, perhaps you can have a look at a great artice (and tool) by Jasper Smith, SQL Server MVP, at http://www.sqldbatips.com/showarticle.asp?ID=27 and http://www.sqldbatips.com/showarticle.asp?ID=29, based on WinNT native scheduler as well... but all these are not SQL Server jobs, as SQLExpress does not provide SQL Server Agent...

UMAR DAR wrote:

Also, what about SSIS in express edition. Do have to install them seperately. Thanks

SSIS are not available as well, in SQLEpress edition.. http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

regards

|||

I have a similar solution posted here that's not incredibly elegant but it works just fine. It goes through and backs up all databases in a given instance and there is an included batch file to schedule it. The video deployment instructions can be seen here:

http://www.jumpstarttv.com/Media.aspx?vid=30

or written instructions here:

http://whiteknighttechnology.com/cs/blogs/brian_knight/archive/2006/08/13/215.aspx

-- Brian

Automating db Backups

Is there a way to automate (schedule) backups of the databases in SQL Express? Similar to Maintenance Plans in SQL2000....Hi

Mark McFarlane,

If you come accross anything reagrding automating a schedule backup,Pls let me know,
Have been on the look out for this some time now.
Will do likewise.
email : papali4@.hotmail.com
Tnx

|||

hi,

not directly as SQL Server Agent is not provided, but you can workarond that using the OS provided native scheduler (AT or SCHTASKS)..

you can write down a cmd file like

<backup.cmd>

REM scheduled backup

SqlCmd-E -S(Local) -Q"SET NOCOUNT ON; SELECT 'Backup executions started at - ' + CONVERT(varchar, GETDATE());" >d:\YourCheckFolder\ScheduledBCK.txt

SqlCmd-E -S(Local) -Q"SET NOCOUNT ON; SELECT 'backup database [db_name]'; PRINT '';" >>d:\YourCheckFolder\ScheduledBCK.txt

SqlCmd-E -S(Local) -Q"BACKUP DATABASE [db_name] TO DISK = N'D:\BackupFolder\db_name.Bak' WITH FORMAT, INIT, NAME = N'Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10" >>d:\YourCheckFolder\ScheduledBCK.txt

SqlCmd-E -S(Local) -Q"SET NOCOUNT ON; SELECT 'Backup terminated at - ' + CONVERT(varchar, GETDATE());" >>d:\YourCheckFolder\ScheduledBCK.txt
</backup.cmd>

then you can schedule it as desired... the script will backup the db as required and will output the result of the task to a text file, d:\YourCheckFolder\ScheduledBCK.txt, you can later review to verify the performed operation...

in my own scenarios, I do add another "features".. I wrote a CLR assmbly exporting a stored procedure to "mimic" database mail feature (not present in SQLExpress) so that the "d:\YourCheckFolder\ScheduledBCK.txt" will be automatically sent to a defined list of recipients (sysadmins, dba or myself as well).. the component (amDBObj) is free and can be downloaded from http://www.asql.biz/en/Download2005.aspx .. feedback is apprecieted

the resulting script can be modified adding the

<add this>

REM adding SMTP mail to sysadmins, dba, etc of the backup operation result..

SqlCmd-E -S(Local) -Q"SET NOCOUNT ON; SELECT 'Mailing backup result of - ' + CONVERT(varchar, GETDATE());" >d:\YourCheckFolder\ScheduledBCK-mailing.txt

SqlCmd -E -S(Local) -Q"SET NOCOUNT ON; DECLARE @.ret int;EXEC @.ret = [db_hosting_the_CLR_assembly].[dbo].[amSMTPmail] @.Server = N'your_mail_server', @.Sender = N'the_SQLsender@.sender.com', @.AddressesTO = N'me@.me.com', @.AddressesCC = N'further_recipients@.domain.com', @.AddressesCCN = NULL, @.AttachFiles = N'd:\YourCheckFolder\ScheduledBCK.txt', @.Subject = N'Backup performed', @.MessageBody = N'Backup performed'; SELECT @.ret AS [Execution result];" >>d:\YourCheckFolder\ScheduledBCK-mailing.txt

</add this>

or whatever required change to the original cmd file...

regards

|||

hi Andrea Montanari ,

That was very informative. Is there any other way to run and schedule jobs. Also, what about SSIS in express edition. Do have to install them seperately. Thanks

|||

UMAR DAR wrote:

hi Andrea Montanari ,

That was very informative. Is there any other way to run and schedule jobs.

hy, perhaps you can have a look at a great artice (and tool) by Jasper Smith, SQL Server MVP, at http://www.sqldbatips.com/showarticle.asp?ID=27 and http://www.sqldbatips.com/showarticle.asp?ID=29, based on WinNT native scheduler as well... but all these are not SQL Server jobs, as SQLExpress does not provide SQL Server Agent...

UMAR DAR wrote:

Also, what about SSIS in express edition. Do have to install them seperately. Thanks

SSIS are not available as well, in SQLEpress edition.. http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

regards

|||

I have a similar solution posted here that's not incredibly elegant but it works just fine. It goes through and backs up all databases in a given instance and there is an included batch file to schedule it. The video deployment instructions can be seen here:

http://www.jumpstarttv.com/Media.aspx?vid=30

or written instructions here:

http://whiteknighttechnology.com/cs/blogs/brian_knight/archive/2006/08/13/215.aspx

-- Brian

automatically compress backups

I have a maintenance plan for handling backups. I need to be able to amend the jobs so that the latest backup can be compressed (e.g. command line compression utility) before it is copied off across the network to a DR machine.

The issue is that the Maintenance Plan produces data/time specific backup file names - mulitiple backups stored in the same directory. Therefore, within a job step, I'm finding it difficult to code it such that only the latest backup is compressed. The command line syntax of these compression utilities is somewhat limited...

e.g. wzzip zipname.zip dbbackup_*.bak

nb. The '*' represents the wild card for the date/time part of the backup files created by the maintenance plan.

The problem is that the above command would keep adding multiple backups to the same zip archive. What I want is to simply add only the latest backup to the archive but to achieve this I would have to know the name of the backup file (created by the maintenance plan) programatically within the job.

I would have thought this was a fairly common requirement/problem. Has anyone got some suggestions/solutions?

CliveRE:
Q1 Has anyone got some suggestions/solutions?

A1 I doubt you'll like hearing this, my suggestion is don't compress production backups if you can possibly avoid it in any production setting (especially certain kinds of software based compression).

I've seen too many failed validation restores that seem to have only the fact of having been compressed and decompressed in common to heavily rely on any compression of backup dumps. (Granted, it does work 'most' of the time, which is generally fine for many development environments. However, in some situations where compression is implemented with larger backups I've seen routine daily restore validations fail or restore with problems every few weeks.)
---

If you are intent on going ahead anyway, what Sql Server version you are running is important. (In 2k installs I've implemented UDF functions instead of stored procedures for extracting dump information.)

Unfortunately no version has provided particularly rich built in backup / maintenence procedures to address the information needs you would require. (There are lots of undocumented ones that are worth looking at, but they are as subject to unannounced changes as the underlying MSDB table structures. I think you may be stuck implementing and maintaining your own special functions / stored procedures). Current Maintenance Plan Procedures (ver 7 / 2k) include:
sp_add_maintenance_plan
sp_add_maintenance_plan_db
sp_add_maintenance_plan_job
sp_delete_maintenance_plan
sp_delete_maintenance_plan_db
sp_delete_maintenance_plan_job
sp_help_maintenance_plan

The following user special stored procedure (implemented on some ver. 7 installs) returns the last DB or TL dumpfor a specified DB and Full or Log Dump on 7.0 and 2k (but not 6.x) installs:

Use
Master
Go
DROP PROCEDURE sp_LastDump
go
CREATE PROCEDURE sp_LastDump
--Fully Qualified Select most recent DBDump or TLDump
--@.pBkpSetTyp = I, D, L, @.pDBNam = DB name,
-- sp parameters:
@.pDBNam VarChar(512) = 'Master',
@.pBkpSetTyp VarChar(50) = 'D'
AS
exec ('Set NoCount On')
SELECT MsDb..backupmediafamily.physical_device_name
FROM MsDb..backupmediafamily INNER JOIN
MsDb..backupset ON
MsDb..backupmediafamily.media_set_id = MsDb..backupset.media_set_id
WHERE MsDb..backupset.backup_finish_date =
(SELECT MAX(MsDb..backupset.backup_finish_date)
AS Mxbackup_finish_date
FROM MsDb..backupmediafamily INNER JOIN
MsDb..backupset ON
MsDb..backupmediafamily.media_set_id = MsDb..backupset.media_set_id
WHERE (MsDb..backupset.database_name = @.pDBNam) AND
(MsDb..backupset.type = @.pBkpSetTyp))

Note: If running 6.x or earlier, you'll need to create a proc referencing 6.x msdb tables instead e.g.(sysbackuphistory sysbackupdetail). If later upgrade to 7.0 or 2k, or later versions, you'll need to rdefine any special stored procs you create to reflect newer MSDB tables.|||How is the currently developed maintenance plan programmed - through the maintenance wizard / dts / stored procedure ... ? Which compression software package are you using ?

Automatic Maintenance Plan Optimization vs Logical Scan Frag.

Hi!

SQL Server 7.0, SP4

I would like to know if it's normal that, with a Maintenance plan Optimization done every week, I got a Logical Scan Fragmentation of 99.99% for a non-clustered index.

I though that the logical scan should be as lower as it can.

I though that the Optimization plan should drop and recreate all indexes in a database.

My table is heavy (7GB). DOes the clustered is recreated too in this maintenance plan? Is it the reason why my Logical Scan Frag. is so high?

PS: Yes, the database is included in the list of the Maintenance plan.

Thanks a lot!

DavidMake sure your non-clustered index is being created after your clustered index. Recreating a clustered index changes the order of the data, and causes all the non-clustered indexes to be rebuilt as well, and not necessarily efficiently.

99% sounds pretty weird though. This could be an erroneous value.

blindman|||I don't recreate any indexes manually or by any script other than the Optimization option in the Maintenance Plan. BOL doesn't tell me a lot of information on what is done by this option "Reorganize data and index pages" and in which order. Does this use un DBCC CHECKDB or DBREINDEX?

Someone know what is done and what should be the result (at least an idea) on the indexes?

A query doing a index scan of 6GB of data shouldn't tell to the Tuning Wizard that the creation of another index on the specified WHERE clause field should be created to get an Index seek?

All this make me lost... Some ideas?

thanks a lot

Originally posted by blindman
Make sure your non-clustered index is being created after your clustered index. Recreating a clustered index changes the order of the data, and causes all the non-clustered indexes to be rebuilt as well, and not necessarily efficiently.

99% sounds pretty weird though. This could be an erroneous value.

blindman