We use SQL for a few minor applications, and currently, we just down the
service at night and run a regular tape backup. However, that is no longer
a solution (SQL has to be up at night) so I'm researching using the built-in
backup features of SQL.
My question is this. I set up a test database, and just did a few backups
of it to a file. When I do a restore database, where is it pulling the
restore information from? (The list of all backups that I have done). If I
delete the actual backup files, I still see the list of all the backups I
have done, even though the actual backup file was deleted. Is there a way
to clear that information out?
Also, if I do a simple backup of each database at night, overwriting the
backup file, how many of the backups will it list? Is this behavior
controllable?
Thanks.Michael,
The backup information is stored in the system tables in the MSDB database.
See sp_delete_backuphistory for clearing the backup history info for a job.
A backup to a file will apend the backup to the file by default if overwrite
is specified only one backup will exist on the file at a time however the
backup history will probably still show all of the backups for the database.
HTH
Jerry
"Michael Long" <michael.long@.jrsmith.com> wrote in message
news:e0tV2LD0FHA.3180@.TK2MSFTNGP14.phx.gbl...
> We use SQL for a few minor applications, and currently, we just down the
> service at night and run a regular tape backup. However, that is no
> longer a solution (SQL has to be up at night) so I'm researching using the
> built-in backup features of SQL.
> My question is this. I set up a test database, and just did a few
> backups of it to a file. When I do a restore database, where is it
> pulling the restore information from? (The list of all backups that I
> have done). If I delete the actual backup files, I still see the list of
> all the backups I have done, even though the actual backup file was
> deleted. Is there a way to clear that information out?
> Also, if I do a simple backup of each database at night, overwriting the
> backup file, how many of the backups will it list? Is this behavior
> controllable?
> Thanks.
>|||Hi,
All the backup information will be stored in backupset and backupfile tables
in MSDB database and all the restore information
is stored in restorehistory table in MSDB.
1. When I do a restore database, where is it pulling the restore information
from?
From the table mentioned above from MSDB
Is there a way to clear that information out?
Use msdb
go
sp_delete_backuphistory 'Until_date_forwhich_history_needs to removed'
overwriting the backup file, how many of the backups will it list?
It lists all the backups becase this information is fetched from MSDB table,
Thanks
Hari
SQL Server MVP
"Michael Long" <michael.long@.jrsmith.com> wrote in message
news:e0tV2LD0FHA.3180@.TK2MSFTNGP14.phx.gbl...
> We use SQL for a few minor applications, and currently, we just down the
> service at night and run a regular tape backup. However, that is no
> longer a solution (SQL has to be up at night) so I'm researching using the
> built-in backup features of SQL.
> My question is this. I set up a test database, and just did a few
> backups of it to a file. When I do a restore database, where is it
> pulling the restore information from? (The list of all backups that I
> have done). If I delete the actual backup files, I still see the list of
> all the backups I have done, even though the actual backup file was
> deleted. Is there a way to clear that information out?
> Also, if I do a simple backup of each database at night, overwriting the
> backup file, how many of the backups will it list? Is this behavior
> controllable?
> Thanks.
>sql
Showing posts with label minor. Show all posts
Showing posts with label minor. Show all posts
Tuesday, March 20, 2012
Backup Question For Newbie
We use SQL for a few minor applications, and currently, we just down the
service at night and run a regular tape backup. However, that is no longer
a solution (SQL has to be up at night) so I'm researching using the built-in
backup features of SQL.
My question is this. I set up a test database, and just did a few backups
of it to a file. When I do a restore database, where is it pulling the
restore information from? (The list of all backups that I have done). If I
delete the actual backup files, I still see the list of all the backups I
have done, even though the actual backup file was deleted. Is there a way
to clear that information out?
Also, if I do a simple backup of each database at night, overwriting the
backup file, how many of the backups will it list? Is this behavior
controllable?
Thanks.
Michael,
The backup information is stored in the system tables in the MSDB database.
See sp_delete_backuphistory for clearing the backup history info for a job.
A backup to a file will apend the backup to the file by default if overwrite
is specified only one backup will exist on the file at a time however the
backup history will probably still show all of the backups for the database.
HTH
Jerry
"Michael Long" <michael.long@.jrsmith.com> wrote in message
news:e0tV2LD0FHA.3180@.TK2MSFTNGP14.phx.gbl...
> We use SQL for a few minor applications, and currently, we just down the
> service at night and run a regular tape backup. However, that is no
> longer a solution (SQL has to be up at night) so I'm researching using the
> built-in backup features of SQL.
> My question is this. I set up a test database, and just did a few
> backups of it to a file. When I do a restore database, where is it
> pulling the restore information from? (The list of all backups that I
> have done). If I delete the actual backup files, I still see the list of
> all the backups I have done, even though the actual backup file was
> deleted. Is there a way to clear that information out?
> Also, if I do a simple backup of each database at night, overwriting the
> backup file, how many of the backups will it list? Is this behavior
> controllable?
> Thanks.
>
|||Hi,
All the backup information will be stored in backupset and backupfile tables
in MSDB database and all the restore information
is stored in restorehistory table in MSDB.
1. When I do a restore database, where is it pulling the restore information
from?
From the table mentioned above from MSDB
Is there a way to clear that information out?
Use msdb
go
sp_delete_backuphistory 'Until_date_forwhich_history_needs to removed'
overwriting the backup file, how many of the backups will it list?
It lists all the backups becase this information is fetched from MSDB table,
Thanks
Hari
SQL Server MVP
"Michael Long" <michael.long@.jrsmith.com> wrote in message
news:e0tV2LD0FHA.3180@.TK2MSFTNGP14.phx.gbl...
> We use SQL for a few minor applications, and currently, we just down the
> service at night and run a regular tape backup. However, that is no
> longer a solution (SQL has to be up at night) so I'm researching using the
> built-in backup features of SQL.
> My question is this. I set up a test database, and just did a few
> backups of it to a file. When I do a restore database, where is it
> pulling the restore information from? (The list of all backups that I
> have done). If I delete the actual backup files, I still see the list of
> all the backups I have done, even though the actual backup file was
> deleted. Is there a way to clear that information out?
> Also, if I do a simple backup of each database at night, overwriting the
> backup file, how many of the backups will it list? Is this behavior
> controllable?
> Thanks.
>
service at night and run a regular tape backup. However, that is no longer
a solution (SQL has to be up at night) so I'm researching using the built-in
backup features of SQL.
My question is this. I set up a test database, and just did a few backups
of it to a file. When I do a restore database, where is it pulling the
restore information from? (The list of all backups that I have done). If I
delete the actual backup files, I still see the list of all the backups I
have done, even though the actual backup file was deleted. Is there a way
to clear that information out?
Also, if I do a simple backup of each database at night, overwriting the
backup file, how many of the backups will it list? Is this behavior
controllable?
Thanks.
Michael,
The backup information is stored in the system tables in the MSDB database.
See sp_delete_backuphistory for clearing the backup history info for a job.
A backup to a file will apend the backup to the file by default if overwrite
is specified only one backup will exist on the file at a time however the
backup history will probably still show all of the backups for the database.
HTH
Jerry
"Michael Long" <michael.long@.jrsmith.com> wrote in message
news:e0tV2LD0FHA.3180@.TK2MSFTNGP14.phx.gbl...
> We use SQL for a few minor applications, and currently, we just down the
> service at night and run a regular tape backup. However, that is no
> longer a solution (SQL has to be up at night) so I'm researching using the
> built-in backup features of SQL.
> My question is this. I set up a test database, and just did a few
> backups of it to a file. When I do a restore database, where is it
> pulling the restore information from? (The list of all backups that I
> have done). If I delete the actual backup files, I still see the list of
> all the backups I have done, even though the actual backup file was
> deleted. Is there a way to clear that information out?
> Also, if I do a simple backup of each database at night, overwriting the
> backup file, how many of the backups will it list? Is this behavior
> controllable?
> Thanks.
>
|||Hi,
All the backup information will be stored in backupset and backupfile tables
in MSDB database and all the restore information
is stored in restorehistory table in MSDB.
1. When I do a restore database, where is it pulling the restore information
from?
From the table mentioned above from MSDB
Is there a way to clear that information out?
Use msdb
go
sp_delete_backuphistory 'Until_date_forwhich_history_needs to removed'
overwriting the backup file, how many of the backups will it list?
It lists all the backups becase this information is fetched from MSDB table,
Thanks
Hari
SQL Server MVP
"Michael Long" <michael.long@.jrsmith.com> wrote in message
news:e0tV2LD0FHA.3180@.TK2MSFTNGP14.phx.gbl...
> We use SQL for a few minor applications, and currently, we just down the
> service at night and run a regular tape backup. However, that is no
> longer a solution (SQL has to be up at night) so I'm researching using the
> built-in backup features of SQL.
> My question is this. I set up a test database, and just did a few
> backups of it to a file. When I do a restore database, where is it
> pulling the restore information from? (The list of all backups that I
> have done). If I delete the actual backup files, I still see the list of
> all the backups I have done, even though the actual backup file was
> deleted. Is there a way to clear that information out?
> Also, if I do a simple backup of each database at night, overwriting the
> backup file, how many of the backups will it list? Is this behavior
> controllable?
> Thanks.
>
Backup Question For Newbie
We use SQL for a few minor applications, and currently, we just down the
service at night and run a regular tape backup. However, that is no longer
a solution (SQL has to be up at night) so I'm researching using the built-in
backup features of SQL.
My question is this. I set up a test database, and just did a few backups
of it to a file. When I do a restore database, where is it pulling the
restore information from? (The list of all backups that I have done). If I
delete the actual backup files, I still see the list of all the backups I
have done, even though the actual backup file was deleted. Is there a way
to clear that information out?
Also, if I do a simple backup of each database at night, overwriting the
backup file, how many of the backups will it list? Is this behavior
controllable?
Thanks.Michael,
The backup information is stored in the system tables in the MSDB database.
See sp_delete_backuphistory for clearing the backup history info for a job.
A backup to a file will apend the backup to the file by default if overwrite
is specified only one backup will exist on the file at a time however the
backup history will probably still show all of the backups for the database.
HTH
Jerry
"Michael Long" <michael.long@.jrsmith.com> wrote in message
news:e0tV2LD0FHA.3180@.TK2MSFTNGP14.phx.gbl...
> We use SQL for a few minor applications, and currently, we just down the
> service at night and run a regular tape backup. However, that is no
> longer a solution (SQL has to be up at night) so I'm researching using the
> built-in backup features of SQL.
> My question is this. I set up a test database, and just did a few
> backups of it to a file. When I do a restore database, where is it
> pulling the restore information from? (The list of all backups that I
> have done). If I delete the actual backup files, I still see the list of
> all the backups I have done, even though the actual backup file was
> deleted. Is there a way to clear that information out?
> Also, if I do a simple backup of each database at night, overwriting the
> backup file, how many of the backups will it list? Is this behavior
> controllable?
> Thanks.
>|||Hi,
All the backup information will be stored in backupset and backupfile tables
in MSDB database and all the restore information
is stored in restorehistory table in MSDB.
1. When I do a restore database, where is it pulling the restore information
from?
From the table mentioned above from MSDB
Is there a way to clear that information out?
Use msdb
go
sp_delete_backuphistory 'Until_date_forwhich_history_needs to removed'
overwriting the backup file, how many of the backups will it list?
It lists all the backups becase this information is fetched from MSDB table,
Thanks
Hari
SQL Server MVP
"Michael Long" <michael.long@.jrsmith.com> wrote in message
news:e0tV2LD0FHA.3180@.TK2MSFTNGP14.phx.gbl...
> We use SQL for a few minor applications, and currently, we just down the
> service at night and run a regular tape backup. However, that is no
> longer a solution (SQL has to be up at night) so I'm researching using the
> built-in backup features of SQL.
> My question is this. I set up a test database, and just did a few
> backups of it to a file. When I do a restore database, where is it
> pulling the restore information from? (The list of all backups that I
> have done). If I delete the actual backup files, I still see the list of
> all the backups I have done, even though the actual backup file was
> deleted. Is there a way to clear that information out?
> Also, if I do a simple backup of each database at night, overwriting the
> backup file, how many of the backups will it list? Is this behavior
> controllable?
> Thanks.
>
service at night and run a regular tape backup. However, that is no longer
a solution (SQL has to be up at night) so I'm researching using the built-in
backup features of SQL.
My question is this. I set up a test database, and just did a few backups
of it to a file. When I do a restore database, where is it pulling the
restore information from? (The list of all backups that I have done). If I
delete the actual backup files, I still see the list of all the backups I
have done, even though the actual backup file was deleted. Is there a way
to clear that information out?
Also, if I do a simple backup of each database at night, overwriting the
backup file, how many of the backups will it list? Is this behavior
controllable?
Thanks.Michael,
The backup information is stored in the system tables in the MSDB database.
See sp_delete_backuphistory for clearing the backup history info for a job.
A backup to a file will apend the backup to the file by default if overwrite
is specified only one backup will exist on the file at a time however the
backup history will probably still show all of the backups for the database.
HTH
Jerry
"Michael Long" <michael.long@.jrsmith.com> wrote in message
news:e0tV2LD0FHA.3180@.TK2MSFTNGP14.phx.gbl...
> We use SQL for a few minor applications, and currently, we just down the
> service at night and run a regular tape backup. However, that is no
> longer a solution (SQL has to be up at night) so I'm researching using the
> built-in backup features of SQL.
> My question is this. I set up a test database, and just did a few
> backups of it to a file. When I do a restore database, where is it
> pulling the restore information from? (The list of all backups that I
> have done). If I delete the actual backup files, I still see the list of
> all the backups I have done, even though the actual backup file was
> deleted. Is there a way to clear that information out?
> Also, if I do a simple backup of each database at night, overwriting the
> backup file, how many of the backups will it list? Is this behavior
> controllable?
> Thanks.
>|||Hi,
All the backup information will be stored in backupset and backupfile tables
in MSDB database and all the restore information
is stored in restorehistory table in MSDB.
1. When I do a restore database, where is it pulling the restore information
from?
From the table mentioned above from MSDB
Is there a way to clear that information out?
Use msdb
go
sp_delete_backuphistory 'Until_date_forwhich_history_needs to removed'
overwriting the backup file, how many of the backups will it list?
It lists all the backups becase this information is fetched from MSDB table,
Thanks
Hari
SQL Server MVP
"Michael Long" <michael.long@.jrsmith.com> wrote in message
news:e0tV2LD0FHA.3180@.TK2MSFTNGP14.phx.gbl...
> We use SQL for a few minor applications, and currently, we just down the
> service at night and run a regular tape backup. However, that is no
> longer a solution (SQL has to be up at night) so I'm researching using the
> built-in backup features of SQL.
> My question is this. I set up a test database, and just did a few
> backups of it to a file. When I do a restore database, where is it
> pulling the restore information from? (The list of all backups that I
> have done). If I delete the actual backup files, I still see the list of
> all the backups I have done, even though the actual backup file was
> deleted. Is there a way to clear that information out?
> Also, if I do a simple backup of each database at night, overwriting the
> backup file, how many of the backups will it list? Is this behavior
> controllable?
> Thanks.
>
Saturday, February 25, 2012
Backup of databases within SQL Server 2005 Express
Hi there!
Is there any replacement for the missing SQL Server Agent which could be configured to backup "all databases" and fix minor errors in databases, rebuild indexes, etc.?
Or let's better ask: is there any solution/tool out there to create and execute maintenance plans without the need of SQL Server Agent? (It might be a simple tool that enumerates the DBs and executes some T-SQL statements on them)
Thanks
JochenManagement Studio Express provides UI to do those things, but there is no Agent in SQL Server Express, so the tasks can only be executed once in the UI.
Instead, you could use the UI to create the T-SQL, script it to a file rather than execute it, and then use the Windows scheduler to launch sqlcmd.exe to execute the script every day.|||This goes into the right direction. With that I could create the backups manually. But it is very time consuming to setup this for many SQL servers and many databases. Especially, when a new database will be created, it's important to not forget to create a new task for the additional database.
Is there another, more elegant way or better a tool to do the job?|||I would recommend using the auto-close feature in your connection string. Basically, when all the users disconnect the database file will automatically detach from the sql server, leaving it free to access by regular backup programs. I really like this feature!
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsse/html/sseoverview.asp|||Automating Database maintenance in SQL 2005 Express Edition Part I
Automating Database maintenance in SQL 2005 Express Edition Part II
Part I deals with creating scheduled tasks and using SQLCMD. Part II use SMO.
Subscribe to:
Posts (Atom)