InstantForum.NET v4.1.4SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 21 Nov 2009 03:11:16 GMT20http://www.sqlservercentral.com/Forums/Topic822853-357-1.aspxServer-A it is in My home Server-B it is in another home they both are connected to the N/W is it is possible for me to take the backup of server-B(another home) and place it in my server-A (my home) through the maintenance Plan back up from My server-A(My home)Both are SQL 2005 Microsoft SQL Server 2005 - 9.00.1399.06 (Build 3790: Service Pack 1) ThanksParthiSat, 21 Nov 2009 00:01:33 GMTparthi-1705http://www.sqlservercentral.com/Forums/Topic821537-357-1.aspxHi all - Recently our transaction log started to increase in size massively.We run hourly tans log backups which are working fine, but the log is not being correctly truncated it seems I have tried to shrink the file but this has not worked either. I have run DBCC OPENTRAN and get the following:Transaction information for database 'ukcrn'.Replicated Transaction Information: Oldest distributed LSN : (1290026:5600:4) Oldest non-distributed LSN : (1290026:10715:1)DBCC execution completed. If DBCC printed error messages, contact your system administrator.Which indicates to me that there are no open transactions currently there. I have also run DBCC LOGINFO and can see a status of 2 on lots of my virtual logsHow can I tell what is causing the problem and how can I shrink the file in keeping with our recovery policy of hourly transaction logs?ThanksThu, 19 Nov 2009 06:20:07 GMTKwisatz78http://www.sqlservercentral.com/Forums/Topic821318-357-1.aspxWhen I try to restore msdb database of SQL server 2008 using a backup copy of SQL 2000 msdb database I can getting following error.Msg 3154, Level 16, State 4, Line 5The backup set holds a backup of a database other than the existing 'msdb' database.Msg 3013, Level 16, State 1, Line 5RESTORE DATABASE is terminating abnormally.Is it because of the version incompatibility of sql server or any other reason.Wed, 18 Nov 2009 18:56:03 GMTajith.ranjanhttp://www.sqlservercentral.com/Forums/Topic821983-357-1.aspxWe plan to install a web app with sql server 2008 installed outside our firewall. The problem is that our backup filer is inside the firewall and we won't be allowing traffic to start from outside the firewall into our network. Can we use a SQL Server instance inside the firewall to access the outside server and back it up? Another idea would be to back up the database locally and then have a copy job pull the backup file through the firewall.Thu, 19 Nov 2009 15:10:08 GMTBill Stuttershttp://www.sqlservercentral.com/Forums/Topic821574-357-1.aspxPlease email me at support@LegalSports.com if you would like to be hired to fix our problem.I have a USA telephone.Thanks :-)An example below: (from the CookieMonster.log file)00:00:00 [20] ERROR - invalid index source=/ScoreEater/ currentRow=/7/ currentCol=/1/ Index was outside the bounds of the array.: at CookieMonster.Eaters.ScoreEater.ParseTable(String table, Int32 totalSubHeaders, Int32 totalItems, String date) 00:00:00 [20] ERROR - invalid index source=/ScoreEater/ currentRow=/7/ currentCol=/2/ Index was outside the bounds of the array.: at CookieMonster.Eaters.ScoreEater.ParseTable(String table, Int32 totalSubHeaders, Int32 totalItems, String date) 00:00:00 [20] ERROR - invalid index source=/ScoreEater/ currentRow=/7/ currentCol=/3/ Index was outside the bounds of the array.: at CookieMonster.Eaters.ScoreEater.ParseTable(String table, Int32 totalSubHeaders, Int32 totalItems, String date) 00:00:00 [20] DEBUG - score saved rotationId=/0/ date=/20091118/ score=/9301/ status=// period=/FirstPeriod/ 00:00:00 [20] ERROR - error updating score Error converting data type varchar to numeric.: at CookieMonster.Data.DBConnection.RunProcedure(SqlCommand command) at CookieMonster.Data.Game.UpdateScore(Int32 rotationId, String date, String score, String status, ScorePeriods period) 00:00:00 [20] ERROR - error updating score source=/ScoreEater/ error updating score: at CookieMonster.Data.Game.UpdateScore(Int32 rotationId, String date, String score, String status, ScorePeriods period) at CookieMonster.Eaters.ScoreEater.SaveScoreByPeriod(Game dbGame, Int32 rotationId, String date, String score, String status, ScorePeriods period) 00:00:00 [20] DEBUG - score saved rotationId=/0/ date=/20091118/ score=/9302/ status=// period=/FirstPeriod/ 00:00:00 [20] ERROR - error updating score Error converting data type varchar to numeric.: at CookieMonster.Data.DBConnection.RunProcedure(SqlCommand command) at CookieMonster.Data.Game.UpdateScore(Int32 rotationId, String date, String score, String status, ScorePeriods period) 00:00:00 [20] ERROR - error updating score source=/ScoreEater/ error updating score: at CookieMonster.Data.Game.UpdateScore(Int32 rotationId, String date, String score, String status, ScorePeriods period) at CookieMonster.Eaters.ScoreEater.SaveScoreByPeriod(Game dbGame, Int32 rotationId, String date, String score, String status, ScorePeriods period) 00:00:00 [20] ERROR - error updating score Error converting data type varchar to numeric.: at CookieMonster.Data.DBConnection.RunProcedure(SqlCommand command) at CookieMonster.Data.Game.UpdateScore(Int32 rotationId, String date, String score, String status, ScorePeriods period) 00:00:00 [20] ERROR - error updating score source=/ScoreEater/ error updating score: at CookieMonster.Data.Game.UpdateScore(Int32 rotationId, String date, String score, String status, ScorePeriods period)Thu, 19 Nov 2009 07:03:52 GMTsupport 29841http://www.sqlservercentral.com/Forums/Topic817351-357-1.aspxWe had to take down the SQL Agent on our primary server due to an emergency and because of that our secondary server is now failing on the lsrestore for the two SQL Agent jobs that are responsible for the lsrestore portion of our log shipping. Can anyone offer any suggestion regardin how I can bring the transactions up to synch to get the lsrestore to once again add the new transactions into these databases?Your help would be greatly appreciated.Thank you,DavidWed, 11 Nov 2009 11:26:40 GMTdaveFromIllhttp://www.sqlservercentral.com/Forums/Topic821463-357-1.aspxWhile taking the backup whether pages will be allocated or not Thu, 19 Nov 2009 03:41:35 GMTparthi-1705http://www.sqlservercentral.com/Forums/Topic820559-357-1.aspxHello alli ve backuped a DB using Microsft SQL Server Management Studio right clik on db -> tasks ->back upthe backup was completed successfully ,but when i try to restore the database , i get this message [Directory lookup for file ...Operating system error 21]what is it wrong that i did?PS :i heard people talking that MSSMS creates two file (.bak,.log) why the MSSMS have created ONLY ONE FILE for my database ?PLZ helpTue, 17 Nov 2009 22:11:43 GMTalaminfadhttp://www.sqlservercentral.com/Forums/Topic750656-357-1.aspxHoping someone can help. My situation is I've inherited an application which allows users the ability to use SQL Server bcp to export table data from one database and import the data back into another database of the same structure, but with a different name. Bat files are the underlying means used to perform the bcp's, and all bcp's are done via views.Recently we finally moved several databases from 2000 to 2005, using backup and restore. We are now having problems using the application to export the data and import, as we have in the past. The import consistently fails on two tables. The error message for both tables is:SQL State = 37000, NativeError = 4816 Error = [Microsoft][ODBC SQL Server Driver][SQL Server] Invalid column type from bcp client for colid 11SQL State = 37000, NativeError = 4816 Error = [Microsoft][ODBC SQL Server Driver][SQL Server] Invalid column type from bcp client for colid 12In each instance, the colid identified is a bit NOT NULL field.If I use Management Studio and change the bit field property on the underlying tables from NOT NULL to NULL, then save it, and then change it back to NOT NULL and save again, I can successfully import the data back into the database without any problems.If I use scripts we have to build a database from scratch, and then try the data import, it also works fine--no problems.I thought it might be a compatibility level issue since the "restored from 2000" dbs are level 80 and the db created in 2005 from scratch is 90. I changed the compatibility level of the restored dbs to 90 and tried the import again, and it still failed.Finally, if I bypass the views, and import directly into the underlying table, there's no problem.Here are the bcp commands that failbcp dbname.dbo.block_types_v out block_types_2005.dat -e block_types_2005.err -N -S slbmdb02 -T -E bcp dbname.dbo.block_types_v in block_types_2005.dat -e block_types_2005.err -N -S slbmdb02 -T -E bcp dbname.dbo.input_subblock_v out input_subblock_2005.dat -e input_subblock_2005.err -N -S slbmdb02 -T -E bcp dbname.dbo.input_subblock_v in input_subblock_2005.dat -e input_subblock_2005.err -N -S slbmdb02 -T -E Sorry for the long post, but this has really got me stumped. Again, I'm hoping someone out there may have run into a similar problem and solution.Thu, 09 Jul 2009 14:45:54 GMTMike Shoop-288395http://www.sqlservercentral.com/Forums/Topic819808-357-1.aspx Hi Friends, Is it possible to perform Automatic Backups (ex: Daily at 8 pm)in SQL Server 2005 Standard edition.If so,Pl give me a detailed explanation about it.... Thanks in advanceRegards,SundarMon, 16 Nov 2009 22:21:10 GMTmanikam_sundarhttp://www.sqlservercentral.com/Forums/Topic820204-357-1.aspxHello,Is there an easy way to change the backup path after the Log Shipping is configured?If at all doable will this work....Thanks In Advance!Tue, 17 Nov 2009 09:57:26 GMTchinnhttp://www.sqlservercentral.com/Forums/Topic820707-357-1.aspxI have a SQL 2000 cluster running on Windows 2003 Enterprise server, the cluster shared storage is provided by a Dell SCSI disk array.Im trying to backup the databases to an ISCSI drive on our SAN. When I select the backup locations for the databases in the maintenance plan the server only shows 2 drives (R & S) which are on the Dell expansion box, all other drives are not visible (local drives c: and d:, quorum drive q: - also on the Dell disk array and finaly x: ISCSI drive on our SAN)Can anybody tell me why these drives are not available to select as backup locations and how to add them.Thanks in advanceWed, 18 Nov 2009 04:29:16 GMTjohn.holmanhttp://www.sqlservercentral.com/Forums/Topic820581-357-1.aspxWhat is impact of database backup on sql server performance ? All thoughts welcome !!Tue, 17 Nov 2009 23:29:21 GMTBoolean_zhttp://www.sqlservercentral.com/Forums/Topic818374-357-1.aspxI am in the Maintenance Plan Wizard(SQL 2005 Standard Edition) on the 'Select Maintenance Task Order' the move up and move down options are dimmed.Please suggest...... :crazy:Fri, 13 Nov 2009 04:26:23 GMTpankaj.balunihttp://www.sqlservercentral.com/Forums/Topic818293-357-1.aspxHi,Can someone help me on how to backup a remote server. scenario in detail:Server : A (Local Server) Having SQL Server 2005 Std EdtServer : B (Remote Server) Having SQL Server 2005 Express EdtNow I wanted to take backup of databases of server B from server A. I want to store the backups of server B in the same server itself I dont want to keep those db's in server A.Can somebody help me...Thanks in Advance.Fri, 13 Nov 2009 01:50:08 GMTvinu.vhttp://www.sqlservercentral.com/Forums/Topic810443-357-1.aspxThis week I setup logshipping in my prod env to another env for around 50 dbs and all of them are in SQL 2005 instance. One of the db is taking hell lot of time to recover the db in standby mode. The db is 13GB size and logs are 5 to 7MB sizes.When I restore full back manually it took long time and when I track the spid using the below query, the percent_complete value is reaching 100 in 2mins 15secs but after it stays more than 30mins to recover the db.SELECT percent_complete, * FROM sys.dm_exec_requestsWHERE session_id=135Once the db restored (after 35mins) I did the LS setup then the .TRN files taking long time to restore. even for 7MB file taking 18+mins to restore. Initially i thought it would be some storage issue but lot of other database files resides and they are going well. Note: The same db is being log shipped 2 other instances, they also having the same issue. Also in the primary, this db is migrated from sql 2000 and still the compatibility level is 80.Anybody faced this kind of issues earlier or any thoughts on this to overcome?ThanksJay.Wed, 28 Oct 2009 19:09:01 GMTJayakumar Krishnanhttp://www.sqlservercentral.com/Forums/Topic820213-357-1.aspxHello World,I can't remember if Transaction Log shipping will ship over changes made to Store Procedures and Functions. Or does it only ship over changes to tables/user data.Thanks!Tue, 17 Nov 2009 10:07:45 GMTSQL Iron Chefhttp://www.sqlservercentral.com/Forums/Topic820108-357-1.aspxI was hoping someone else is getting the following error with Litespeed. We are getting the error 2 -4 times a day for full backups and t-log backups. SLS Error: No Result XML returned from Engine. (Possible abend/termination...We are running SQL 2005 Standard edition(SP3) on a 2003 sp1 x64 server. Servers have 16 gig of RAM and 13 has been given to SQL Server. This is a clustered environment 2 node active passive cluster.Per the suggestions from Quest we have spaced out our jobs, and set the MaxTransfer Size parameter when running their extended stored procedure.I have noticed that when we get errors with the backups we see a drop of around 200mb of RAM available on the server.Any help would be appreciatedThanksErichTue, 17 Nov 2009 08:23:52 GMTErich Brinkerhttp://www.sqlservercentral.com/Forums/Topic819178-357-1.aspxHi, One of our databases log file growing very huge. One of our team members recommended the following instead of dbcc shrinkdatabase (of course i would recommend dbcc shrinkfile instead of dbcc shrinkdatabase)exec SP_dboption @dbname,'trunc. log on chkpt.','true' DBCC shrinkdatabase (@dbname)But when I checked online I noticed the few points1. If the trunc. log on chkpt. database option is enabled, the log is truncated when periodic checkpoints occur. Only full database and differential database backups are allowed because the log has been truncated and any log backups made would be unusable. 2.Important When using transaction log backups, do not set the trunc. log on chkpt. database option to TRUE. Setting this option to TRUE causes the transaction log to be truncated, [b][i]without backing up the truncated part of the transaction log[/i][/b], every time a checkpoint occurs in the database, preventing more transaction log backups from being created. Will it create any data loss ([b]without backing up the truncated part of the transaction log every time a checkpoint occurs in the database[/b])3. trunc. log on chkpt. When true, a checkpoint truncates the inactive part of the log when the database is in log truncate mode. Important: Starting with SQL Server 2000, setting the trunc. log on chkpt. option to true sets the recovery model of the database to SIMPLE. Setting the option to false sets the recovery model to FULL. What is the best method to reduce the log size without data loss.Sun, 15 Nov 2009 20:49:23 GMTnewbie2sqlhttp://www.sqlservercentral.com/Forums/Topic819962-357-1.aspxhi all,we have a very large database (about 165GB) which is part of our datawarehouse (SQL Server 2005 database). we need to plan database backup as well as DR plan.is it a good idea if i take a full database backup once a month and schedule a differential backup everyday. we would'nt require a point in time recovery so i was thinking of avoiding transaction log backups.we have just started with this datawarehouse so we do not know at what rate the size of the database will grow. but it is expected to grow at a fast rate. the database is divided in 4 filegroups.so taking filegroup abckups will also be time consuming.primary filegroup - 72 MBsecondary filegroup_1 - 165 GBsecondary filegroup_2 - 224 MBsecondary filegroup_3 - 10MBlog file - 2GBthanks for your time and help.Tue, 17 Nov 2009 05:02:59 GMTsharon-644450http://www.sqlservercentral.com/Forums/Topic819654-357-1.aspxWe learned today that our backups were not working. Fortunately, we didn't have any database failures along the way.We have a "locked down" shop and do not have access to the backups themselves, or any logs created when they ran/failed. Is there a way to query msdb to determine if a backup fails? I tried the following, but I don't see anything that would indicate a failure.[code="plain"]use msdbselect *from backupsetwhere database_name = 'camp'order by backup_start_date desc[/code]Mon, 16 Nov 2009 13:40:51 GMTshewhttp://www.sqlservercentral.com/Forums/Topic819614-357-1.aspxI recently noticed that the Recovery Model on a database had been changed from FULL to SIMPLE by an administrator.Does SQL capture any "queryable" data / metadata to indicate when that change was made?ThanksMon, 16 Nov 2009 12:45:21 GMTryanm-829779http://www.sqlservercentral.com/Forums/Topic819548-357-1.aspxHello,I've been using a script to create backups of a small database before I run batch updates 3 or 4 times a day. Originally, it would take a couple minutes and was working fine. Late last week, I realized that one of my SSIS packages was still running after almost 18 hours (usually took 4 to 5 minutes). The step it was stuck on was the backup, which when disabled the package would run in its normal time. I've tried running backups on other databases, but they work fine, so it seems just my database is the issue and it isn't very big at all.Here are some stats on the database size: (I'm pretty new to this, so I'm still learning what statistics actually matter)database_size unallocated space1346.69 MB 421.52 MBreserved data index_size unused80368 KB 77600 KB 1400 KB 1368 KBHere's the script:[code="sql"]Declare @FullPath as VarChar (200)Declare @Server as VarChar(100)Declare @FileName as VarChar(50)Declare @Date as VarChar(50)Set @Server = '\\Ddcdc1.doubledaycanada.com\data\Jeff\0001. DBA\Book Web App Proccess\99. Database_Backups\''Set @FileName = '.bak'Set @Date = Convert(VarChar(4),DatePart(yyyy,GetDate())) + Right('0' + Convert(VarChar(2),DatePart(mm,GetDate())),2) + Right('0' + Convert(VarChar(2),DatePart(dd,GetDate())),2) + Right('0' + Convert(VarChar(2),DatePart(HH,GetDate())),2) + Right('0' + Convert(VarChar(2),DatePart(n,GetDate())),2) + Right('0' + Convert(VarChar(2),DatePart(SS,GetDate())),2)Set @FullPath = @Server + @Date + @FileName--Select @FullPath -- This just shows the result of the above code (full path)Backup Database BookNMWebApplicationsTo Disk = @FullPathWith Format;GO[/code]Mon, 16 Nov 2009 11:14:27 GMTfreens77http://www.sqlservercentral.com/Forums/Topic817247-357-1.aspxI have a new SQL2005 database with several databases on it. I have a pretty de-facto backup (from using the backup wizard) that does a full backup of the user databases every night.Yesterday I added a new database and it won't backup. The errors are2009-11-11 15:09:32.70 Backup Error: 3041, Severity: 16, State: 1.2009-11-11 15:09:32.70 Backup BACKUP failed to complete the command BACKUP DATABASE Database. Check the backup application log for detailed messages.2009-11-11 15:11:18.57 spid99 Error: 18204, Severity: 16, State: 1.2009-11-11 15:11:18.57 spid99 BackupDiskFile::CreateMedia: Backup device 'N:\Backups\Userdatabases\Database\Database _backup_200911111511.bak' failed to create. Operating system error 3(error not found).2009-11-11 15:11:18.57 Backup Error: 3041, Severity: 16, State: 1.I've migrated another database on today and that backups up fine to the same area.The issue seems to be creating the files in the sub-directory \Userdatabases\Database\The job runs fine if it doesn't create the sub-directory for each database.Ideas?thanksWed, 11 Nov 2009 09:02:55 GMTadrian.saundershttp://www.sqlservercentral.com/Forums/Topic551139-357-1.aspxHi allI am a new bie to SQL server 2005.Below is my requirement:Need to backup all the records in particular table every month, schedule this job and send a notification as and when the job gets executed.For which I've done the following:>>Right Clicked MyDB instance> Tasks>Export data // provided the destination table source tables, pacakage name to save this on File System.>>Then I moved to SQL Server Agent and created a recurring job under Jobs Folder. Note that I've not added any alerts or notifications.>> Now when the time to execute this job comes, the following error is thrownMessage[364] The Messenger service has not been started - NetSend notifications will not be sent>>Not sure why I am seeing this. After seeing other forums. I enabled the windows Messenger service. And I dont see any Notification Services created. >> Note that When I ran the table export for the first time, the data got transmitted.Not sure if I am following the correct process. Kindly advise with correct steps and help me in understanding the Notification part.Thanks a lot!!DeepthiTue, 12 Aug 2008 09:39:11 GMTDeepthi-847396http://www.sqlservercentral.com/Forums/Topic818155-357-1.aspxHi Folkscan someone point me in the right direction as to where there might be a script that will clean out/delete all the backup history records from all the different tables based on a specified date ?Looks like there are multiple tables involvedI have a maint plan that i generated to delete records older than 7 days and it runs once a week , but it looks like it does not work even though the history of the plan shows successful the T-SQL is as follows:declare @dt datetime select @dt = cast(N'2009-11-05T14:09:09' as datetime) exec msdb.dbo.sp_delete_backuphistory @dtGOEXEC msdb.dbo.sp_purge_jobhistory @oldest_date='2009-11-05T14:09:09'GOEXECUTE msdb..sp_maintplan_delete_log null,null,'2009-11-05T14:09:09'Report Output:NEW COMPONENT OUTPUTMicrosoft(R) Server Maintenance Utility (Unicode) Version 9.0.4035Report was generated on "NSAB-SS80-SQL-N".Maintenance Plan: MP-Maintenance-CleanupDuration: 00:00:01Status: Succeeded.Details:Execute T-SQL Statement Task (NSAB-SS80-SQL-N)Execute TSQL on Local server connectionExecution time out: 0Task start: 2009-11-12T14:15:53.Task end: 2009-11-12T14:15:54.SuccessThanksJimThu, 12 Nov 2009 15:17:41 GMTJC-3113http://www.sqlservercentral.com/Forums/Topic818257-357-1.aspxHi All, I have .mdf, .log files all those files are online, I conform with the following querry [left] [b]select fg.name as FilegroupName, fg.type_desc as FileGroupType, df.name as FileName, df.physical_name, df.state_desc from sys.database_files df left outer join sys.filegroups fg on df.data_space_id = fg.data_space_id[/b][center][left][/left][/center][/left]But i am uable to take back up, Please help me, it's important.Note: I dont have full back up., Please help me, it's important.Note: I dont have full back up.Error Message:Backup failed for server 'Employee' System.data.sqlclient. Sqlerror: The backup of the file or folegroup 'Emplyeetest' is not permitted because it is not online. Backup can be performed by using the FILEGROUP or FILE clauses to restrict theselection to include only online data.(Microsoft .sqlserver.Smo)[b]Emplyeetest [/b]is My MDF file name.Thu, 12 Nov 2009 23:23:23 GMTsuresh.maddalihttp://www.sqlservercentral.com/Forums/Topic819497-357-1.aspxHi I would like to know about comparison of DPM 2007 & CA arc serve.ThanksAbdulMon, 16 Nov 2009 09:59:41 GMTmohdwaheed84http://www.sqlservercentral.com/Forums/Topic819433-357-1.aspxAll -Looking for best practice or way to keep transaction logs cleaned up. Working with (5) test region databases , all set to Simple Recovery. I have saw where Best Practices wants you to stay away from Shrinking the database regularly but wondered if there is some code to run regularly that would keep transaction logs trimmed down to acceptable levels. I thought of doing a Check Point then maybe a ShrinkFile on the log file but looking for opinons on that.Example:Use myDatabase1; checkpoint dbcc shrinkfile (myDatabase1_log, 15) Any and all help appreciated, thanks !Mon, 16 Nov 2009 08:50:35 GMTmark_copleyhttp://www.sqlservercentral.com/Forums/Topic680730-357-1.aspxThe Maintenance plans i was using for backups was running fine till yesterday. Yesterday night we moved the server from one data center to another physically and started the services.Since then the maintenance plans are failing.Actually the view history tab of maintenance plan does not show anything. the error is shown in SQL server jobs only. i think the job is not able to go to maintenance plan.Here is the error.Executed as user: Domain\devsqlservice. his task is "Maintenance Plan Reporting Task; Microsoft Corporation; Microsoft SQL Server v9; ? 2004 Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;1". End Error Error: 2009-03-20 16:42:23.56 Code: 0xC0024107 Source: Reporting Task for subplan-{587BE1DE-326E-4A79-B159-9B0A65E70316} Description: There were errors during task validation. End Error Error: 2009-03-20 16:42:23.56 Code: 0xC0010025 Source: TLog_Backup Description: The package cannot execute because it contains tasks that failed to load. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 4:42:07 PM Finished: 4:42:23 PM Elapsed: 15.61 seconds. The package execution failed. The step failed.Please let me know if you need more info...Fri, 20 Mar 2009 14:52:55 GMTreddi_arjunhttp://www.sqlservercentral.com/Forums/Topic818936-357-1.aspxI'm trying to work out a way to use SQLCMD to run a backup script through an SSIS package. I think I have determined that you need to include a Execute Process Task but to call SQLCMD.exe but not sure what is the next steps needed? Would I need to add a Execute SQL Task as well and link them? The purpose of the script is to create a backup of a database on one SQL server instance, copy that to a share location, and then from that share location restore the database to a different SQL server instance.Appreciate any help you can provide.ThanksFri, 13 Nov 2009 17:25:08 GMTseandeyoung-1145978http://www.sqlservercentral.com/Forums/Topic817653-357-1.aspxHi , I am trying to restore DB with no recovery model for Log shipping. while i try to restore from backup with No recovery model . But it hangs all over there , just showing message "restoring " .Please help me .RegardsSureshThu, 12 Nov 2009 01:11:50 GMTtsd_sureshhttp://www.sqlservercentral.com/Forums/Topic818970-357-1.aspxIs there in option in SQL server 2005, to create a [b]new login[/b] and give him the [b]privileges[/b] to take BackUp of Databases and also Resotre Databases [b]only[/b], and no other rights?BhaveshSat, 14 Nov 2009 01:25:02 GMTBhavesh_Patelhttp://www.sqlservercentral.com/Forums/Topic818145-357-1.aspxMy understanding is that a backup started after a table in the target database begins a DML operation (DELETE/INSERT/UPDATE) will wait for that operation to complete before backing up that particular table's data.Assuming that's an accurate stmt (but pls correct me if otherwise), I would appreciate help in understanding what happens when the reverse situation occurs and a backup (full backup in my case) starts and before it completes one or more DML operations is/are run against the same d/b?Will the results of the DML operation be excluded or included in the backup or is that a "maybe" based on other factors?Thanks in advance!Chris.Thu, 12 Nov 2009 14:56:54 GMTchris.worthingtonhttp://www.sqlservercentral.com/Forums/Topic818741-357-1.aspxHi All,We have a sql 6.5 version database files abc.dat and abclog.dat,can anyone tell how to restore it on sql 2000 or sql 2005 server, i tried many options but didnt work, please let me know if anyone knows about it.ThanksFri, 13 Nov 2009 11:14:34 GMTALIF-662928http://www.sqlservercentral.com/Forums/Topic817393-357-1.aspxI have been provided a SQL Safe (idera) script by my manager and he wants me to either use SSIS or create a SQL job for it. Unfortunatly I don't have access to any testing environments so I need to have this all worked out in the implementation cognitively (no trial and error). I was told this script works fine when running it from through Query Analyzer (i.e. SSMS) it needs to be created and scheduled as a job. I have pasted the script below for your reference.If I created a SQL job and added this as a step my assumption is that it would run under the login I specify the job step and if it was run created on and run under the eSitedb1 server instance - that contains the database to backup - it should be able to call the [master].[dbo].[xp_ss_backup] procedure and pass in the specified parameters. I don't see why there should be a need for the ":CONNECT eSitedb1" line entry in the script? I have never seen this ":CONNECT" command before but assume it serves the same purpose as USE [i]databasename[/i] would? The first part of the script instructs the xp_ss_backup procedure what database to backup and where to copy the backup to (i.e. \\esitedb2\eSiteMonthEndLogBackups\eSiteProductionCopy.safe'), and the name to give the backup. From this I have question:1) It is my understanding that the copy process to a share location should be seemless if the job is run with a integrated windows login that has permissions to that network share location? However, if it is run under a SQL Login wouldn't something like a device or linked server need to be created and configured?The second part of the script is supposed to restore the copied database to another a different instance on another server.Any insights and suggestions is greatly appreciated. Second day on new job as a Application System Analyst and this is what my manager tasks me with...-- Start of script... --:CONNECT eSitedb1GODECLARE @Returncode intEXEC @Returncode = [master].[dbo].[xp_ss_backup] @database = 'eSiteProduction' , @filename = '\\esitedb2\eSiteMonthEndLogBackups\eSiteProductionCopy.safe' , @overwrite = 1 -- Specifies that the backup does not affect the normal sequence of backups (SQL Server 2005 Only). Allowed values {0|1}. , @copyonly = 1if @Returncode != 0 begin Print 'Backup failed: ' + convert(nvarchar(5),@Returncode) return endGO:CONNECT eSitedb2 DECLARE @DatabaseName nvarchar(255)DECLARE @ArchivePathAndFilename varchar(255)DECLARE @Debug intDECLARE @BackupSet intDECLARE @DisconnectUsers intDECLARE @WindowsUsername nvarchar(255)DECLARE @EncryptedWindowsPassword nvarchar(255)DECLARE @NoStatus intDECLARE @withMoveData nvarchar(1024)DECLARE @withMoveLog nvarchar(1024)DECLARE @Replace intDECLARE @RecoveryMode nvarchar(10)DECLARE @UndoFile nvarchar(1024)DECLARE @StopAt nvarchar(255)DECLARE @StopAtMark nvarchar(255)DECLARE @StopBeforeMark nvarchar(255)DECLARE @After nvarchar(255)DECLARE @ContinueAfterError intDECLARE @RestoreReturncode int SET @DatabaseName = 'eSiteProductionCopy'SET @ArchivePathAndFilename = '\\esitedb2\eSiteMonthEndLogBackups\eSiteProductionCopy.safe'SET @Debug = 1SET @Replace = 1Set @DisconnectUsers = 1Set @withMoveData = 'esiteproduction_data F:\Program Files\Microsoft SQL Server\MSSQL\Data\' + @DatabaseName + '_data.mdf'Set @withMoveLog = 'esiteproduction_log E:\Program Files\Microsoft SQL Server\MSSQL\Logs\' + @DatabaseName + '_log.ldf'EXEC @RestoreReturncode = [master].[dbo].[xp_ss_restore] -- *************** Required parameters *************** -- Name of database to backup. @database = @DatabaseName, -- Archive path and filename (where backup set is located). @filename = @ArchivePathAndFilename, -- *************** Common Options *************** -- The backup set to restore (1 based). @backupset = @BackupSet, -- Disconnects all users from the target database before the restore operation begins. Allowed values {0|1}. @disconnectusers = @DisconnectUsers, -- The database logical filename to move to the physical target filename. -- '<logical_file_to_move> <physical_target_filename>', e.g., @withmove = 'data c:ewfile.ndf'. --@withmove = @withMoveData, --@withmove = @withMoveLog, -- If true (1), During restore, create the specified database and its related files even if another database already exists with the same name. Allowed values {0|1}. @replace = @Replace, -- *************** Security Options *************** -- The Widnows user that will be used to read the backup archive file on the remote server. @windowsusername = @WindowsUsername, -- The password for the Windows user specified in the @windowsusername. @encryptedwindowspassword = @EncryptedWindowsPassword, -- *************** Advanced Options *************** -- Instructs SQL Server to continue the operation despite encountering errors such as invalid checksums (SQL Server 2005 Only). Allowed values {0|1}. @continueaftererror = @ContinueAfterError, -- Toggles display of the command line arguments which can be used to invoke SQLsafeCMD from the command line. Allowed values {0|1}. @debug = @Debug, -- Prevents status messages from being cached or sent to the Repository. Allowed values {0|1}. @nostatus = @NoStatus, -- Specifies the mode in which to leave the database after restore {norecovery | standby}. @recoverymode = @RecoveryMode, -- The absolute path to the undo filename (standby recovery mode only). @undofile = @UndoFile, -- Specifies that the database be restored to the state it was in as of the specified date and time (log backup type only). @stopat = @StopAt, -- Specifies recovery to the specified mark, including the transaction that contains the mark (log backup type only). @stopatmark = @StopAtMark, -- Specifies recovery to the specified mark, but does not include the transaction that contains the mark. @stopbeforemark = @StopBeforeMark, -- Recovery stops at the first mark having the specified name exactly at or after the datetime (log backup only, stop at/before mark only). @after = @After IF @RestoreReturncode != 0 BEGIN RAISERROR ('Restore failure of database %s.', 16, 1, @DatabaseName) ENDWed, 11 Nov 2009 12:33:19 GMTseandeyoung-1145978http://www.sqlservercentral.com/Forums/Topic815705-357-1.aspxHi there,I'm a reasonably new member to SSC but it has fast become my must read website of a morning. I especially like the QOTD although it does show me that I need to improve. Anyways on to the point.I am a DB Developer who is also dabbling in DBA for one of our clients. The databases here have been largely ignored and I have slowly been sorting them out alongside my other work.We have a couple of databases but I will concerntrate on the ASPState database that is currently being used for one of our online applications.So here goes.We have an ASPState DB on SQL2005 which is currently 44671.69MB large. The data file is 110MB and the log file is 44563MB! It is on Full recovery model and backups are done nightly.The log file is 99.6% used and 0.4% unused according to the Disk usage report. I have performed a couple of transaction log backups in the hope that it would truncate the major part of the log and I would then be able to shrink the log file down to a more acceptable size say 5Gb. However the Log backups work but they hardly reduce the used part of the log file at all.I don't know the full history of the server but I have done a DBCC CheckDB and there are no errors or inconsistancies. I have done a DBCC CheckAlloc and there are no errors or inconsistancies (although I must admit I didn't fully understand the output of this command). I have also done a DBCC OpenTran and there are no open transactions blocking the truncation of the log file.Now I can think of ways round this problem but I don't understand it which is infuriating me. Does anyone have any ideas or suggestions as to what is going on. It would be most appreciated.Mon, 09 Nov 2009 03:41:14 GMTsbowellhttp://www.sqlservercentral.com/Forums/Topic817469-357-1.aspxI have a 20GB database with a transaction log that averages about 9MB. I have the recovery mode set to full and I back up the transaction log every 30min. At night I run a maintenance job that checks database integrity, rebuilds the index, and then does a full backup. The backed up file size is about 15GB.The problem I have is that the rebuild index task increase the 9MB transaction log to almost 13GB! The next tlog backup after the full backup averages about 10GB compared to the running average of 600KB of the other tlog backups. I can understand the rebuild index task causing a very large increase of the transaction log size but isn't the full backup task that runs next supposed to bring that size back down? The transaction log only decreases back to it's normal size after the first tlog backup, but the tlog backup is then 10GB. How can I keep that first tlog backup's size in check? I thought the full backup was supposed to truncate it?Thanks in advance for any help!Wed, 11 Nov 2009 14:34:18 GMTBrian Fischerhttp://www.sqlservercentral.com/Forums/Topic817594-357-1.aspxHi,I'm taking the Tail backup using the below script and the .trn file is creating with Mydb_tail.trn. But I want the date-time stamp for .trn file. Could you please tell me what change I need to do or Is there any good script to take tail backp?[quote]BACKUP LOG [Mydb] TO DISK = N'C:\Taillog_backup\Mydb_Tail.trn' WITH NO_TRUNCATE , NOFORMAT, NOINIT, NAME = N'Mydb-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, NORECOVERY , STATS = 10GOdeclare @backupSetId as intselect @backupSetId = position from msdb..backupset where database_name=N'Mydb' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'Mydb' )if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''Mydb'' not found.', 16, 1) endRESTORE VERIFYONLY FROM DISK = N'C:\Taillog_backup\Mydb_Tail.trn' WITH FILE = @backupSetId, NOUNLOAD, NOREWINDGO[/quote]thanksWed, 11 Nov 2009 21:45:12 GMTgmamata7http://www.sqlservercentral.com/Forums/Topic814506-357-1.aspxHi there,From what I've seen this question gets asked quite a bit, but I can't get a solution that works for me.I am running a database using SQL Server Management Studio Express. There is no backup wizard. My HMI program can run short SQL scripts on a schedule. I want to take a daily backup of the database, say at midnite. And then I want to take transaction log backups every few hours.I want each transaction log backup to have date AND timestamp included in the filename, but this does not seem easy. I can use this script:DECLARE @fullpath nvarchar(255) set @fullpath = 'c:\sql\logbak' + str(year(getdate())) + str(month(getdate())) + str(day(getdate())) + '.trn' BACKUP log cimplicity TO DISK = @fullpath WITH FORMAT, STATSAnd it works, but it only has a datestamp. I need time included. It seems that many time data types and procedures aren't included or something on this instance of SQL.Does anyone know how to get a timestamp included with filename? I just don't want to keep overwriting a file or keep growing a single file.Thanks,Scott Cheneyscheney@coritech.comThu, 05 Nov 2009 12:51:50 GMTscheney-1152259