Copied Database Maintains Original Name In Backups
I made a copy of a database "sac_prod" and named the new copy "vgs_prod". Now, when I do a backup of the new database, it still shows the name of the original. Is there any way to change this so it will be the same as the new database name?
Here is the BACKUP script:
BACKUPdatabase vgs_prod TODISK=
'\sac-srvr1data$TechnicalSharedProductionSQLBackup LasVegasvgs_prod_CopyOnly.BAK'
with COPY_ONLY
Here is the messages I received from this BACKUP:
Processed 1752 pages for database 'vgs_prod', file 'sac_prod' on file 1.
Processed 6 pages for database 'vgs_prod', file 'sac_prod_log' on file 1.
BACKUP DATABASE successfully processed 1758 pages in 0.412 seconds (34.955 MB/sec).
I would like to change the file 'sac_prod' to be 'vgs_prod' in lines 1 and 2 just above.
Thanks,
View Complete Forum Thread with Replies
Related Forum Messages:
Different Results Same Query Between Original And Copied Db
Hi all, I restored a backup of a database running SQL Server in W2K to my own laptop (Windows XP) for report testing pourposes. The restore worked perfectly, but when I ran the store procedure that returns my "report" set I noticed that several of the fields within the result set are different, the number of rows and customers are a perfect match to the production report. The fields that are different are calculated fields that invoque a user defined function, which again are exactly the same on both databases. I tried dropping the stored procedure and the 4 functions and recreating them again but I get the same results, the number of rows, the customers and all "non" function calculated fields are perfect, only the fields calculated with the functions are wrong. Has anybody seen this behavior? Thanks for your help Luis Torres
View Replies !
Weekly Update Part Of Database With Data From Original Database
Hi! I have an original database that I want to copy once to another database. Then I want to update the data weekly with the data of the original database. I don€™t change any tables or columns in that part of the database. Just some tables more in the €˜new€™ database (than the tables from the original database) with some references to the tables that have to be updated weekly. How can I do this? (if you know what I mean... it's a sort of a datawarehouse where different sources come together.. that part that represents the original database is just a part of the datawarehouse. That part is exactly the same structure as the original database.) Thanks, Sandra
View Replies !
Changes To Cloned Database Are Affecting The Original
I am trying to use SQL Express to support unit testing of functionality which requires database access. I created an express database (.mdf) which contains a database snapshot which is the database environment that each test expects to see when it starts. Through the execution of the test the database will be modified arbitrarily and I need to reset the database to the original state before the next test executes. Here's what I do today: Before Test: The original database is copied to a unique location in the temp directory A unique database name is generated (GUID-based) I connect to the new database file with: Server=.SQLEXPRESS;AttachDbFilename="PathToTempFile";Database="GuidDBName";Initial Catalog="GuidDBName";Trusted_Connection=Yes;User Instance=true During the test all database access is routed to the temporary database GuidDBName After Test: Close all active database connections and clear the connection pool Connect to the master database and detach the database: Server=.SQLEXPRESS;Database=master;Initial Catalog=master;Trusted_Connection=Yes;User Instance=true - sp_detach_db 'GuidDBName' Delete the temporary file When I do this, everything seems to work correctly but when I re-open the original database it has changed unexpectedly and includes the modifications that were made by the test as it ran. Can anyone explain why this is happening and/or provide a solution?
View Replies !
How To Getback Original Copy Of Northwind Database?
Hi,Good morning to All. While doing practice I did some modifications to some tables in Northwind database.Now, how can I get my original tables back again? Take Oracle for example:We can modify table data. But when we run demobld.sql script then all tables will replaces with original entries. Like this I want in SQL Server also. Is there any such facility available in SQL Server? Thanks in advance,Ashok kumar.
View Replies !
Saving Original SQL Database And My.Settings When Reinstalling Or Updating
Hello all... I am working on a project that contains several databases in SQL and many settings. The problem I am encountering is that I want to be able to update the program for all my customers. That is no problem. The problem I am meeting is where, when the program is updated, all the settings and all the information in the databases reverts to the default values, or the values that were compiled with the new update. I want to be able to update their program without deleting all the settings they have. Thanks, Andreas Renberg
View Replies !
Is It Possible To Prevent Databases From Being Copied?
Hi, We have a point of sale application (C# .NET 2.0) and a Sql Server 2005 database back end. Our customers are concerned that employees could create a backup of the SQL Server database (or even of the MDF file) and use it to steel customer data. Very often, the application is running on a single PC in a shop using Sql Server Express Edition 2005 under Windows XP. The users usually log on as local administrator. It's hard for us to force our customers to change their local security policies. Ideally, I would like some form of security mechanism that prevents a backup from being restored on to another PC without either a password or some other form of authentication. Is this possible? Regards, Sigol.
View Replies !
Attach Db File Copied Previously
I got a mdf file from my co-workerwho just stopped SQL server and delete ldf fileand give me a mdf file.if it was detached, it would work finebut it was just copied.Is there any way to attach this mdf file?I tried sp_attach_db and sp_attach_single_file_dband both failed.thanks,
View Replies !
Replication SPs Copied W/CRLF In Names
Hi folks - New DBA (longtime developer) here. I've just noticed something strange on a couple of my databases (SQL 2k). There are some stored procs that have a CRLF or CR+CRLF prefixing the name (they show up as unprintable-char black squares in EM). They all appear to be replication-related SPs (e.g. ). The names are as follows: sp_MSdel_<replicated_table_name> sp_MSins_<replicated_table_name> sp_MSupd_<replicated_table_name> There are corresponding normally-named SPs. Not all the replication-related SPs have been copied this way. The create date of all the new SPs is the same, and is (IIRC, I wasn't involved then...) the date of the SP4 application to the server. Anyone ever seen this? More importantly, is there a way to determine if these SPs are being used? I'd like to delete them if I can, but not until I'm sure of what's happened. TIA for any input.
View Replies !
Char Varchar Not Copied While Transferring
Hi, I have the following problem. I am using DTS connecting SQL SERVER 2005 and Sybase9. I successfully copy from Sybase9 to SQL SERVER 2005. Now when i am copying from SQL SERVER 2005 to Sybase9 char and varchar types are not copied. The data types are the same as well as data type length. Integers, numeric and date values are copied. I have tried data conversion but that did not help. sql server 2005 collation is Greek_CI_AI and Sybase9 collection is 1253ELL Any solution how to solve loosing char and varchar types when copying from SQL SERVER 2005 to Sybase9 ? Note: I connected to Sybase using System DSN Regards, Vasilis
View Replies !
Log Backups - What Does A Database Name Of 0 Mean?
I'm looking at msdb..backupset to try to determine if backups have beenoccuring. For a particular instance I have records in the backupset tablelike:Log 0 Feb 10 2005 Feb 10 2005 14848.There are 20 some user databases and the maintenance plan says to back upthe logs on an hourly basis.On the server there are files like:0_tlog_200502100900.TRNThese files are being created once an hour.Only one user database is showing that the logs are being backed up.I don't know what to make of this.--Message posted via http://www.sqlmonster.com
View Replies !
Database Backups..
Hello there again, Do you know if there is any automatic task with SQL 6.5 to keep a certain number of days of backups (ie the 7 different days of the week) and then to have them overwritten on the corresponding file. This is to avoid the disk to fill up...... The mention Retaindays or expiredate allows the otherway round, that is, it maintains one backup for a certain amount of days specified. Thanks for your help. Regards, Rachel
View Replies !
Database Backups
I know that I have read not to backup a database over a netwrok. So I am curious as to what others are doing out there. BAckup to your local hard drive on the server and then move the backup files to a repository some where on the network? Do others have a file structure out on another server that stores all of the backups from all of the different servers that have SQL 7.0 on them? We are a small company and are just starting to migrate data to SQL Server 7.0.
View Replies !
Database Backups
I have to perform a backup for disaster recovery purposes before an application upgrade. The upgrade will alter the database and stored procedures. My cuurent backup takes a backup of master and msdb weekly. The user database uses the Full Recovery model and is backed up daily at 21:00 and the logs daily at 13:00. Assuming the databse is modified between the last backup and the upgrade starting at 9:00am what should my backup stratergy be for roll back purposes. 1) backup master, msdb and the user Database to a different location than the normal backups. Use these to restore if required 2) backup the master, msdb and user databases using the same jobs and therefore overwriting the original evening backups 3) do nothing and just restore master and msdb from a backup and replay the logs to a given point in time for thr user database should the upgrade fail Any help or suggestions will be much appreciated.
View Replies !
One Table Is Ultra Slow At Being Copied, Others Are Fine
This is a really really odd problem. Here's the situation. I've got a DB with several tables. Let's pick out two of those tables from the bunch.. they both have approx 2500 rows in them. I take one and duplicate it (right click, All Tasks->Export Data, copy tables, etc) and it duplicates just fine into another DB. Now, the second table.. takes FOREVER. Yet it has the same amount of rows in it! And this is a flat table export.. I'm not including dependent objects or anything like that. Not to mention this is slow, but when I use Red-Gate SQL Data Compare, it gets stuck when it hits this specific table. Anyone have any idea what would cause this?
View Replies !
SSIS Error With Duplicate IDs Of Copied Packages
Hi, I recently encountered an error when I created several copies of one package. It's always nearly the same package with small modifications. I call this packages from a parent package which is part of our datawarehouseing-framework. The problem is, when copying a packages or using a packages as template the packages' IDs and Task's-IDs are the same. And this isn't only an issue concerning logging!! : When the parent package calls one of the copied packages the first task is executed in every package parallely. Furthermore ... when I for example set a breakpoint on a data transformation task in one of the packages, the breakpoint is set in all packages on the same task! This is resulting in strange errors because the tasks-states and variable values seem to get mixed up. Unfortunately there is only a possibility to change the package's ID, but the IDs of tasks are readonly! One solution is, to create a new package and copy all the tasks to the new package which creates new IDs, but doing so, I have to manually recreate a long list of variables, all the configurations, all the connection-managers once again. Furthermore I loose the layout of tasks. I found some posts about it here http://groups.google.de/group/microsoft.public.sqlserver.dts/browse_thread/thread/6f85a31ea190608a/0eae312aa8440cf8?lnk=gst&q=pitfall&rnum=1&hl=de#0eae312aa8440cf8 or http://groups.google.de/group/microsoft.public.sqlserver.dts/browse_thread/thread/760093d58bf6ccb5/32ced2f2020ef3f7?lnk=st&q=data+flow+task+id+copy&rnum=2&hl=de#32ced2f2020ef3f7 saying the issue will be fixed by SP2, but now I don't see any comment on it in the CTP of Service Pack 2. Is there any solution to this problem or official roadmap about a fix from Microsoft?? Greetings Monte
View Replies !
Backup Files In Append Mode To Be Copied.
Hi, I have a database on sqlserver 2005.I usually take a full db backup and sequential transaction log backups and append each of the backups it to a backup file.Now if i want to copy my second transaction log file to a specific folder on my server.Do we have any procedure to do it. Regards Arvind L
View Replies !
Database Backups && Compression
Hi All I have a database which is 72GB, which is backed up every night as part of the maintenance plan. I have plenty of storage space, and the server that runs the database is fairly powerful (quad-processor 3.2ghz, 64bit, 48GB RAM) and is part of an active-passive cluster. The database backup is also copied to a SAN location. My issue is with the size of the backup file. As part of the Disaster Recovery plan, I need to copy this database backup file accross the network to a remote site, so that in the event of a disaster at the site, business can continue at the remote site after restoring the database backup file. However, my database backup file is so big that I cannot copy it accross the network in time for the next morning. I have tried using WinRar and have managed to achieve a file about 20% of its original size, but it takes 2 hours to produce this file. Is there any recommended reeading for this type of issue? Log shipping / mirroring has been investigated and will be part of the DR model but the 'powers that be' insist on having a full copy performed to the remote site. Any suggestions? Thanks in advance guys n gals :-)
View Replies !
SQL Server Database Backups
I am looking for the best method to backup SQL Server databases. Currently we are running a dump database statement to disk and backing up the files to tape through Arcserve. One problem that I am having is the statement to dump the database. I would like to retain the dump for at least three days and be able to restore the database from any one of those three days. My current statement is: "DUMP DATABASE CHOISDAT TO DISK=`D:BACKUPCHOIS.BAK` WITH NOUNLOAD , STATS = 10, INIT , RETAINDAYS = 3, NOSKIP" but, every other day I receive the message from SQL executive: "Can`t open dump device `D:BACKUPCHOIS.BAK`, device error or device off line. Please consult the SQL Server error log for more details. (Message 3201)" What am I doing wrong? Any suggestions? P.S. Is there anyway to tell the Maintenance Wizard to delete the backups. I tried using the wizard but the backup files still remain on the disk and I have to delete them every week.
View Replies !
Dyanamic Database Backups In 6.5
Hello, Can anyone tell me what the impact of dynamic database backups in sql 6.06.5 will have on users using the database? Will their user processes be blocked? Will their queries run slower than normal (how slower)? Will there be a lot of locking activity as the SQl tries to backup? Will the serverdatabase run slower Anything else I may be missing? Regards, Hitesh
View Replies !
SQL 7 Database Backups To DLTs
All, Our database contains about 120GB of data and it is backed up onto 2 DLT7000 (striped). The theoretical capacity of the two DLTs combined is 140GB. Does any one know (or have experience) of how much database data can actually be stored on a DLT (40GB uncompressed / 70GB compressed). Thanks in advance for your help. Samir
View Replies !
Merge Replication Issue With Records Not Being Copied To Subscriber
I have an issue with merge replication between sql2005 sp2 and spl2000 sp4 the merge replication runs fine to 6 other sql servers but the sql2000 sp4 server has a lot of locking going on, but the merge replication doesnt fail it just loses several hundred records without notifying me I have to manually copy the missing records every week or so I have dropped the replication and rebuilt it twice but it still happens
View Replies !
Report Security - Parent Folder Not Copied To Child
Hi Guys, In the reproting services web page I have inserted different users who can run reports. I have home page and sub folders for each company department. only problem is when I add a new user I have to add him/her to all the sub folders one by one (although the user is inserted in the parent folder as well) What am I doing wrong? Cheers Sonny
View Replies !
Auto-restore Of Database Backups
Our DBs are set up to do a full backup once a day (late at night) and then transaction log backups during the day at shorter intervals. I want to setup a dev database on the same server. I want this database to be an automatically restored copy of the live database. So every night, after the full backup of the live DB, I want to restore the live DB to this dev DB. Can this be automated? Can the restore automatically stop the dev database in case some open connections exist? Is there a better way to do this?
View Replies !
Database Backups Growing Exponentially
Hi All.I'm currently maintaining 4 servers - 1 for public/customers and 3for backups, development, etc...I regularly backup the entire SQL database for our public server andrestore it on each of the other servers. Lately, however, the databasebackups have grown (in size) incredibly fast - they've gone from about200MB to 2+ GB in 2 months. (I wasn't entirely surprised by this atfirst since our client traffic has drastically increased as well.) Theweird thing, though, is that (on two of the backup servers) when Irestore the backup then use those servers to create a new completebackup, the new backup is only about 200-300 MB in size.My assumption is that there's some kind of setting buried deep insidethe sql configuration allowing it to compress or otherwise alterbackups. Does anyone have any ideas/thoughts as to what may be causingthis issue?We're using SQL Server 7 on Windows 2000 servers.Thanks in advance.GreggJoin Bytes!
View Replies !
Cluster Database Backups Question
In a non-clustered environment, I am under the impression that backups must be to a local disk or local tape device. My plan is to have a separate disk in a clustered environment on a shared array for holding my backups, until they can be transferred somewhere else. My question is, will SQL Server 2K support backing up to the disk in the shared array since it is (I believe) not considered a local disk? What key points may I need to know. Thanks very much for the help!! solart
View Replies !
Log Backups On Heavy And Busy Database
What will be best procedure for the following situation. Heavy traffic database on daily basis. G growth every day. so Full backups every nights are needed. Vendor recommends not taking Log backups but copy just log files over other location. will this help avoid degrading the performance during business hours. if i don't take log backups, i am not able to recover Point in time if needed. also log files can then grow faster and then i will have to shrink it more often. what are risks and suggestions. Thanks,
View Replies !
How To Check For Failed Database Backups
Hello everyone! Looking for some insight here on database backups that fail. We have many SQL servers that we maintain by storing Job/Maintenence Plan history on a central server, which then emails out daily reports to let us know what backed up last night and what didn't This was easy to do in SQL 2000, not so much in SQL 2005. I have put together a query that gathers the info I need for the successes: SELECT DISTINCT '00000000-0000-0000-0000-000000000001' AS Plan_ID, mpld.line1 as "Plan Name", bud.database_name as "Database", mpld.server_name, 'Backup Database' as Activity, mpld.succeeded, bs.backup_finish_date, DATEDIFF (MS,bs.backup_start_date,bs.backup_finish_date) as Duration, bs.backup_start_date, mpld.error_number, mpld.error_messageFROM msdb.dbo.sysmaintplan_logdetail mpld INNER JOIN msdb.dbo.backupset bs on (select convert(char(12),mpld.start_time,109))=(select convert(char(12),bs.backup_start_date,109))-- on bs.database_name=bud.database_name inner join msdb.dbo.bu_dbs bud on bs.database_name = bud.database_name WHERE mpld.succeeded = 1 and mpld.line2 like 'Backup%' and bs.type='d' and bs.backup_start_date > ( SELECT CONVERT(char(12), (GETDATE()-1), 109) ) ORDER BY bud.database_name DESC But I am having trouble using a query to determine the databases the FAILED during backup. MSDB.BackupSet and MSDB.SYSMaintPlan_LogDetail really have nothing,because often times, even if a step in a Maint. Plan fails, the plan finishes reporting success. Does anyone know of a good way to gather info about failed database backups? Thank you! Mario
View Replies !
Identity Field Settings Not Copied By Import Wizard (2005)
It appears that when you use the import/export wizard from within Microsoft SQL Server Managment Studio, the identity attributes of the table being copied are not transferred. For example, say the source table has a column [ref] [int] IDENTITY ( 1 , 1 ) NOT NULL, When the import wizard is done the destination table will have a column named ref, but will not be an identity column. The column definition will be [ref] [int] NOT NULL, instead. Is there a way to change this behavior somewhere in the gui? When doing the import, the only options seems to be 'Enable Identity Insert', but checking this does not affect the definition of the column. -Eric
View Replies !
Named Backups Using Database Maint Wizard
Has anyone been able to find a way to name the files within your backup using the Database Maintenance Wizard in SQL 7.0? We do nightly backups, and transaction logs during the day all on one tape per day. This gives us quite a few files on the tape and we haven't had any success finding a way to give the backups a name. For example: We have servers that contain 10+ databases. It would be great to be able to view some sort of naming standard in the "Restore" window of SQL 7, as opposed to seeing a long list of files and guestimating. Thanks for your input. Scott
View Replies !
Is It OK To Copy Database Backups To Tape Using Compression?
Hi, I back up SQL Server 2000 and SQL server 2005 databases to hard disk using the SQL Server Backup Wizard and maintenance plans. Then, I copy the resulting backups to tape using third party tape backup software and compression by the backup software and hardware. I do not use the SQL Server Agent available for the third party backup software. Is this acceptable, or does the compression performed by the third party backup system introduce opportunities for database corruption or other negative effects? Thanks
View Replies !
|
TRACKER
