Database Doesn't Show up under maint wizard

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Database Doesn't Show up under maint wizard

Author

Topic

peoriaparks
Starting Member

8 Posts

Posted - 2008-10-09 : 13:12:04

We imported a database from SQL 2000 to our SQL 2005 server. I have been trying to create a maintenance task to back the database up to file nightly, but when I go into the screen where I select the database, it isn't showing up as an option. I'm trying to create the plan for database class. I have logged in as several different users, but no go... Any help is greatly appreciated! I do have screen shots if I haven't explained it properly. Thank you!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-10-09 : 13:45:35

What is the status of the database?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

peoriaparks
Starting Member

8 Posts

Posted - 2008-10-09 : 13:47:05

It is online and in use. We have about 50 users currently using the database. I thought it may be a permissions issue, but I don't know. Thank you for your fast response!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-10-09 : 13:51:36

Are you able to backup the database using T-SQL commands rather than through the maintenance plan?

BACKUP DATABASE dbName
TO DISK = 'E:\Backup\dbName.bak'
WITH INIT, STATS

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

peoriaparks
Starting Member

8 Posts

Posted - 2008-10-09 : 14:37:28

Yes

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-10-09 : 15:16:27

What permissions do you have?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

peoriaparks
Starting Member

8 Posts

Posted - 2008-10-09 : 15:28:56

I'm logging in as the Administrator of the domain.....I'm not sure what permissions that account has, as it isn't a user on that database, but I am able to manually back the database up...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-10-09 : 15:34:18

I'm not sure how to resolve your issue, but perhaps I can help you with automating backups. I don't use maintenance plans, but rather I use custom scripts. My scripts are used around the world by various companies. A lot of people dislike maintenance plans.

Here is my backup script: http://weblogs.sqlteam.com/tarad/archive/2008/08/19/Backup-SQL-Server-Databases.aspx

Here are all of my scripts that replace the maintenance plans: http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

And here is how I have my production environments configured: http://weblogs.sqlteam.com/tarad/archive/2008/06/30/SQL-Server-jobs-on-production-instances.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

peoriaparks
Starting Member

8 Posts

Posted - 2008-10-09 : 16:19:51

Thank you! How do I schedule this to run every day?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-10-09 : 16:30:30

Add a job in SQL Server Agent to call isp_Backup, hopefully you aren't using the Express edition.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

peoriaparks
Starting Member

8 Posts

Posted - 2008-10-09 : 17:03:10

I ran the job after attaching the file, it said it was successful, but there is no file in the directory...and it completed rather quickly......any ideas?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-10-09 : 17:42:04

Post what you put in the job step.

Verify in the SQL Server error log if the backup happened on the database(s).

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

peoriaparks
Starting Member

8 Posts

Posted - 2008-10-09 : 18:00:01

Can I just use the 3 lines from earlier and schedule those?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-10-09 : 18:44:25

Yes but it'll overwrite the file each time. We have to keep several days of backups, so that solution wouldn't work for us.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog