Sql Auto grow: bak

Showing posts with label bak. Show all posts

Thursday, March 29, 2012

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

Tuesday, March 20, 2012

Automatic Restore

Hi,
I have a database .BAK file on disk that I want to resotre everynight.
RESTORE DATABASE db_name from DISK='c:\backup\db_name_yyyymmddhh'
If I want to automate this everyday, how can I create yyyymmdd
variable?
Thanks,
hjThis should get you started:
DECLARE @.sql nvarchar(300), @.d char(8)
SET @.d = CONVERT(char(8), CURRENT_TIMESTAMP, 112)
SET @.sql = 'RESTORE DATABASE db_name from DISK=''c:\backup\db_name_' + @.d + '.bak'''
SELECT @.sql
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hitesh Joshi" <hitesh287@.gmail.com> wrote in message
news:1151340039.332861.226090@.u72g2000cwu.googlegroups.com...
> Hi,
> I have a database .BAK file on disk that I want to resotre everynight.
> RESTORE DATABASE db_name from DISK='c:\backup\db_name_yyyymmddhh'
> If I want to automate this everyday, how can I create yyyymmdd
> variable?
> Thanks,
> hj
>|||Hitesh Joshi wrote:
> Hi,
> I have a database .BAK file on disk that I want to resotre everynight.
> RESTORE DATABASE db_name from DISK='c:\backup\db_name_yyyymmddhh'
> If I want to automate this everyday, how can I create yyyymmdd
> variable?
> Thanks,
> hj
>
Instead of assuming a given filename/format, why not just check to see
what the most recent backup was, and restore that? The following should
work for you, add whatever switches you need to the restore command:
DECLARE @.DBName VARCHAR(50)
DECLARE @.LastBackupFileName VARCHAR(255)
DECLARE @.Command VARCHAR(1024)
SELECT @.DBName = 'YourDBName'
SELECT TOP 1 @.LastBackupFileName = backupmediafamily.physical_device_name
FROM msdb.dbo.backupset backupset
INNER JOIN msdb.dbo.backupmediafamily backupmediafamily
ON backupset.media_set_id = backupmediafamily.media_set_id
WHERE backupset.type = 'D'
AND backupset.database_name = @.DBName
ORDER BY backupset.backup_start_date DESC
SELECT @.Command = 'RESTORE DATABASE ' + RTRIM(@.DBName) + '_New FROM
DISK=''' + @.LastBackupFileName + ''''
EXEC (@.Command)|||Hi,
Thank you for the suggestion... it works awesome with file name like
db_name_yyyymmdd.bak but my files have name something like
db_name_yyyymmddhhmm.bak.
and last hhmm is not always same. is there a way I can use wildchars?
SET @.sql = 'RESTORE DATABASE db_name from DISK=''c:\backup\db_name_' +
@.d + '*.bak'''
I tried this but did not work :(
Thanks in advance.
Tibor Karaszi wrote:
> This should get you started:
> DECLARE @.sql nvarchar(300), @.d char(8)
> SET @.d = CONVERT(char(8), CURRENT_TIMESTAMP, 112)
> SET @.sql = 'RESTORE DATABASE db_name from DISK=''c:\backup\db_name_' + @.d + '.bak'''
> SELECT @.sql
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Hitesh Joshi" <hitesh287@.gmail.com> wrote in message
> news:1151340039.332861.226090@.u72g2000cwu.googlegroups.com...
> >
> > Hi,
> >
> > I have a database .BAK file on disk that I want to resotre everynight.
> >
> > RESTORE DATABASE db_name from DISK='c:\backup\db_name_yyyymmddhh'
> > If I want to automate this everyday, how can I create yyyymmdd
> > variable?
> >
> > Thanks,
> > hj
> >|||This solution looks very interesting but I am getting bak'ed up db
files from a third server and I do not have access to msdb on that
server...
Tracy McKibben wrote:
> Hitesh Joshi wrote:
> > Hi,
> >
> > I have a database .BAK file on disk that I want to resotre everynight.
> >
> > RESTORE DATABASE db_name from DISK='c:\backup\db_name_yyyymmddhh'
> > If I want to automate this everyday, how can I create yyyymmdd
> > variable?
> >
> > Thanks,
> > hj
> >
>
> Instead of assuming a given filename/format, why not just check to see
> what the most recent backup was, and restore that? The following should
> work for you, add whatever switches you need to the restore command:
> DECLARE @.DBName VARCHAR(50)
> DECLARE @.LastBackupFileName VARCHAR(255)
> DECLARE @.Command VARCHAR(1024)
> SELECT @.DBName = 'YourDBName'
> SELECT TOP 1 @.LastBackupFileName = backupmediafamily.physical_device_name
> FROM msdb.dbo.backupset backupset
> INNER JOIN msdb.dbo.backupmediafamily backupmediafamily
> ON backupset.media_set_id = backupmediafamily.media_set_id
> WHERE backupset.type = 'D'
> AND backupset.database_name = @.DBName
> ORDER BY backupset.backup_start_date DESC
> SELECT @.Command = 'RESTORE DATABASE ' + RTRIM(@.DBName) + '_New FROM
> DISK=''' + @.LastBackupFileName + ''''
> EXEC (@.Command)|||Hitesh Joshi wrote:
> This solution looks very interesting but I am getting bak'ed up db
> files from a third server and I do not have access to msdb on that
> server...
>
Create a linked server on your restore server that points to the "third"
server you mentioned. Then query the backupset and backupmediafamily
tables using their fully-qualified names:
SERVERNAME.msdb.dbo.backupset
SERVERNAME.msdb.dbo.backupmediafamily|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:upB3v4VmGHA.2120@.TK2MSFTNGP05.phx.gbl...
> Hitesh Joshi wrote:
> > This solution looks very interesting but I am getting bak'ed up db
> > files from a third server and I do not have access to msdb on that
> > server...
> >
> Create a linked server on your restore server that points to the "third"
> server you mentioned. Then query the backupset and backupmediafamily
> tables using their fully-qualified names:
> SERVERNAME.msdb.dbo.backupset
> SERVERNAME.msdb.dbo.backupmediafamily
>
BTW, this is what we used to do.
Though, if possible what works better if the source server is doing
transaction log backups is to do a full backup/restore and then restore only
the logs every N hours. This is quicker and can reduce your disaster
recovery time.
(google for log shipping for more info. It's possible to roll your own.)|||You can't do wildcards in the restore command. I suggest you go the path suggested by Tracy. Also
consider Log Shipping as suggested by Greg.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hitesh Joshi" <hitesh287@.gmail.com> wrote in message
news:1151350714.948572.273570@.r2g2000cwb.googlegroups.com...
> Hi,
> Thank you for the suggestion... it works awesome with file name like
> db_name_yyyymmdd.bak but my files have name something like
> db_name_yyyymmddhhmm.bak.
> and last hhmm is not always same. is there a way I can use wildchars?
> SET @.sql = 'RESTORE DATABASE db_name from DISK=''c:\backup\db_name_' +
> @.d + '*.bak'''
> I tried this but did not work :(
> Thanks in advance.
> Tibor Karaszi wrote:
>> This should get you started:
>> DECLARE @.sql nvarchar(300), @.d char(8)
>> SET @.d = CONVERT(char(8), CURRENT_TIMESTAMP, 112)
>> SET @.sql = 'RESTORE DATABASE db_name from DISK=''c:\backup\db_name_' + @.d + '.bak'''
>> SELECT @.sql
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Hitesh Joshi" <hitesh287@.gmail.com> wrote in message
>> news:1151340039.332861.226090@.u72g2000cwu.googlegroups.com...
>> >
>> > Hi,
>> >
>> > I have a database .BAK file on disk that I want to resotre everynight.
>> >
>> > RESTORE DATABASE db_name from DISK='c:\backup\db_name_yyyymmddhh'
>> > If I want to automate this everyday, how can I create yyyymmdd
>> > variable?
>> >
>> > Thanks,
>> > hj
>> >
>

Monday, February 13, 2012

Auto restore .bak from Production to same server''s TEST db with other Dbs & .baks in same

Howdy;

I've tried this in the 'tools' area, but that didn't work too well. I suspect, I will have to generate a T-SQL code then schedule it as a job. Why I can't just drag and drop with basic desires, is beyond me, but THAT probably does exist.

anyway here is the problem

[this server has many databases, on SQL 2000 sp2]

1. User only wants me to use Monday morning's full backup, which is good in that it doesn't include transaction logs.

2. Restore that data overtop/into Developement db. = good, no data to worry about damaging.

3. User does NOT want me to do this by hand, but schedule it.

ok,

a. must do a RESTORE WITH FILELISTONLY from [?] what ?, master? and if I user the *.bak of the production, it has

a coded date field in the name entry SO, I would, I guess, have to generate all sorts of wonderful code to find the date and build a file name. Why, because using the FROM DISK = 'F:\MSSQL\BACKUP\DB\PRODUCTION_yyyyddmm.BAK' is not going to work with a wild card.

Can I do a file lookup using a 'PRODUCTION' prefix into a variable, then use that or should I look for latest file date [remember there are several database backups here], or ?

then. How does one schedule such a T-SQL. Do I save it to some text file, and invoke it using a job scheduler.

any help appreciated.

IS there an easier way.

rik

Howdy;

anyway here is the problem

[this server has many databases, on SQL 2000 sp2]

1. User only wants me to use Monday morning's full backup, which is good in that it doesn't include transaction logs.

2. Restore that data overtop/into Developement db. = good, no data to worry about damaging.

3. User does NOT want me to do this by hand, but schedule it.

ok,

a. must do a RESTORE WITH FILELISTONLY from [?] what ?, master? and if I user the *.bak of the production, it has

Can I do a file lookup using a 'PRODUCTION' prefix into a variable, then use that or should I look for latest file date [remember there are several database backups here], or ?

then. How does one schedule such a T-SQL. Do I save it to some text file, and invoke it using a job scheduler.

any help appreciated.

rik

|||

Here is a script that dump the filename sorted by latest date last. You should be to process it and create your desired restore statement.

create table #tb(i int identity primary key, name nvarchar(80) null)

declare @.sql nvarchar(1000), @.dir sysname
set @.dir='c:\windows'
set @.sql='dir '+@.dir+' /b /od /aa /ar'

insert #tb(name)
exec xp_cmdshell @.sql

select * from #tb

|||

ok, this was what I was afraid of: having to always write sql code.

oh, well. Too bad the EM just doesn't give you the option to ALWAYS ask if you want to schedule what you have just done as a job. That way I could create code on the fly by drag and drop.

|||

If you feel this can make your life and others easier, file a request at http://connect.microsoft.com/sqlserver

Be sure to include a business case if you want it to have any merit.

|||

thanks

appreciate it.

rik

Auto restore .bak from Production to same server''s TEST db with other Dbs & .baks in same

Howdy;

anyway here is the problem

[this server has many databases, on SQL 2000 sp2]

1. User only wants me to use Monday morning's full backup, which is good in that it doesn't include transaction logs.

2. Restore that data overtop/into Developement db. = good, no data to worry about damaging.

3. User does NOT want me to do this by hand, but schedule it.

ok,

a. must do a RESTORE WITH FILELISTONLY from [?] what ?, master? and if I user the *.bak of the production, it has

Can I do a file lookup using a 'PRODUCTION' prefix into a variable, then use that or should I look for latest file date [remember there are several database backups here], or ?

then. How does one schedule such a T-SQL. Do I save it to some text file, and invoke it using a job scheduler.

any help appreciated.

IS there an easier way.

rik

Howdy;

anyway here is the problem

[this server has many databases, on SQL 2000 sp2]

1. User only wants me to use Monday morning's full backup, which is good in that it doesn't include transaction logs.

2. Restore that data overtop/into Developement db. = good, no data to worry about damaging.

3. User does NOT want me to do this by hand, but schedule it.

ok,

a. must do a RESTORE WITH FILELISTONLY from [?] what ?, master? and if I user the *.bak of the production, it has

Can I do a file lookup using a 'PRODUCTION' prefix into a variable, then use that or should I look for latest file date [remember there are several database backups here], or ?

then. How does one schedule such a T-SQL. Do I save it to some text file, and invoke it using a job scheduler.

any help appreciated.

rik

|||

Here is a script that dump the filename sorted by latest date last. You should be to process it and create your desired restore statement.

create table #tb(i int identity primary key, name nvarchar(80) null)

declare @.sql nvarchar(1000), @.dir sysname
set @.dir='c:\windows'
set @.sql='dir '+@.dir+' /b /od /aa /ar'

insert #tb(name)
exec xp_cmdshell @.sql

select * from #tb

|||

ok, this was what I was afraid of: having to always write sql code.

oh, well. Too bad the EM just doesn't give you the option to ALWAYS ask if you want to schedule what you have just done as a job. That way I could create code on the fly by drag and drop.

|||

If you feel this can make your life and others easier, file a request at http://connect.microsoft.com/sqlserver

Be sure to include a business case if you want it to have any merit.

|||

thanks

appreciate it.

rik

Auto restore .bak from Production to same server''s TEST db with other Dbs & .baks in same

Howdy;

anyway here is the problem

[this server has many databases, on SQL 2000 sp2]

1. User only wants me to use Monday morning's full backup, which is good in that it doesn't include transaction logs.

2. Restore that data overtop/into Developement db. = good, no data to worry about damaging.

3. User does NOT want me to do this by hand, but schedule it.

ok,

a. must do a RESTORE WITH FILELISTONLY from [?] what ?, master? and if I user the *.bak of the production, it has

Can I do a file lookup using a 'PRODUCTION' prefix into a variable, then use that or should I look for latest file date [remember there are several database backups here], or ?

then. How does one schedule such a T-SQL. Do I save it to some text file, and invoke it using a job scheduler.

any help appreciated.

IS there an easier way.

rik

Howdy;

anyway here is the problem

[this server has many databases, on SQL 2000 sp2]

1. User only wants me to use Monday morning's full backup, which is good in that it doesn't include transaction logs.

2. Restore that data overtop/into Developement db. = good, no data to worry about damaging.

3. User does NOT want me to do this by hand, but schedule it.

ok,

a. must do a RESTORE WITH FILELISTONLY from [?] what ?, master? and if I user the *.bak of the production, it has

Can I do a file lookup using a 'PRODUCTION' prefix into a variable, then use that or should I look for latest file date [remember there are several database backups here], or ?

then. How does one schedule such a T-SQL. Do I save it to some text file, and invoke it using a job scheduler.

any help appreciated.

rik

|||

Here is a script that dump the filename sorted by latest date last. You should be to process it and create your desired restore statement.

create table #tb(i int identity primary key, name nvarchar(80) null)

declare @.sql nvarchar(1000), @.dir sysname
set @.dir='c:\windows'
set @.sql='dir '+@.dir+' /b /od /aa /ar'

insert #tb(name)
exec xp_cmdshell @.sql

select * from #tb

|||

ok, this was what I was afraid of: having to always write sql code.

oh, well. Too bad the EM just doesn't give you the option to ALWAYS ask if you want to schedule what you have just done as a job. That way I could create code on the fly by drag and drop.

|||

If you feel this can make your life and others easier, file a request at http://connect.microsoft.com/sqlserver

Be sure to include a business case if you want it to have any merit.

|||

thanks

appreciate it.

rik

Auto restore .bak from Production to same server''s TEST db with other Dbs & .baks in same

Howdy;

anyway here is the problem

[this server has many databases, on SQL 2000 sp2]

1. User only wants me to use Monday morning's full backup, which is good in that it doesn't include transaction logs.

2. Restore that data overtop/into Developement db. = good, no data to worry about damaging.

3. User does NOT want me to do this by hand, but schedule it.

ok,

a. must do a RESTORE WITH FILELISTONLY from [?] what ?, master? and if I user the *.bak of the production, it has

Can I do a file lookup using a 'PRODUCTION' prefix into a variable, then use that or should I look for latest file date [remember there are several database backups here], or ?

then. How does one schedule such a T-SQL. Do I save it to some text file, and invoke it using a job scheduler.

any help appreciated.

IS there an easier way.

rik

Howdy;

anyway here is the problem

[this server has many databases, on SQL 2000 sp2]

1. User only wants me to use Monday morning's full backup, which is good in that it doesn't include transaction logs.

2. Restore that data overtop/into Developement db. = good, no data to worry about damaging.

3. User does NOT want me to do this by hand, but schedule it.

ok,

a. must do a RESTORE WITH FILELISTONLY from [?] what ?, master? and if I user the *.bak of the production, it has

Can I do a file lookup using a 'PRODUCTION' prefix into a variable, then use that or should I look for latest file date [remember there are several database backups here], or ?

then. How does one schedule such a T-SQL. Do I save it to some text file, and invoke it using a job scheduler.

any help appreciated.

rik

|||

Here is a script that dump the filename sorted by latest date last. You should be to process it and create your desired restore statement.

create table #tb(i int identity primary key, name nvarchar(80) null)

declare @.sql nvarchar(1000), @.dir sysname
set @.dir='c:\windows'
set @.sql='dir '+@.dir+' /b /od /aa /ar'

insert #tb(name)
exec xp_cmdshell @.sql

select * from #tb

|||

ok, this was what I was afraid of: having to always write sql code.

oh, well. Too bad the EM just doesn't give you the option to ALWAYS ask if you want to schedule what you have just done as a job. That way I could create code on the fly by drag and drop.

|||

If you feel this can make your life and others easier, file a request at http://connect.microsoft.com/sqlserver

Be sure to include a business case if you want it to have any merit.

|||

thanks

appreciate it.

rik

Thursday, March 29, 2012

Automating Restoring of *.BAK files

Automating Restoring of *.BAK files

Automating Restoring of *.BAK files

Tuesday, March 20, 2012

Automatic Restore

Monday, February 13, 2012

Auto restore .bak from Production to same server''s TEST db with other Dbs & .baks in same

Auto restore .bak from Production to same server''s TEST db with other Dbs & .baks in same

Auto restore .bak from Production to same server''s TEST db with other Dbs & .baks in same

Auto restore .bak from Production to same server''s TEST db with other Dbs & .baks in same

Sql Auto grow

Blog Archive

About Me