Greetings...
I have a tape drive that my data base it backed up to. I'm running SQL
Server 7 and I backup the entire MSSQL7 folder.
I needed to restore some data in the database. The file was going to
restore was my_database_name.mdf.
Is that the actual database file, and if not, what is?
The reason I ask is because when looking at the file in Windows Explorer,
the date modified was from a week ago, and I know the database has been
modified since then...it changes daily of course.
So basically...if I wanted to back up the database not using the backup in
SQL server, but the one I use for my tape drive, which file or files would I
back up? Any why doesn't the date modified show the current date, since the
database has been modified today?
Thanks
Dan
It's normal that the database file has an old time stamp even it's being
changed actively. The timestamp seems to reflect the time that the file gets
created.
The .mdf file is the data file, not the backup file. you can't restore from
it. You can use sp_attach_db to attach it. If you want to restore, probably
look for a backup file (with or without extension).
HTH.
"Dan B" <none@.none.com> wrote in message
news:eLzrIfI3EHA.3416@.TK2MSFTNGP09.phx.gbl...
> Greetings...
> I have a tape drive that my data base it backed up to. I'm running SQL
> Server 7 and I backup the entire MSSQL7 folder.
> I needed to restore some data in the database. The file was going to
> restore was my_database_name.mdf.
> Is that the actual database file, and if not, what is?
> The reason I ask is because when looking at the file in Windows Explorer,
> the date modified was from a week ago, and I know the database has been
> modified since then...it changes daily of course.
> So basically...if I wanted to back up the database not using the backup
> in SQL server, but the one I use for my tape drive, which file or files
> would I back up? Any why doesn't the date modified show the current date,
> since the database has been modified today?
> Thanks
> Dan
>
|||In order to successfully backup your .MDF and .NDF files (data and log),
using the scenario that you posed, you must FIRST stop the MSSQLServer
service. While SQL Server is running, those files will be open and you will
not be able to back them up properly.
Another option is to perform an sp_detachdb and once detached, backup the
data file.
You should really use the Backup features in SQL Server however.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Dan B" <none@.none.com> wrote in message
news:eLzrIfI3EHA.3416@.TK2MSFTNGP09.phx.gbl...
> Greetings...
> I have a tape drive that my data base it backed up to. I'm running SQL
> Server 7 and I backup the entire MSSQL7 folder.
> I needed to restore some data in the database. The file was going to
> restore was my_database_name.mdf.
> Is that the actual database file, and if not, what is?
> The reason I ask is because when looking at the file in Windows Explorer,
> the date modified was from a week ago, and I know the database has been
> modified since then...it changes daily of course.
> So basically...if I wanted to back up the database not using the backup
> in SQL server, but the one I use for my tape drive, which file or files
> would I back up? Any why doesn't the date modified show the current date,
> since the database has been modified today?
> Thanks
> Dan
>
|||On Tue, 7 Dec 2004 11:08:39 -0700, "Dan B" <none@.none.com> wrote:
>So basically...if I wanted to back up the database not using the backup in
>SQL server, but the one I use for my tape drive, which file or files would I
>back up?
By default, everything in your PRIMARY filegroup is in the one .mdf
file.
You can set up additional filegroups which are separately restorable.
As usual, see BOL.
J.
Showing posts with label folder. Show all posts
Showing posts with label folder. Show all posts
Thursday, March 22, 2012
Database Backup question
Greetings...
I have a tape drive that my data base it backed up to. I'm running SQL
Server 7 and I backup the entire MSSQL7 folder.
I needed to restore some data in the database. The file was going to
restore was my_database_name.mdf.
Is that the actual database file, and if not, what is?
The reason I ask is because when looking at the file in Windows Explorer,
the date modified was from a week ago, and I know the database has been
modified since then...it changes daily of course.
So basically...if I wanted to back up the database not using the backup in
SQL server, but the one I use for my tape drive, which file or files would I
back up? Any why doesn't the date modified show the current date, since the
database has been modified today?
Thanks
Dan
It's normal that the database file has an old time stamp even it's being
changed actively. The timestamp seems to reflect the time that the file gets
created.
The .mdf file is the data file, not the backup file. you can't restore from
it. You can use sp_attach_db to attach it. If you want to restore, probably
look for a backup file (with or without extension).
HTH.
"Dan B" <none@.none.com> wrote in message
news:eLzrIfI3EHA.3416@.TK2MSFTNGP09.phx.gbl...
> Greetings...
> I have a tape drive that my data base it backed up to. I'm running SQL
> Server 7 and I backup the entire MSSQL7 folder.
> I needed to restore some data in the database. The file was going to
> restore was my_database_name.mdf.
> Is that the actual database file, and if not, what is?
> The reason I ask is because when looking at the file in Windows Explorer,
> the date modified was from a week ago, and I know the database has been
> modified since then...it changes daily of course.
> So basically...if I wanted to back up the database not using the backup
> in SQL server, but the one I use for my tape drive, which file or files
> would I back up? Any why doesn't the date modified show the current date,
> since the database has been modified today?
> Thanks
> Dan
>
|||In order to successfully backup your .MDF and .NDF files (data and log),
using the scenario that you posed, you must FIRST stop the MSSQLServer
service. While SQL Server is running, those files will be open and you will
not be able to back them up properly.
Another option is to perform an sp_detachdb and once detached, backup the
data file.
You should really use the Backup features in SQL Server however.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Dan B" <none@.none.com> wrote in message
news:eLzrIfI3EHA.3416@.TK2MSFTNGP09.phx.gbl...
> Greetings...
> I have a tape drive that my data base it backed up to. I'm running SQL
> Server 7 and I backup the entire MSSQL7 folder.
> I needed to restore some data in the database. The file was going to
> restore was my_database_name.mdf.
> Is that the actual database file, and if not, what is?
> The reason I ask is because when looking at the file in Windows Explorer,
> the date modified was from a week ago, and I know the database has been
> modified since then...it changes daily of course.
> So basically...if I wanted to back up the database not using the backup
> in SQL server, but the one I use for my tape drive, which file or files
> would I back up? Any why doesn't the date modified show the current date,
> since the database has been modified today?
> Thanks
> Dan
>
|||On Tue, 7 Dec 2004 11:08:39 -0700, "Dan B" <none@.none.com> wrote:
>So basically...if I wanted to back up the database not using the backup in
>SQL server, but the one I use for my tape drive, which file or files would I
>back up?
By default, everything in your PRIMARY filegroup is in the one .mdf
file.
You can set up additional filegroups which are separately restorable.
As usual, see BOL.
J.
I have a tape drive that my data base it backed up to. I'm running SQL
Server 7 and I backup the entire MSSQL7 folder.
I needed to restore some data in the database. The file was going to
restore was my_database_name.mdf.
Is that the actual database file, and if not, what is?
The reason I ask is because when looking at the file in Windows Explorer,
the date modified was from a week ago, and I know the database has been
modified since then...it changes daily of course.
So basically...if I wanted to back up the database not using the backup in
SQL server, but the one I use for my tape drive, which file or files would I
back up? Any why doesn't the date modified show the current date, since the
database has been modified today?
Thanks
Dan
It's normal that the database file has an old time stamp even it's being
changed actively. The timestamp seems to reflect the time that the file gets
created.
The .mdf file is the data file, not the backup file. you can't restore from
it. You can use sp_attach_db to attach it. If you want to restore, probably
look for a backup file (with or without extension).
HTH.
"Dan B" <none@.none.com> wrote in message
news:eLzrIfI3EHA.3416@.TK2MSFTNGP09.phx.gbl...
> Greetings...
> I have a tape drive that my data base it backed up to. I'm running SQL
> Server 7 and I backup the entire MSSQL7 folder.
> I needed to restore some data in the database. The file was going to
> restore was my_database_name.mdf.
> Is that the actual database file, and if not, what is?
> The reason I ask is because when looking at the file in Windows Explorer,
> the date modified was from a week ago, and I know the database has been
> modified since then...it changes daily of course.
> So basically...if I wanted to back up the database not using the backup
> in SQL server, but the one I use for my tape drive, which file or files
> would I back up? Any why doesn't the date modified show the current date,
> since the database has been modified today?
> Thanks
> Dan
>
|||In order to successfully backup your .MDF and .NDF files (data and log),
using the scenario that you posed, you must FIRST stop the MSSQLServer
service. While SQL Server is running, those files will be open and you will
not be able to back them up properly.
Another option is to perform an sp_detachdb and once detached, backup the
data file.
You should really use the Backup features in SQL Server however.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Dan B" <none@.none.com> wrote in message
news:eLzrIfI3EHA.3416@.TK2MSFTNGP09.phx.gbl...
> Greetings...
> I have a tape drive that my data base it backed up to. I'm running SQL
> Server 7 and I backup the entire MSSQL7 folder.
> I needed to restore some data in the database. The file was going to
> restore was my_database_name.mdf.
> Is that the actual database file, and if not, what is?
> The reason I ask is because when looking at the file in Windows Explorer,
> the date modified was from a week ago, and I know the database has been
> modified since then...it changes daily of course.
> So basically...if I wanted to back up the database not using the backup
> in SQL server, but the one I use for my tape drive, which file or files
> would I back up? Any why doesn't the date modified show the current date,
> since the database has been modified today?
> Thanks
> Dan
>
|||On Tue, 7 Dec 2004 11:08:39 -0700, "Dan B" <none@.none.com> wrote:
>So basically...if I wanted to back up the database not using the backup in
>SQL server, but the one I use for my tape drive, which file or files would I
>back up?
By default, everything in your PRIMARY filegroup is in the one .mdf
file.
You can set up additional filegroups which are separately restorable.
As usual, see BOL.
J.
Database Backup question
Greetings...
I have a tape drive that my data base it backed up to. I'm running SQL
Server 7 and I backup the entire MSSQL7 folder.
I needed to restore some data in the database. The file was going to
restore was my_database_name.mdf.
Is that the actual database file, and if not, what is?
The reason I ask is because when looking at the file in Windows Explorer,
the date modified was from a week ago, and I know the database has been
modified since then...it changes daily of course.
So basically...if I wanted to back up the database not using the backup in
SQL server, but the one I use for my tape drive, which file or files would I
back up? Any why doesn't the date modified show the current date, since the
database has been modified today?
Thanks
DanIn order to successfully backup your .MDF and .NDF files (data and log),
using the scenario that you posed, you must FIRST stop the MSSQLServer
service. While SQL Server is running, those files will be open and you will
not be able to back them up properly.
Another option is to perform an sp_detachdb and once detached, backup the
data file.
You should really use the Backup features in SQL Server however.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Dan B" <none@.none.com> wrote in message
news:eLzrIfI3EHA.3416@.TK2MSFTNGP09.phx.gbl...
> Greetings...
> I have a tape drive that my data base it backed up to. I'm running SQL
> Server 7 and I backup the entire MSSQL7 folder.
> I needed to restore some data in the database. The file was going to
> restore was my_database_name.mdf.
> Is that the actual database file, and if not, what is?
> The reason I ask is because when looking at the file in Windows Explorer,
> the date modified was from a week ago, and I know the database has been
> modified since then...it changes daily of course.
> So basically...if I wanted to back up the database not using the backup
> in SQL server, but the one I use for my tape drive, which file or files
> would I back up? Any why doesn't the date modified show the current date,
> since the database has been modified today?
> Thanks
> Dan
>|||It's normal that the database file has an old time stamp even it's being
changed actively. The timestamp seems to reflect the time that the file gets
created.
The .mdf file is the data file, not the backup file. you can't restore from
it. You can use sp_attach_db to attach it. If you want to restore, probably
look for a backup file (with or without extension).
HTH.
"Dan B" <none@.none.com> wrote in message
news:eLzrIfI3EHA.3416@.TK2MSFTNGP09.phx.gbl...
> Greetings...
> I have a tape drive that my data base it backed up to. I'm running SQL
> Server 7 and I backup the entire MSSQL7 folder.
> I needed to restore some data in the database. The file was going to
> restore was my_database_name.mdf.
> Is that the actual database file, and if not, what is?
> The reason I ask is because when looking at the file in Windows Explorer,
> the date modified was from a week ago, and I know the database has been
> modified since then...it changes daily of course.
> So basically...if I wanted to back up the database not using the backup
> in SQL server, but the one I use for my tape drive, which file or files
> would I back up? Any why doesn't the date modified show the current date,
> since the database has been modified today?
> Thanks
> Dan
>|||On Tue, 7 Dec 2004 11:08:39 -0700, "Dan B" <none@.none.com> wrote:
>So basically...if I wanted to back up the database not using the backup in
>SQL server, but the one I use for my tape drive, which file or files would I
>back up?
By default, everything in your PRIMARY filegroup is in the one .mdf
file.
You can set up additional filegroups which are separately restorable.
As usual, see BOL.
J.
I have a tape drive that my data base it backed up to. I'm running SQL
Server 7 and I backup the entire MSSQL7 folder.
I needed to restore some data in the database. The file was going to
restore was my_database_name.mdf.
Is that the actual database file, and if not, what is?
The reason I ask is because when looking at the file in Windows Explorer,
the date modified was from a week ago, and I know the database has been
modified since then...it changes daily of course.
So basically...if I wanted to back up the database not using the backup in
SQL server, but the one I use for my tape drive, which file or files would I
back up? Any why doesn't the date modified show the current date, since the
database has been modified today?
Thanks
DanIn order to successfully backup your .MDF and .NDF files (data and log),
using the scenario that you posed, you must FIRST stop the MSSQLServer
service. While SQL Server is running, those files will be open and you will
not be able to back them up properly.
Another option is to perform an sp_detachdb and once detached, backup the
data file.
You should really use the Backup features in SQL Server however.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Dan B" <none@.none.com> wrote in message
news:eLzrIfI3EHA.3416@.TK2MSFTNGP09.phx.gbl...
> Greetings...
> I have a tape drive that my data base it backed up to. I'm running SQL
> Server 7 and I backup the entire MSSQL7 folder.
> I needed to restore some data in the database. The file was going to
> restore was my_database_name.mdf.
> Is that the actual database file, and if not, what is?
> The reason I ask is because when looking at the file in Windows Explorer,
> the date modified was from a week ago, and I know the database has been
> modified since then...it changes daily of course.
> So basically...if I wanted to back up the database not using the backup
> in SQL server, but the one I use for my tape drive, which file or files
> would I back up? Any why doesn't the date modified show the current date,
> since the database has been modified today?
> Thanks
> Dan
>|||It's normal that the database file has an old time stamp even it's being
changed actively. The timestamp seems to reflect the time that the file gets
created.
The .mdf file is the data file, not the backup file. you can't restore from
it. You can use sp_attach_db to attach it. If you want to restore, probably
look for a backup file (with or without extension).
HTH.
"Dan B" <none@.none.com> wrote in message
news:eLzrIfI3EHA.3416@.TK2MSFTNGP09.phx.gbl...
> Greetings...
> I have a tape drive that my data base it backed up to. I'm running SQL
> Server 7 and I backup the entire MSSQL7 folder.
> I needed to restore some data in the database. The file was going to
> restore was my_database_name.mdf.
> Is that the actual database file, and if not, what is?
> The reason I ask is because when looking at the file in Windows Explorer,
> the date modified was from a week ago, and I know the database has been
> modified since then...it changes daily of course.
> So basically...if I wanted to back up the database not using the backup
> in SQL server, but the one I use for my tape drive, which file or files
> would I back up? Any why doesn't the date modified show the current date,
> since the database has been modified today?
> Thanks
> Dan
>|||On Tue, 7 Dec 2004 11:08:39 -0700, "Dan B" <none@.none.com> wrote:
>So basically...if I wanted to back up the database not using the backup in
>SQL server, but the one I use for my tape drive, which file or files would I
>back up?
By default, everything in your PRIMARY filegroup is in the one .mdf
file.
You can set up additional filegroups which are separately restorable.
As usual, see BOL.
J.
Database Backup over the network
I've read in some threads that it's possible to backup a database on a network shared folder using this..
Backup Database temp to disk = "\\servername\shared_folder\temp.bkp" with init
I have tried this first using a local folder and it succeeds but when I try to do the backup on a network shared folder, the backup fails saying unable to find backup device. I already made sure that the shared folder gives read/write access to 'Everyone"
on the sharing option. By the way, the pcs are configured only as workgroup. I read in one thread that you have be in a domain to be able to do this.
hope to get help from you guys
thanks,
ace
Hi,
Try doing this:-
1. Have a common OS user in both systems, Say name of the OS user is
backupuser.
2. Start the MSSQL Server and SQl Agent service using backupuser
How to do that:
So go to Control Panel -- Admin Tools -- Services -- MSSQL Server sercice--
Double click and select the "Log on" option. There you give a the backupuser
and password to start the service.
Now stop and start the MSSQL Serevr service.
3. Give the necessary privileges (write) in the remote machine directory
and share.
4. After this you try to execute the Backup database command in Query
Analyzer:-
Backup database <dbname> to
Disk='\\RemoteServername\share\folder\dbname.bak' with init ( With init will
overwrite the backup file every time)
I think this will definitely work out.
Thanks
Hari
MCDBA
"ace rojas" <acerojas3@.yahoo.com> wrote in message
news:B588771A-EB3A-4E0A-A438-238BB7D7102D@.microsoft.com...
> I've read in some threads that it's possible to backup a database on a
network shared folder using this..
> Backup Database temp to disk = "\\servername\shared_folder\temp.bkp" with
init
> I have tried this first using a local folder and it succeeds but when I
try to do the backup on a network shared folder, the backup fails saying
unable to find backup device. I already made sure that the shared folder
gives read/write access to 'Everyone" on the sharing option. By the way, the
pcs are configured only as workgroup. I read in one thread that you have be
in a domain to be able to do this.
> hope to get help from you guys
> thanks,
> ace
|||The problem is still permissions... Since you are in a workgroup, you will
either have to add the SQL Server login account or SQL AGent login account
as having permissions on the Other server.
The file permissions needed for backup are those for the login that sql
server service uses (when the backup is being done in SEM or Query
Analyzer), and the permissions are those that SQL Agent uses when the backup
is scheduled...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:OlVlyMGSEHA.3140@.tk2msftngp13.phx.gbl...
> Hi,
> Try doing this:-
> 1. Have a common OS user in both systems, Say name of the OS user is
> backupuser.
> 2. Start the MSSQL Server and SQl Agent service using backupuser
> How to do that:
> So go to Control Panel -- Admin Tools -- Services -- MSSQL Server
sercice--
> Double click and select the "Log on" option. There you give a the
backupuser
> and password to start the service.
> Now stop and start the MSSQL Serevr service.
> 3. Give the necessary privileges (write) in the remote machine directory
> and share.
> 4. After this you try to execute the Backup database command in Query
> Analyzer:-
> Backup database <dbname> to
> Disk='\\RemoteServername\share\folder\dbname.bak' with init ( With init
will[vbcol=seagreen]
> overwrite the backup file every time)
> I think this will definitely work out.
> Thanks
> Hari
> MCDBA
>
> "ace rojas" <acerojas3@.yahoo.com> wrote in message
> news:B588771A-EB3A-4E0A-A438-238BB7D7102D@.microsoft.com...
> network shared folder using this..
with
> init
> try to do the backup on a network shared folder, the backup fails saying
> unable to find backup device. I already made sure that the shared folder
> gives read/write access to 'Everyone" on the sharing option. By the way,
the
> pcs are configured only as workgroup. I read in one thread that you have
be
> in a domain to be able to do this.
>
|||ace rojas
alliance technologies corp, phil
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
|||Thanks guys, I did it and it worked even if the two pcs belong to
different workgroups. As long as there is common user and password for
the Sqlserver NTservice (logon as) and the PC sharing the folder on
where i'll do the backup.
thanks again
ace rojas
alliance technologies corp, phil
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
Backup Database temp to disk = "\\servername\shared_folder\temp.bkp" with init
I have tried this first using a local folder and it succeeds but when I try to do the backup on a network shared folder, the backup fails saying unable to find backup device. I already made sure that the shared folder gives read/write access to 'Everyone"
on the sharing option. By the way, the pcs are configured only as workgroup. I read in one thread that you have be in a domain to be able to do this.
hope to get help from you guys
thanks,
ace
Hi,
Try doing this:-
1. Have a common OS user in both systems, Say name of the OS user is
backupuser.
2. Start the MSSQL Server and SQl Agent service using backupuser
How to do that:
So go to Control Panel -- Admin Tools -- Services -- MSSQL Server sercice--
Double click and select the "Log on" option. There you give a the backupuser
and password to start the service.
Now stop and start the MSSQL Serevr service.
3. Give the necessary privileges (write) in the remote machine directory
and share.
4. After this you try to execute the Backup database command in Query
Analyzer:-
Backup database <dbname> to
Disk='\\RemoteServername\share\folder\dbname.bak' with init ( With init will
overwrite the backup file every time)
I think this will definitely work out.
Thanks
Hari
MCDBA
"ace rojas" <acerojas3@.yahoo.com> wrote in message
news:B588771A-EB3A-4E0A-A438-238BB7D7102D@.microsoft.com...
> I've read in some threads that it's possible to backup a database on a
network shared folder using this..
> Backup Database temp to disk = "\\servername\shared_folder\temp.bkp" with
init
> I have tried this first using a local folder and it succeeds but when I
try to do the backup on a network shared folder, the backup fails saying
unable to find backup device. I already made sure that the shared folder
gives read/write access to 'Everyone" on the sharing option. By the way, the
pcs are configured only as workgroup. I read in one thread that you have be
in a domain to be able to do this.
> hope to get help from you guys
> thanks,
> ace
|||The problem is still permissions... Since you are in a workgroup, you will
either have to add the SQL Server login account or SQL AGent login account
as having permissions on the Other server.
The file permissions needed for backup are those for the login that sql
server service uses (when the backup is being done in SEM or Query
Analyzer), and the permissions are those that SQL Agent uses when the backup
is scheduled...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:OlVlyMGSEHA.3140@.tk2msftngp13.phx.gbl...
> Hi,
> Try doing this:-
> 1. Have a common OS user in both systems, Say name of the OS user is
> backupuser.
> 2. Start the MSSQL Server and SQl Agent service using backupuser
> How to do that:
> So go to Control Panel -- Admin Tools -- Services -- MSSQL Server
sercice--
> Double click and select the "Log on" option. There you give a the
backupuser
> and password to start the service.
> Now stop and start the MSSQL Serevr service.
> 3. Give the necessary privileges (write) in the remote machine directory
> and share.
> 4. After this you try to execute the Backup database command in Query
> Analyzer:-
> Backup database <dbname> to
> Disk='\\RemoteServername\share\folder\dbname.bak' with init ( With init
will[vbcol=seagreen]
> overwrite the backup file every time)
> I think this will definitely work out.
> Thanks
> Hari
> MCDBA
>
> "ace rojas" <acerojas3@.yahoo.com> wrote in message
> news:B588771A-EB3A-4E0A-A438-238BB7D7102D@.microsoft.com...
> network shared folder using this..
with
> init
> try to do the backup on a network shared folder, the backup fails saying
> unable to find backup device. I already made sure that the shared folder
> gives read/write access to 'Everyone" on the sharing option. By the way,
the
> pcs are configured only as workgroup. I read in one thread that you have
be
> in a domain to be able to do this.
>
|||ace rojas
alliance technologies corp, phil
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
|||Thanks guys, I did it and it worked even if the two pcs belong to
different workgroups. As long as there is common user and password for
the Sqlserver NTservice (logon as) and the PC sharing the folder on
where i'll do the backup.
thanks again
ace rojas
alliance technologies corp, phil
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
Database Backup over the network
I've read in some threads that it's possible to backup a database on a network shared folder using this.
Backup Database temp to disk = "\\servername\shared_folder\temp.bkp" with ini
I have tried this first using a local folder and it succeeds but when I try to do the backup on a network shared folder, the backup fails saying unable to find backup device. I already made sure that the shared folder gives read/write access to 'Everyone" on the sharing option. By the way, the pcs are configured only as workgroup. I read in one thread that you have be in a domain to be able to do this
hope to get help from you guy
thanks
aceHi,
Try doing this:-
1. Have a common OS user in both systems, Say name of the OS user is
backupuser.
2. Start the MSSQL Server and SQl Agent service using backupuser
How to do that:
So go to Control Panel -- Admin Tools -- Services -- MSSQL Server sercice--
Double click and select the "Log on" option. There you give a the backupuser
and password to start the service.
Now stop and start the MSSQL Serevr service.
3. Give the necessary privileges (write) in the remote machine directory
and share.
4. After this you try to execute the Backup database command in Query
Analyzer:-
Backup database <dbname> to
Disk='\\RemoteServername\share\folder\dbname.bak' with init ( With init will
overwrite the backup file every time)
I think this will definitely work out.
Thanks
Hari
MCDBA
"ace rojas" <acerojas3@.yahoo.com> wrote in message
news:B588771A-EB3A-4E0A-A438-238BB7D7102D@.microsoft.com...
> I've read in some threads that it's possible to backup a database on a
network shared folder using this..
> Backup Database temp to disk = "\\servername\shared_folder\temp.bkp" with
init
> I have tried this first using a local folder and it succeeds but when I
try to do the backup on a network shared folder, the backup fails saying
unable to find backup device. I already made sure that the shared folder
gives read/write access to 'Everyone" on the sharing option. By the way, the
pcs are configured only as workgroup. I read in one thread that you have be
in a domain to be able to do this.
> hope to get help from you guys
> thanks,
> ace|||The problem is still permissions... Since you are in a workgroup, you will
either have to add the SQL Server login account or SQL AGent login account
as having permissions on the Other server.
The file permissions needed for backup are those for the login that sql
server service uses (when the backup is being done in SEM or Query
Analyzer), and the permissions are those that SQL Agent uses when the backup
is scheduled...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:OlVlyMGSEHA.3140@.tk2msftngp13.phx.gbl...
> Hi,
> Try doing this:-
> 1. Have a common OS user in both systems, Say name of the OS user is
> backupuser.
> 2. Start the MSSQL Server and SQl Agent service using backupuser
> How to do that:
> So go to Control Panel -- Admin Tools -- Services -- MSSQL Server
sercice--
> Double click and select the "Log on" option. There you give a the
backupuser
> and password to start the service.
> Now stop and start the MSSQL Serevr service.
> 3. Give the necessary privileges (write) in the remote machine directory
> and share.
> 4. After this you try to execute the Backup database command in Query
> Analyzer:-
> Backup database <dbname> to
> Disk='\\RemoteServername\share\folder\dbname.bak' with init ( With init
will
> overwrite the backup file every time)
> I think this will definitely work out.
> Thanks
> Hari
> MCDBA
>
> "ace rojas" <acerojas3@.yahoo.com> wrote in message
> news:B588771A-EB3A-4E0A-A438-238BB7D7102D@.microsoft.com...
> > I've read in some threads that it's possible to backup a database on a
> network shared folder using this..
> >
> > Backup Database temp to disk = "\\servername\shared_folder\temp.bkp"
with
> init
> >
> > I have tried this first using a local folder and it succeeds but when I
> try to do the backup on a network shared folder, the backup fails saying
> unable to find backup device. I already made sure that the shared folder
> gives read/write access to 'Everyone" on the sharing option. By the way,
the
> pcs are configured only as workgroup. I read in one thread that you have
be
> in a domain to be able to do this.
> >
> > hope to get help from you guys
> >
> > thanks,
> > ace
>
Backup Database temp to disk = "\\servername\shared_folder\temp.bkp" with ini
I have tried this first using a local folder and it succeeds but when I try to do the backup on a network shared folder, the backup fails saying unable to find backup device. I already made sure that the shared folder gives read/write access to 'Everyone" on the sharing option. By the way, the pcs are configured only as workgroup. I read in one thread that you have be in a domain to be able to do this
hope to get help from you guy
thanks
aceHi,
Try doing this:-
1. Have a common OS user in both systems, Say name of the OS user is
backupuser.
2. Start the MSSQL Server and SQl Agent service using backupuser
How to do that:
So go to Control Panel -- Admin Tools -- Services -- MSSQL Server sercice--
Double click and select the "Log on" option. There you give a the backupuser
and password to start the service.
Now stop and start the MSSQL Serevr service.
3. Give the necessary privileges (write) in the remote machine directory
and share.
4. After this you try to execute the Backup database command in Query
Analyzer:-
Backup database <dbname> to
Disk='\\RemoteServername\share\folder\dbname.bak' with init ( With init will
overwrite the backup file every time)
I think this will definitely work out.
Thanks
Hari
MCDBA
"ace rojas" <acerojas3@.yahoo.com> wrote in message
news:B588771A-EB3A-4E0A-A438-238BB7D7102D@.microsoft.com...
> I've read in some threads that it's possible to backup a database on a
network shared folder using this..
> Backup Database temp to disk = "\\servername\shared_folder\temp.bkp" with
init
> I have tried this first using a local folder and it succeeds but when I
try to do the backup on a network shared folder, the backup fails saying
unable to find backup device. I already made sure that the shared folder
gives read/write access to 'Everyone" on the sharing option. By the way, the
pcs are configured only as workgroup. I read in one thread that you have be
in a domain to be able to do this.
> hope to get help from you guys
> thanks,
> ace|||The problem is still permissions... Since you are in a workgroup, you will
either have to add the SQL Server login account or SQL AGent login account
as having permissions on the Other server.
The file permissions needed for backup are those for the login that sql
server service uses (when the backup is being done in SEM or Query
Analyzer), and the permissions are those that SQL Agent uses when the backup
is scheduled...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:OlVlyMGSEHA.3140@.tk2msftngp13.phx.gbl...
> Hi,
> Try doing this:-
> 1. Have a common OS user in both systems, Say name of the OS user is
> backupuser.
> 2. Start the MSSQL Server and SQl Agent service using backupuser
> How to do that:
> So go to Control Panel -- Admin Tools -- Services -- MSSQL Server
sercice--
> Double click and select the "Log on" option. There you give a the
backupuser
> and password to start the service.
> Now stop and start the MSSQL Serevr service.
> 3. Give the necessary privileges (write) in the remote machine directory
> and share.
> 4. After this you try to execute the Backup database command in Query
> Analyzer:-
> Backup database <dbname> to
> Disk='\\RemoteServername\share\folder\dbname.bak' with init ( With init
will
> overwrite the backup file every time)
> I think this will definitely work out.
> Thanks
> Hari
> MCDBA
>
> "ace rojas" <acerojas3@.yahoo.com> wrote in message
> news:B588771A-EB3A-4E0A-A438-238BB7D7102D@.microsoft.com...
> > I've read in some threads that it's possible to backup a database on a
> network shared folder using this..
> >
> > Backup Database temp to disk = "\\servername\shared_folder\temp.bkp"
with
> init
> >
> > I have tried this first using a local folder and it succeeds but when I
> try to do the backup on a network shared folder, the backup fails saying
> unable to find backup device. I already made sure that the shared folder
> gives read/write access to 'Everyone" on the sharing option. By the way,
the
> pcs are configured only as workgroup. I read in one thread that you have
be
> in a domain to be able to do this.
> >
> > hope to get help from you guys
> >
> > thanks,
> > ace
>
Database Backup over the network
I've read in some threads that it's possible to backup a database on a netwo
rk shared folder using this..
Backup Database temp to disk = "\\servername\shared_folder\temp.bkp" with in
it
I have tried this first using a local folder and it succeeds but when I try
to do the backup on a network shared folder, the backup fails saying unable
to find backup device. I already made sure that the shared folder gives read
/write access to 'Everyone"
on the sharing option. By the way, the pcs are configured only as workgroup.
I read in one thread that you have be in a domain to be able to do this.
hope to get help from you guys
thanks,
aceHi,
Try doing this:-
1. Have a common OS user in both systems, Say name of the OS user is
backupuser.
2. Start the MSSQL Server and SQl Agent service using backupuser
How to do that:
So go to Control Panel -- Admin Tools -- Services -- MSSQL Server sercice--
Double click and select the "Log on" option. There you give a the backupuser
and password to start the service.
Now stop and start the MSSQL Serevr service.
3. Give the necessary privileges (write) in the remote machine directory
and share.
4. After this you try to execute the Backup database command in Query
Analyzer:-
Backup database <dbname> to
Disk='\\RemoteServername\share\folder\db
name.bak' with init ( With init will
overwrite the backup file every time)
I think this will definitely work out.
Thanks
Hari
MCDBA
"ace rojas" <acerojas3@.yahoo.com> wrote in message
news:B588771A-EB3A-4E0A-A438-238BB7D7102D@.microsoft.com...
> I've read in some threads that it's possible to backup a database on a
network shared folder using this..
> Backup Database temp to disk = "\\servername\shared_folder\temp.bkp" with
init
> I have tried this first using a local folder and it succeeds but when I
try to do the backup on a network shared folder, the backup fails saying
unable to find backup device. I already made sure that the shared folder
gives read/write access to 'Everyone" on the sharing option. By the way, the
pcs are configured only as workgroup. I read in one thread that you have be
in a domain to be able to do this.
> hope to get help from you guys
> thanks,
> ace|||The problem is still permissions... Since you are in a workgroup, you will
either have to add the SQL Server login account or SQL AGent login account
as having permissions on the Other server.
The file permissions needed for backup are those for the login that sql
server service uses (when the backup is being done in SEM or Query
Analyzer), and the permissions are those that SQL Agent uses when the backup
is scheduled...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:OlVlyMGSEHA.3140@.tk2msftngp13.phx.gbl...
> Hi,
> Try doing this:-
> 1. Have a common OS user in both systems, Say name of the OS user is
> backupuser.
> 2. Start the MSSQL Server and SQl Agent service using backupuser
> How to do that:
> So go to Control Panel -- Admin Tools -- Services -- MSSQL Server
sercice--
> Double click and select the "Log on" option. There you give a the
backupuser
> and password to start the service.
> Now stop and start the MSSQL Serevr service.
> 3. Give the necessary privileges (write) in the remote machine directory
> and share.
> 4. After this you try to execute the Backup database command in Query
> Analyzer:-
> Backup database <dbname> to
> Disk='\\RemoteServername\share\folder\db
name.bak' with init ( With init
will
> overwrite the backup file every time)
> I think this will definitely work out.
> Thanks
> Hari
> MCDBA
>
> "ace rojas" <acerojas3@.yahoo.com> wrote in message
> news:B588771A-EB3A-4E0A-A438-238BB7D7102D@.microsoft.com...
> network shared folder using this..
with[vbcol=seagreen]
> init
> try to do the backup on a network shared folder, the backup fails saying
> unable to find backup device. I already made sure that the shared folder
> gives read/write access to 'Everyone" on the sharing option. By the way,
the
> pcs are configured only as workgroup. I read in one thread that you have
be
> in a domain to be able to do this.
>|||ace rojas
alliance technologies corp, phil
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!|||Thanks guys, I did it and it worked even if the two pcs belong to
different workgroups. As long as there is common user and password for
the Sqlserver NTservice (logon as) and the PC sharing the folder on
where i'll do the backup.
thanks again
ace rojas
alliance technologies corp, phil
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
rk shared folder using this..
Backup Database temp to disk = "\\servername\shared_folder\temp.bkp" with in
it
I have tried this first using a local folder and it succeeds but when I try
to do the backup on a network shared folder, the backup fails saying unable
to find backup device. I already made sure that the shared folder gives read
/write access to 'Everyone"
on the sharing option. By the way, the pcs are configured only as workgroup.
I read in one thread that you have be in a domain to be able to do this.
hope to get help from you guys
thanks,
aceHi,
Try doing this:-
1. Have a common OS user in both systems, Say name of the OS user is
backupuser.
2. Start the MSSQL Server and SQl Agent service using backupuser
How to do that:
So go to Control Panel -- Admin Tools -- Services -- MSSQL Server sercice--
Double click and select the "Log on" option. There you give a the backupuser
and password to start the service.
Now stop and start the MSSQL Serevr service.
3. Give the necessary privileges (write) in the remote machine directory
and share.
4. After this you try to execute the Backup database command in Query
Analyzer:-
Backup database <dbname> to
Disk='\\RemoteServername\share\folder\db
name.bak' with init ( With init will
overwrite the backup file every time)
I think this will definitely work out.
Thanks
Hari
MCDBA
"ace rojas" <acerojas3@.yahoo.com> wrote in message
news:B588771A-EB3A-4E0A-A438-238BB7D7102D@.microsoft.com...
> I've read in some threads that it's possible to backup a database on a
network shared folder using this..
> Backup Database temp to disk = "\\servername\shared_folder\temp.bkp" with
init
> I have tried this first using a local folder and it succeeds but when I
try to do the backup on a network shared folder, the backup fails saying
unable to find backup device. I already made sure that the shared folder
gives read/write access to 'Everyone" on the sharing option. By the way, the
pcs are configured only as workgroup. I read in one thread that you have be
in a domain to be able to do this.
> hope to get help from you guys
> thanks,
> ace|||The problem is still permissions... Since you are in a workgroup, you will
either have to add the SQL Server login account or SQL AGent login account
as having permissions on the Other server.
The file permissions needed for backup are those for the login that sql
server service uses (when the backup is being done in SEM or Query
Analyzer), and the permissions are those that SQL Agent uses when the backup
is scheduled...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:OlVlyMGSEHA.3140@.tk2msftngp13.phx.gbl...
> Hi,
> Try doing this:-
> 1. Have a common OS user in both systems, Say name of the OS user is
> backupuser.
> 2. Start the MSSQL Server and SQl Agent service using backupuser
> How to do that:
> So go to Control Panel -- Admin Tools -- Services -- MSSQL Server
sercice--
> Double click and select the "Log on" option. There you give a the
backupuser
> and password to start the service.
> Now stop and start the MSSQL Serevr service.
> 3. Give the necessary privileges (write) in the remote machine directory
> and share.
> 4. After this you try to execute the Backup database command in Query
> Analyzer:-
> Backup database <dbname> to
> Disk='\\RemoteServername\share\folder\db
name.bak' with init ( With init
will
> overwrite the backup file every time)
> I think this will definitely work out.
> Thanks
> Hari
> MCDBA
>
> "ace rojas" <acerojas3@.yahoo.com> wrote in message
> news:B588771A-EB3A-4E0A-A438-238BB7D7102D@.microsoft.com...
> network shared folder using this..
with[vbcol=seagreen]
> init
> try to do the backup on a network shared folder, the backup fails saying
> unable to find backup device. I already made sure that the shared folder
> gives read/write access to 'Everyone" on the sharing option. By the way,
the
> pcs are configured only as workgroup. I read in one thread that you have
be
> in a domain to be able to do this.
>|||ace rojas
alliance technologies corp, phil
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!|||Thanks guys, I did it and it worked even if the two pcs belong to
different workgroups. As long as there is common user and password for
the Sqlserver NTservice (logon as) and the PC sharing the folder on
where i'll do the backup.
thanks again
ace rojas
alliance technologies corp, phil
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
Wednesday, March 21, 2012
Database backup and maintenance plan
In EM, what are the diffeneces between these two under the Management folder
?
I can schedule the database backup in Backup and also can schedule the
database schedule in Database Maintenance Plans ?
I set up the full backup and schedule in 'BackUp', it can create the full
backup file in the directory but cannot view contents by right click the
item on the right pane of 'Backup' folder.Different way of scheduling, quite simply. To understand why you cannot see backups created by maint
plan, read about sp_addumpdevice. Main plan doesn't create backup devices, it addresses files
directly (see the BACKUP command and DISK = 'filename' option.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:%232W0oQCWEHA.1380@.TK2MSFTNGP09.phx.gbl...
> In EM, what are the diffeneces between these two under the Management folder
> ?
> I can schedule the database backup in Backup and also can schedule the
> database schedule in Database Maintenance Plans ?
> I set up the full backup and schedule in 'BackUp', it can create the full
> backup file in the directory but cannot view contents by right click the
> item on the right pane of 'Backup' folder.
>|||Another difference between the two is that the backup dialog box simply
allows you do create/schedule a backup..
The database maintenance plan wizard is intended to allow you to set up
several jobs which should cover most of your maintenance needs... not only
backups, but index maintenance, and dbcc checkdb...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:%232W0oQCWEHA.1380@.TK2MSFTNGP09.phx.gbl...
> In EM, what are the diffeneces between these two under the Management
folder
> ?
> I can schedule the database backup in Backup and also can schedule the
> database schedule in Database Maintenance Plans ?
> I set up the full backup and schedule in 'BackUp', it can create the full
> backup file in the directory but cannot view contents by right click the
> item on the right pane of 'Backup' folder.
>|||Thanks.
However, I have setup the backup device for the Northwind database for
example in the Management|Backup subfolder, and schedule the full and
differential backup for that, it can backup according to the schedule,
that's fine, BUT I cannot review the schedule of the backup that I created.
I had schedule the full backup at every Sunday and differential backup every
weekday at 5 pm, how do I review the schedule of these backup ?
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:uXawQ%23EWEHA.3472@.TK2MSFTNGP09.phx.gbl...
> Another difference between the two is that the backup dialog box simply
> allows you do create/schedule a backup..
> The database maintenance plan wizard is intended to allow you to set up
> several jobs which should cover most of your maintenance needs... not only
> backups, but index maintenance, and dbcc checkdb...
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
> news:%232W0oQCWEHA.1380@.TK2MSFTNGP09.phx.gbl...
> > In EM, what are the diffeneces between these two under the Management
> folder
> > ?
> >
> > I can schedule the database backup in Backup and also can schedule the
> > database schedule in Database Maintenance Plans ?
> >
> > I set up the full backup and schedule in 'BackUp', it can create the
full
> > backup file in the directory but cannot view contents by right click the
> > item on the right pane of 'Backup' folder.
> >
> >
>|||Look in the SQL Server agent folder. Each time you press "Schedule" in the backup dialog, a SQL
Server Agent job is created.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:eaL4RSLWEHA.2928@.tk2msftngp13.phx.gbl...
> Thanks.
> However, I have setup the backup device for the Northwind database for
> example in the Management|Backup subfolder, and schedule the full and
> differential backup for that, it can backup according to the schedule,
> that's fine, BUT I cannot review the schedule of the backup that I created.
> I had schedule the full backup at every Sunday and differential backup every
> weekday at 5 pm, how do I review the schedule of these backup ?
>
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:uXawQ%23EWEHA.3472@.TK2MSFTNGP09.phx.gbl...
> > Another difference between the two is that the backup dialog box simply
> > allows you do create/schedule a backup..
> >
> > The database maintenance plan wizard is intended to allow you to set up
> > several jobs which should cover most of your maintenance needs... not only
> > backups, but index maintenance, and dbcc checkdb...
> >
> >
> > --
> > Wayne Snyder, MCDBA, SQL Server MVP
> > Mariner, Charlotte, NC
> > www.mariner-usa.com
> > (Please respond only to the newsgroups.)
> >
> > I support the Professional Association of SQL Server (PASS) and it's
> > community of SQL Server professionals.
> > www.sqlpass.org
> >
> > "Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
> > news:%232W0oQCWEHA.1380@.TK2MSFTNGP09.phx.gbl...
> > > In EM, what are the diffeneces between these two under the Management
> > folder
> > > ?
> > >
> > > I can schedule the database backup in Backup and also can schedule the
> > > database schedule in Database Maintenance Plans ?
> > >
> > > I set up the full backup and schedule in 'BackUp', it can create the
> full
> > > backup file in the directory but cannot view contents by right click the
> > > item on the right pane of 'Backup' folder.
> > >
> > >
> >
> >
>|||Yes, I got it, thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ueqsT1OWEHA.1356@.TK2MSFTNGP09.phx.gbl...
> Look in the SQL Server agent folder. Each time you press "Schedule" in the
backup dialog, a SQL
> Server Agent job is created.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
> news:eaL4RSLWEHA.2928@.tk2msftngp13.phx.gbl...
> > Thanks.
> > However, I have setup the backup device for the Northwind database for
> > example in the Management|Backup subfolder, and schedule the full and
> > differential backup for that, it can backup according to the schedule,
> > that's fine, BUT I cannot review the schedule of the backup that I
created.
> >
> > I had schedule the full backup at every Sunday and differential backup
every
> > weekday at 5 pm, how do I review the schedule of these backup ?
> >
> >
> >
> > "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> > news:uXawQ%23EWEHA.3472@.TK2MSFTNGP09.phx.gbl...
> > > Another difference between the two is that the backup dialog box
simply
> > > allows you do create/schedule a backup..
> > >
> > > The database maintenance plan wizard is intended to allow you to set
up
> > > several jobs which should cover most of your maintenance needs... not
only
> > > backups, but index maintenance, and dbcc checkdb...
> > >
> > >
> > > --
> > > Wayne Snyder, MCDBA, SQL Server MVP
> > > Mariner, Charlotte, NC
> > > www.mariner-usa.com
> > > (Please respond only to the newsgroups.)
> > >
> > > I support the Professional Association of SQL Server (PASS) and it's
> > > community of SQL Server professionals.
> > > www.sqlpass.org
> > >
> > > "Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
> > > news:%232W0oQCWEHA.1380@.TK2MSFTNGP09.phx.gbl...
> > > > In EM, what are the diffeneces between these two under the
Management
> > > folder
> > > > ?
> > > >
> > > > I can schedule the database backup in Backup and also can schedule
the
> > > > database schedule in Database Maintenance Plans ?
> > > >
> > > > I set up the full backup and schedule in 'BackUp', it can create the
> > full
> > > > backup file in the directory but cannot view contents by right click
the
> > > > item on the right pane of 'Backup' folder.
> > > >
> > > >
> > >
> > >
> >
> >
>
?
I can schedule the database backup in Backup and also can schedule the
database schedule in Database Maintenance Plans ?
I set up the full backup and schedule in 'BackUp', it can create the full
backup file in the directory but cannot view contents by right click the
item on the right pane of 'Backup' folder.Different way of scheduling, quite simply. To understand why you cannot see backups created by maint
plan, read about sp_addumpdevice. Main plan doesn't create backup devices, it addresses files
directly (see the BACKUP command and DISK = 'filename' option.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:%232W0oQCWEHA.1380@.TK2MSFTNGP09.phx.gbl...
> In EM, what are the diffeneces between these two under the Management folder
> ?
> I can schedule the database backup in Backup and also can schedule the
> database schedule in Database Maintenance Plans ?
> I set up the full backup and schedule in 'BackUp', it can create the full
> backup file in the directory but cannot view contents by right click the
> item on the right pane of 'Backup' folder.
>|||Another difference between the two is that the backup dialog box simply
allows you do create/schedule a backup..
The database maintenance plan wizard is intended to allow you to set up
several jobs which should cover most of your maintenance needs... not only
backups, but index maintenance, and dbcc checkdb...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:%232W0oQCWEHA.1380@.TK2MSFTNGP09.phx.gbl...
> In EM, what are the diffeneces between these two under the Management
folder
> ?
> I can schedule the database backup in Backup and also can schedule the
> database schedule in Database Maintenance Plans ?
> I set up the full backup and schedule in 'BackUp', it can create the full
> backup file in the directory but cannot view contents by right click the
> item on the right pane of 'Backup' folder.
>|||Thanks.
However, I have setup the backup device for the Northwind database for
example in the Management|Backup subfolder, and schedule the full and
differential backup for that, it can backup according to the schedule,
that's fine, BUT I cannot review the schedule of the backup that I created.
I had schedule the full backup at every Sunday and differential backup every
weekday at 5 pm, how do I review the schedule of these backup ?
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:uXawQ%23EWEHA.3472@.TK2MSFTNGP09.phx.gbl...
> Another difference between the two is that the backup dialog box simply
> allows you do create/schedule a backup..
> The database maintenance plan wizard is intended to allow you to set up
> several jobs which should cover most of your maintenance needs... not only
> backups, but index maintenance, and dbcc checkdb...
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
> news:%232W0oQCWEHA.1380@.TK2MSFTNGP09.phx.gbl...
> > In EM, what are the diffeneces between these two under the Management
> folder
> > ?
> >
> > I can schedule the database backup in Backup and also can schedule the
> > database schedule in Database Maintenance Plans ?
> >
> > I set up the full backup and schedule in 'BackUp', it can create the
full
> > backup file in the directory but cannot view contents by right click the
> > item on the right pane of 'Backup' folder.
> >
> >
>|||Look in the SQL Server agent folder. Each time you press "Schedule" in the backup dialog, a SQL
Server Agent job is created.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:eaL4RSLWEHA.2928@.tk2msftngp13.phx.gbl...
> Thanks.
> However, I have setup the backup device for the Northwind database for
> example in the Management|Backup subfolder, and schedule the full and
> differential backup for that, it can backup according to the schedule,
> that's fine, BUT I cannot review the schedule of the backup that I created.
> I had schedule the full backup at every Sunday and differential backup every
> weekday at 5 pm, how do I review the schedule of these backup ?
>
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:uXawQ%23EWEHA.3472@.TK2MSFTNGP09.phx.gbl...
> > Another difference between the two is that the backup dialog box simply
> > allows you do create/schedule a backup..
> >
> > The database maintenance plan wizard is intended to allow you to set up
> > several jobs which should cover most of your maintenance needs... not only
> > backups, but index maintenance, and dbcc checkdb...
> >
> >
> > --
> > Wayne Snyder, MCDBA, SQL Server MVP
> > Mariner, Charlotte, NC
> > www.mariner-usa.com
> > (Please respond only to the newsgroups.)
> >
> > I support the Professional Association of SQL Server (PASS) and it's
> > community of SQL Server professionals.
> > www.sqlpass.org
> >
> > "Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
> > news:%232W0oQCWEHA.1380@.TK2MSFTNGP09.phx.gbl...
> > > In EM, what are the diffeneces between these two under the Management
> > folder
> > > ?
> > >
> > > I can schedule the database backup in Backup and also can schedule the
> > > database schedule in Database Maintenance Plans ?
> > >
> > > I set up the full backup and schedule in 'BackUp', it can create the
> full
> > > backup file in the directory but cannot view contents by right click the
> > > item on the right pane of 'Backup' folder.
> > >
> > >
> >
> >
>|||Yes, I got it, thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ueqsT1OWEHA.1356@.TK2MSFTNGP09.phx.gbl...
> Look in the SQL Server agent folder. Each time you press "Schedule" in the
backup dialog, a SQL
> Server Agent job is created.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
> news:eaL4RSLWEHA.2928@.tk2msftngp13.phx.gbl...
> > Thanks.
> > However, I have setup the backup device for the Northwind database for
> > example in the Management|Backup subfolder, and schedule the full and
> > differential backup for that, it can backup according to the schedule,
> > that's fine, BUT I cannot review the schedule of the backup that I
created.
> >
> > I had schedule the full backup at every Sunday and differential backup
every
> > weekday at 5 pm, how do I review the schedule of these backup ?
> >
> >
> >
> > "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> > news:uXawQ%23EWEHA.3472@.TK2MSFTNGP09.phx.gbl...
> > > Another difference between the two is that the backup dialog box
simply
> > > allows you do create/schedule a backup..
> > >
> > > The database maintenance plan wizard is intended to allow you to set
up
> > > several jobs which should cover most of your maintenance needs... not
only
> > > backups, but index maintenance, and dbcc checkdb...
> > >
> > >
> > > --
> > > Wayne Snyder, MCDBA, SQL Server MVP
> > > Mariner, Charlotte, NC
> > > www.mariner-usa.com
> > > (Please respond only to the newsgroups.)
> > >
> > > I support the Professional Association of SQL Server (PASS) and it's
> > > community of SQL Server professionals.
> > > www.sqlpass.org
> > >
> > > "Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
> > > news:%232W0oQCWEHA.1380@.TK2MSFTNGP09.phx.gbl...
> > > > In EM, what are the diffeneces between these two under the
Management
> > > folder
> > > > ?
> > > >
> > > > I can schedule the database backup in Backup and also can schedule
the
> > > > database schedule in Database Maintenance Plans ?
> > > >
> > > > I set up the full backup and schedule in 'BackUp', it can create the
> > full
> > > > backup file in the directory but cannot view contents by right click
the
> > > > item on the right pane of 'Backup' folder.
> > > >
> > > >
> > >
> > >
> >
> >
>
database backup
In procedure create folder for each database and backup of the
database should get saved in that folder.
For e.g : If your database name is 'Test' then in procedure create
folder 'test' and store backup of test database in test folder. Then
for database 'AdventureWorks' create folder called 'AdventureWorks'
and store backup in that folder.
Mohit
SET @.CommandString = 'IF NOT EXIST "' + @.LocalBackupPath + '\' + @.name +
'" MD "' + @.LocalBackupPath + '\' + @.name + '"'
EXEC xp_cmdshell @.CommandString
SET @.SQL = 'BACKUP DATABASE [' + @.name + '] TO DISK = N''' +
@.LocalBackupPath + '\' + @.name + '\' + @.name + CONVERT(VARCHAR, GETDATE(),
112) + '.bak'' WITH INIT, NOUNLOAD, NAME = N''' + @.name + ' backup'', SKIP,
STATS = 10, NOFORMAT'
EXEC(@.SQL)
"mohit" <goenka.mohit@.gmail.com> wrote in message
news:69386449-4e88-44f4-9081-455ab5e32941@.k2g2000hse.googlegroups.com...
> In procedure create folder for each database and backup of the
> database should get saved in that folder.
>
> For e.g : If your database name is 'Test' then in procedure create
> folder 'test' and store backup of test database in test folder. Then
> for database 'AdventureWorks' create folder called 'AdventureWorks'
> and store backup in that folder.
sql
database should get saved in that folder.
For e.g : If your database name is 'Test' then in procedure create
folder 'test' and store backup of test database in test folder. Then
for database 'AdventureWorks' create folder called 'AdventureWorks'
and store backup in that folder.
Mohit
SET @.CommandString = 'IF NOT EXIST "' + @.LocalBackupPath + '\' + @.name +
'" MD "' + @.LocalBackupPath + '\' + @.name + '"'
EXEC xp_cmdshell @.CommandString
SET @.SQL = 'BACKUP DATABASE [' + @.name + '] TO DISK = N''' +
@.LocalBackupPath + '\' + @.name + '\' + @.name + CONVERT(VARCHAR, GETDATE(),
112) + '.bak'' WITH INIT, NOUNLOAD, NAME = N''' + @.name + ' backup'', SKIP,
STATS = 10, NOFORMAT'
EXEC(@.SQL)
"mohit" <goenka.mohit@.gmail.com> wrote in message
news:69386449-4e88-44f4-9081-455ab5e32941@.k2g2000hse.googlegroups.com...
> In procedure create folder for each database and backup of the
> database should get saved in that folder.
>
> For e.g : If your database name is 'Test' then in procedure create
> folder 'test' and store backup of test database in test folder. Then
> for database 'AdventureWorks' create folder called 'AdventureWorks'
> and store backup in that folder.
sql
Monday, March 19, 2012
database backup
In procedure create folder for each database and backup of the
database should get saved in that folder.
For e.g : If your database name is 'Test' then in procedure create
folder 'test' and store backup of test database in test folder. Then
for database 'AdventureWorks' create folder called 'AdventureWorks'
and store backup in that folder.Mohit
SET @.CommandString = 'IF NOT EXIST "' + @.LocalBackupPath + '\' + @.name +
'" MD "' + @.LocalBackupPath + '\' + @.name + '"'
EXEC xp_cmdshell @.CommandString
SET @.SQL = 'BACKUP DATABASE [' + @.name + '] TO DISK = N''' +
@.LocalBackupPath + '\' + @.name + '\' + @.name + CONVERT(VARCHAR, GETDATE(),
112) + '.bak'' WITH INIT, NOUNLOAD, NAME = N''' + @.name + ' backup'', SKIP,
STATS = 10, NOFORMAT'
EXEC(@.SQL)
"mohit" <goenka.mohit@.gmail.com> wrote in message
news:69386449-4e88-44f4-9081-455ab5e32941@.k2g2000hse.googlegroups.com...
> In procedure create folder for each database and backup of the
> database should get saved in that folder.
>
> For e.g : If your database name is 'Test' then in procedure create
> folder 'test' and store backup of test database in test folder. Then
> for database 'AdventureWorks' create folder called 'AdventureWorks'
> and store backup in that folder.|||On Jan 10, 4:51=A0pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Mohit
> =A0 SET @.CommandString =3D 'IF NOT EXIST "' + @.LocalBackupPath + '\' + @.na=me +
> '" MD "' + @.LocalBackupPath + '\' + @.name + '"'
> =A0 EXEC xp_cmdshell @.CommandString
> =A0 SET @.SQL =3D 'BACKUP DATABASE [' + @.name + '] TO DISK =3D N''' +
> @.LocalBackupPath + '\' + @.name + '\' + @.name + CONVERT(VARCHAR, GETDATE(),=
> 112) + '.bak'' WITH INIT, NOUNLOAD, NAME =3D N''' + @.name + ' backup'', SK=IP,
> STATS =3D 10, NOFORMAT'
> =A0 EXEC(@.SQL)
> "mohit" <goenka.mo...@.gmail.com> wrote in message
> news:69386449-4e88-44f4-9081-455ab5e32941@.k2g2000hse.googlegroups.com...
>
> > In procedure create folder for each database and backup of the
> > database should get saved in that folder.
> > For e.g =A0: If your database name is 'Test' then in procedure create
> > folder 'test' and store backup of test database in test folder. Then
> > for database 'AdventureWorks' create folder called 'AdventureWorks'
> > and store backup in that folder.- Hide quoted text -
> - Show quoted text -
hi,
i dont have admin permission to execute xp_cmdshell. Is there any
other option to solve this question.
database should get saved in that folder.
For e.g : If your database name is 'Test' then in procedure create
folder 'test' and store backup of test database in test folder. Then
for database 'AdventureWorks' create folder called 'AdventureWorks'
and store backup in that folder.Mohit
SET @.CommandString = 'IF NOT EXIST "' + @.LocalBackupPath + '\' + @.name +
'" MD "' + @.LocalBackupPath + '\' + @.name + '"'
EXEC xp_cmdshell @.CommandString
SET @.SQL = 'BACKUP DATABASE [' + @.name + '] TO DISK = N''' +
@.LocalBackupPath + '\' + @.name + '\' + @.name + CONVERT(VARCHAR, GETDATE(),
112) + '.bak'' WITH INIT, NOUNLOAD, NAME = N''' + @.name + ' backup'', SKIP,
STATS = 10, NOFORMAT'
EXEC(@.SQL)
"mohit" <goenka.mohit@.gmail.com> wrote in message
news:69386449-4e88-44f4-9081-455ab5e32941@.k2g2000hse.googlegroups.com...
> In procedure create folder for each database and backup of the
> database should get saved in that folder.
>
> For e.g : If your database name is 'Test' then in procedure create
> folder 'test' and store backup of test database in test folder. Then
> for database 'AdventureWorks' create folder called 'AdventureWorks'
> and store backup in that folder.|||On Jan 10, 4:51=A0pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Mohit
> =A0 SET @.CommandString =3D 'IF NOT EXIST "' + @.LocalBackupPath + '\' + @.na=me +
> '" MD "' + @.LocalBackupPath + '\' + @.name + '"'
> =A0 EXEC xp_cmdshell @.CommandString
> =A0 SET @.SQL =3D 'BACKUP DATABASE [' + @.name + '] TO DISK =3D N''' +
> @.LocalBackupPath + '\' + @.name + '\' + @.name + CONVERT(VARCHAR, GETDATE(),=
> 112) + '.bak'' WITH INIT, NOUNLOAD, NAME =3D N''' + @.name + ' backup'', SK=IP,
> STATS =3D 10, NOFORMAT'
> =A0 EXEC(@.SQL)
> "mohit" <goenka.mo...@.gmail.com> wrote in message
> news:69386449-4e88-44f4-9081-455ab5e32941@.k2g2000hse.googlegroups.com...
>
> > In procedure create folder for each database and backup of the
> > database should get saved in that folder.
> > For e.g =A0: If your database name is 'Test' then in procedure create
> > folder 'test' and store backup of test database in test folder. Then
> > for database 'AdventureWorks' create folder called 'AdventureWorks'
> > and store backup in that folder.- Hide quoted text -
> - Show quoted text -
hi,
i dont have admin permission to execute xp_cmdshell. Is there any
other option to solve this question.
Subscribe to:
Posts (Atom)