make sure that The 'Side margins' is not cheacked in X Axis and Y Axis tab
sql
Showing posts with label specify. Show all posts
Showing posts with label specify. Show all posts
Thursday, March 22, 2012
Bar Chart problem
I have created this report using a chart where I specify a series group as well as a category group. It works fine. The problem is the bar chart is displaying funny, the bars are REALLY thin. How can I make the bars thicker?
Friday, February 24, 2012
backup\restore password?
How and where in the GUI (Enterprise Manager) can I specify a password for
restoring databases?
Like this:
1. Create db backup job
2. set pwd
3. restore db, by specifying the pwd set in step 2
Please, if possible, show me a screen shot.
Thanks!
MagnusYou can't do it through Enterprise Manager. You need to use
T-SQL backup, restore commands. Or use SQL-DMO which also
exposes the password property through the Backup2 object
(MediaPassword property).
-Sue
On Tue, 2 May 2006 15:57:37 +0300, "Magnus sterberg"
<magnus_osterberg@.hotmail.com> wrote:
>How and where in the GUI (Enterprise Manager) can I specify a password for
>restoring databases?
>Like this:
>1. Create db backup job
>2. set pwd
>3. restore db, by specifying the pwd set in step 2
>Please, if possible, show me a screen shot.
>Thanks!
>Magnus
>|||In QA, you can make a T-SQL using password.
BACKUP DATABASE Northwind TO MyNwind_1
WITH PASSWORD='password'
RESTORE DATABASE MyNwind FROM MyNwind_1
WITH PASSWORD='password'
"Magnus ?sterberg"?? ??? ??:
> How and where in the GUI (Enterprise Manager) can I specify a password for
> restoring databases?
> Like this:
> 1. Create db backup job
> 2. set pwd
> 3. restore db, by specifying the pwd set in step 2
> Please, if possible, show me a screen shot.
> Thanks!
> Magnus
>
>|||OK, now I found out how to add this to my SQL Server Agent backup job. But..
.the pwd is stored as a part of the SQL statement in plain text. Any sa-user
can easily check up the pwd by opening the job.
Mayby one can also schedule the job by T-SQL? As in:
BACKUP DATABASE [ABC_XZY] TO DISK = N'C:\ABCDB_BACKUPS\ABC_XZY' WITH I
NIT , NOUNLOAD , NAME = N'ABC_XZY', NOSKIP , STATS = 10, NOFORMAT, PASS
WORD='sjkf7wed8', SCHEDULE=daily, 21:00
...sounds a bit far-fetched. But my idea is that I could execute the T-SQL
statement once, which should store the job in the DBMSS, without allowing an
yone to see the pwd. Possible?
Mayby I have to take care of the scheduling myself?
Magnus
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message news:fjre5254k8q0e06t68tnmrhthsigu70
57u@.4ax.com...
> You can't do it through Enterprise Manager. You need to use
> T-SQL backup, restore commands. Or use SQL-DMO which also
> exposes the password property through the Backup2 object
> (MediaPassword property).
>
> -Sue
>
> On Tue, 2 May 2006 15:57:37 +0300, "Magnus sterberg"
> <magnus_osterberg@.hotmail.com> wrote:
>
>|||anyone got an answer to this one'
"Magnus sterberg" <magnus_osterberg@.hotmail.com> wrote in message news:u2XH
eGobGHA.4040@.TK2MSFTNGP02.phx.gbl...
OK, now I found out how to add this to my SQL Server Agent backup job. But..
.the pwd is stored as a part of the SQL statement in plain text. Any sa-user
can easily check up the pwd by opening the job.
Mayby one can also schedule the job by T-SQL? As in:
BACKUP DATABASE [ABC_XZY] TO DISK = N'C:\ABCDB_BACKUPS\ABC_XZY' WITH I
NIT , NOUNLOAD , NAME = N'ABC_XZY', NOSKIP , STATS = 10, NOFORMAT, PASS
WORD='sjkf7wed8', SCHEDULE=daily, 21:00
..sounds a bit far-fetched. But my idea is that I could execute the T-SQL s
tatement once, which should store the job in the DBMSS, without allowing any
one to see the pwd. Possible?
Mayby I have to take care of the scheduling myself?
Magnus
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message news:fjre5254k8q0e06t68tnmrhthsigu70
57u@.4ax.com...
> You can't do it through Enterprise Manager. You need to use
> T-SQL backup, restore commands. Or use SQL-DMO which also
> exposes the password property through the Backup2 object
> (MediaPassword property).
>
> -Sue
>
> On Tue, 2 May 2006 15:57:37 +0300, "Magnus sterberg"
> <magnus_osterberg@.hotmail.com> wrote:
>
>|||Can't do that. Even if you could execute a statement once and have it
lie dormant inside the DBMS somehow, a sysadmin can still look in the
proc cache and see SQL statements & also at the current processes to see
what statements are running (including any sensitive information
included in the statement batch).
The closest you could come to that with pure SQL (and not some 3rd party
tool) would be to write a stored proc that contains an infinite WHILE
loop (eg. "WHILE 1=1") and in the loop you'd have the backup statement
and a "WAITFOR TIME '2:00:00'" statement (see BOL), which would put the
thread to sleep until 2am for example at which time the thread would
wake up, do the backup, loop and go to sleep again. Then you'd get SQL
Server to run the proc on start up (see sp_procoption in BOL) and you
could create the proc with the WITH ENCRYPTION option to make it a
little harder to see the password embedded in the proc. However, a
sysadmin would still be able to see that T-SQL code by querying the
appropriate system metadata (proc cache and/or current processes).
What's the point of password protecting a backup so that the sysadmins
on the box cannot restore that database when they can see the real
online live version of the DB anyway? Seems like not a very credible
situation. Why would you make a login a sysadmin if they're not allowed
to see the data in one of the databases? Isn't that the point of NDAs
(non-disclosure agreements) for DBAs?
*mike hodgson*
http://sqlnerd.blogspot.com
Magnus sterberg wrote:
> anyone got an answer to this one'
>
> "Magnus sterberg" <magnus_osterberg@.hotmail.com
> <mailto:magnus_osterberg@.hotmail.com>> wrote in message
> news:u2XHeGobGHA.4040@.TK2MSFTNGP02.phx.gbl...
> OK, now I found out how to add this to my SQL Server Agent backup
> job. But...the pwd is stored as a part of the SQL statement in
> plain text. Any sa-user can easily check up the pwd by opening the
> job.
> Mayby one can also schedule the job by T-SQL? As in:
> BACKUP DATABASE [ABC_XZY] TO DISK = N'C:\ABCDB_BACKUPS\ABC_XZY'
> WITH INIT , NOUNLOAD , NAME = N'ABC_XZY', NOSKIP , STATS =
> 10, NOFORMAT, PASSWORD='sjkf7wed8', *SCHEDULE=daily, 21:00*
> **
> ...sounds a bit far-fetched. But my idea is that I could execute
> the T-SQL statement once, which should store the job in the DBMSS,
> without allowing anyone to see the pwd. Possible?
> Mayby I have to take care of the scheduling myself?
>
> Magnus
> "Sue Hoegemeier" <Sue_H@.nomail.please
> <mailto:Sue_H@.nomail.please>> wrote in message
> news:fjre5254k8q0e06t68tnmrhthsigu7057u@.
4ax.com...
> <mailto:magnus_osterberg@.hotmail.com>> wrote:
> password for
>
restoring databases?
Like this:
1. Create db backup job
2. set pwd
3. restore db, by specifying the pwd set in step 2
Please, if possible, show me a screen shot.
Thanks!
MagnusYou can't do it through Enterprise Manager. You need to use
T-SQL backup, restore commands. Or use SQL-DMO which also
exposes the password property through the Backup2 object
(MediaPassword property).
-Sue
On Tue, 2 May 2006 15:57:37 +0300, "Magnus sterberg"
<magnus_osterberg@.hotmail.com> wrote:
>How and where in the GUI (Enterprise Manager) can I specify a password for
>restoring databases?
>Like this:
>1. Create db backup job
>2. set pwd
>3. restore db, by specifying the pwd set in step 2
>Please, if possible, show me a screen shot.
>Thanks!
>Magnus
>|||In QA, you can make a T-SQL using password.
BACKUP DATABASE Northwind TO MyNwind_1
WITH PASSWORD='password'
RESTORE DATABASE MyNwind FROM MyNwind_1
WITH PASSWORD='password'
"Magnus ?sterberg"?? ??? ??:
> How and where in the GUI (Enterprise Manager) can I specify a password for
> restoring databases?
> Like this:
> 1. Create db backup job
> 2. set pwd
> 3. restore db, by specifying the pwd set in step 2
> Please, if possible, show me a screen shot.
> Thanks!
> Magnus
>
>|||OK, now I found out how to add this to my SQL Server Agent backup job. But..
.the pwd is stored as a part of the SQL statement in plain text. Any sa-user
can easily check up the pwd by opening the job.
Mayby one can also schedule the job by T-SQL? As in:
BACKUP DATABASE [ABC_XZY] TO DISK = N'C:\ABCDB_BACKUPS\ABC_XZY' WITH I
NIT , NOUNLOAD , NAME = N'ABC_XZY', NOSKIP , STATS = 10, NOFORMAT, PASS
WORD='sjkf7wed8', SCHEDULE=daily, 21:00
...sounds a bit far-fetched. But my idea is that I could execute the T-SQL
statement once, which should store the job in the DBMSS, without allowing an
yone to see the pwd. Possible?
Mayby I have to take care of the scheduling myself?
Magnus
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message news:fjre5254k8q0e06t68tnmrhthsigu70
57u@.4ax.com...
> You can't do it through Enterprise Manager. You need to use
> T-SQL backup, restore commands. Or use SQL-DMO which also
> exposes the password property through the Backup2 object
> (MediaPassword property).
>
> -Sue
>
> On Tue, 2 May 2006 15:57:37 +0300, "Magnus sterberg"
> <magnus_osterberg@.hotmail.com> wrote:
>
>|||anyone got an answer to this one'
"Magnus sterberg" <magnus_osterberg@.hotmail.com> wrote in message news:u2XH
eGobGHA.4040@.TK2MSFTNGP02.phx.gbl...
OK, now I found out how to add this to my SQL Server Agent backup job. But..
.the pwd is stored as a part of the SQL statement in plain text. Any sa-user
can easily check up the pwd by opening the job.
Mayby one can also schedule the job by T-SQL? As in:
BACKUP DATABASE [ABC_XZY] TO DISK = N'C:\ABCDB_BACKUPS\ABC_XZY' WITH I
NIT , NOUNLOAD , NAME = N'ABC_XZY', NOSKIP , STATS = 10, NOFORMAT, PASS
WORD='sjkf7wed8', SCHEDULE=daily, 21:00
..sounds a bit far-fetched. But my idea is that I could execute the T-SQL s
tatement once, which should store the job in the DBMSS, without allowing any
one to see the pwd. Possible?
Mayby I have to take care of the scheduling myself?
Magnus
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message news:fjre5254k8q0e06t68tnmrhthsigu70
57u@.4ax.com...
> You can't do it through Enterprise Manager. You need to use
> T-SQL backup, restore commands. Or use SQL-DMO which also
> exposes the password property through the Backup2 object
> (MediaPassword property).
>
> -Sue
>
> On Tue, 2 May 2006 15:57:37 +0300, "Magnus sterberg"
> <magnus_osterberg@.hotmail.com> wrote:
>
>|||Can't do that. Even if you could execute a statement once and have it
lie dormant inside the DBMS somehow, a sysadmin can still look in the
proc cache and see SQL statements & also at the current processes to see
what statements are running (including any sensitive information
included in the statement batch).
The closest you could come to that with pure SQL (and not some 3rd party
tool) would be to write a stored proc that contains an infinite WHILE
loop (eg. "WHILE 1=1") and in the loop you'd have the backup statement
and a "WAITFOR TIME '2:00:00'" statement (see BOL), which would put the
thread to sleep until 2am for example at which time the thread would
wake up, do the backup, loop and go to sleep again. Then you'd get SQL
Server to run the proc on start up (see sp_procoption in BOL) and you
could create the proc with the WITH ENCRYPTION option to make it a
little harder to see the password embedded in the proc. However, a
sysadmin would still be able to see that T-SQL code by querying the
appropriate system metadata (proc cache and/or current processes).
What's the point of password protecting a backup so that the sysadmins
on the box cannot restore that database when they can see the real
online live version of the DB anyway? Seems like not a very credible
situation. Why would you make a login a sysadmin if they're not allowed
to see the data in one of the databases? Isn't that the point of NDAs
(non-disclosure agreements) for DBAs?
*mike hodgson*
http://sqlnerd.blogspot.com
Magnus sterberg wrote:
> anyone got an answer to this one'
>
> "Magnus sterberg" <magnus_osterberg@.hotmail.com
> <mailto:magnus_osterberg@.hotmail.com>> wrote in message
> news:u2XHeGobGHA.4040@.TK2MSFTNGP02.phx.gbl...
> OK, now I found out how to add this to my SQL Server Agent backup
> job. But...the pwd is stored as a part of the SQL statement in
> plain text. Any sa-user can easily check up the pwd by opening the
> job.
> Mayby one can also schedule the job by T-SQL? As in:
> BACKUP DATABASE [ABC_XZY] TO DISK = N'C:\ABCDB_BACKUPS\ABC_XZY'
> WITH INIT , NOUNLOAD , NAME = N'ABC_XZY', NOSKIP , STATS =
> 10, NOFORMAT, PASSWORD='sjkf7wed8', *SCHEDULE=daily, 21:00*
> **
> ...sounds a bit far-fetched. But my idea is that I could execute
> the T-SQL statement once, which should store the job in the DBMSS,
> without allowing anyone to see the pwd. Possible?
> Mayby I have to take care of the scheduling myself?
>
> Magnus
> "Sue Hoegemeier" <Sue_H@.nomail.please
> <mailto:Sue_H@.nomail.please>> wrote in message
> news:fjre5254k8q0e06t68tnmrhthsigu7057u@.
4ax.com...
> <mailto:magnus_osterberg@.hotmail.com>> wrote:
> password for
>
Backup/restore to different servers
Hi,
Is it possible to specify a server in a backup script & likewise specify a
restore to a different server e.g.
BACKUP DATABASE 'LIVESERVER..' + @.DBName
TO DISK = @.Path
Where LIVESERVER is the server you want to backup from?
Is this possible or must you backup & restore from the server you are in?
Many thanks for your help in advance
AntIt can only be a local database.
If you have configured a linked server, then you can use sp_executesql to execute the backup command
on the remote server.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:F0107985-322B-40C5-91B7-E25A6EE76FA6@.microsoft.com...
> Hi,
> Is it possible to specify a server in a backup script & likewise specify a
> restore to a different server e.g.
> BACKUP DATABASE 'LIVESERVER..' + @.DBName
> TO DISK = @.Path
> Where LIVESERVER is the server you want to backup from?
> Is this possible or must you backup & restore from the server you are in?
> Many thanks for your help in advance
> Ant|||Oh well,
Thanks very much none the less Tibor
Ant
"Tibor Karaszi" wrote:
> It can only be a local database.
> If you have configured a linked server, then you can use sp_executesql to execute the backup command
> on the remote server.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:F0107985-322B-40C5-91B7-E25A6EE76FA6@.microsoft.com...
> > Hi,
> > Is it possible to specify a server in a backup script & likewise specify a
> > restore to a different server e.g.
> >
> > BACKUP DATABASE 'LIVESERVER..' + @.DBName
> > TO DISK = @.Path
> >
> > Where LIVESERVER is the server you want to backup from?
> >
> > Is this possible or must you backup & restore from the server you are in?
> >
> > Many thanks for your help in advance
> >
> > Ant
>
>|||But you can always do it remotely from a client utility using osql or sqlcmd
and direct the backup file to a network drive. If you want to manage multiple
server instances, T-SQL may not be the best tool. A scripting language or
even a batch file would be more convenient.
Linchi
"Ant" wrote:
> Oh well,
> Thanks very much none the less Tibor
> Ant
> "Tibor Karaszi" wrote:
> > It can only be a local database.
> >
> > If you have configured a linked server, then you can use sp_executesql to execute the backup command
> > on the remote server.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://sqlblog.com/blogs/tibor_karaszi
> >
> >
> > "Ant" <Ant@.discussions.microsoft.com> wrote in message
> > news:F0107985-322B-40C5-91B7-E25A6EE76FA6@.microsoft.com...
> > > Hi,
> > > Is it possible to specify a server in a backup script & likewise specify a
> > > restore to a different server e.g.
> > >
> > > BACKUP DATABASE 'LIVESERVER..' + @.DBName
> > > TO DISK = @.Path
> > >
> > > Where LIVESERVER is the server you want to backup from?
> > >
> > > Is this possible or must you backup & restore from the server you are in?
> > >
> > > Many thanks for your help in advance
> > >
> > > Ant
> >
> >
> >|||"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:F0107985-322B-40C5-91B7-E25A6EE76FA6@.microsoft.com...
> Hi,
> Is it possible to specify a server in a backup script & likewise specify a
> restore to a different server e.g.
> BACKUP DATABASE 'LIVESERVER..' + @.DBName
> TO DISK = @.Path
> Where LIVESERVER is the server you want to backup from?
> Is this possible or must you backup & restore from the server you are in?
> Many thanks for your help in advance
> Ant
You can't quite do that.
But you can do
BACKUP DATABASE FOO to DISK ='\\otherserver\backup\backup.bkf'
Assuming you have share permissions setup correctly.
You can then on otherserver do
RESTORE DATABASE FOO from DISK='c:\backup\backup.bkf'
Or variations thereof.
And if you're really fancy, you can have a backup job on one server that
when it's done with its backup fires off a job ON the other server to do a
restore.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Hi greg,
That's pretty much what I'm after, I created a share folder & it backusp ok
but when it tries to restore it comes up with a device offline error:
Below is the sql for it: ( I removed the .bak extension off the finlesname
but didn't seem to make a differnce)
ALTER PROC MyBackupLiveData
@.Office NVARCHAR(4),
@.SecmanDB NVARCHAR(20),
@.ERPEDB NVARCHAR(20),
@.ControlDB NVARCHAR(20),
@.CompanyDB NVARCHAR(20)
AS
DECLARE @.FilePath NVARCHAR(60)
DECLARE @.FullPath NVARCHAR(60)
DECLARE @.DatePart CHAR(8)
DECLARE @.DBName NVARCHAR(30)
-- CREATE DATE TO APPEND TO BACKUP NAME
SET@.DatePart = '_' + CONVERT(NVARCHAR, DATEPART(yyyy,GETDATE()))+
CONVERT(NVARCHAR, DATEPART(mm,GETDATE())) +
CONVERT(NVARCHAR, DATEPART(dd,GETDATE()))
-- TEMP TBL TO STORE DB NAMES TO BE BACKED UP
CREATE Table #Databases(DBName NVARCHAR(20))
-- INSERT ALL TABLES PASSED INTO PROC
INSERT #Databases VALUES (@.SecmanDB)
INSERT #Databases VALUES (@.ERPDB)
INSERT #Databases VALUES (@.ControlDB)
INSERT #Databases VALUES (@.CompanyDB)
-- CREATE CURSOR
DECLARE TablesCursor CURSOR
FOR
SELECT DBName FROM #Databases
OPEN TablesCursor
FETCH NEXT FROM TablesCursor INTO @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- SET PATH & FILENAME OF BACKUP FILE
SET @.FilePath = @.Office + '\' + @.DBName + @.DatePart + '.bak'
SET @.FullPath = 'C:\Backups\' + @.FilePath
-- PERFORM BACKUP FROM LIVE SERVER
BACKUP DATABASE @.DBName
TO DISK = @.FullPath
-- PERFORM RESTORE TO TEST SERVER
SET @.FullPath = '\\etestsql\Backups\' + @.FilePath
RESTORE DATABASE @.DBName
FROM DISK = @.FullPath
FETCH NEXT FROM TablesCursor INTO @.DBName
END
-- REMOVE ALL OBJECTS
CLOSE TablesCursor
DEALLOCATE TablesCursor
DROP TABLE #Databases
-- Thanks very much for your time on this
Ant
"Greg D. Moore (Strider)" wrote:
>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:F0107985-322B-40C5-91B7-E25A6EE76FA6@.microsoft.com...
> > Hi,
> > Is it possible to specify a server in a backup script & likewise specify a
> > restore to a different server e.g.
> >
> > BACKUP DATABASE 'LIVESERVER..' + @.DBName
> > TO DISK = @.Path
> >
> > Where LIVESERVER is the server you want to backup from?
> >
> > Is this possible or must you backup & restore from the server you are in?
> >
> > Many thanks for your help in advance
> >
> > Ant
> You can't quite do that.
> But you can do
> BACKUP DATABASE FOO to DISK ='\\otherserver\backup\backup.bkf'
> Assuming you have share permissions setup correctly.
> You can then on otherserver do
> RESTORE DATABASE FOO from DISK='c:\backup\backup.bkf'
> Or variations thereof.
> And if you're really fancy, you can have a backup job on one server that
> when it's done with its backup fires off a job ON the other server to do a
> restore.
>
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
>
>|||"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:06B83767-9E27-4B0A-979F-C9CB6F398EA1@.microsoft.com...
> Hi greg,
> That's pretty much what I'm after, I created a share folder & it backusp
> ok
> but when it tries to restore it comes up with a device offline error:
Make sure the box doing the restore has access to \\etestsql\Backups\.
Remember, this needs to be in the context that the SQL Server engine runs
in.
> Below is the sql for it: ( I removed the .bak extension off the finlesname
> but didn't seem to make a differnce)
> ALTER PROC MyBackupLiveData
> @.Office NVARCHAR(4),
> @.SecmanDB NVARCHAR(20),
> @.ERPEDB NVARCHAR(20),
> @.ControlDB NVARCHAR(20),
> @.CompanyDB NVARCHAR(20)
> AS
> DECLARE @.FilePath NVARCHAR(60)
> DECLARE @.FullPath NVARCHAR(60)
> DECLARE @.DatePart CHAR(8)
> DECLARE @.DBName NVARCHAR(30)
>
> -- CREATE DATE TO APPEND TO BACKUP NAME
> SET@.DatePart = '_' + CONVERT(NVARCHAR, DATEPART(yyyy,GETDATE()))+
> CONVERT(NVARCHAR, DATEPART(mm,GETDATE())) +
> CONVERT(NVARCHAR, DATEPART(dd,GETDATE()))
>
> -- TEMP TBL TO STORE DB NAMES TO BE BACKED UP
> CREATE Table #Databases(DBName NVARCHAR(20))
>
> -- INSERT ALL TABLES PASSED INTO PROC
> INSERT #Databases VALUES (@.SecmanDB)
> INSERT #Databases VALUES (@.ERPDB)
> INSERT #Databases VALUES (@.ControlDB)
> INSERT #Databases VALUES (@.CompanyDB)
>
> -- CREATE CURSOR
> DECLARE TablesCursor CURSOR
> FOR
> SELECT DBName FROM #Databases
>
> OPEN TablesCursor
> FETCH NEXT FROM TablesCursor INTO @.DBName
>
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> -- SET PATH & FILENAME OF BACKUP FILE
> SET @.FilePath = @.Office + '\' + @.DBName + @.DatePart + '.bak'
> SET @.FullPath = 'C:\Backups\' + @.FilePath
>
> -- PERFORM BACKUP FROM LIVE SERVER
> BACKUP DATABASE @.DBName
> TO DISK = @.FullPath
>
> -- PERFORM RESTORE TO TEST SERVER
> SET @.FullPath = '\\etestsql\Backups\' + @.FilePath
> RESTORE DATABASE @.DBName
> FROM DISK = @.FullPath
>
> FETCH NEXT FROM TablesCursor INTO @.DBName
> END
>
> -- REMOVE ALL OBJECTS
> CLOSE TablesCursor
> DEALLOCATE TablesCursor
> DROP TABLE #Databases
>
> -- Thanks very much for your time on this
> Ant
>
>
>
> "Greg D. Moore (Strider)" wrote:
>>
>> "Ant" <Ant@.discussions.microsoft.com> wrote in message
>> news:F0107985-322B-40C5-91B7-E25A6EE76FA6@.microsoft.com...
>> > Hi,
>> > Is it possible to specify a server in a backup script & likewise
>> > specify a
>> > restore to a different server e.g.
>> >
>> > BACKUP DATABASE 'LIVESERVER..' + @.DBName
>> > TO DISK = @.Path
>> >
>> > Where LIVESERVER is the server you want to backup from?
>> >
>> > Is this possible or must you backup & restore from the server you are
>> > in?
>> >
>> > Many thanks for your help in advance
>> >
>> > Ant
>> You can't quite do that.
>> But you can do
>> BACKUP DATABASE FOO to DISK ='\\otherserver\backup\backup.bkf'
>> Assuming you have share permissions setup correctly.
>> You can then on otherserver do
>> RESTORE DATABASE FOO from DISK='c:\backup\backup.bkf'
>> Or variations thereof.
>> And if you're really fancy, you can have a backup job on one server that
>> when it's done with its backup fires off a job ON the other server to do
>> a
>> restore.
>>
>>
>> --
>> Greg Moore
>> SQL Server DBA Consulting Remote and Onsite available!
>> Email: sql (at) greenms.com
>> http://www.greenms.com/sqlserver.html
>>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Hi Greg,
Many thanks for your help. Your answer was in fact what helped me out here.
Much Appreciated
"Greg D. Moore (Strider)" wrote:
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:06B83767-9E27-4B0A-979F-C9CB6F398EA1@.microsoft.com...
> > Hi Greg,
> >
> > That's pretty much what I'm after, I created a share folder & it backusp
> > ok
> > but when it tries to restore it comes up with a device offline error:
> Make sure the box doing the restore has access to \\etestsql\Backups\.
> Remember, this needs to be in the context that the SQL Server engine runs
> in.
>
>
> >
> > Below is the sql for it: ( I removed the .bak extension off the finlesname
> > but didn't seem to make a differnce)
> >
> > ALTER PROC MyBackupLiveData
> >
> > @.Office NVARCHAR(4),
> > @.SecmanDB NVARCHAR(20),
> > @.ERPEDB NVARCHAR(20),
> > @.ControlDB NVARCHAR(20),
> > @.CompanyDB NVARCHAR(20)
> >
> > AS
> >
> > DECLARE @.FilePath NVARCHAR(60)
> > DECLARE @.FullPath NVARCHAR(60)
> > DECLARE @.DatePart CHAR(8)
> > DECLARE @.DBName NVARCHAR(30)
> >
> >
> >
> > -- CREATE DATE TO APPEND TO BACKUP NAME
> > SET@.DatePart = '_' + CONVERT(NVARCHAR, DATEPART(yyyy,GETDATE()))+
> > CONVERT(NVARCHAR, DATEPART(mm,GETDATE())) +
> > CONVERT(NVARCHAR, DATEPART(dd,GETDATE()))
> >
> >
> >
> > -- TEMP TBL TO STORE DB NAMES TO BE BACKED UP
> > CREATE Table #Databases(DBName NVARCHAR(20))
> >
> >
> >
> > -- INSERT ALL TABLES PASSED INTO PROC
> > INSERT #Databases VALUES (@.SecmanDB)
> > INSERT #Databases VALUES (@.ERPDB)
> > INSERT #Databases VALUES (@.ControlDB)
> > INSERT #Databases VALUES (@.CompanyDB)
> >
> >
> > -- CREATE CURSOR
> > DECLARE TablesCursor CURSOR
> > FOR
> > SELECT DBName FROM #Databases
> >
> >
> >
> > OPEN TablesCursor
> >
> > FETCH NEXT FROM TablesCursor INTO @.DBName
> >
> >
> > WHILE @.@.FETCH_STATUS = 0
> > BEGIN
> > -- SET PATH & FILENAME OF BACKUP FILE
> > SET @.FilePath = @.Office + '\' + @.DBName + @.DatePart + '.bak'
> >
> > SET @.FullPath = 'C:\Backups\' + @.FilePath
> >
> >
> > -- PERFORM BACKUP FROM LIVE SERVER
> > BACKUP DATABASE @.DBName
> > TO DISK = @.FullPath
> >
> >
> > -- PERFORM RESTORE TO TEST SERVER
> >
> > SET @.FullPath = '\\etestsql\Backups\' + @.FilePath
> >
> > RESTORE DATABASE @.DBName
> > FROM DISK = @.FullPath
> >
> >
> > FETCH NEXT FROM TablesCursor INTO @.DBName
> > END
> >
> >
> > -- REMOVE ALL OBJECTS
> >
> > CLOSE TablesCursor
> > DEALLOCATE TablesCursor
> >
> > DROP TABLE #Databases
> >
> >
> > -- Thanks very much for your time on this
> >
> > Ant
> >
> >
> >
> >
> >
> >
> > "Greg D. Moore (Strider)" wrote:
> >
> >>
> >>
> >> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> >> news:F0107985-322B-40C5-91B7-E25A6EE76FA6@.microsoft.com...
> >> > Hi,
> >> > Is it possible to specify a server in a backup script & likewise
> >> > specify a
> >> > restore to a different server e.g.
> >> >
> >> > BACKUP DATABASE 'LIVESERVER..' + @.DBName
> >> > TO DISK = @.Path
> >> >
> >> > Where LIVESERVER is the server you want to backup from?
> >> >
> >> > Is this possible or must you backup & restore from the server you are
> >> > in?
> >> >
> >> > Many thanks for your help in advance
> >> >
> >> > Ant
> >>
> >> You can't quite do that.
> >>
> >> But you can do
> >>
> >> BACKUP DATABASE FOO to DISK ='\\otherserver\backup\backup.bkf'
> >>
> >> Assuming you have share permissions setup correctly.
> >>
> >> You can then on otherserver do
> >>
> >> RESTORE DATABASE FOO from DISK='c:\backup\backup.bkf'
> >>
> >> Or variations thereof.
> >>
> >> And if you're really fancy, you can have a backup job on one server that
> >> when it's done with its backup fires off a job ON the other server to do
> >> a
> >> restore.
> >>
> >>
> >>
> >>
> >> --
> >> Greg Moore
> >> SQL Server DBA Consulting Remote and Onsite available!
> >> Email: sql (at) greenms.com
> >> http://www.greenms.com/sqlserver.html
> >>
> >>
> >>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
>
>
Is it possible to specify a server in a backup script & likewise specify a
restore to a different server e.g.
BACKUP DATABASE 'LIVESERVER..' + @.DBName
TO DISK = @.Path
Where LIVESERVER is the server you want to backup from?
Is this possible or must you backup & restore from the server you are in?
Many thanks for your help in advance
AntIt can only be a local database.
If you have configured a linked server, then you can use sp_executesql to execute the backup command
on the remote server.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:F0107985-322B-40C5-91B7-E25A6EE76FA6@.microsoft.com...
> Hi,
> Is it possible to specify a server in a backup script & likewise specify a
> restore to a different server e.g.
> BACKUP DATABASE 'LIVESERVER..' + @.DBName
> TO DISK = @.Path
> Where LIVESERVER is the server you want to backup from?
> Is this possible or must you backup & restore from the server you are in?
> Many thanks for your help in advance
> Ant|||Oh well,
Thanks very much none the less Tibor
Ant
"Tibor Karaszi" wrote:
> It can only be a local database.
> If you have configured a linked server, then you can use sp_executesql to execute the backup command
> on the remote server.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:F0107985-322B-40C5-91B7-E25A6EE76FA6@.microsoft.com...
> > Hi,
> > Is it possible to specify a server in a backup script & likewise specify a
> > restore to a different server e.g.
> >
> > BACKUP DATABASE 'LIVESERVER..' + @.DBName
> > TO DISK = @.Path
> >
> > Where LIVESERVER is the server you want to backup from?
> >
> > Is this possible or must you backup & restore from the server you are in?
> >
> > Many thanks for your help in advance
> >
> > Ant
>
>|||But you can always do it remotely from a client utility using osql or sqlcmd
and direct the backup file to a network drive. If you want to manage multiple
server instances, T-SQL may not be the best tool. A scripting language or
even a batch file would be more convenient.
Linchi
"Ant" wrote:
> Oh well,
> Thanks very much none the less Tibor
> Ant
> "Tibor Karaszi" wrote:
> > It can only be a local database.
> >
> > If you have configured a linked server, then you can use sp_executesql to execute the backup command
> > on the remote server.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://sqlblog.com/blogs/tibor_karaszi
> >
> >
> > "Ant" <Ant@.discussions.microsoft.com> wrote in message
> > news:F0107985-322B-40C5-91B7-E25A6EE76FA6@.microsoft.com...
> > > Hi,
> > > Is it possible to specify a server in a backup script & likewise specify a
> > > restore to a different server e.g.
> > >
> > > BACKUP DATABASE 'LIVESERVER..' + @.DBName
> > > TO DISK = @.Path
> > >
> > > Where LIVESERVER is the server you want to backup from?
> > >
> > > Is this possible or must you backup & restore from the server you are in?
> > >
> > > Many thanks for your help in advance
> > >
> > > Ant
> >
> >
> >|||"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:F0107985-322B-40C5-91B7-E25A6EE76FA6@.microsoft.com...
> Hi,
> Is it possible to specify a server in a backup script & likewise specify a
> restore to a different server e.g.
> BACKUP DATABASE 'LIVESERVER..' + @.DBName
> TO DISK = @.Path
> Where LIVESERVER is the server you want to backup from?
> Is this possible or must you backup & restore from the server you are in?
> Many thanks for your help in advance
> Ant
You can't quite do that.
But you can do
BACKUP DATABASE FOO to DISK ='\\otherserver\backup\backup.bkf'
Assuming you have share permissions setup correctly.
You can then on otherserver do
RESTORE DATABASE FOO from DISK='c:\backup\backup.bkf'
Or variations thereof.
And if you're really fancy, you can have a backup job on one server that
when it's done with its backup fires off a job ON the other server to do a
restore.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Hi greg,
That's pretty much what I'm after, I created a share folder & it backusp ok
but when it tries to restore it comes up with a device offline error:
Below is the sql for it: ( I removed the .bak extension off the finlesname
but didn't seem to make a differnce)
ALTER PROC MyBackupLiveData
@.Office NVARCHAR(4),
@.SecmanDB NVARCHAR(20),
@.ERPEDB NVARCHAR(20),
@.ControlDB NVARCHAR(20),
@.CompanyDB NVARCHAR(20)
AS
DECLARE @.FilePath NVARCHAR(60)
DECLARE @.FullPath NVARCHAR(60)
DECLARE @.DatePart CHAR(8)
DECLARE @.DBName NVARCHAR(30)
-- CREATE DATE TO APPEND TO BACKUP NAME
SET@.DatePart = '_' + CONVERT(NVARCHAR, DATEPART(yyyy,GETDATE()))+
CONVERT(NVARCHAR, DATEPART(mm,GETDATE())) +
CONVERT(NVARCHAR, DATEPART(dd,GETDATE()))
-- TEMP TBL TO STORE DB NAMES TO BE BACKED UP
CREATE Table #Databases(DBName NVARCHAR(20))
-- INSERT ALL TABLES PASSED INTO PROC
INSERT #Databases VALUES (@.SecmanDB)
INSERT #Databases VALUES (@.ERPDB)
INSERT #Databases VALUES (@.ControlDB)
INSERT #Databases VALUES (@.CompanyDB)
-- CREATE CURSOR
DECLARE TablesCursor CURSOR
FOR
SELECT DBName FROM #Databases
OPEN TablesCursor
FETCH NEXT FROM TablesCursor INTO @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- SET PATH & FILENAME OF BACKUP FILE
SET @.FilePath = @.Office + '\' + @.DBName + @.DatePart + '.bak'
SET @.FullPath = 'C:\Backups\' + @.FilePath
-- PERFORM BACKUP FROM LIVE SERVER
BACKUP DATABASE @.DBName
TO DISK = @.FullPath
-- PERFORM RESTORE TO TEST SERVER
SET @.FullPath = '\\etestsql\Backups\' + @.FilePath
RESTORE DATABASE @.DBName
FROM DISK = @.FullPath
FETCH NEXT FROM TablesCursor INTO @.DBName
END
-- REMOVE ALL OBJECTS
CLOSE TablesCursor
DEALLOCATE TablesCursor
DROP TABLE #Databases
-- Thanks very much for your time on this
Ant
"Greg D. Moore (Strider)" wrote:
>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:F0107985-322B-40C5-91B7-E25A6EE76FA6@.microsoft.com...
> > Hi,
> > Is it possible to specify a server in a backup script & likewise specify a
> > restore to a different server e.g.
> >
> > BACKUP DATABASE 'LIVESERVER..' + @.DBName
> > TO DISK = @.Path
> >
> > Where LIVESERVER is the server you want to backup from?
> >
> > Is this possible or must you backup & restore from the server you are in?
> >
> > Many thanks for your help in advance
> >
> > Ant
> You can't quite do that.
> But you can do
> BACKUP DATABASE FOO to DISK ='\\otherserver\backup\backup.bkf'
> Assuming you have share permissions setup correctly.
> You can then on otherserver do
> RESTORE DATABASE FOO from DISK='c:\backup\backup.bkf'
> Or variations thereof.
> And if you're really fancy, you can have a backup job on one server that
> when it's done with its backup fires off a job ON the other server to do a
> restore.
>
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
>
>|||"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:06B83767-9E27-4B0A-979F-C9CB6F398EA1@.microsoft.com...
> Hi greg,
> That's pretty much what I'm after, I created a share folder & it backusp
> ok
> but when it tries to restore it comes up with a device offline error:
Make sure the box doing the restore has access to \\etestsql\Backups\.
Remember, this needs to be in the context that the SQL Server engine runs
in.
> Below is the sql for it: ( I removed the .bak extension off the finlesname
> but didn't seem to make a differnce)
> ALTER PROC MyBackupLiveData
> @.Office NVARCHAR(4),
> @.SecmanDB NVARCHAR(20),
> @.ERPEDB NVARCHAR(20),
> @.ControlDB NVARCHAR(20),
> @.CompanyDB NVARCHAR(20)
> AS
> DECLARE @.FilePath NVARCHAR(60)
> DECLARE @.FullPath NVARCHAR(60)
> DECLARE @.DatePart CHAR(8)
> DECLARE @.DBName NVARCHAR(30)
>
> -- CREATE DATE TO APPEND TO BACKUP NAME
> SET@.DatePart = '_' + CONVERT(NVARCHAR, DATEPART(yyyy,GETDATE()))+
> CONVERT(NVARCHAR, DATEPART(mm,GETDATE())) +
> CONVERT(NVARCHAR, DATEPART(dd,GETDATE()))
>
> -- TEMP TBL TO STORE DB NAMES TO BE BACKED UP
> CREATE Table #Databases(DBName NVARCHAR(20))
>
> -- INSERT ALL TABLES PASSED INTO PROC
> INSERT #Databases VALUES (@.SecmanDB)
> INSERT #Databases VALUES (@.ERPDB)
> INSERT #Databases VALUES (@.ControlDB)
> INSERT #Databases VALUES (@.CompanyDB)
>
> -- CREATE CURSOR
> DECLARE TablesCursor CURSOR
> FOR
> SELECT DBName FROM #Databases
>
> OPEN TablesCursor
> FETCH NEXT FROM TablesCursor INTO @.DBName
>
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> -- SET PATH & FILENAME OF BACKUP FILE
> SET @.FilePath = @.Office + '\' + @.DBName + @.DatePart + '.bak'
> SET @.FullPath = 'C:\Backups\' + @.FilePath
>
> -- PERFORM BACKUP FROM LIVE SERVER
> BACKUP DATABASE @.DBName
> TO DISK = @.FullPath
>
> -- PERFORM RESTORE TO TEST SERVER
> SET @.FullPath = '\\etestsql\Backups\' + @.FilePath
> RESTORE DATABASE @.DBName
> FROM DISK = @.FullPath
>
> FETCH NEXT FROM TablesCursor INTO @.DBName
> END
>
> -- REMOVE ALL OBJECTS
> CLOSE TablesCursor
> DEALLOCATE TablesCursor
> DROP TABLE #Databases
>
> -- Thanks very much for your time on this
> Ant
>
>
>
> "Greg D. Moore (Strider)" wrote:
>>
>> "Ant" <Ant@.discussions.microsoft.com> wrote in message
>> news:F0107985-322B-40C5-91B7-E25A6EE76FA6@.microsoft.com...
>> > Hi,
>> > Is it possible to specify a server in a backup script & likewise
>> > specify a
>> > restore to a different server e.g.
>> >
>> > BACKUP DATABASE 'LIVESERVER..' + @.DBName
>> > TO DISK = @.Path
>> >
>> > Where LIVESERVER is the server you want to backup from?
>> >
>> > Is this possible or must you backup & restore from the server you are
>> > in?
>> >
>> > Many thanks for your help in advance
>> >
>> > Ant
>> You can't quite do that.
>> But you can do
>> BACKUP DATABASE FOO to DISK ='\\otherserver\backup\backup.bkf'
>> Assuming you have share permissions setup correctly.
>> You can then on otherserver do
>> RESTORE DATABASE FOO from DISK='c:\backup\backup.bkf'
>> Or variations thereof.
>> And if you're really fancy, you can have a backup job on one server that
>> when it's done with its backup fires off a job ON the other server to do
>> a
>> restore.
>>
>>
>> --
>> Greg Moore
>> SQL Server DBA Consulting Remote and Onsite available!
>> Email: sql (at) greenms.com
>> http://www.greenms.com/sqlserver.html
>>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Hi Greg,
Many thanks for your help. Your answer was in fact what helped me out here.
Much Appreciated
"Greg D. Moore (Strider)" wrote:
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:06B83767-9E27-4B0A-979F-C9CB6F398EA1@.microsoft.com...
> > Hi Greg,
> >
> > That's pretty much what I'm after, I created a share folder & it backusp
> > ok
> > but when it tries to restore it comes up with a device offline error:
> Make sure the box doing the restore has access to \\etestsql\Backups\.
> Remember, this needs to be in the context that the SQL Server engine runs
> in.
>
>
> >
> > Below is the sql for it: ( I removed the .bak extension off the finlesname
> > but didn't seem to make a differnce)
> >
> > ALTER PROC MyBackupLiveData
> >
> > @.Office NVARCHAR(4),
> > @.SecmanDB NVARCHAR(20),
> > @.ERPEDB NVARCHAR(20),
> > @.ControlDB NVARCHAR(20),
> > @.CompanyDB NVARCHAR(20)
> >
> > AS
> >
> > DECLARE @.FilePath NVARCHAR(60)
> > DECLARE @.FullPath NVARCHAR(60)
> > DECLARE @.DatePart CHAR(8)
> > DECLARE @.DBName NVARCHAR(30)
> >
> >
> >
> > -- CREATE DATE TO APPEND TO BACKUP NAME
> > SET@.DatePart = '_' + CONVERT(NVARCHAR, DATEPART(yyyy,GETDATE()))+
> > CONVERT(NVARCHAR, DATEPART(mm,GETDATE())) +
> > CONVERT(NVARCHAR, DATEPART(dd,GETDATE()))
> >
> >
> >
> > -- TEMP TBL TO STORE DB NAMES TO BE BACKED UP
> > CREATE Table #Databases(DBName NVARCHAR(20))
> >
> >
> >
> > -- INSERT ALL TABLES PASSED INTO PROC
> > INSERT #Databases VALUES (@.SecmanDB)
> > INSERT #Databases VALUES (@.ERPDB)
> > INSERT #Databases VALUES (@.ControlDB)
> > INSERT #Databases VALUES (@.CompanyDB)
> >
> >
> > -- CREATE CURSOR
> > DECLARE TablesCursor CURSOR
> > FOR
> > SELECT DBName FROM #Databases
> >
> >
> >
> > OPEN TablesCursor
> >
> > FETCH NEXT FROM TablesCursor INTO @.DBName
> >
> >
> > WHILE @.@.FETCH_STATUS = 0
> > BEGIN
> > -- SET PATH & FILENAME OF BACKUP FILE
> > SET @.FilePath = @.Office + '\' + @.DBName + @.DatePart + '.bak'
> >
> > SET @.FullPath = 'C:\Backups\' + @.FilePath
> >
> >
> > -- PERFORM BACKUP FROM LIVE SERVER
> > BACKUP DATABASE @.DBName
> > TO DISK = @.FullPath
> >
> >
> > -- PERFORM RESTORE TO TEST SERVER
> >
> > SET @.FullPath = '\\etestsql\Backups\' + @.FilePath
> >
> > RESTORE DATABASE @.DBName
> > FROM DISK = @.FullPath
> >
> >
> > FETCH NEXT FROM TablesCursor INTO @.DBName
> > END
> >
> >
> > -- REMOVE ALL OBJECTS
> >
> > CLOSE TablesCursor
> > DEALLOCATE TablesCursor
> >
> > DROP TABLE #Databases
> >
> >
> > -- Thanks very much for your time on this
> >
> > Ant
> >
> >
> >
> >
> >
> >
> > "Greg D. Moore (Strider)" wrote:
> >
> >>
> >>
> >> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> >> news:F0107985-322B-40C5-91B7-E25A6EE76FA6@.microsoft.com...
> >> > Hi,
> >> > Is it possible to specify a server in a backup script & likewise
> >> > specify a
> >> > restore to a different server e.g.
> >> >
> >> > BACKUP DATABASE 'LIVESERVER..' + @.DBName
> >> > TO DISK = @.Path
> >> >
> >> > Where LIVESERVER is the server you want to backup from?
> >> >
> >> > Is this possible or must you backup & restore from the server you are
> >> > in?
> >> >
> >> > Many thanks for your help in advance
> >> >
> >> > Ant
> >>
> >> You can't quite do that.
> >>
> >> But you can do
> >>
> >> BACKUP DATABASE FOO to DISK ='\\otherserver\backup\backup.bkf'
> >>
> >> Assuming you have share permissions setup correctly.
> >>
> >> You can then on otherserver do
> >>
> >> RESTORE DATABASE FOO from DISK='c:\backup\backup.bkf'
> >>
> >> Or variations thereof.
> >>
> >> And if you're really fancy, you can have a backup job on one server that
> >> when it's done with its backup fires off a job ON the other server to do
> >> a
> >> restore.
> >>
> >>
> >>
> >>
> >> --
> >> Greg Moore
> >> SQL Server DBA Consulting Remote and Onsite available!
> >> Email: sql (at) greenms.com
> >> http://www.greenms.com/sqlserver.html
> >>
> >>
> >>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
>
>
Backup/restore to different servers
Hi,
Is it possible to specify a server in a backup script & likewise specify a
restore to a different server e.g.
BACKUP DATABASE 'LIVESERVER..' + @.DBName
TO DISK = @.Path
Where LIVESERVER is the server you want to backup from?
Is this possible or must you backup & restore from the server you are in?
Many thanks for your help in advance
Ant
Oh well,
Thanks very much none the less Tibor
Ant
"Tibor Karaszi" wrote:
> It can only be a local database.
> If you have configured a linked server, then you can use sp_executesql to execute the backup command
> on the remote server.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:F0107985-322B-40C5-91B7-E25A6EE76FA6@.microsoft.com...
>
>
|||But you can always do it remotely from a client utility using osql or sqlcmd
and direct the backup file to a network drive. If you want to manage multiple
server instances, T-SQL may not be the best tool. A scripting language or
even a batch file would be more convenient.
Linchi
"Ant" wrote:
[vbcol=seagreen]
> Oh well,
> Thanks very much none the less Tibor
> Ant
> "Tibor Karaszi" wrote:
|||"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:F0107985-322B-40C5-91B7-E25A6EE76FA6@.microsoft.com...
> Hi,
> Is it possible to specify a server in a backup script & likewise specify a
> restore to a different server e.g.
> BACKUP DATABASE 'LIVESERVER..' + @.DBName
> TO DISK = @.Path
> Where LIVESERVER is the server you want to backup from?
> Is this possible or must you backup & restore from the server you are in?
> Many thanks for your help in advance
> Ant
You can't quite do that.
But you can do
BACKUP DATABASE FOO to DISK ='\\otherserver\backup\backup.bkf'
Assuming you have share permissions setup correctly.
You can then on otherserver do
RESTORE DATABASE FOO from DISK='c:\backup\backup.bkf'
Or variations thereof.
And if you're really fancy, you can have a backup job on one server that
when it's done with its backup fires off a job ON the other server to do a
restore.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||Hi greg,
That's pretty much what I'm after, I created a share folder & it backusp ok
but when it tries to restore it comes up with a device offline error:
Below is the sql for it: ( I removed the .bak extension off the finlesname
but didn't seem to make a differnce)
ALTER PROC MyBackupLiveData
@.Office NVARCHAR(4),
@.SecmanDB NVARCHAR(20),
@.ERPEDB NVARCHAR(20),
@.ControlDB NVARCHAR(20),
@.CompanyDB NVARCHAR(20)
AS
DECLARE @.FilePath NVARCHAR(60)
DECLARE @.FullPath NVARCHAR(60)
DECLARE @.DatePart CHAR(8)
DECLARE @.DBName NVARCHAR(30)
-- CREATE DATE TO APPEND TO BACKUP NAME
SET@.DatePart = '_' + CONVERT(NVARCHAR, DATEPART(yyyy,GETDATE()))+
CONVERT(NVARCHAR, DATEPART(mm,GETDATE())) +
CONVERT(NVARCHAR, DATEPART(dd,GETDATE()))
-- TEMP TBL TO STORE DB NAMES TO BE BACKED UP
CREATE Table #Databases(DBName NVARCHAR(20))
-- INSERT ALL TABLES PASSED INTO PROC
INSERT #Databases VALUES (@.SecmanDB)
INSERT #Databases VALUES (@.ERPDB)
INSERT #Databases VALUES (@.ControlDB)
INSERT #Databases VALUES (@.CompanyDB)
-- CREATE CURSOR
DECLARE TablesCursor CURSOR
FOR
SELECT DBName FROM #Databases
OPEN TablesCursor
FETCH NEXT FROM TablesCursor INTO @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- SET PATH & FILENAME OF BACKUP FILE
SET @.FilePath = @.Office + '\' + @.DBName + @.DatePart + '.bak'
SET @.FullPath = 'C:\Backups\' + @.FilePath
-- PERFORM BACKUP FROM LIVE SERVER
BACKUP DATABASE @.DBName
TO DISK = @.FullPath
-- PERFORM RESTORE TO TEST SERVER
SET @.FullPath = '\\etestsql\Backups\' + @.FilePath
RESTORE DATABASE @.DBName
FROM DISK = @.FullPath
FETCH NEXT FROM TablesCursor INTO @.DBName
END
-- REMOVE ALL OBJECTS
CLOSE TablesCursor
DEALLOCATE TablesCursor
DROP TABLE #Databases
-- Thanks very much for your time on this
Ant
"Greg D. Moore (Strider)" wrote:
>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:F0107985-322B-40C5-91B7-E25A6EE76FA6@.microsoft.com...
> You can't quite do that.
> But you can do
> BACKUP DATABASE FOO to DISK ='\\otherserver\backup\backup.bkf'
> Assuming you have share permissions setup correctly.
> You can then on otherserver do
> RESTORE DATABASE FOO from DISK='c:\backup\backup.bkf'
> Or variations thereof.
> And if you're really fancy, you can have a backup job on one server that
> when it's done with its backup fires off a job ON the other server to do a
> restore.
>
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
>
>
|||"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:06B83767-9E27-4B0A-979F-C9CB6F398EA1@.microsoft.com...
> Hi greg,
> That's pretty much what I'm after, I created a share folder & it backusp
> ok
> but when it tries to restore it comes up with a device offline error:
Make sure the box doing the restore has access to \\etestsql\Backups\.
Remember, this needs to be in the context that the SQL Server engine runs
in.
[vbcol=seagreen]
> Below is the sql for it: ( I removed the .bak extension off the finlesname
> but didn't seem to make a differnce)
> ALTER PROC MyBackupLiveData
> @.Office NVARCHAR(4),
> @.SecmanDB NVARCHAR(20),
> @.ERPEDB NVARCHAR(20),
> @.ControlDB NVARCHAR(20),
> @.CompanyDB NVARCHAR(20)
> AS
> DECLARE @.FilePath NVARCHAR(60)
> DECLARE @.FullPath NVARCHAR(60)
> DECLARE @.DatePart CHAR(8)
> DECLARE @.DBName NVARCHAR(30)
>
> -- CREATE DATE TO APPEND TO BACKUP NAME
> SET@.DatePart = '_' + CONVERT(NVARCHAR, DATEPART(yyyy,GETDATE()))+
> CONVERT(NVARCHAR, DATEPART(mm,GETDATE())) +
> CONVERT(NVARCHAR, DATEPART(dd,GETDATE()))
>
> -- TEMP TBL TO STORE DB NAMES TO BE BACKED UP
> CREATE Table #Databases(DBName NVARCHAR(20))
>
> -- INSERT ALL TABLES PASSED INTO PROC
> INSERT #Databases VALUES (@.SecmanDB)
> INSERT #Databases VALUES (@.ERPDB)
> INSERT #Databases VALUES (@.ControlDB)
> INSERT #Databases VALUES (@.CompanyDB)
>
> -- CREATE CURSOR
> DECLARE TablesCursor CURSOR
> FOR
> SELECT DBName FROM #Databases
>
> OPEN TablesCursor
> FETCH NEXT FROM TablesCursor INTO @.DBName
>
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> -- SET PATH & FILENAME OF BACKUP FILE
> SET @.FilePath = @.Office + '\' + @.DBName + @.DatePart + '.bak'
> SET @.FullPath = 'C:\Backups\' + @.FilePath
>
> -- PERFORM BACKUP FROM LIVE SERVER
> BACKUP DATABASE @.DBName
> TO DISK = @.FullPath
>
> -- PERFORM RESTORE TO TEST SERVER
> SET @.FullPath = '\\etestsql\Backups\' + @.FilePath
> RESTORE DATABASE @.DBName
> FROM DISK = @.FullPath
>
> FETCH NEXT FROM TablesCursor INTO @.DBName
> END
>
> -- REMOVE ALL OBJECTS
> CLOSE TablesCursor
> DEALLOCATE TablesCursor
> DROP TABLE #Databases
>
> -- Thanks very much for your time on this
> Ant
>
>
>
> "Greg D. Moore (Strider)" wrote:
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||Hi Greg,
Many thanks for your help. Your answer was in fact what helped me out here.
Much Appreciated
"Greg D. Moore (Strider)" wrote:
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:06B83767-9E27-4B0A-979F-C9CB6F398EA1@.microsoft.com...
> Make sure the box doing the restore has access to \\etestsql\Backups\.
> Remember, this needs to be in the context that the SQL Server engine runs
> in.
>
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
>
>
Is it possible to specify a server in a backup script & likewise specify a
restore to a different server e.g.
BACKUP DATABASE 'LIVESERVER..' + @.DBName
TO DISK = @.Path
Where LIVESERVER is the server you want to backup from?
Is this possible or must you backup & restore from the server you are in?
Many thanks for your help in advance
Ant
Oh well,
Thanks very much none the less Tibor
Ant
"Tibor Karaszi" wrote:
> It can only be a local database.
> If you have configured a linked server, then you can use sp_executesql to execute the backup command
> on the remote server.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:F0107985-322B-40C5-91B7-E25A6EE76FA6@.microsoft.com...
>
>
|||But you can always do it remotely from a client utility using osql or sqlcmd
and direct the backup file to a network drive. If you want to manage multiple
server instances, T-SQL may not be the best tool. A scripting language or
even a batch file would be more convenient.
Linchi
"Ant" wrote:
[vbcol=seagreen]
> Oh well,
> Thanks very much none the less Tibor
> Ant
> "Tibor Karaszi" wrote:
|||"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:F0107985-322B-40C5-91B7-E25A6EE76FA6@.microsoft.com...
> Hi,
> Is it possible to specify a server in a backup script & likewise specify a
> restore to a different server e.g.
> BACKUP DATABASE 'LIVESERVER..' + @.DBName
> TO DISK = @.Path
> Where LIVESERVER is the server you want to backup from?
> Is this possible or must you backup & restore from the server you are in?
> Many thanks for your help in advance
> Ant
You can't quite do that.
But you can do
BACKUP DATABASE FOO to DISK ='\\otherserver\backup\backup.bkf'
Assuming you have share permissions setup correctly.
You can then on otherserver do
RESTORE DATABASE FOO from DISK='c:\backup\backup.bkf'
Or variations thereof.
And if you're really fancy, you can have a backup job on one server that
when it's done with its backup fires off a job ON the other server to do a
restore.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||Hi greg,
That's pretty much what I'm after, I created a share folder & it backusp ok
but when it tries to restore it comes up with a device offline error:
Below is the sql for it: ( I removed the .bak extension off the finlesname
but didn't seem to make a differnce)
ALTER PROC MyBackupLiveData
@.Office NVARCHAR(4),
@.SecmanDB NVARCHAR(20),
@.ERPEDB NVARCHAR(20),
@.ControlDB NVARCHAR(20),
@.CompanyDB NVARCHAR(20)
AS
DECLARE @.FilePath NVARCHAR(60)
DECLARE @.FullPath NVARCHAR(60)
DECLARE @.DatePart CHAR(8)
DECLARE @.DBName NVARCHAR(30)
-- CREATE DATE TO APPEND TO BACKUP NAME
SET@.DatePart = '_' + CONVERT(NVARCHAR, DATEPART(yyyy,GETDATE()))+
CONVERT(NVARCHAR, DATEPART(mm,GETDATE())) +
CONVERT(NVARCHAR, DATEPART(dd,GETDATE()))
-- TEMP TBL TO STORE DB NAMES TO BE BACKED UP
CREATE Table #Databases(DBName NVARCHAR(20))
-- INSERT ALL TABLES PASSED INTO PROC
INSERT #Databases VALUES (@.SecmanDB)
INSERT #Databases VALUES (@.ERPDB)
INSERT #Databases VALUES (@.ControlDB)
INSERT #Databases VALUES (@.CompanyDB)
-- CREATE CURSOR
DECLARE TablesCursor CURSOR
FOR
SELECT DBName FROM #Databases
OPEN TablesCursor
FETCH NEXT FROM TablesCursor INTO @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- SET PATH & FILENAME OF BACKUP FILE
SET @.FilePath = @.Office + '\' + @.DBName + @.DatePart + '.bak'
SET @.FullPath = 'C:\Backups\' + @.FilePath
-- PERFORM BACKUP FROM LIVE SERVER
BACKUP DATABASE @.DBName
TO DISK = @.FullPath
-- PERFORM RESTORE TO TEST SERVER
SET @.FullPath = '\\etestsql\Backups\' + @.FilePath
RESTORE DATABASE @.DBName
FROM DISK = @.FullPath
FETCH NEXT FROM TablesCursor INTO @.DBName
END
-- REMOVE ALL OBJECTS
CLOSE TablesCursor
DEALLOCATE TablesCursor
DROP TABLE #Databases
-- Thanks very much for your time on this
Ant
"Greg D. Moore (Strider)" wrote:
>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:F0107985-322B-40C5-91B7-E25A6EE76FA6@.microsoft.com...
> You can't quite do that.
> But you can do
> BACKUP DATABASE FOO to DISK ='\\otherserver\backup\backup.bkf'
> Assuming you have share permissions setup correctly.
> You can then on otherserver do
> RESTORE DATABASE FOO from DISK='c:\backup\backup.bkf'
> Or variations thereof.
> And if you're really fancy, you can have a backup job on one server that
> when it's done with its backup fires off a job ON the other server to do a
> restore.
>
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
>
>
|||"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:06B83767-9E27-4B0A-979F-C9CB6F398EA1@.microsoft.com...
> Hi greg,
> That's pretty much what I'm after, I created a share folder & it backusp
> ok
> but when it tries to restore it comes up with a device offline error:
Make sure the box doing the restore has access to \\etestsql\Backups\.
Remember, this needs to be in the context that the SQL Server engine runs
in.
[vbcol=seagreen]
> Below is the sql for it: ( I removed the .bak extension off the finlesname
> but didn't seem to make a differnce)
> ALTER PROC MyBackupLiveData
> @.Office NVARCHAR(4),
> @.SecmanDB NVARCHAR(20),
> @.ERPEDB NVARCHAR(20),
> @.ControlDB NVARCHAR(20),
> @.CompanyDB NVARCHAR(20)
> AS
> DECLARE @.FilePath NVARCHAR(60)
> DECLARE @.FullPath NVARCHAR(60)
> DECLARE @.DatePart CHAR(8)
> DECLARE @.DBName NVARCHAR(30)
>
> -- CREATE DATE TO APPEND TO BACKUP NAME
> SET@.DatePart = '_' + CONVERT(NVARCHAR, DATEPART(yyyy,GETDATE()))+
> CONVERT(NVARCHAR, DATEPART(mm,GETDATE())) +
> CONVERT(NVARCHAR, DATEPART(dd,GETDATE()))
>
> -- TEMP TBL TO STORE DB NAMES TO BE BACKED UP
> CREATE Table #Databases(DBName NVARCHAR(20))
>
> -- INSERT ALL TABLES PASSED INTO PROC
> INSERT #Databases VALUES (@.SecmanDB)
> INSERT #Databases VALUES (@.ERPDB)
> INSERT #Databases VALUES (@.ControlDB)
> INSERT #Databases VALUES (@.CompanyDB)
>
> -- CREATE CURSOR
> DECLARE TablesCursor CURSOR
> FOR
> SELECT DBName FROM #Databases
>
> OPEN TablesCursor
> FETCH NEXT FROM TablesCursor INTO @.DBName
>
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> -- SET PATH & FILENAME OF BACKUP FILE
> SET @.FilePath = @.Office + '\' + @.DBName + @.DatePart + '.bak'
> SET @.FullPath = 'C:\Backups\' + @.FilePath
>
> -- PERFORM BACKUP FROM LIVE SERVER
> BACKUP DATABASE @.DBName
> TO DISK = @.FullPath
>
> -- PERFORM RESTORE TO TEST SERVER
> SET @.FullPath = '\\etestsql\Backups\' + @.FilePath
> RESTORE DATABASE @.DBName
> FROM DISK = @.FullPath
>
> FETCH NEXT FROM TablesCursor INTO @.DBName
> END
>
> -- REMOVE ALL OBJECTS
> CLOSE TablesCursor
> DEALLOCATE TablesCursor
> DROP TABLE #Databases
>
> -- Thanks very much for your time on this
> Ant
>
>
>
> "Greg D. Moore (Strider)" wrote:
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||Hi Greg,
Many thanks for your help. Your answer was in fact what helped me out here.
Much Appreciated
"Greg D. Moore (Strider)" wrote:
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:06B83767-9E27-4B0A-979F-C9CB6F398EA1@.microsoft.com...
> Make sure the box doing the restore has access to \\etestsql\Backups\.
> Remember, this needs to be in the context that the SQL Server engine runs
> in.
>
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
>
>
Subscribe to:
Posts (Atom)