Tag Info

New answers tagged

0

I tilted on your "data loss could have legal ramifications here" comment. Then, you definitely want to get a powerful 3rd party tool (like DPM) that can handle backups (and recover from catastrophics events in a flash and minimal fussing around) alot faster and alot better than any script you can pull off the Internet. Having backups is one thing. Knowing ...


1

you can create a table variable(or temp table) and insert all the .bak file metadata in that table and then fetch the row with max value of modified time. DECLARE @FileMeta table (columns nvarchar(200)); INSERT INTO @FileMeta EXEC sys.xp_cmdshell 'dir D:\FolderName\*.bak'; SELECT * FROM @FileMeta WHERE columns not like '%volume%' AND columns is not ...


1

You could consider raising a connect item (Microsoft bug report) as you have a reliable "repro", but then there are a number of workarounds for the problem - eg there is no real need for the APPLY, eg ;WITH TableA AS ( SELECT 101 as A_ID ), TableB AS (SELECT 1 as B_ID, 101 as B_A_ID , 'xxx' as B_Courses UNION ALL SELECT 2 , 101 , 'YYY' ...


1

There are a number of them, a lot of articles on the topic if you have google at it. Some of them: Default Schema for Windows Groups User Defined Server Roles Enhancments to Auditing User Contained Databases, authentication without logins. TDE - Transparent Data Encryption Hashing Functions - improved.


1

Since you have downtime, best is to use this powershell script- with backup restore option This script will take care of moving databases, logins, jobs,etc on the new server. make sure to use backup restore switch as opposed to detach/attach.


0

I'm currently working on this same issue. Here is what I'm thinking about doing to keep the entries in the database low. id | item_id | time_period | time_stamp | count id = unique, auto increment id item_id = the id of the item you are counting for time_period = the time period that you want to hold that count for options would be something like: ...


1

There is no built-in way to backup all the databases into a single file, and even if there was, that file would probably become impractically big. I would go with the ideas you got from your original question, and implement those in an automated script that loops through all your databases; One script to back every database up, one script to restore those ...


0

You mentioned you have 200 source tables and 225 databases. I am assuming the 200 source tables is a count of all tables from all the 225 databases (cause if you had 200 tables in each database that will put your total table count to 45000). You also mentioned that the schema of the database is the same for the 225 databases. You could build the SSIS ...


1

I thought that ALTER AUTHORIZATION might offer a way to transfer ownership, but no, according to the docs (emphasis mine): Ownership of the following entities cannot be transferred: <many things> and event notifications. So, script them, drop them, and re-create them under a more generic account that is not tied to an employee. This is similar ...


0

Right click and generate scripts, then change the name of the database to target (USE db). Explained here


2

DECLARE @d DATE = DATEADD(DAY, -5, CURRENT_TIMESTAMP); SELECT keyid = 12345, [date] = d, Hits = COUNT(*), HomePageHits = COUNT(CASE WHEN Action = 'Home' THEN 1 END) FROM dbo.mytable WHERE [date] >= @d AND [date] < DATEADD(DAY, 1, @d) AND keyid = 12345; (I'm assuming where column='Home' was a typo.) Some tips: Bad habits to kick : ...


3

Is there a particular reason you are specifying TRUNCATEONLY? As per the documentation on DBCC SHRINKFILE: TRUNCATEONLY Releases all free space at the end of the file to the operating system but does not perform any page movement inside the file. The data file is shrunk only to the last allocated extent. target_size is ignored if ...


0

This is covered in Frequently Asked Questions for Replication Administrators: Are tables locked during snapshot generation? The length of time that the locks are taken depends on the type of replication used: For merge publications, the Snapshot Agent does not take any locks. For transactional publications, by default the Snapshot Agent takes ...


0

Thanks so much guys for all you effort. This actually does the job by changing int to nvarchar(10) from the script that was posted by Mikael Eriksson: select cast(msg as xml).value('(/DLR_NOTIFICATION/siteid/text())[1]','nvarchar(10)')SiteID


1

Firstly, you are correct in that the (slightly counter-intuitive) way to prevent access to the [sys] and [INFORMATION_SCHEMA] schemas is to first ensure that the login (well, server-level principal) exists as a user (erm, database-level principal) in the master database. Assume you have a SQL login for simplicity: CREATE LOGIN [testy] WITH ...


0

I solved the problem by adding couple of paremeter to restore command. RESTORE DATABASE [test] FROM ... MAXTRANSFERSIZE=4194304, BUFFERCOUNT=2048 I am surprised, it is the first case in my practice when additional parameters so influenced restoration speed.



Top 50 recent answers are included